AppNote

This application note describes an attempt at putting together a prototype architecture for providing historical reports for roles and entitlements for users.

The required Novell products are Identity Manager 3.51, Identity Manager Roles Based Provisioning Module 3.6, and the Identity Manager driver for Database.

The required 3rd party products are Microsoft SQL Server(any edition) and of course Windows. I am using SQL Server 2005 and Windows 2003 Standard Edition just because my demo system was based on that for Sentinel. I am also leveraging Crystal Reports XI r2 Developer to create custom reports, and Crystal Reports Server(included with Sentinel, again I am leveraging my existing demo system), but this is optional and plain SQL Queries against the database, other reporting tools, or the Novell Audit plug-in for iManager could be used to generate reports.

A bit of background

When it comes to reporting, Novell Sentinel(or Novell Audit) provides reporting capabilities to typically answer the question “Who has done What, from Where, When, and How”.

And User Application reports for Roles(with version 3.6 as of June 2008) typically answer the questions “What are the Roles available?”, “To Whom these Roles are currently assigned?”, “What are the SoD Constraints defined?” and “What are the current SoD Violations?”.

There are a few gaps, and many of them are going to be addressed with the upcoming releases of Identity Manager and Sentinel later in 2008. But I wanted to explore some specific questions or concerns about reporting, more specifically in the context of Roles and RBAC.

Below are the additional questions I am trying to answer with my prototype solution:

“What were Michel's accesses(Roles, Entitlements through Roles, Direct Entitlements) back in January?”
“What are Michel's current accesses(Roles, Entitlements through Roles, Direct Entitlements)?”
“What is the proportion of directly assigned privileges(Direct Entitlements) vs Roles Based assigned privileges(Entitlements through Roles)?”
“What were the effective SoD Violations last February?”
“Can I create fully customizable historical reports against the Roles subsystem?”
“Can I generate historical statistics reports against the Roles subsystem?”
etc...
And the list goes on...

Reporting tools like Crystal Reports, or open source alternatives like Jasper, do not typically provide datasource support for LDAP directories like eDirectory, where the Roles subsystem's objects(Roles, Entitlements, SoD Constraints, etc) are stored, and LDAP-JDBC or LDAP-ODBC bridges are usually inefficient(they have to load the full tables in RAM before performing a query). Web Services is an option, and since Novell eDirectory and Identity Manager are exposed as Web Services endpoints now(refer to Novell online documentation http://www.novell.com/documentation for more on this), that seems like an interesting approach. But there is still a gap around historical reports, since LDAP does not contain historical information, only the NOW. And Sentinel/Audit reports can provide historical information about actions/events, but establishing PAST NOWs is a bit tedious and sometimes impossible.

So the option I am investigating is to replicate the NOWs into database tables on a regular basis(NOWs window). For example, by replicating the NOWs once a week into databases tables, one could track down the evolution of the NOWs to a granularity of 1 week. By adjusting the NOWs window to one day or one month would allow any level of granularity for generating reports, but there is probably a compromise to make between granularity and the size of those database tables. In practice, one could also implement variable granularity, e.g. daily for the past month, weekly for the past year, monthly for the past 3 years, etc. But once the data is in the tables, then any types of reports should be available including statistics or trending reports.

Well, one may ask the questions “Why use LDAP for my Identity Store if I need to replicate to a database for reports? Why not using a database in the first place to save the extra step of replicating?”. But when we analyze what is at stake, eDirectory is also a database, a specialized one, tuned for the task of being an Identity Store, so we what we are really talking about is replicating a database with a database, or tables with other tables, something that Identity Stores running on top of databases have to put up with too in order to provide historical data(the current NOW and PAST NOWs reside in different tables or different records within the same tables). There is no way to include historical data using a single table and a single record: by design, a database must leverage foreign keys to other tables, or multiple records/rows for the same object, in order to maintain historical data. So we are comparing apples with apples here, and there is no high-level logical difference in design when it comes to historical reporting.

The framework

The first step is to create database objects that can receive our data. We will use Microsoft SQL Server in our example, but this could be adapted to Oracle, MySQL, or another database by comparing with the examples on the Identity Manager CD.

Figure 1: Using Microsoft SQL Server Management Studio(New Query) to run the script sections.

I always prefer to run the script in chunks, but the full script is also provided in this appnote.

First, we need to create the database:

--------------------------------------------------------------------------------

-- CREATE SAMPLE DATABASE                                                     --

--------------------------------------------------------------------------------



USE master

GO



CREATE DATABASE idm

GO



--------------------------------------------------------------------------------

Then, we need to create the accounts for Identity Manager to access the database data, and to host the database objects:

--------------------------------------------------------------------------------

-- CREATE DRIVER'S USER/LOGIN ACCOUNTS                                        --

--                                                                            --

-- Create the login and database accounts used by the driver.  In practice,   --

-- these should be dedicated accounts used only by the driver.                --

--------------------------------------------------------------------------------



EXECUTE sp_addlogin @loginame = 'idm',

                    @passwd   = 'novell',

                    @defdb    = 'idm'

GO









--------------------------------------------------------------------------------

-- CREATE SAMPLE USER/LOGIN ACCOUNTS                                          --

--                                                                            --

-- Create the login and database accounts that own objects referenced by the  --

-- driver.                                                                    --

--------------------------------------------------------------------------------



EXECUTE sp_addlogin @loginame = 'direct',

                    @passwd   = 'novell',

                    @defdb    = 'idm'

EXECUTE sp_addlogin @loginame = 'indirect',

                    @passwd   = 'novell',

                    @defdb    = 'idm'

GO

Then we need to grant some access:

USE idm

GO

                   

EXECUTE sp_grantdbaccess @loginame   = 'idm',

                         @name_in_db = 'idm'

EXECUTE sp_grantdbaccess @loginame   = 'direct',

                         @name_in_db = 'direct'

EXECUTE sp_grantdbaccess @loginame   = 'indirect',

                         @name_in_db = 'indirect'

GO

Now, we will create the schemas and tables for direct and indirect objects:

CREATE SCHEMA AUTHORIZATION indirect

CREATE TABLE indirect.usr

(

  idu         BIGINT         NOT NULL   IDENTITY(1,1),

  fname       NVARCHAR(64),      

  lname       NCHAR(64)      NOT NULL,          

  username    VARCHAR(128),

  fullname    VARCHAR(128),

  workforceID    VARCHAR(128),

  manager     VARCHAR(128),

  lastLogin		DATETIME,

  time		DATETIME	default getdate(),
  loginDisabled		BIT,

  reason	VARCHAR(128),

  CONSTRAINT pk_usr_idu     PRIMARY KEY (idu)

)



 CREATE TABLE indirect.role

(

  idg         BIGINT   NOT NULL  IDENTITY(1,1),

  rolename 		VARCHAR(128),

  owner 		VARCHAR(128),

  CONSTRAINT pk_role_idg PRIMARY KEY (idg)

) 



 CREATE TABLE indirect.entitlement

(

  ide         BIGINT   NOT NULL  IDENTITY(1,1),

  entname 		VARCHAR(128),

  descr 		VARCHAR(128),

  displayname 		VARCHAR(128),

  CONSTRAINT pk_ent_ide PRIMARY KEY (ide)

) 



 CREATE TABLE indirect.sod

(

  ids         BIGINT   NOT NULL  IDENTITY(1,1),

  sodname 		VARCHAR(128),

  descr 		VARCHAR(128),

  role1 		BIGINT NOT NULL,

  role2 		BIGINT NOT NULL,

  CONSTRAINT pk_ent_ids PRIMARY KEY (ids),

  CONSTRAINT fk_role1 FOREIGN KEY (role1)

    REFERENCES indirect.role(idg)      

    ON DELETE NO ACTION,      

  CONSTRAINT fk_role2 FOREIGN KEY (role2)

    REFERENCES indirect.role(idg)      

    ON DELETE NO ACTION      

) 



CREATE TABLE indirect.usr_mbr_of

(

  idu  BIGINT  NOT NULL,

  idg  BIGINT  NOT NULL,

  CONSTRAINT fk_mbr_of_idu FOREIGN KEY (idu)

    REFERENCES indirect.usr(idu)      

    ON DELETE CASCADE,      

  CONSTRAINT fk_mbr_of_idg FOREIGN KEY (idg)

    REFERENCES indirect.role(idg)      

    ON DELETE CASCADE      

)


CREATE TABLE indirect.usr_ent

(

  idu  BIGINT  NOT NULL,

  ide  BIGINT  NOT NULL,

  CONSTRAINT fk_ent_idu FOREIGN KEY (idu)

    REFERENCES indirect.usr(idu)      

    ON DELETE CASCADE,      

  CONSTRAINT fk_usr_ent_ide FOREIGN KEY (ide)

    REFERENCES indirect.entitlement(ide)      

    ON DELETE CASCADE      

)


CREATE TABLE indirect.role_ent

(

  idg  BIGINT  NOT NULL,

  ide  BIGINT  NOT NULL,

  CONSTRAINT fk_ent_idg FOREIGN KEY (idg)

    REFERENCES indirect.role(idg)      

    ON DELETE CASCADE,      

  CONSTRAINT fk_role_ent_ide FOREIGN KEY (ide)

    REFERENCES indirect.entitlement(ide)      

    ON DELETE CASCADE      

)





CREATE TABLE indirect.event_time

(

  stamp  DATETIME  NOT NULL

)

GO





-- NOTE:

-- There are restrictions on when GETDATE() can be called.

INSERT INTO indirect.event_time VALUES(GETDATE()) -- prime table



CREATE NONCLUSTERED INDEX idx_mbr_of_idu

  ON indirect.usr_mbr_of(idu)

CREATE NONCLUSTERED INDEX idx_mbr_of_idg

  ON indirect.usr_mbr_of(idg)

CREATE NONCLUSTERED INDEX idx_usr_ent_idu

  ON indirect.usr_ent(idu)

CREATE NONCLUSTERED INDEX idx_usr_ent_ide

  ON indirect.usr_ent(ide)

CREATE NONCLUSTERED INDEX idx_role_ent_idg

  ON indirect.role_ent(idg)

CREATE NONCLUSTERED INDEX idx_role_ent_ide

  ON indirect.role_ent(ide)

CREATE NONCLUSTERED INDEX idx_usr_workforceID

  ON indirect.usr(workforceID)

CREATE NONCLUSTERED INDEX idx_usr_time

  ON indirect.usr(time)

CREATE NONCLUSTERED INDEX idx_sod_ids

  ON indirect.sod(ids)

CREATE NONCLUSTERED INDEX idx_sod_role1

  ON indirect.sod(role1)

CREATE NONCLUSTERED INDEX idx_sod_role2

  ON indirect.sod(role2)

GO

We want to synchronize the above tables with corresponding eDirectory objects:

indirect.usr – User object
indirect.role – nrfRole object
indirect.entitlement – DirXML-Entitlement
indirect.sod – nrfSod
indirect.usr_mbr_of – nrfMemberOf attribute for User(multivalue)
indirect.usr_ent – DirXML-EntitlementRef attribute for User(multivalue)
indirect.role_ent – nrfEntitlementRef attribute for nrfRole(multivalue)

The prototype we are looking at right now could be extended to include the Group object and the relationship with entitlements and roles, but we will limit the scope to the above objects for now.

The complete script also include some direct objects/views but they are optional for our discussion.

The last step, we need to provide some privileges to the IdM user for updating the tables. Alternatively, we could leverage views for updates and select statements.

GRANT SELECT, INSERT, UPDATE, DELETE ON indirect.usr        TO idm

GRANT SELECT, INSERT, UPDATE, DELETE ON indirect.usr_mbr_of TO idm

GRANT SELECT, INSERT, UPDATE, DELETE ON indirect.role        TO idm

GRANT SELECT, INSERT, UPDATE, DELETE ON indirect.entitlement        TO idm

GRANT SELECT, INSERT, UPDATE, DELETE ON indirect.sod        TO idm

GRANT SELECT, INSERT, UPDATE, DELETE ON indirect.usr_ent TO idm

GRANT SELECT, INSERT, UPDATE, DELETE ON indirect.role_ent TO idm

Now we need to create an Identity Manager driver to synchronize the data down to the tables. We are uni-directional for now, with reporting in mind, but one could change the logic of the driver and open up the possibility to use the database tables as a way to manage the security model(Role Subsystem). I started with the template for MS-SQL provided with the Identity Manager CD and modified the driver. An export script is available from this appnote, so I will just cover a few highlights.

Figure 2: Designer showing the properties for the driver. This is the Authentication page. Don't forget to download(from the Microsoft web site) and copy the MS-SQL JDBC driver to your lib directory(see documentation for more details).

Figure 3: Under Driver Parameters, all the tables that we have created must be listed, otherwise they won't be available for mapping.

Figure 4: This is the Schema Mapping Rule for the synchronized objects. Note that I have not extended the schema and used some existing unused attributes for sending data to the database, e.g. co, company, rbsAssignedRoles. I am using these attributes only for Destination on the Subscriber Channel(from eDirectory to MS-SQL) so these attributes are never used in eDirectory, just an artifice to simplify Mapping and Transformation.

Figure 5: This is the Filter. Everything is uni-directional, from eDirectory to MS-SQL. Note that I am using Notify for some attributes like nrfRoles(nrfSOD) because I am copying the multivalue content(2 values) to temporary single value attributes(rbsAssignedRoles) that I then sync with columns in the Sod table.

Figure 6: We need to perform some reformating for complex attributes before we send simple values to the database.

Figure 7: This is the Rule that takes care of the nrfRoles attribute, and copies its content to 2 temporary attributes before synchronization.

Now, the piece that makes it possible to create historical reports. I am using a trick here, which consist of dropping the Associations for the User Objects on a schedule interval. By dropping the Association, each User that is going to be modified(new Entitlement or Role) will get created again in the database with a different unique ID in MS-SQL, but still recognizable as the same user when running reports.

Figure 8: Rule that detects the execution of a specific Job(RemoveAssociation) and removes all the Associations for User Objects in the tree. DriverDN is a global variable adjustable through the driver's properties.

Figure 9: Job that is responsible for triggering the Remove Association Rule. The Schedule can be adjusted to the granularity we wish to obtain in reports. The Job can be executed manually for testing.

Figure 10: Indirect.usr table contains all instances of the User Objects for different windows. User will appear multiple times(multiple instances) only when Entitlements or Roles are modified for a given User in multiple windows. A window correspond to the interval at which the Job is Scheduled. Each instance of a User has a different idu.

Figure 11: The Entitlement table contains all Entitlements defined at the driver level in eDirectory. Each Entitlement has a unique identity value for ide.

Figure 12: The Role table includes all Roles(nrfRole) and each Role has a unique identity value for idg.

Figure 13: The table usr_mbr_of links idu with idg for Roles Memberships(nrfMemberOf) for User, through foreign keys(idu, idg).

Figure 14: The usr_ent table is similar to the usr_mbr_of table, but links Users with Entitlements(DirXML-EntitlementRef), idu and ide.

Figure 15: The role_ent table list the Entitlements associated to Roles(Roles with Entitlements). We want to be able to track Entitlements that are either assigned directly to the User, or through Roles.

Now that we have our data model in place, and data in our tables, we are ready to create some custom reports. And here, the sky is the limit. You want statistics, complex reports, name 'em. You have them. If you are a hardcore DBA, you can probably just leverage the SQL language to get the reports you want, but chances are you probably prefer to use a nice Graphical Interface in front that can generate those SQL queries for you through a wizard assistant.

In the examples that follow, I am using Crystal Report Developer and Server.

Figure 16: Crystal Report Developer, Select New/Standard Report.

Figure 17: Select Create New Connection, then OLE DB (ADO).

Figure 18: Select the Provider for SQL Server.

Figure 19: Enter the information for your database.

Figure 20: Select the table(s) you which to use for your report. Don't forget to select tables with primary keys that are referenced with foreign keys by other tables, otherwise the relationship will be impossible to establish between the tables.

Figure 21: Now you can see that the relationships have been figured out automatically through foreign keys.

Figure 22: Now you can select the human readable columns you want in your table. You do not need to select the keys.

Figure 23: Now you can select to group the information in your report by Users, then by Time. This will allow you to view the evolution of User's Roles and Entitlements with respect to time.

Figure 24: You can select different templates for the presentation.

Figure 25: Now you can manage the elements in your report for presentation.

Figure 26: You can also preview your report inside Developer accessing real data. Note how you can take advantage of grouping(underlying SQL Group By) to present the information in the way you want, for example by grouping all instances of a given user.

Figure 27: On the Crystal Reports Server, you can launch the Publishing Wizard to publish you report so it can be accessed through the web client.

Figure 28: You must authenticate to the Crystal Reports Server.

Figure 29: You can Add one or more Files for Reports you created with Developer.

Figure 30: You can specify where you want your reports to be published.

Figure 31: You can decide to modify the properties for the report to replace the file name with something more user friendly for display.

Figure 32: Adding some Description and Modifying the Title for the report.

Figure 33: You can specify that you want users to be prompted for credentials when they run the reports. This can be useful if you want to control access to the information displayed in reports through database users or roles.

Figure 34: Accessing the web client Infoview for running the reports.

Figure 35: List of published reports created for the prototype. We also included additional information for reporting like Last Login Time for User, or Active Users(Login Disabled = True), to illustrate that the data model can be extended.

Figure 36: Because of our earlier choice during the Publishing process, the user is prompted for credentials.

Figure 37: We are now looking at an example report grouping users' instances together under Users, then grouping by time, for Entitlements granted through Roles.

We completed our quick overview of the prototype framework for historical reporting. While it is usable as is, maybe with further embellishment of the presentation for reports, it is intended has a starting point more than a final solution. As always, I am welcoming feedback, comments, ideas and suggestions, so feel free to email me.

Below are the files for the appnotes:

MS-SQL Script:
Driver export:
Example reports:

Attachment	Size
MyReports.zip	170.44 KB
MSSQL-RoleHistory.zip	25.2 KB