Security for data access - Microstrategy

MicroStrategy has a robust security model that enables you to create users and groups, determine how they are authenticated, control what data they can see, and what functional privileges they can have. For detailed information, please refer to the MicroStrategy System Administration Guide. This section discusses the Access Control List (ACL) and security filters that relate to Freeform SQL reports only.

Access control list

An access control list (ACL) is a list of users and groups and the access permission that each one has to objects in aMicroStrategy project. Different users may have different permissions on the same object.

When you use existing objects (including project objects) in column mapping, the ACL of these objects remains. However, new objects (attributes and metrics) created in Freeform SQL reports inherit the ACL from what is defined as default in the Project Configuration Editor (Project Configuration -> Project definition -> Security -> Access control -> Set Freeform SQL and MDX objects default security). When you click Modify, the Properties [XDA Objects] dialog box is displayed. The Permissions list has the following settings:

Access control list

In addition, whoever creates the new objects (attributes, metrics) has the Full Control permission.

In the Properties [XDA Objects] dialog box, you can change the settings of the default ACL for different groups of users. However, note that the changed settings will only affect the new objects (attributes and metrics) created subsequently in Freeform SQL reports, but not those objects created prior to the change.

Security filters

A security filter is a filter object that is used to narrow down the result set that users or groups obtain when they execute reports or browse elements. Usually assigned by Administrators, security filters control, at the MicroStrategy level, what warehouse data users can see.

A security filter has three components that are defined by Administrators:

  • Filter expression: specifies the subset of the data that a user can analyze (for example, Region in (A,B)).
  • Top range attribute: specifies the highest level of detail that the security filter allows the user to view.
  • Bottom range attribute: specifies the lowest level of detail that the security filter allows the user to view.

As for regular reports in MicroStrategy, security filters can also be applied to Freeform SQL reports. Actually, a security filter for a Freeform SQL report is not much different from that for a standard report. The same concepts still apply.

If you use an older version of MicroStrategy prior to 8.0, make sure that you run the project update for the metadata; otherwise, the security filter functionality will not be applied to Freeform SQL reports.

By default, Freeform SQL reports do not take into account security filters. The Report Designer has to insert a security filter placeholder in a Freeform SQL report and configure it; otherwise, any user can run the report without being limitedin the data he or she sees.

Because the SQL statement is static, a security filter string (“where Security Filter” or “and Security Filter”) needs to be manually embedded into the statement, such as the following:

Select Year_ID, Store_ID, M1 From Store_Fact Where Security Filter

The string Where Security Filter would be replaced by Where Store_ID = 1 when the report is executed for a user who has a security filter (Store@ID = 1) like the following:

Select Year_ID, Store_ID, M1 From Store_Fact Where Store_ID = 1

Parameters for security filters

The following parameters need to be specified when you create security filters in the Freeform SQL Security Filter Editor:

  • Replacement string: The Replacement String field is located at the bottom of the Freeform SQL Security Filter Editor. The default value for the replacement string is “Security Filter”, which is replaced by the security filter condition when the report is generated.

    To complete the string, add “where” or “and” in front of “Security Filter”. If there is no valid security filter, then the whole string (“where Security Filter” or “and Security Filter”) does not appear in the generated report SQL. For example, following the example mentioned above, when a user without a security filter runs the same report, the SQL looks like the following:

    Select Year_ID, Store_ID, M1 From Store_Fact

    As you can see, the whole security filter string is dropped from the generated SQL statement.

    If you write “where” or “and” directly into the SQL statement in the Freeform SQL Editor, instead of in the ReplacementString field in the Freeform SQL Security Filter Editor, the following will happen:

    • For a user with a security filter: The report will be generated without any problem.
    • For a user without a security filter: The report will fail due to invalid SQL statement.
  • Attribute mappings: The Attribute Mapping panel is located on the upper right side of the Freeform SQL Security Filter Editor. This is where you map attribute forms to columns in the database. For every attribute form used in security filter, you need to provide the string that the Engine uses to replace the form itself when building a security filter expression into the SQL statement. This string is also displayed when SQL is generated for the report. For example
    Parameters for security filters
  • Ignorable attributes:specify attributes that may beignored by the Engine when the report is being generated, even if they appear in a security filter. For example, if you define the following:
    • Ignore: Customer
    • Security filter definition: Year = 1998 and Customer = Bob

    Only Year = 1998 is applied when the report is generated.

  • Allow security filters with Top and Bottom levels to be evaluated based on the select level of this report: This check box option is located at the lower part of the Freeform SQL Security Filter Editor. This option meansthat the Select Level of the report (displayed in the linejust above this option) is the true level of the data that is to be retrieved for the report when Top and Bottom criteria are evaluated.

    Exercise caution with this option:

    • Not selecting this option when the user indeed has Top and Bottom levels defined will cause the report to fail.
    • Select this option only when you are sure that your SQL statement does not retrieve data outside the restriction defined by the security filters. This means that you may need to check the security filters for individual users one by one and see how each one may interact with the SQL statement.

Creating a security filter

Security filters are created in the Freeform SQL Security Filter Editor, which can be accessed by selecting Insert Security Filter option from the Edit menu in the Freeform SQL Editor. For step-by-step instructions, please refer to the online help (search for “Creating security filters for Freeform SQL reports”).

When you close the editor, the security filter string is automatically inserted into the SQL statement at the current cursor location. The string is displayed in an uneditable mode, just like a prompt, and is bold and underlined in green, for example, Where Store ID = 1.

You can edit the security filter after it is inserted into the SQL statement by double- clicking it or right-clicking it and selecting Edit.

All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd Protection Status

Microstrategy Topics