 |
|
Configuring permissions for SQL Server Analysis Services
|
By: Scott Murray | Read Comments (2) | Related Tips: > Analysis Services Security |
Problem
You have created your first SSAS database and cube. Now you want to secure the cube and related dimensions. What techniques are used to apply permissions to the cube?
Solution
Securing Analysis Services does have some similarities to applying security to a SQL Server database in Management Studio; however, the options are definitely much more limited. First, all SSAS permissions center around a role concept; second, all role members must be Windows / Active directory based. SQL Server logins cannot be used! As such, security cannot be directly assigned to windows / active directory user or group. That user or group must be added as a member of a role. One other issue that may come up when working on a cube locally and UAC is turned on, security changes can not be completed unless Management Studio or Business Intelligence Development Studio (BIDS) is executed in "Run as Administrator" mode. The first step in setting up security begins during the SQL Server installation.
Administrator Configuration During SQL Server Installation
During the SQL Server installation, first time DBAs and DWAs are tempted to just click next.. next ... next. However, many of the installation screens contain vitally important selections that impact your department for the life of your installation. It is of the utmost importance to plan your installation well and ultimately test your installation results. One such planning item is determining who the SSAS Administrators will be. Once that decision is made, adding the appropriate users on the SSAS Account provision screen is as easy as adding the appropriate users as noted in the below screen print. These users will have full rights to ALL SSAS databases, cubes, and dimensions, and more importantly, the ability to add other users as full administrators to the instance or individually to single SSAS databases.
If additional administrators need to be added after the Installation, they can be added in Management Studio (SSMS). Depending on your level of experience, you may not realize that you can connect to Analysis Services (and Reporting Services for that matter) in Management Studio. First Open SSMS and then click Connect > Analysis Services.
Once the connection is successful, right click on the Instance and Select Properties.
As noted below, within the SSAS Instance Properties, select security. On this window, new instance level administrators (including groups) can be added (or old ones can be removed). The normal Windows user selection box appears which allows you to search for and validate users. Now that the instance level security is setup, database and cube level security can be created.
Database and Cube Level Security
Database and cube level security can be maintained in either BIDS or Management Studio (if you are using a SSAS Project, which helps with deployment and version control, remember each deployment will overwrite the current security unless you use the deployment wizard (more details on the deployment wizard can be found at http://msdn.microsoft.com/en-us/library/ms174817(v=sql.105).aspx). My preference is to use BIDS, although the screens are very similar in either Management Studio or BIDS. In order to create a new role, simply right click on Roles and Select New Roles.
As shown on the below screen print, step 1 is to define the Role Name in the properties windows; use a name which is both descriptive and meaningful. If the properties window is not visible, select View > Properties (or hit F4). Next, fill in the Role Description as needed. As important as the name is, the three other check boxes below the role description play a vital role in defining database level access to the members of this role. Each check box is described next:
- Full control: This permission grants full access to this particular SSAS database. Members with this permission have similar access rights as the server role noted above. However, these permissions apply to this database only, and not to all the databases within the SSAS instance. Full control grants members access to add other users and to process and maintain databases, cubes, and dimensions.
- Process database: This permission allows a member to process this database and its related cubes and dimensions.
- Read definitions: This permission allows role members to read the database metadata. Granting this access, though, does not provide access to read the metadata of other objects within the database such as cube meta data or dimension metadata. This lower level meta data access will be discussed later in the article.
Often, none of these check boxes are checked for regular users of the cube.
Switching to the members tab allows the administrator to add specific users to the role. Membership can be added either by typing the individual users or by using the windows Advanced > Find option. Again, you can add active directory groups, local windows groups, domain users, or local users; however, SQL users cannot be used! The Add members processes is illustrated in the following screen print. Of course for manageability, using groups is recommended.
Moving on to the Data Source tab, intuitively, it would seem that a user would need access to the data source. However, granting access to the data source actually grants permissions to the underlying data sources of the SSAS project or database. Generally, this option should be set to None, unless users are utilizing data mining structures or links to external datasets.
Switching to the Cubes tab finally provides methods which will grant read data access to role members. If a SSAS database has multiple cubes, access is given on a cube by cube basis and is broken into the following three categories as described below and illustrated subsequently:
- Access
- None: Role members are not able to access this particular cube
- Read: Users can read data from the cube, but not write data back to the cube. This option is most commonly selected
- Read/Write: Users can read data from cube and write data back to the cube (for additional details on write back see: http://ssas-wiki.com/w/Articles#Write_Back )
- Local Cube/Drillthrough Access
- None: No Drillthough or local cube creation
- Drill through: Drillthrough is permitted for this cube (see Cube > Actions tab)
- Drill through and Local Cube: Drillthrough is permitted as is the creation of local cubes from the Server cube.
- Process:
- Role members are able to process this particular cube.
Remember, if access is not specifically granted to the cube, end users will not even see the cube from their client applications.
The Cell Data tab affords administrators the ability to granularly set read, read-contingent, and read/write permissions to role members. Once any of these options are enabled by selecting the appropriate check box, the administrator must enter a MDX expression which defines the cells which are available or restricted for the role members. Although, Cell Data permission restrict access to certain cells or cell ranges, these restrictions work hand and hand with the dimension permissions which will be subsequently discussed. Additionally, if certain cells are restricted, but other non-restricted calculated cells are derived from that restricted values, users may be able to make a determination of a particular value. To see additional details about this complex area of security see http://cwebbbi.wordpress.com/2008/05/20/cell-security-when-read-permissions-are-actually-read-contingent/.
The Dimension tab assigns privileges to role members at the dimension level. First, two options exists at the dimensions level, either Read or Read and Write. Roles whose access is set to none will not even see the dimension marked as such. Furthermore, roles can be granted Read Definition access which allows for the role members to read the metadata concerning the dimension. Similar to the Cubes Access Rights, granting Process rights to a role, allows the members to process that particular dimension. The next tab to the right of the dimension tab is the Dimensions data tab; moving from dimension security at the dimension level, the dimension data tab allows the administrator to restrict or deny access to certain attribute values. For example, if a sales group only should have access to sales data in their state geographic territory, then a role could be designed to only allow access the sales person's assigned states. As seen in the next illustration, two options for entering the dimension restrictions: the basic option which allows the selection of specific values to display or restrict. The advanced option relies on the entry of MDX queries for generating the list of available or restrict access points.
One caveat in the use of Dimension Data security; total rows will display the total for all values in the dimension even those that restricted which in turn could allow end users to determine the value of the restricted attributes. To have the end client reporting tools only rollup the unrestricted values, check the enable Visual Totals check box, displayed at the bottom of the following illustration, which will then only rollup the unrestricted values. Please note, this feature can cause slower performance.
Conclusion-Security
Implementing SSAS Security is as important as every other methods of restricting organizational data. AS a DBA/ DWA, we are ultimately responsible for providing not only the correct and appropriate data (all very quickly), but we also must provide such data using a method and path which appropriately protects access to a SSAS database with such data. Therefore, SSAS allows us to setup the following security points:
- who administers the database
- who processes the databases, cubes, and dimensions
- who can access the meta data about the cubes and dimensions
- who can read data from the SSAS database
- including drill through
- dimension level and dimension data security
Next Steps
- Some additional reading on Permissions and Access list: http://msdn.microsoft.com/en-us/library/ms174786(v=sql.105).aspx
Last Update: 9/25/2012
About the author
View all my tips
PrintComments and Feedback:
| Sunday, February 10, 2013 - 8:02:59 PM - Vugar | Read The Tip |
|
Hi Scott, Thanksfor greate article. I have created and deployed cubes. I can connect and browse with excel. Problem: when i use Integrated Security=SSPI;Persist Security Info=True I can see all ssas Databases/Cubes even though I have not defined any role for those databases. Why? I could not find single article where the following described: no role defined and Integrated Security=SSPI; is used to access Cube.
Thank you in advance, //Vugar |
|
| Monday, February 11, 2013 - 11:25:56 AM - scott murray | Read The Tip |
|
If now roles are defined all have access. |
|
|
privacy | disclaimer | copyright | advertise | about authors | contribute | feedback | giveaways | user groups Some names and products listed are the registered trademarks of their respective owners. Edgewood Solutions LLC | MSSharePointTips.com | MSSQLTips.com |