Permissions
|
|
Untangling the SQL Server permission matrix can be complex, especially if you use very granular, column-level permissions. There are three types of actions that you can perform for security: granting, denying, and revoking. As it may sound, granting a user rights to an object gives them access to that particular object. Denying a user access to an object disallows a user any access to that object. A way to implicitly deny a user access is to revoke access; the user will neither be granted nor denied access to the object.
The most important thing to remember with SQL server permissions is that the permissions are in most cases cumulative. For example, Brian has been granted rights to the Employees table to all columns except the Salary column. Brian is also a member of the Managers group, which has rights to the Salary column. By the rule of cumulative rights, Brian is also given rights to the Salary column.
The exception to this rule is in denied access. If Brian is explicitly denied access to the Employees table, he will not be able to see any data in the table, even if he’s a member of a group that has access to the table. If the Managers group is granted access to the Salary column, but Brian is denied access to that column, he will not be able to see the data in the column, though the rest of the group will.
| Note | A member of the sysadmin role supercedes any rights assigned to the individual user or group. If you’ve been granted sysadmin rights and have been denied access to a database, you will have access to the database because of your sysadmin rights. |
Because of the complexity and the potential negative effects of explicitly denying users access to certain data, it is much better to just revoke their access and let the cumulative security take hold. A case for issuing a DENY statement is if you have a troublesome employee who has been assigned to a Windows group that has access to data. By issuing the DENY statement, you ensure that the one user will not be able to perform the given action on the database, but the rest of the group will be able to continue to access the object. This is also very useful when an employee has been terminated and has not yet been removed from the Windows group.
WITH GRANT Option
The WITH GRANT option of the GRANT statement allows a user access to an object and also allows that user to grant others access to the same object. For example, if you grant Mary access to a stored procedure with the WITH GRANT option, she could in turn issue a GRANT command giving Bob access to the stored procedure. The following syntax shows you how to use the WITH GRANT option to grant Mary access to the stored procedure:
GRANT EXECUTE ON USP_STOREDPROCNAME TO Mary WITH GRANT OPTION
This is especially nice when you want to grant a manager access to a table so he can grant access to anyone who works for him. If you revoke access by default, the people Mary has granted access will continue to have access unless you use the CASCADE command at the end of the revoke as shown here:
REVOKE EXECUTE on USP_STOREDPROCNAME to mary CASCADE
By executing this, you revoke Mary from using the stored procedure as well as anyone she’s ever granted access to.
 |
Permissions are stored in the syspermissions table at the database level. If you want to find out what permissions a user has in T-SQL, you can query the sysprotects pseudo-table. A GRANT permission will show in the sysprotects table with 204 or 205 in the ProtectType column. A DENY would show with the value of 206 in the same column. A record would not be in the table at all if a REVOKE has been issued.
For example, if you want to find out who has permissions to the Categories table in the Northwind database, you can run the following query:
SELECT OBJECT_NAME(id) as ObjectName, USER_NAME(uid) as UserName, protecttype, action, USER_NAME(grantor) as Grantor FROM sysprotects WHERE id = object_id('categories') This will return the following results:
| ObjectNm | User | ProtectType | Action | Grantor |
|---|---|---|---|---|
| Categories | public | 205 | 26 | dbo |
| Categories | public | 205 | 193 | dbo |
| Categories | public | 205 | 195 | dbo |
| Categories | public | 205 | 196 | dbo |
| Categories | public | 205 | 197 | dbo |
The Action column holds the information to complete the picture of what rights the user has. The column can hold the following values:
| 26 = REFERENCES | 207 = CREATE VIEW |
| 178 = CREATE FUNCTION | 222 = CREATE PROCEDURE |
| 193 = SELECT | 224 = EXECUTE |
| 195 = INSERT | 228 = BACKUP DATABASE |
| 196 = DELETE | 233 = CREATE DEFAULT |
| 197 = UPDATE | 235 = BACKUP LOG |
| 198 = CREATE TABLE | 236 = CREATE RULE |
| 203 = CREATE DATABASE |
|
Server Roles
Fixed server roles provide an easy way to grant users specific global access to your database server. Table 3-1 shows the fixed server roles and what types of access you grant logins by granting them access to these roles.
| Fixed Server Role | Purpose |
|---|---|
| sysadmin | Equivalent of the SA account. Gives the user full access to perform any function on the SQL Server. |
| serveradmin | Grants the user rights to configure server-wide functions and stop and start the SQL Server services. Members in this role can also manage the server messages. |
| setupadmin | Grants the user rights to create and manage linked servers and startup procedures. |
| securityadmin | Grants the user the right to manage logins and can grant CREATE DATABASE rights. Also gives the user the right to read the SQL Server error logs and change passwords for other users. |
| processadmin | Grants the user rights to manage SQL Server processes and issue the KILL command. |
| dbcreator | Grants the user rights to create, alter, and drop databases. |
| diskadmin | Grants the user the right to manage the disk files. |
| bulkadmin | Grants the user the right to execute the BULK INSERT command. |
To allow a user to be a member of a certain role, you can run the following command in T-SQL:
EXECUTE sp_addsrvrolemember 'ybknight', 'sysadmin'
To remove the login’s rights to the sysadmin role, simply run the following command:
EXECUTE sp_dropsrvrolemember 'ybknight', 'sysadmin'
| Note | The SA account cannot be taken out of the sysadmin role. |
Database Roles
Fixed database roles are a way to easily give a user global rights to an individual database. They can range from giving a user complete control of a specific database to denying them access to reading any data. Table 3-2 outlines what rights you can assign a user by making them members of database fixed roles.
| Fixed Database Role | Purpose |
|---|---|
| public | Any user who has been granted access to the database will be given the rights of this role. |
| db_owner | Grants the user the right to perform any action in the given database. |
| db_accessadmin | Grants the user the right to add and remove users from a given database. |
| db_securityadmin | Grants the user the right to manage all permissions, roles, membership to those roles, and object owners. |
| db_ddladmin | Grants the user the right to execute any DDL except for GRANT, REVOKE, and DENY. |
| db_backupoperator | Grants the user the right to back up a database and issue DBCC and CHECKPOINT statements. |
| db_datareader | Grants the user the right to run SELECT statements against any table or view in the database. This does not include executing stored procedures in the database. |
| db_datawriter | Grants the user the right to update, insert, or delete data from the database. |
| db_denydatareader | Explicitly denies the user rights to select data from the database. |
| db_denydatawriter | Explicitly denies the user rights to run UPDATE, INSERT, or DELETE statements. |
| Note | It’s especially important to emphasize that if you assign a user to the db_datareader fixed role, it does not give him rights to execute stored procedures. |
User Database Roles
User database roles are a great way to efficiently manage permissions for your users. They allow you to group users by function, much like groups in Windows domains or in the Active Directory. For example, it is much easier to create a role called Accounting and assign all accounting representatives to that user-defined role. Then, rather than grant each user specific permissions to the objects, you need only grant the role the access. To create a new role in T-SQL, connect to the database and run the following command:
sp_addrole 'Accounting'
Then you need only run the sp_addrolemember procedure to grant the user rights to the role. The following example shows how to add the login of Mary to the Accounting fixed database role:
sp_addrolemember 'Accounting', 'Mary'
To remove Mary, simply run the sp_droprolemember stored procedure with similar syntax as adding the member to the role:
sp_droprolemember 'Accounting', 'Mary'
Alternatively, you can run the following command to remove the role altogether and all the associated memberships to it:
sp_droprole 'Accounting'
To determine who has access to a certain role, you can run sp_helprolemember. For example, if you were to connect to a database and wanted to find out who had db_owner rights to that database, you could run the following command:
sp_helprolemember 'db_owner'
This would retrieve the following results (your results may vary based on the logins on your server):
DbRole MemberName MemberSID ----------- -------------------- ------------------------------------- db_owner bknight2 0x61380050DC31EE4CBFD04DC8B9531638 db_owner dbo 0x01
Public Role and Guest Account
The public role and the guest account are two items that must be watched carefully. If a user has been granted access to a database, that user automatically is placed into the public role and cannot be removed from that role. At that point, the user will retain all rights of the role. Because of that, you should never assign any rights to the public role that aren’t granted to it by default. If you grant rights to the public role, you lose control of explicitly granting users rights to objects. Be especially wary of denying rights to this role. If you deny rights to the public role, you deny every user (with the exception of members of the sysadmin role) from accessing that object.
| Note | The public role exists in every database and members cannot be removed from the role. |
The guest account is a virtual user that, when granted access to a database, gives all logins to the server that type of access. For example, if you allow the guest account access to your database, every login in the server will then be granted access to that database and all rights of the public role, even if he has not been granted access to the database. This dangerous user should be removed from every database where possible. It is not added to a database by default.
| Note | The guest account cannot be removed from the master or tempdb database. |
If you would like to grant the guest account access to your database, you can run the following command in Query Analyzer:
USE DBNAME GO EXECUTE sp_grantdbaccess guest
To drop the guest account from your database, execute the following command:
USE DBNAME GO EXECUTE sp_dropuser guest
| Note | At SQL Server install time, the guest account is granted access to the Northwind and pubs user databases and should be removed. In production, the Northwind and pubs databases should be removed altogether. |
Column- and Row-Level Security
Often, you must secure data in a horizontal (row-level) and vertical (column-level) method. In SQL Server, you can lock down users from accessing certain columns by using the GRANT statement as shown here:
GRANT SELECT ON [dbo].[tablename] ( [Column1], [Column2] ) TO [user or role name]
For example, to grant the login ybknight rights to the stor_id and ord_num columns in the Sales database, use the following syntax:
GRANT SELECT ON [dbo].[sales] ( [stor_id], [ord_num] ) TO [ybknight]
If the ybknight user then tries to run the following query:
SELECT * FROM Sales
he will receive the following errors:
Server: Msg 230, Level 14, State 1, Line 1 SELECT permission denied on column 'title_id' of object 'sales', database 'pubs', owner 'dbo'. Server: Msg 230, Level 14, State 1, Line 1 SELECT permission denied on column 'payterms' of object 'sales', database 'pubs', owner 'dbo'. Server: Msg 230, Level 14, State 1, Line 1 SELECT permission denied on column 'qty' of object 'sales', database 'pubs', owner 'dbo'. Server: Msg 230, Level 14, State 1, Line 1 SELECT permission denied on column 'ord_date' of object 'sales', database 'pubs', owner 'dbo'.
| Tip | If you’re using column-level security, it becomes especially important for the application to handle the error 230, which is a permission denied error. Otherwise, you would display to the user every sensitive column in the table, as shown in the example. |
Implementing row-level security is a little trickier and must be done through views, stored procedures, and functions. There is no built-in method of protecting your SQL Server data at a row level, so you will have to build a custom solution. Creating views for each user could pose a problem if there are more than a few users on your system. The framework that I propose in this section is dependent on the following:
-
Every user or group that you’d like to partition your data based on has their own login.
-
All data access is done through stored procedures or views.
-
An extra column must be added to the table that holds the login that has permissions to the row. You can also make a many-to-many table if you need to allow multiple users to see one row. That optional configuration will not be covered in this section.
Let’s look at a simple sales and lead generation system. If your company has a competitive sales force where one salesperson should not be able to view another salesperson’s data, you could create a record ownership column to accomplish this type of system. First, consider the following schema:
CREATE TABLE [dbo].[LEADS] ( [LEAD_ID] [int] IDENTITY (1, 1) NOT NULL , [LEAD_PHONE_NN] [varchar] (12) NULL , [LEAD_NM] [varchar] (45) NULL , [LEAD_OWNER_NM] [varchar] (75) NULL ) ON [PRIMARY] ALTER TABLE [dbo].[LEADS] WITH NOCHECK ADD CONSTRAINT [DF_LEADS_LEAD_OWNER_NM] DEFAULT (suser_sname()) FOR LEAD_OWNER_NM]
The main point to watch in this table is the suser_sname() function, which will output the login the user logged in with. You then would need to create a view or a stored procedure so one user couldn’t see another’s data. The following is a simple view to perform such an action:
CREATE VIEW VIEW_LEADS as SELECT * FROM LEADS WHERE LEAD_OWNER_NM = suser_sname()
So if Mary were to create a new lead in the application that executed the following command:
INSERT INTO leads (LEAD_PHONE_NN, LEAD_NM) VALUES('904-555-5555', 'Sample Lead') SQL Server would mark Mary as the owner. When Mary selects against the view, she should see the following:
LEAD_ID LEAD_PHONE_NN LEAD_NM LEAD_OWNER_NM ----------- ------------- -------------------- -------------- 1 904-555-5555 Sample Lead mary
Then you could lock down the Leads table and prevent direct access. If any violating user were to try to access that table directly, they would receive the following message:
Server: Msg 229, Level 14, State 5, Line 1 SELECT permission denied on object 'LEADS', database 'Northwind', owner 'dbo'
| Note | This could pose a problem if a login is renamed or moves to a different domain in Windows. You’ll need to develop a procedure if you implement this to rename the data in this table or reassign it if an employee is removed from the system. |
Keep in mind that this is a very simple answer to a very complex problem. In actuality, you would probably need a many-to-many table in this type of scenario which allowed multiple employees to own the record. For example, if you wanted Brian and his boss to be able to view the record, this many-to-many lookup table would be useful.
Other Database Environments
A sticky point with DBAs and developers is often how much to restrict other environments. A compromise must be reached between the developers, who need an area to create objects, and the DBA, who must guarantee a clean cut-over into production. A general best practice that I like to use is to not give the developers sysadmin or even db_owner rights to the database. Instead, explicitly grant them access to write stored procedures, functions, and (potentially) views. To grant the login of mary access to create and modify stored procedures, simply issue the following command:
GRANT CREATE PROCEDURE TO mary
By explicitly granting this type of access, you ensure that no developer will interfere with another developer’s work. Each object that is created will have the developer’s login associated with it as the object owner. For example, if Mary Smith has the login of mary and creates a stored procedure, it will be called mary.storedprocedurename. You also ensure that all changes that enter the testing environment have been approved by you. This is because before an object is ready to be integrated into the full application, its ownership will need to be changed back to dbo using the following command:
EXECUTE sp_changeobjectowner 'mary.storedprocedurename, 'dbo'
By issuing the sp_changeobjectowner command, you change the owner back to dbo and the application will no longer need to fully qualify the stored procedure by owner name. What’s nice about each developer being flagged as the owner of their own stored procedures is it allows multiple developers to have their own version of the stored procedure.
| Caution | As you implement this type of ownership chain, you will need to ensure that before you integrate the objects the T-SQL inside that stored procedure or function doesn’t call another object with a named owner such as Mary. |
Another item to keep in mind in your environment is to make sure that your production server is not in the same domain as any other environment. Make sure you also do not use the same sysadmin passwords in the various environments in case of a security breach. For example, there are password cracker programs that could be run in development or in QA and go unnoticed, whereas in production you may have auditing enabled to catch someone cracking your SA password.
I generally like to ensure that I do not start SQL Server with the same account also in production. Instead, group similar applications together and have common startup accounts for each group. This ensures that if someone finds out the password for the SQL Server startup account, they cannot interfere with other areas of production.
|
|
