System and method for automatically synchronizing security-relevant information between a relational database and a multidimensional database
View Patent ↗A security management system and method for managing access security in an IT-environment comprises a relational database of the ROLAP type and a data warehouse of the MOLAP type. The system shares a subset of common data, wherein database access security for accessing particular database tables is managed by user-related access limitations in a table form. The present system transforms the access-limiting conditions existing at the ROLAP system automatically to a respective filter criterion for the MOLAP system. The access-limiting conditions exiting at the ROLAP system are defined in a table form. The present system and method automatically activates filter criterion whenever an access to the MOLAP system is requested by a user.
1. A method for managing access security to a ROLAP system and a MOLAP system that share a subset of common data, the method comprising:
defining on a computer comprising a processor and memory a security table for a ROLAP system, the security table comprising a userID column, a data group column defining a security profile, and a profile column;
defining on the computer an OLAP cube description table comprising, for one or more cubes in the MOLAP, a minimum user security definition for user access to the one or more cubes in the MOLAP;
defining on the computer an OLAP cube profile description table comprising, for the one or more cubes in the MOLAP, one or more access profiles;
joining the security table, the OLAP cube description table, and the OLAP cube profile description table to autonomously generate a MOLAP security definition consistent with a ROLAP security definition.
2. The method of claim 1 , wherein autonomously generating a MOLAP security definition further comprises autonomously executing a user verification sequence, a filter generation sequence, and a user-to-group assignment sequence.
3. The method of claim 2 , wherein the user verification sequence further comprises:
for each cube in the OLAP cube description table, generating a qualified users data set comprising one or more users for whom the security profile defined in the security table meets the minimum user security definition for the cube defined in the OLAP cube description table; and
for each cube in the OLAP cube description table, dynamically maintaining the qualified users data set.
4. The method of claim 2 , wherein the filter generation sequence further comprises:
generating input for filter creation by joining the security table with the OLAP cube description table and adding the one or more access profiles of the OLAP cube profile description table;
generate from the input a filter for each unique combination of security setting and access profile; and
generating for each filter an associated user group.
5. The method of claim 2 , wherein the user-to-group assignment sequence further comprises assigning one or more users to a corresponding user group.
6. The method of claim 2 , further comprising executing more than one user verification sequences in parallel for more than one cube.
7. The method of claim 2 , further comprising executing the user-to-group assignment sequence subsequent to completion of the user verification sequence and the filter generation sequence.
8. A computer program product having a plurality of executable instruction codes for executing on a processor and a memory a method for managing access security to a ROLAP system and a MOLAP system that share a subset of common data, the method comprising:
defining a security table for a ROLAP system, the security table comprising a userID column, a data group column defining a security profile, and a profile column;
defining an OLAP cube description table comprising, for one or more cubes in the MOLAP, a minimum user security definition for user access to the one or more cubes in the MOLAP;
defining an OLAP cube profile description table comprising, for the one or more cubes in the MOLAP, one or more access profiles;
joining the security table, the OLAP cube description table, and the OLAP cube profile description table to autonomously generate a MOLAP security definition consistent with a ROLAP security definition;
wherein the security table, OLAP cube description table, and OLAP cube profile description table are stored in computer readable memory.
9. The computer program product of claim 8 , wherein autonomously generating a MOLAP security definition further comprises autonomously executing a user verification sequence, a filter generation sequence, and a user-to-group assignment sequence.
10. The computer program product of claim 9 , wherein the user verification sequence further comprises:
for each cube in the OLAP cube description table, generating a qualified users data set comprising one or more users for whom the security profile defined in the security table meets the minimum user security definition for the cube defined in the OLAP cube description table; and
for each cube in the OLAP cube description table, dynamically maintaining the qualified users data set.
11. The computer program product of claim 9 , wherein the filter generation sequence further comprises:
generating input for filter creation by joining the security table with the OLAP cube description table and adding the one or more access profiles of the OLAP cube profile description table;
generate from the input a filter for each unique combination of security setting and access profile; and
generating for each filter an associated user group.
12. The computer program product of claim 9 , wherein the user-to-group assignment sequence further comprises assigning one or more users to a corresponding user group.
13. The computer program product of claim 9 , further comprising executing more than one user verification sequences in parallel for more than one cube.
14. The computer program product of claim 9 , further comprising executing the user-to-group assignment sequence subsequent to completion of the user verification sequence and the filter generation sequence.
15. A system for managing access security to a ROLAP system and a MOLAP system that share a subset of common data, the system comprising:
a ROLAP system that operates on a computer having a processor and memory;
a MOLAP system that operates on a computer having a processor and memory;
a security table for a ROLAP system, the security table comprising a userID column, a data group column defining a security profile, and a profile column;
an OLAP cube description table comprising, for one or more cubes in the MOLAP, a minimum user security definition for user access to the one or more cubes in the MOLAP;
an OLAP cube profile description table comprising, for the one or more cubes in the MOLAP, one or more access profiles;
an application that joins the security table, the OLAP cube description table, and the OLAP cube profile description table to autonomously generate a MOLAP security definition consistent with a ROLAP security definition.
16. The system of claim 15 , wherein autonomously generating a MOLAP security definition further comprises autonomously executing a user verification sequence, a filter generation sequence, and a user-to-group assignment sequence.
17. The system of claim 16 , wherein the user verification sequence further comprises:
for each cube in the OLAP cube description table, generating a qualified users data set comprising one or more users for whom the security profile defined in the security table meets the minimum user security definition for the cube defined in the OLAP cube description table; and
for each cube in the OLAP cube description table, dynamically maintaining the qualified users data set.
18. The system of claim 16 , wherein the filter generation sequence further comprises:
generating input for filter creation by joining the security table with the OLAP cube description table and adding the one or more access profiles of the OLAP cube profile description table;
generate from the input a filter for each unique combination of security setting and access profile; and
generating for each filter an associated user group.
19. The system of claim 16 , wherein the user-to-group assignment sequence further comprises assigning one or more users to a corresponding user group.
20. The system of claim 16 , the application executing more than one user verification sequences in parallel for more than one cube.