Logo

Navigation
  • Home
  • Services
    • ERP Solutions
    • Implementation Solutions
    • Support and Maintenance Solutions
    • Custom Solutions
    • Upgrade Solutions
    • Training and Mentoring
    • Web Solutions
    • Production Support
    • Architecture Designing
    • Independent Validation and Testing Services
    • Infrastructure Management
  • Expertise
    • Microsoft Development Expertise
    • Mobile Development
    • SQL Server Database and BI
    • SAP BI, SAP Hana, SAP BO
    • Oracle and BI
    • Oracle RAC
  • Technical Training
    • Learn Data Management
      • Business Intelligence
      • Data Mining
      • Data Modeling
      • Data Warehousing
      • Disaster Recovery
    • Learn Concepts
      • Application Development
      • Client Server
      • Cloud Computing Tutorials
      • Cluster Computing
      • CRM Tutorial
      • EDI Tutorials
      • ERP Tutorials
      • NLP
      • OOPS
      • Concepts
      • SOA Tutorial
      • Supply Chain
      • Technology Trends
      • UML
      • Virtualization
      • Web 2.0
    • Learn Java
      • JavaScript Tutorial
      • JSP Tutorials
      • J2EE
    • Learn Microsoft
      • MSAS
      • ASP.NET
      • ASP.NET 2.0
      • C Sharp
      • MS Project Training
      • Silverlight
      • SQL Server 2005
      • VB.NET 2005
    • Learn Networking
      • Networking
      • Wireless
    • Learn Oracle
      • Oracle 10g
      • PL/SQL
      • Oracle 11g Tutorials
      • Oracle 9i
      • Oracle Apps
    • Learn Programming
      • Ajax Tutorial
      • C Language
      • C++ Tutorials
      • CSS Tutorial
      • CSS3 Tutorial
      • JavaScript Tutorial
      • jQuery Tutorial
      • MainFrame
      • PHP Tutorial
      • VBScript Tutorial
      • XML Tutorial
    • Learn Software Testing
      • Software Testing Types
      • SQA
      • Testing
  • Career Training
    • Career Improvement
      • Career Articles
      • Certification Articles
      • Conflict Management
      • Core Skills
      • Decision Making
      • Entrepreneurship
      • Goal Setting
      • Life Skills
      • Performance Development
      • Personal Excellence
      • Personality Development
      • Problem Solving
      • Relationship Management
      • Self Confidence
      • Self Supervision
      • Social Networking
      • Strategic Planning
      • Time Management
    • Education Help
      • Career Tracks
      • Essay Writing
      • Internship Tips
      • Online Education
      • Scholarships
      • Student Loans
    • Managerial Skills
      • Business Communication
      • Business Networking
      • Facilitator Skills
      • Managing Change
      • Marketing Management
      • Meeting Management
      • Process Management
      • Project Management
      • Project Management Life Cycle
      • Project Management Process
      • Project Risk Management
      • Relationship Management
      • Task Management
      • Team Building
      • Virtual Team Management
    • Essential Life Skills
      • Anger Management
      • Anxiety Management
      • Attitude Development
      • Coaching and Mentoring
      • Emotional Intelligence
      • Stress Management
      • Positive Thinking
    • Communication Skills
      • Conversation Skills
      • Cross Culture Competence
      • English Vocabulary
      • Listening Skills
      • Public Speaking Skills
      • Questioning Skills
    • Soft Skills
      • Assertive Skills
      • Influence Skills
      • Leadership Skills
      • Memory Skills
      • People Skills
      • Presentation Skills
    • Finding a Job
      • Etiquette Tips
      • Group Discussions
      • HR Interviews
      • Interview Notes
      • Job Search Tips
      • Resume Tips
      • Sample Resumes
 

MSAS – Introducing Analysis Services Security

By Exforsys | on May 2, 2005 |
MSAS
Security of data is of paramount importance to most industries and organizations. All organizations want to restrict access to data hierarchically or define role based access. Analysis Services provides for this need.

The number of Administrators and users having access to Analysis services data can be restricted by setting the role definitions in Analysis manager. End users who have access to data through client applications can be restricted in various ways. Security levels can be set for various objects within the database such as cubes, dimensions and cells.

The Microsoft Windows NT or Windows 2000 Administrator role is used to control Analysis services Administrator security by creating a group named OLAP administrators. End user security is controlled using the authentication during the process of connecting to the Analysis server. Database, cube and mining model roles are defined in the Analysis manager.

The roles assigned to the various entities, determine the level of access available to them. Roles are assigned to the database first and thereafter assigned to the cubes and other objects that users access in the database. Some changes can be made to the roles at cube level, but these changes do not impact the roles assigned at database level. This feature enables the assignment of different role definitions for different cubes.

Analysis services support a windows integrated security system. In this lesson we will be studying the type of security provided by Analysis services:

  1. Understanding Administrator Security
  2. Securing User Authentication
  3. Understanding Database Roles
  4. Implementing Dimension Security
  5. Managing Cube roles.

Understanding Administrator Security

The Administrator in Analysis services is the person who performs administrative functions. The Administrator can define user roles, set database and cube level security roles and also maintain the various components of the database. Administrator roles are granted by membership in the OLAP Administrators group defined in Windows NT or Windows 2000. This group is created on the installation of Analysis Services and the logged on user is added to the group by default. Members of this group can access the Analysis server through Analysis Manager and perform administrative functions. He can perform programmatic functions with Decision Support Objects(DSO). The user manager window in Windows NT 4.0 or Computer Management Window in Windows 2000 can be used to manage OLAP Administrators group. Administrator security does not have multiple levels.

Normally the Administrator will have full access and all read write permissions on objects in the database. He can login to the server from client applications with all his permissions intact.

The Administrators access rights to cubes are listed in the Cube owner’s Control list (ACL). He will be able to access the cube from any client terminal provided the cube was created when he had logged into the domain account and not the server’s local account.

In the latter instance he will be denied access to the cube if he logs in from the workstation’s local account and tries to access the cube. Therefore it is important that cubes should be created by Administrators when they are logged into the Domain account. The Administrator should also assign a role to the cube after it is created so that it can be accessed from other computers on role based access.

There are a number of operational considerations while administrating the Analysis server. The Administrator has to set service logon Account permissions for access to data sources. The service for Analysis Services is named MSSQLOLAPService. In Windows NT 4.0 or Windows 2000, Windows integrated security is used and the logon account associated with this service must have permissions to access the data sources. Else the Administrator cannot process the objects maintained in the Analysis Manager. The Logon Account can be maintained by using the Services application in the control panel.

Protection of data is of paramount importance. Administrators who have access to Web browsers, productivity applications and emails should ensure that they refrain from accessing Web pages, productivity applications and email applications that support scripts and macros when logged on as administrators. Only trusted accounts and web pages should be accessed and security should be set to the highest priority. The Windows NT 4.0 or Windows 2000 user accounts can be used to establish special Administrator accounts for managing Analysis services.

The Administrator can control an end user’s access to a cube. He assigns the roles to end users for access to various objects in the database and the database itself. The End user security relies on the definition of user accounts and groups in Microsoft NT 4.0 or Windows 2000. When Analysis Services security roles are defined, a set of users and groups is defined within the Analysis Services. The Auto Synch Period property determines the time that lapses between the definition of the end user’s access rights and the point in time when the definitions become effective.

The value of the Auto Synch Period property controls the frequency of Client server synchronization and defaults at 10,000 milliseconds. It is passed in each connection string to the Analysis services. This default can be overridden by end users and client applications and therefore, can vary from one end user to another. If the Auto Synch Period property is set to Null, synchronization is not constant. It is impacted by end users actions. For instance if changes are made to an end user’s access rights while he is connected and browsing a cube, the changes will not be effective till he disconnects from the cube. He cannot be forcibly disconnected from the cube during a query session after access has been obtained. However if the Auto Synch Period is set to a non-null value, the end user’s access rights are reviewed or synchronized at the specified intervals and any changes made in the interregnum have an immediate impact. For instance if the value is set to a nonzero value and the end users’ access to the cube has been removed, the end user will be immediately disconnected from the cube.

The Administrator performs the following functions with reference to end users.

1. He has to review and revise Windows NT 4.0 or Windows 2000 user accounts and groups in accordance with the various access requirements of the end users.
2. He has to create security roles and assign each role to the cubes or data mining models that the users in the role are permitted to access.
3. Define each role assigned to a cube or mining model. Each role’s definition can vary for each cube or mining model to which it is assigned.

User Accounts and Groups in Microsoft SQL Server 2000 Analysis Services, is created after the user accounts and groups are created in the User Manager in Microsoft Windows NT 4.0 or in Computer management window in Windows 2000. The time required for role maintenance can be reduced if the Administrator has a clear charter or the memberships of groups before the roles are created in the Analysis Services. Where NTLM Security Support providers are used for authentication, all user accounts and groups must be in the same trusted domain if they are to be granted access rights to cubes. User accounts and groups in other domains will not be able to connect to the Analysis server in this case.

The Administrator assigns Database, cube and Mining model roles to end users. A role is defined as a set of user accounts and groups with the same access rights and permissions to Analysis Services data. Roles help the Administrator implement end user security by controlling access to data on the Analysis server. A database role can be assigned to multiple cubes or mining models in the database. In this instance the end users have access to the cubes or mining models contained in the database. The role provides defaults for cube or mining model roles of the same name. Once a database role is granted, the Administrator can specify the type and scope of access to dimension members for cubes. Database roles are defined at database level and are maintained by the Database role manager.

The database role, by default specifies a read only access. It also does not limit the dimension members or cube cells from being visible to end users. Once a user has a database role he can view the entire cube. However, the Administrator can specify a read/write access and limit the dimension members that are visible and updateable in both the database role and the cube role. We will see how this is done a little later in this tutorial. The mining model role confines the user to a read only access.

The Cube role is defined for single cubes. The default role is derived from the database role of the same name, but the Administrator can override these defaults in the cube role. The additional options in cube role settings enable the Administrator define cell security. Cube roles are created at the cube level after the database role is assigned to the cube. These roles are maintained in the Cube role manager. The Administrator can also indicate whether he wants to give the end user a right to drillthrough to the cell’s source data. To use this capability the cube or at least one partition of the cube should be write enabled.

Mining models roles are also defined for single models. If a database role has been assigned and the mining model carries the same name, the user derives a default mining role. This can, however, be overridden by the Administrator. Mining model roles are created when the database role is created and are maintained in the Mining Model Role Manager.

An end user may have multiple roles on an Analysis Server. Within the server the user has combined access to the objects specified in these roles. However, it is possible that there is a conflict of roles. The resolution of such conflicts is done through exceptions. Exceptions are custom rules in dimension security. It must be noted that all combinations of custom rules from multiple roles cannot be resolved.

« « Oracle 9i Utilities
MSAS – Securing User Authentication » »

Author Description

Avatar

Editorial Team at Exforsys is a team of IT Consulting and Training team led by Chandra Vennapoosa.

Free Training

RSSSubscribe 392 Followers
  • Popular
  • Recent
  • Data Warehouse database and OLTP database

    February 25, 2005 - 0 Comment
  • MSAS: Dimension Storage Modes and Levels

    April 3, 2005 - 0 Comment
  • Tutorial 65: MSAS – Managing Cube Roles

    May 5, 2005 - 0 Comment
  • MSAS : Building the Cube Part #1

    March 9, 2005 - 0 Comment
  • MSAS: Analysis Services Aggregations

    April 13, 2005 - 0 Comment
  • MSAS : Meta data Repository

    March 23, 2005 - 0 Comment
  • MSAS – Implementing Calculations Using MDX Part 2

    April 21, 2005 - 0 Comment
  • Microsoft Analysis Services Training

    February 25, 2005 - 0 Comment
  • MSAS: Understanding Hierarchies

    April 3, 2005 - 0 Comment
  • MSAS – Applying security to a Dimension

    May 5, 2005 - 0 Comment
  • MSAS – Browsing the Dependency Network

    May 6, 2005 - 0 Comment
  • MSAS – Building a Relational Decision Tree Model

    May 6, 2005 - 0 Comment
  • MSAS – Introduction to Data Mining

    May 6, 2005 - 0 Comment
  • MSAS – Applying security to a Dimension

    May 5, 2005 - 0 Comment
  • Tutorial 65: MSAS – Managing Cube Roles

    May 5, 2005 - 0 Comment
  • MSAS – Understanding Database Roles

    May 5, 2005 - 0 Comment
  • MSAS – Securing User Authentication

    May 2, 2005 - 0 Comment
  • MSAS – Writebacks

    April 28, 2005 - 0 Comment
  • MSAS – Defining and Creating Drillthrough

    April 26, 2005 - 0 Comment
  • MSAS – Defining and Creating Auctions

    April 26, 2005 - 0 Comment

Exforsys e-Newsletter

ebook
 

Related Articles

  • MSAS – Browsing the Dependency Network
  • MSAS – Building a Relational Decision Tree Model
  • MSAS – Introduction to Data Mining
  • MSAS – Applying security to a Dimension
  • Tutorial 65: MSAS – Managing Cube Roles

Latest Articles

  • Project Management Techniques
  • Product Development Best Practices
  • Importance of Quality Data Management
  • How to Maximize Quality Assurance
  • Utilizing Effective Quality Assurance Strategies
  • Sitemap
  • Privacy Policy
  • DMCA
  • Trademark Information
  • Contact Us
© 2023. All Rights Reserved.IT Training and Consulting
This website uses cookies to improve your experience. We'll assume you're ok with this, but you can opt-out if you wish.AcceptReject Read More
Privacy & Cookies Policy

Privacy Overview

This website uses cookies to improve your experience while you navigate through the website. Out of these, the cookies that are categorized as necessary are stored on your browser as they are essential for the working of basic functionalities of the website. We also use third-party cookies that help us analyze and understand how you use this website. These cookies will be stored in your browser only with your consent. You also have the option to opt-out of these cookies. But opting out of some of these cookies may affect your browsing experience.
Necessary
Always Enabled
Necessary cookies are absolutely essential for the website to function properly. This category only includes cookies that ensures basic functionalities and security features of the website. These cookies do not store any personal information.
Non-necessary
Any cookies that may not be particularly necessary for the website to function and is used specifically to collect user personal data via analytics, ads, other embedded contents are termed as non-necessary cookies. It is mandatory to procure user consent prior to running these cookies on your website.
SAVE & ACCEPT