:::: MENU ::::

Posts Categorized / Security and Permissions

  • Jun 03 / 2009
  • 0
dbDigger, Security and Permissions, T-SQL Tips and Tricks, User Defined Views

Implement required restrictions on view by using WITH CHECK OPTION

I have created views for employees in different departments. Each employee is able to view records for his own department in the view. Also employees can update or insert records for their department through that view. I noticed that employees are even update and insert data that is related to any other department.
Now it was required to restrict that no employee can insert/update data that conflicts with department restriction that i have implemented in each view. For example consider the following imaginary view where records are picked based on a department provided in where clause

 
Create VIEW UVW_DeptRecords  
 AS  
 SELECT name, designation, department  
 FROM employees   
 WHERE department = 'Finance'  
 GO  

Employees from finance department are able to perform DML operations on this view. It is required that these employees may not be able to insert a record with department name other than Finance. First i planned to use a trigger for implementation of this logic. But while discussing this issue with fellow DBA i came to know that there is an option provided with the view definition itself, that will prohibit any DML that conflicts with the conditions provided in where clause of view definition.
So creating the above view with additional WITH CHECK OPTION in following way will not allow any DML operation for which employee does not belong to Finance department.

 
Create VIEW UVW_DeptRecords  
 AS  
 SELECT name, designation, department  
 FROM employees   
 WHERE department = 'Finance'  
 WITH CHECK OPTION  
 GO  
  • May 13 / 2009
  • 0
Analysis Services SSAS, dbDigger, Security and Permissions, SQL Server Agent scheduled Jobs

Service Accounts for SQL Server Analysis Services

To understand the permissions required for the MSSQLServerOLAPService and SQL Server Agent service accounts, you need to understand the security context in which various operations are executed. Certain tasks are performed in the context of the logged-on user, and other tasks are performed in the security context of the MSSQLServerOLAPService service account. When you request that Analysis Manager create a new object or browse for an existing object, the task is executed in the security context of the interactive user account of the user performing the task. When Analysis Services processes dimensions, partitions, and mining models, this task is performed in the security context of the MSSQLServerOLAPService service account. You must grant sufficient rights to this service account to ensure that processing will succeed. It is not uncommon for users to assume that if they can create objects, then they can process them. You must ensure that the MSSQLServerOLAPService and the SQL Server Agent service accounts have sufficient permissions for the tasks they need to perform. At a minimum, the service account must be a member of the OLAP Administrators group. This permission is needed for any user (or service that is acting on behalf of a user) that manages an Analysis Services server.

  • May 13 / 2009
  • 0
Analysis Services SSAS, dbDigger, Security and Permissions

Service Accounts for SQL Server Analysis Services

To understand the permissions required for the MSSQLServerOLAPService and SQL Server Agent service accounts, you need to understand the security context in which various operations are executed. Certain tasks are performed in the context of the logged-on user, and other tasks are performed in the security context of the MSSQLServerOLAPService service account. When you request that Analysis Manager create a new object or browse for an existing object, the task is executed in the security context of the interactive user account of the user performing the task. When Analysis Services processes dimensions, partitions, and mining models, this task is performed in the security context of the MSSQLServerOLAPService service account. You must grant sufficient rights to this service account to ensure that processing will succeed. It is not uncommon for users to assume that if they can create objects, then they can process them. You must ensure that the MSSQLServerOLAPService and the SQL Server Agent service accounts have sufficient permissions for the tasks they need to perform. At a minimum, the service account must be a member of the OLAP Administrators group. This permission is needed for any user (or service that is acting on behalf of a user) that manages an Analysis Services server.

  • May 13 / 2009
  • 0
Analysis Services SSAS, dbDigger, Security and Permissions

Security Considerations for SQL Server Analysis Services

Administrator Security

When Analysis Services is installed, the setup program creates the OLAP Administrators local group on the Analysis Services computer and adds the user account of the person installing Analysis Services to this group. All members of the local Administrators group are automatically members of the OLAP Administrators group, regardless of whether they are explicitly added to the OLAP Administrators group. The OLAP Administrators group is granted the following rights on the Analysis Services computer:

  • Full control permission to the Server Connection
  • Write permissions through the MsOLAPRepository$ hidden share (the ..Microsoft Analysis ServicesBin folder). The MsOLAPRepository$ hidden share is created during setup. Analysis Services uses the hidden share when reads from or writes to the repository when it is stored in an Access database (this is the default location and store for the repository).
  • Full control rights to the Bin and Data folders under the ..Microsoft Analysis Services directory. This includes full control rights to the repository files, Msmdrep.mdb and Msmdrep.ldb. With clustering, if the Data folder is on a different computer than the computer on which Analysis Services is running, you must ensure that the members of the OLAP Administrators group on the Analysis Services computer have full control rights to this Data folder. This includes the account under which Analysis Services is running. Generally this is accomplished through the use of a domain group. There is only one level of administrative access to Analysis Services. A member of the OLAP Administrators group has complete administrative access to Analysis Services objects, full read access to all cubes and dimensions, and full write access to all write-enabled cubes and dimensions (regardless of any contrary role definitions). A domain or local user that is not a member of the OLAP Administrators group can perform no administrative tasks and has read or write access to the extent permitted based on dimension-level or cell-level security.

End-User Security

End-user security in Analysis Services is based on Windows user accounts and groups. Before you begin configuring end-user security in Analysis Services, you must first create the user accounts and groups within Active Directory. A frequently asked question is whether Analysis Services supports other kinds of authentication. The answer is Yes and No. Yes, it can support other types using HTTP access and IIS (IIS 6.0 includes some new authentication options). However, all these authentication types must ultimately map to a Windows user account in the general sense: including domain accounts, local accounts, the guest account (if enabled), or the built-in NT AUTHORITYANONYMOUS LOGON account. Therefore, no, Analysis Services does not support SQL standard security or any similar technology where the authentication is not based on Windows user accounts.

Security Roles

After you have created the appropriate Windows user and group accounts, you create security roles within Analysis Services that contain Windows user and group accounts, and define the access each role has to Analysis Services data. You can use database roles, cube roles, and mining model roles.

  • A database role can be assigned to multiple cubes or mining models in a database. Database roles provide default permissions for cube or mining model roles. By default, a database role specifies only read access and does not limit the dimension members or cube cells visible to end users. You can, how ever, specify read/write access and limit dimension members that are visible and updatable.
  • A cube role applies to a single cube. Defaults in a cube role are derived from the database role of the same name, but some of these defaults can be overridden in the cube role. In addition to the database role features of specifying read/write access and limiting dimension members that are visible and updatable, a cube role also enables you to specify cell-level security. Cell-level security has less memory overhead than dimension security.
  • A mining role applies to a single mining model. Defaults in a mining role are derived from the database role of the same name, but some of these defaults can be overridden in the mining role. A domain user or group can be a member of multiple roles within Analysis Services. In this case, the effective rights of the user are the combined access characteristics specified in these roles.

Dimension-, Cell-, or Application-Level Security

When you use dimension-level security to limit the dimension members that are visible or updateable, Analysis Services must create a replica dimension in memory when a user connects which reflects the dimension members that user is permitted to see.

A user is not in any role: no access is permitted to the dimension at all.

This is actually an interesting case. If a user is allowed access to a cube (based on the user’s membership in the roles), the user can see the cube as a valid cube, capable of being queried. However, when dimension security is applied, the allowed set is empty in one or more dimensions. This places Analysis Services in a difficult position because Analysis Services cannot tell the user where access is being denied (because that is a security violation in and of itself). As a result, Analysis Services forcibly disconnects the session with the user – and the user receives the purposely ambiguous error message “The connection to the server is lost.”

  • Apr 23 / 2009
  • 1
dbDigger, Security and Permissions, System Administration

How to get users currently accessing my files through network

Some of my colleagues use my PC as a file server by making some shared folders there. They access files in those folders. Before shut down of my PC at the end of shift i have to make sure that no one is currently accessing any file on my PC through network. For this purpose i use a command net files in cmd or windows power shell

Users currently accessing the files on my pc through network

If list is populated as shown in snap, then it means that there are users accessing files on my PC through network and shut down may cause interruption in their work.

  • Mar 06 / 2009
  • 3
dbDigger, Monitoring and Analysis, Security and Permissions, T-SQL Scripts

Generate script for all permissions in a database

In many scenarios DBA is required to configure permissions of a user. If permissions are to be re configured for an existing user, due to user deletion or some other reason, then it may be a nightmare to exactly configure the same. A handy solution is that you may generate the permissions for your users regularly in a usable form and use it when ever required. Following script will serve the same purpose. T-SQL for Permissions may be generated by following script. This T-SQL may be used later with a single click whenever required. The script will be used on SQL Server 2005 and later

 
Use AdventureWorks  
 GO  
 SELECT  
 state_desc + ' ' + permission_name +  
 ' on ['+ SYS.SCHEMAS.name + '].[' + SYS.OBJECTS.name + ']  
 to [' + SYS.DATABASE_PRINCIPALS.name + ']'  
 COLLATE LATIN1_General_CI_AS as [Permissions T-SQL]  
 FROM SYS.DATABASE_PERMISSIONS JOIN sys.objects  
 ON SYS.DATABASE_PERMISSIONS.major_id = sys.objects.OBJECT_ID  
 JOIN SYS.SCHEMAS  
 ON SYS.OBJECTS.SCHEMA_ID = SYS.SCHEMAS.SCHEMA_ID  
 JOIN SYS.DATABASE_PRINCIPALS  
 ON SYS.DATABASE_PERMISSIONS.grantee_principal_id = SYS.DATABASE_PRINCIPALS.principal_id  
 order by [Permissions T-SQL]  
 GO  

each and every permission will be provided in T-SQL form for every user. Whole result may be saved to use later.

T-SQL for permissions

If you require to generate permissions for a single user or single object then conditions may be applied through where clause in our script.

  • For specific object put [SYS.SCHEMAS.name] in where clause
  • For specific user put [SYS.DATABASE_PRINCIPALS.name] in where clause

To get all permissions for a login in all databases please read my other post.

Consult us to explore the Databases. Contact us