MicroStrategy ONE
Controlling Access to Data at the Database (RDBMS) Level
Database servers have their own security architectures that provide authentication, access control, and auditing. As mentioned above, you may choose to use these RDBMS techniques to manage access to data, or you may choose to use mechanisms in the MicroStrategy application layer to manage access to data, or you may use a combination of the two. They are not mutually exclusive. One advantage of using the database-level security mechanisms to secure data is that all applications accessing the database benefit from those security measures. If only MicroStrategy mechanisms are used, then only those users accessing the MicroStrategy application benefit from those security measures. If other applications access the database without going through the MicroStrategy system, the security mechanisms are not in place.
Security Views
Most databases provide a way to restrict access to data. For example, a user may be able to access only certain tables, or they may be restricted to certain rows and columns within a table. The subset of data available to a user is called the user's security view.
Security views are often used when splitting fact tables by columns and splitting fact tables by rows (discussed below) cannot be used. The rules that determine which rows each user is allowed to see typically vary so much that users cannot be separated into a manageable number of groups. In the extreme, each user is allowed to see a different set of rows.
Note that restrictions on tables, or rows and columns within tables, may not be directly evident to a user. However, they do affect the values displayed in a report. You need to inform users as to which data they can access so that they do not inadvertently run a report that yields misleading final results. For example, if a user has access to only half of the sales information in the data warehouse but runs a summary report on all sales, the summary reflects only half of the sales. Reports do not indicate the database security view used to generate the report.
Consult your database vendor's product documentation to learn how to create security views for your database.
Splitting Fact Tables by Rows
You can split fact tables by rows to separate a logical data set into multiple physical tables based on values in the rows (this is also known as table partitioning). The resultant tables are physically distinct tables in the data warehouse, and security administration is simple because permissions are granted to entire tables rather than to rows and columns.
If the data to be secured can be separated by rows, then this may be a useful technique. For example, suppose a fact table contains the key Customer ID, Address, Member Bank and two fact columns, as shown below:
Customer ID |
Customer Address |
Member Bank |
Transaction Amount ($) |
Current Balance ($) |
123456 |
12 Elm St. |
1st National |
400.80 |
40,450.00 |
945940 |
888 Oak St. |
Eastern Credit |
150.00 |
60,010.70 |
908974 |
45 Crest Dr. |
People's Bank |
3,000.00 |
100,009.00 |
886580 |
907 Grove Rd. |
1st National |
76.35 |
10,333.45 |
562055 |
1 Ocean Blvd. |
Eastern Credit |
888.50 |
1,000.00 |
You can split the table into separate tables (based on the value in Member Bank), one for each bank: 1st National, Eastern Credit, and so on. In this example, the table for 1st National bank would look like this:
Customer ID |
Customer Address |
Member Bank |
Transaction Amount ($) |
Current Balance ($) |
123456 |
12 Elm St. |
1st National |
400.80 |
40,450.00 |
886580 |
907 Grove Rd. |
1st National |
76.35 |
10,333.45 |
The table for Eastern Credit would look like this:
Customer ID |
Customer Address |
Member Bank |
Transaction Amount ($) |
Current Balance ($) |
945940 |
888 Oak St. |
Eastern Credit |
150.00 |
60,010.70 |
562055 |
1 Ocean Blvd. |
Eastern Credit |
888.50 |
1,000.00 |
This makes it simple to grant permissions by table to managers or account executives who should only be looking at customers for a certain bank.
In most RDBMSs, split fact tables by rows are invisible to system users. Although there are many physical tables, the system "sees" one logical fact table.
Support for Split fact tables by rows for security reasons should not be confused with the support that Intelligence Server provides for split fact tables by rows for performance benefits. For more information about partitioning, see the Advanced Reporting Help.
Splitting Fact Tables by Columns
You can split fact tables by columns to separate a logical data set into multiple physical tables by columns. If the data to be secured can be separated by columns, then this may be a useful technique.
Each new table has the same primary key, but contains only a subset of the fact columns in the original fact table. Splitting fact tables by columns allows fact columns to be grouped based on user community. This makes security administration simple because permissions are granted to entire tables rather than to columns. For example, suppose a fact table contains the key labeled Customer ID and fact columns as follows:
Customer ID |
Customer Address |
Member Bank |
Transaction Amount ($) |
Current Balance ($) |
|
|
|
|
|
|
|
|
|
|
You can split the table into two tables, one for the marketing department and one for the finance department. The marketing fact table would contain everything except the financial fact columns as follows:
Customer ID |
Customer Address |
Member Bank |
|
|
|
|
|
|
The second table used by the financial department would contain only the financial fact columns but not the marketing-related information as follows:
Customer ID |
Transaction Amount ($) |
Current Balance ($) |
|
|
|
|
|
|