Using Native SQL and MDX securely
Updated: 2009-04-30
Planning Server features a language that allows you to easily specify calculation rules. These rules can be used to enter data into a model, move data within or between models, and calculate new values from existing data, among other capabilities. These rules can be run by a member of the modeler role by using Planning Business Modeler in two ways. The first way to run a rule is as part of a job, which is then scheduled or assigned to a business user. The second method is to have the rule automatically executed by the system whenever data has changed. Modeler members can also specify execution in Microsoft SQL Server 2005 or MDX, each of which have a advantages and disadvantages in functionality and in performance.
Under some conditions, the PerformancePoint Expression Language (PEL) might be too restrictive for advanced users. This especially is true for customers who are SQL Server or MDX experts might want to enhance their stored procedures for performance, utilize built-in SQL Server functions that we do not expose, or do something outside the capabilities of PEL. What PEL provides is the power of raw SQL Server or MDX, along with the convenience of our rules infrastructure, which allows different styles of execution.
To achieve this, PEL exposes a new implementation of rules called NativeSql and NativeMdxScript. However, these new implementations can pose a security risk. They could potentially enable a user who has access to only one model site to make drastic changes in multiple model sites throughout the whole application. Specifically, Planning Server cannot do any analysis on these rules because they are written in SQL or MDX, which Planning Server cannot easily parse. This requires a new implementation of rules to run as the Service Identity, which has Database Owner permissions on the SQL Server computer that contains the application database. This would enable a malicious member of the modeler role to change data anywhere in the application, drop tables, modify the audit trail, and many other things.
To lessen this risk, there are two options: the approval system and the running of all rules as a low-privileged user (described in another document).
Sample
The following steps describe how to create and run a native rule by using the approval system.
In Planning Administration Console, a member of the global administrator role must select the Allow Native SQL\MDX Rules check box. This sets the corresponding flag to "True." The flag is stored as a Boolean on the application object. Users who have EditMetadata permissions at the application level have access to modify it. This step is not required for regular rules.
In Planning Business Modeler, a member of the modeler role must write and then save a raw rule in an InActive state. Whenever any rule edits are saved, including edits to regular rules, Planning Server checks for the EditRules operation type in the context of the model in which the rules are being saved. In the case of raw rules, the server additionally checks that the application Allow Native SQL\MDX Rules flag is enabled (the check box is selected), and that each native rule is InActive. Any rule set to InActive cannot be deployed in the database or OLAP cube and cannot be executed.
In the SQL Server database, a database administrator must approve the rule by setting the “IsActive” flag to true for the rule. This flag is stored in the RuleSetsOrRules table in the IsActivated column. Access to this table is controlled according to standard SQL permissions. This step is not required for regular rules.
The model that contains the rule must be deployed by a member of the modeler role. This step is required even for regular rules. As part of deploying the model, Planning Server will always check for the GenerateApplication operation type in the scope of the model being deployed. Additionally, for raw rules, the server will check if the application Allow Native SQL\MDX Rules flag is enabled. Both regular and native rules are never deployed if this flag is set to InActive.
A member of the modeler role is now able to execute the native rule by using any one of the standard execution paths. If executed directly in Planning Business Modeler, the ExecuteRule operation type will be checked in the context of the model. If the modeler member creates a job and schedules it or assigns it to a user, the ManageWorkflow operation type will be checked with the scope of the model site in which the model is in. If the rule is set to be executed by the system (this has to have been done when the rule was created), no additional operation types are checked. In addition to the standard checks here, which are applied to all rules, there is an additional check for the Allow Native SQL\MDX Rules flag whenever a native rule is executed from any code path. If the flag is "false," then the execution will fail.
If any changes are made to the native rule, it must be saved in an InActive state as described in step 2. Steps 3 and 4 must be repeated to reapprove the rule. This creates an approval process around every native rule. In some cases, a global administrator member might decide that native rules are not required for their Planning application. They can select to never enable the Allow Native SQL\MDX Rules flag. By default, this flag is set to False. Even if the flag is set to True, every rule must be authored by a member of the modeler role and approved by a database administrator. This gives administrators a chance to create a review system, for use before the rule is enabled. Finally, if the global administrator member ever decides that they no longer want raw rules in the system, they can disable the Allow Native SQL\MDX Rules bit. When they do this, native rules cannot be created, updated, deployed, or executed; native rules can only be deleted.
Download this book
This topic is included in the following downloadable book for easier reading and printing:
See the full list of available books at Downloadable content for PerformancePoint Planning Server.