Sentry plugin with external table
In this tutorial, we will learn how to create a custom table that contains foreign keys to existing tables. We will also learn how to create a Sentry plugin that enforces additional restrictions on the contact table.
The objective is to demonstrate how to control what the user is allowed to see. In this case, the user is only allowed to see contacts with a business type equal to that of the user’s company business type.
Tables
The following diagram shows the relationship between the 3 tables namely contact, associate, and the custom table superofficetrainingtable.
Let's begin!
Create a custom table using Dictionary SDK
First, we focus on how to generate a table in the database using the Dictionary SDK.
Pre-requisite:
To use this SDK, SODictionarySDK.dll must be registered using regsvr32. The file can be found in the SuperOffice installation directory.
Note
It is important to make sure the correct values are set in the Admin client replication screen because these values are used by the SODictionary whenever the manipulations are done in the database.
using System;
using System.Collections.Generic;
using System.Text;
using SuperOffice.COM.DictionarySDK;
namespace CustomTableWithSentry
{
class Program
{
static void Main(string[] args)
{
try
{
// Create a new dictionary
Dictionary dictionary = new Dictionary();
// set the database
dictionary.SetDatabase("ODBC:SuperOffice", "TJE0", "tje0", "CRM5");
// Create the table
Table table;
table = dictionary.CreateTable("SuperOfficeTrainingTable");
table.Description = "Detailed information about the new table";
table.NetServerName = "SuperOfficeTrainingTable";
table.ReplicateDown = false;
table.ReplicateToPrototype = false;
table.ReplicateUp = false;
// Create foreign keys
Field fkField = table.CreateForeignKey("Associate", ERelationCardinality.enOneToMany);
fkField.Description = "This is the key of the Associate table.";
fkField.AddImportName(1034, "ImportNameOfASSForeignKey");
fkField.NetServerName = "Associate";
// Create foreign keys
Field fkField2 = table.CreateForeignKey("Business", ERelationCardinality.enOneToMany);
fkField2.Description = "This is the key of the Business table.";
fkField2.AddImportName(1033, "ImportNameOfBusinessForeignKey");
fkField2.NetServerName = "Business";
dictionary.CommitChanges();
Console.WriteLine("Done.");
}
catch (Exception exp) { Console.WriteLine(exp.Message);
}
}
}
}
In the above code segment, we have created a Dictionary object, with the use of which a table is created in the database. Further, two foreign keys namely the associate ID and the business ID are created and the table names and the relationship multiplicity are specified.
Generate user-defined table classes for use in NetServer
To access the new user-defined table (UDT) using NetServer, we must generate NetServer OSQL and Row types that define the UDT. These types are easily generated using the online code generator hosted on DevNet.
When the code generation is complete, a Visual Studio solution with one project is created in the location specified by the user. This project contains 2 folders:
- A folder with class files for supporting the data access layer
- A folder for supporting Rows
Create the Sentry plugin
To begin creating a new Sentry plugin, you must create a class that inherits from and implements the SuperOffice.CRM.Security.ISentryPlugin interface.
In the plugin, it is required to access the generated NetServer classes for the UDT. To accomplish this, compile the code generation solution and add a reference to the DLL in the Plugin project.
using System;
using System.Collections.Generic;
using System.Text;
using SuperOffice.CRM.Security;
using SuperOffice.Data.SQL;
using SuperOffice.CRM.Data;
using SuperOffice.CRM.Rows;
using SuperOffice.CRM.Entities;
using MyNetServerCode.Data;
namespace SentryForCustomTableDll
{
[SentryPlugin("contact")] public class CustomSentryPlugin : ISentryPlugin
{
/// <summary>
/// Storing reference to the sentry the plugin works on behalf of.
/// </summary>
SuperOffice.CRM.Security.Sentry _sentry = null;
/// <summary>
/// Default constructor, we do nothing here.
/// </summary>
public CustomSentryPlugin() { }
#region ISentryPlugin Members
/// <summary>
/// Here we initialize sentry type we want. The parameter will give us the sentry type.
/// </summary>
/// <param name="sentry"></param>
public void Init(Sentry sentry)
{
_sentry = sentry;
}
/// <summary>
/// Modify field rights.
/// </summary>
public void ModifyFieldRights(FieldRights rights)
{
// No changes in field rights
}
/// <summary>
/// Modify table rights.
/// </summary>
public void ModifyTableRights(TableRights rights)
{
// No changes in field rights
}
#endregion
#region helpers
/// <summary>
/// Casting <see cref="sentry.SentryQueryInfo"/> to <see cref="ContactSentryQueryInfo"/>.
/// </summary>
private ContactSentryQueryInfo QueryInfo
{
get { return (ContactSentryQueryInfo)_sentry.SentryQueryInfo; }
}
/// <summary>
/// Obtain value of a field without trigging sentry calculations.
/// </summary>
/// <param name="fieldInfo">Field to get value for</param>
/// <returns>Value of the field.</returns>
object GetFieldValue(FieldInfo fieldInfo)
{
using (_sentry.Lookups.BeginIgnoreSentryCheck())
{
return _sentry.Lookups.GetFieldValue(fieldInfo);
}
}
#endregion
}
///<summary>
///Demonstration of sentry plugin query table updater for table Contact.
///</summary>
///<remarks>Enforce the restriction contacts with the same business-id as the current user are visible to any user.</remarks>
[SentryPluginQueryTableUpdater("contact")]
public class SentryPluginQueryTableUpdaterContact : ISentryPluginQueryTableUpdater
{
#region ISentryPluginQueryTableUpdater Members
public void ModifySelect(Select sql, TableInfo tableInfo)
{
// Get the ContactTableInfo and SuperOfficeTrainingTableTableInfo
ContactTableInfo newConTable = (ContactTableInfo)tableInfo;
SuperOfficeTrainingTableTableInfo newCustomTable = MyNetServerCode.Data.CustomTablesInfo.GetSuperOfficeTrainingTableTableInfo();
// Set the restriction
sql.RestrictionAnd(newCustomTable.AssociateId.Equal(SuperOffice.Data.S.Parameter(SuperOffice.SoContext.CurrentPrincipal.AssociateId)));
// Join the tables Contact and the custom table
sql.JoinRestriction.InnerJoin(newConTable.BusinessIdx.Equal(newCustomTable.BusinessId));
}
#endregion
}
}
As we can see in the above code segment, we have created another class called SentryPluginQueryTableUpdaterContact which implements the SuperOffice.CRM.Security.ISentryPluginQueryTableUpdater interface. This interface has a single method called ModifySelect where we have implemented the sentry restriction to retrieve only the contact information where the business ID of which is the same as the currently logged in user’s business ID.
TableInfo objects are required for the tables of interest (the Contact table and SuperOfficeTrainingTable).
ContactTableInfo is retrieved by casting the TableInfo object passed to the ModifySelect method. Then the restriction is enforced to narrow the data selection to the current user’s business ID.
Finally, we have specified the join condition so that the custom table is joined in whenever the Contact table is queried upon.
We can see that we have marked this class as a SentryPluginQueryTableUpdater for the Contact table using attributes signaling NetServer that we have developed SentryPluginQueryTableUpdater for the Contact table. So whenever the Contact table is queried upon, this method will get fired.
Using the Sentry plugin
To use the plugin, modifications are required in the config file signaling NetServer that we have our own plugin and mentioning where the DLL is located.
Below is the section that we have to modify in the app.config file.
<Factory>
<DynamicLoad>
<add key="SentryPlugin" value="C:\\AllBackups\\SuperOffice_Working_Files\\SentryForCustomTableDll\\SentryForCustomTableDll\\bin\\Debug\\SentryForCustomTableDll.dll"
/>
</DynamicLoad>
</Factory>
The following code snippet is an example that uses the plugin. Here we attempt to retrieve the contact information logging on with different users and outputting the list of contacts that each user can see.
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using SuperOffice.CRM.Data;
using SuperOffice.Data;
using SuperOffice.Data.SQL;
using SuperOffice;
using System.Collections.Specialized;
using System.Collections;
private void btnShowContacts_Click(object sender, EventArgs e)
{
try
{
using (SoSession session = SoSession.Authenticate(this.txtUserName.Text.Trim(), this.txtPWD.Text.Trim()))
{
// Retriveing tableInfo for the ContactTable
ContactTableInfo newConTable = TablesInfo.GetContactTableInfo();
// Retrieving data in to an instance of the Select Class
Select newSelect = S.NewSelect();
// Choosing the columns that should be retrieved
newSelect.ReturnFields.Add(newConTable.ContactId, newConTable.Department, newConTable.Name);
// Ordering the retrieved Data
newSelect.OrderBy.SortOrder.Add(newConTable.ContactId, SuperOffice.Util.OrderBySortType.ASC);
// Establishing a database connection
SoConnection myConn = ConnectionFactory.GetConnection();
// Creating SoCommand instance and assigning the Select statement
SoCommand myComm = myConn.CreateCommand();
myComm.SqlCommand = newSelect;
myConn.Open();
// Loading the data into the DataReader
SoDataReader myReader = myComm.ExecuteReader();
ArrayList arrConData = new ArrayList();
// Retrieving the Data from the Reader
while (myReader.Read())
{
int conID = myReader.GetInt32(0);
string conDept = myReader.GetString(1);
string conName = myReader.GetString(2);
// Creating a user defined contact object to be stored
// in the ArrayList
ContactData contact = new ContactData(conID, conName, conDept);
arrConData.Add(contact);
}
// Setting the results to the data grid
this.grdContactData.DataSource = arrConData;
// Closing the reader
myReader.Close();
}
}
catch (Exception exception)
{
// Clear textboxes and the data grid
this.clearAll();
MessageBox.Show("Invalid user.");
}
}
We have first retrieved the TableInfo object for the Contact table.
Next, a Select object is created to retrieve data. The columns to be included in the selection and the order in which the results to be sorted are specified next. The select command is executed against the Contact table to retrieve the contact information. This is the point where our plugin comes into the picture: when we attempt to run a query against the contact table, the NetServer sentry mechanism calls our sentry plugin and the plugin logic gets executed. With the restriction specified in the ModifySelect method of the plugin, any user can retrieve only the contact information where the BusinessId of which is the same as the currently logged-in user’s BusinessId.
The next step is to convert the retrieved contact data into a format that can be displayed in a data grid. The approach taken in this example is to iterate over the retrieved DataReader and encapsulate those data into a custom object type called ContactData. These objects are stored in an ArrayList, which is set as the data source for the contact information data grid.
The following screenshots show the results of the same query run on the contact table for 2 users namely SAL0 and P.
The sal0 user is restricted to a smaller subset of the contact table, due to the entries we put in the external table we have created.