Version 2021

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 ($)