Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

MS SQL

Posted Jan 22, 2010

Introduction to Security in Analysis Services - Page 3

By William Pearson

Permissions

Table 2 describes the permissions available in an Analysis Services database, as well as the access rights managed by each permission.

Permission

Access Rights

Database

Database access defines access to objects and data in an Analysis Services database.

Available access rights include:

  • Administer
  • Process
  • ReadDefinition

Data source

Data source access defines access to data sources in an Analysis Services database.

Available access rights include:

  • Access

    (None or Read)
  • ReadDefinition
flexibility in granting both Read and Read/Write access to portions of cubes. We can specify which dimension members and cube cells a role can view and update.

Available access rights include:

  • Access

    (None, Read, or ReadWrite)
  • LocalCube/DrillthroughAccess

    (None, Drillthrough, Drillthrough and Local Cube)
  • Process

Cell

Cell data access defines access to cells in a cube. There are three types of access to cells in a cube:

  • Read
  • ReadContingent
  • Read/Write

Cell security in a cube is defined for each type of cell access with an MDX expression that resolves to True or False for each cube cell. Any nonzero value in a numeric expression is evaluated as True while zero is evaluated as False. Access is allowed when an expression resolves to True and denied when an expression resolves to False.

Available access rights include:

  • Access

    (None, Read, ReadContingent, or Read/Write)

Dimension

Dimension access properties define access to the database dimensions in an Analysis Services database irrespective of their participation in cubes. Dimension access allows users that are members of a role to browse a dimension in client applications. Cube dimension permissions can also be specified that override the database access permissions for a role when a dimension is accessed in a particular cube.

Available access rights include:

  • Access

    (Read or Read/Write)
  • Process
  • ReadDefinition

Attribute

Dimension data access controls which dimension attributes can be accessed by members of a role. Allowing or denying access to an attribute defines access to levels in the dimension hierarchies based upon that attribute. If a role is denied access to an attribute, then it is denied access all levels derived from the attribute.

If denying access to an attribute creates a hole in a hierarchy, then the entire hierarchy is invalidated and is no longer accessible to members of the role. For example, in the hierarchy CountryRegion-State-City-Name, the levels State and Name are not contiguous levels in the hierarchy. Denying access to the City attribute therefore leaves a hole and invalidates the hierarchy. In contrast, denying access to the CountryRegion attribute would create no hole and leave the valid hierarchy State-City-Name of contiguous levels. Similarly, denying access to the Name attribute retains the valid hierarchy:

CountryRegion-State-City.

When you allow members of a role access to an attribute, you can allow or deny access to selected members of the attribute.

Available access rights include:

  • AllowedSet
  • DefaultMember
  • (NOTE: As we mentioned earlier, the DefaultMember access right defines the default member of the dimension.)

  • DeniedSet
  • VisualTotals

Mining Structure

Mining structure access determines permissions surrounding mining structures and mining models and their data.

Available access rights include:

  • Access

    (None or Read)
  • Process
  • ReadDefinition

Mining Model

Mining structure access determines permissions to mining structures and mining models and their data.

Available access rights include:

  • Access

    (None, Read, or Read/Write)
  • Browse
  • Drill Through
  • ReadDefinition

Table 2: Permissions Available in an Analysis Services Database, together with the Access Rights Managed by each Permission (Source: MSSQL Server 2008 Books Online)

Permissions and Inheritance

When an object contains other objects (such as cubes or dimensions in an Analysis Services database) the Administer, Process and ReadDefinition permissions on the parent object are inherited by the child objects. Details of these permissions’ inheritance appear in Table 3.

Permission

Inheritance

Administer

Members of the Analysis Services server role have permission to administer a server; therefore they also have full access to all the objects on the server. Members of an Analysis Services database role granted permission to administer a database have full access to all the objects in the database.

Process

By default, the Process setting on an object applies to any child object. This property can also be set on a child object to override the permission inherited from the parent object.

  • If a user is permitted to process a cube but not permitted to process a dimension in the cube, then the user can successfully process the cube only if the dimension is already processed.
  • When a user processes a database only those cubes and dimensions in the database which the user is permitted to process are processed.

ReadDefinition

By default, the ReadDefinition property setting on an object is inherited by any child objects. This property can also be set on a child object to override the permission inherited from the parent object.


Table 3: Permissions Inheritance Details in Analysis Services (Source: MSSQL Server 2008 Books Online)

In considering permissions as a part of the design, development and maintenance of an Analysis Services solution, it is important to realize that a user can belong to more than one role in an Analysis Services database. Permissions across multiple roles are additive. If a role provides access to an object, then a member of that role has access to the object regardless of whether or not that member is explicitly denied access to the object in another role.



MS SQL Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM


















Thanks for your registration, follow us on our social networks to keep up-to-date