Security for Data Access

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 on these features, see the System Administration Help. 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 a MicroStrategy project. Different users may have different permissions on the same object.

When you use existing objects (including project objects) in Freeform SQL column mapping, the ACLs of these objects are used. However, new attributes and metrics created in Freeform SQL reports inherit the default ACL defined in the Project Configuration Editor. You can modify the default ACL in MicroStrategy Developer by right-clicking a project and selecting Project Configuration. In the Project Configuration window point to Project definition, then Security, and then for Set Freeform SQL and MDX objects default security select Modify. The Properties[XDA Objects] dialog box is displayed. The Permissions list has the following settings:

User	Children
Administrator	Full Control
Everyone	View
Public/Guest	View

The user who creates the new attributes and metrics with Freeform SQL is automatically given the Full Control permission of the new objects.

In the Properties [XDA Objects] dialog box, you can change the settings of the default ACL for different groups of users.

The changed settings will only affect the new attributes and metrics created subsequently in Freeform SQL reports, but not those objects created prior to the change.

Security Filters

In MicroStrategy, a security filter is a filter object that is used to narrow down the result set that users or groups can view when they execute reports or browse elements. Usually assigned by administrators, security filters control what warehouse data users can see within MicroStrategy.

Once a security filter is created for users or groups, it is automatically applied when those users or groups view report data or browse attribute elements. However, you must perform some additional configuration to apply security filters to a Freeform SQL report. You configure your Freeform SQL report to apply security filter qualifications by inserting and configuring a placeholder for a user or group's security filter. Placeholders of this type created in Freeform SQL reports are referred to as "security filter placeholders" in this section.

Freeform SQL works with security filters in the following ways:

If you use a version of MicroStrategy earlier than 8.0, make sure that you run the project update for the metadata; otherwise, security filter functionality with Freeform SQL is not supported.

• By default, Freeform SQL reports ignore security filters. The Report Designer has to insert a security filter placeholder as a qualification in the WHERE clause of a Freeform SQL report and configure it; otherwise, any user can run the Freeform SQL report without being limited in the data they see. For more information on creating security filter placeholders, see Create security filter placeholders.
• Security filter qualifications are performed on Freeform SQL reports only if the attribute in the qualification is mapped to a column in the Freeform SQL report using a security filter placeholder. For more information on mapping attributes in security filter placeholders, see Attribute MappingsThe Attribute Mapping pane is located on the upper-right side of the Freeform SQL Security Filter Dialog. This is where you map attributes to columns in the SQL statement. For every attribute qualification to be applied in a security filter, you need to provide the form and string for the column mapped to that attribute in the SQL statement. The column mapped in the SQL statement must also be a column (attribute form) that is used in security filters. For example:AttributeFormStringCustomerIDLU_CUSTOMER.CUSTOMER_ID.
• Security filter qualifications are ignored in Freeform SQL reports if the attributes in the qualifications are explicitly ignored in a security filter placeholder. For more information on ignoring security filter attributes in security filter placeholders, see Ignored AttributesThe Ignored Attributes pane is located below the Attribute Mapping pane in the Freeform SQL Security Filter Dialog. This is where you specify attributes that may be ignored by the Engine when the report is being generated, even if they appear in a security filter qualification. This option can be useful if your Freeform SQL report does not include attributes that are included in security filters. By ignoring attributes that are not included in the Freeform SQL report, it allows security filters to apply qualifications for mapped attributes without creating any security holes. For example, if you define the following:Attribute Mappings: Year is mapped to the LU_YEAR.YEAR_ID columnIgnored Attributes: Customer is chosen to be ignored because it is not part of the Freeform SQL reportSecurity filter definition: Year = 2026 and Customer = BobUsers with the security filter defined above are able to run the report with the Year = 2026 qualification applied to the Freeform SQL report. There is no security hole because the Customer attribute does not appear on the report.You can ignore attributes that are included in the Freeform SQL report, but this can cause security holes because all qualifications on these attributes within security filters are ignored..
• Security filters which qualify on multiple attributes can be used with Freeform SQL reports only if every attribute in the security filter is either mapped to a column or ignored in a security filter placeholder. Freeform SQL reports fail for users with security filters containing multiple attribute qualifications that are not mapped or set to be ignored by a security filter placeholder.
• A security filter can restrict the attributes a user can view in relation to the level at which attributes are found within a MicroStrategy hierarchy. For more information on allowing top range and bottom range attributes in security filter placeholders see Allow Security Filters with Top and Bottom Levels to be Evaluated Based on the Select Level of this ReportThis check box option is located in the lower part of the Freeform SQL Security Filter Dialog. This option explicitly defines the Select Level of the report (displayed in the line just above this option) as the true level of the data that is retrieved for the report when Top and Bottom criteria are evaluated. Not selecting this option when the user has Top and Bottom levels defined causes the report to fail.Select this option only when you are sure that your query 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 query.A security filter can be applied to a Freeform SQL report only if every attribute in the security filter is either mapped to a column or ignored. The report fails if only a subset of the attributes used in a security filter are either mapped to columns or ignored within the security filter placeholder. For example, a security filter that includes qualifications on Year and Category is applied for a group of users. In the Freeform SQL report, Year is the only attribute that has been mapped to a column in the security filter placeholder. Since Category is not included either as an attribute mapping or an ignored attribute, the report fails for all users with this security filter.. The two attribute range options are as follows:
• 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.
• Because the SQL statement is static, a security filter placeholder string ("WHERE Security Filter" or "and Security Filter") needs to be manually embedded into the statement, such as the following:

  

  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) similar to the following syntax:

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

  For more information on embedding security filter placeholder strings, see Replacement StringThe Replacement String field is located at the bottom of the Freeform SQL Security Filter Dialog. The default value for the replacement string is "Security Filter", which is replaced by the security filter qualification 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, when using the replacement string "WHERE Security Filter" as mentioned above, if a user without a security filter runs the same report, the SQL looks like the following:Select Year_ID, Store_ID, M1From Store_FactThe whole replacement string is dropped from the generated SQL statement and no security filter is used. If you write "WHERE" or "and" directly into the SQL statement in the Freeform SQL Editor, instead of in the Replacement String field in the Freeform SQL Security Filter Dialog, the following will happen:For a user with a project security filter: The report will be generated without any problem.For a user without a project security filter: The report will fail due to invalid SQL statement..

For more information on security filters in general, refer to the Setting Up User Security chapter in the System Administration Help.

Create security filter placeholders

Security filter placeholders are created in the Freeform SQL Security Filter Dialog, which can be accessed by selecting the Insert Security Filter option from the Edit menu in the Freeform SQL Editor.

When you close the editor, the security filter placeholder 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:

You can edit the security filter placeholder after it is inserted into the SQL statement by double-clicking the statement.

Within the Freeform SQL Security Filter Dialog that opens, you can create a security filter placeholder using the following options:

• Replacement String

  The Replacement String field is located at the bottom of the Freeform SQL Security Filter Dialog. The default value for the replacement string is "Security Filter", which is replaced by the security filter qualification 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, when using the replacement string "WHERE Security Filter" as mentioned above, if 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

  The whole replacement string is dropped from the generated SQL statement and no security filter is used.

  If you write "WHERE" or "and" directly into the SQL statement in the Freeform SQL Editor, instead of in the Replacement String field in the Freeform SQL Security Filter Dialog, the following will happen:

  • For a user with a project security filter: The report will be generated without any problem.
  • For a user without a project security filter: The report will fail due to invalid SQL statement.
• Attribute Mappings

  The Attribute Mapping pane is located on the upper-right side of the Freeform SQL Security Filter Dialog. This is where you map attributes to columns in the SQL statement. For every attribute qualification to be applied in a security filter, you need to provide the form and string for the column mapped to that attribute in the SQL statement. The column mapped in the SQL statement must also be a column (attribute form) that is used in security filters. For example:

  Attribute	Form	String
  Customer	ID	LU_CUSTOMER.CUSTOMER_ID

• Ignored Attributes

  The Ignored Attributes pane is located below the Attribute Mapping pane in the Freeform SQL Security Filter Dialog. This is where you specify attributes that may be ignored by the Engine when the report is being generated, even if they appear in a security filter qualification. This option can be useful if your Freeform SQL report does not include attributes that are included in security filters. By ignoring attributes that are not included in the Freeform SQL report, it allows security filters to apply qualifications for mapped attributes without creating any security holes. For example, if you define the following:

  • Attribute Mappings: Year is mapped to the LU_YEAR.YEAR_ID column
  • Ignored Attributes: Customer is chosen to be ignored because it is not part of the Freeform SQL report
  • Security filter definition: Year = 2026 and
    Customer = Bob

    Users with the security filter defined above are able to run the report with the Year = 2026 qualification applied to the Freeform SQL report. There is no security hole because the Customer attribute does not appear on the report.

    You can ignore attributes that are included in the Freeform SQL report, but this can cause security holes because all qualifications on these attributes within security filters are ignored.

• 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 in the lower part of the Freeform SQL Security Filter Dialog. This option explicitly defines the Select Level of the report (displayed in the line just above this option) as the true level of the data that is retrieved for the report when Top and Bottom criteria are evaluated.

  Not selecting this option when the user has Top and Bottom levels defined causes the report to fail.

  Select this option only when you are sure that your query 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 query.

  A security filter can be applied to a Freeform SQL report only if every attribute in the security filter is either mapped to a column or ignored. The report fails if only a subset of the attributes used in a security filter are either mapped to columns or ignored within the security filter placeholder.

  For example, a security filter that includes qualifications on Year and Category is applied for a group of users. In the Freeform SQL report, Year is the only attribute that has been mapped to a column in the security filter placeholder. Since Category is not included either as an attribute mapping or an ignored attribute, the report fails for all users with this security filter.