Permissions - SQL Server 2008

Permissions are the individual rights, granted (or denied) to a principal, to access a securable. As in previous SQL Server versions, you can GRANT permissions, DENY permissions, or REVOKE permissions that have already been granted. What’s changed is the sheer number of permissions that you can grant—there are 181 combinations of permissions and securables!

Types of Permissions

The exact permissions that can be granted, and the format for the GRANT and DENY commands, vary according to the securable. These can be broken down into 12 groups:

  • Server permissions: Permissions that apply to the server as a whole, such as permission to connect to the server or to an endpoint, permission to create or alter DDL or other events, and permission to access resources external to SQL Server. The controlling permission is CONTROL SERVER, which gives the grantee authority to perform any action and is effectively equivalent to adding the login to the sysadmin fixed server role.
  • HTTP endpoint permissions: Permissions to connect to the endpoint and to control, alter, view the definition of, or take ownership of the object.
  • Certificate permissions: Permissions to alter or control a specific certificate.
  • Database permissions: Database-wide permissions that apply to all objects in the current database. For example, they include permissions to create, alter, and execute objects in the database; to perform select, insert, update, or delete operations in any object in the database; and to control or take ownership of the database.
  • Schema permissions: Permissions that apply to a named schema or to all objects within the schema. They include the ability to perform select, insert, update, and delete operations on any object in the schema; to execute any procedure or function in the schema; or to control, alter, or take ownership of the schema.
  • Assembly permissions: Permissions on a specific assembly, such as permission to execute, control, alter, or take ownership of the assembly.
  • Type permissions: Permissions on a specific user-defined type, such as permission to execute, control, or take ownership of the type.
  • Full-text catalog permissions: Permissions to reference, take ownership of, view the definition of, or control the catalog.
  • Service Broker permissions: Permissions on a specific Service Broker object. These vary slightly depending on the type of object.
  • Server principal permissions: Permissions to impersonate a given login account or to alter, view the definition of, take ownership of, or control the login.
  • Database principal permissions: Permissions to impersonate a given user or to alter, control, or view the definition of a specific database principal.
  • Object permissions: Permissions granted on a schema-scoped securable such as a table, view, or stored procedure, such as to execute or to perform select, delete, and other operations on the object. You can also specify ALL (or ALL PRIVILEGES) to grant all available permissions on the object.

We can’t cover every permission here, so please consult SQL Server Books Online for a full list of the permissions supported by each type of object.

The basic syntax for the GRANT statement is as follows:

GRANT <permission>
[ON [<securable type>::]<securable>]
TO <principal>
[AS {<group> | <role> }]

The ON clause is omitted for database permissions and server permissions, which apply to the current database or server, respectively, as a whole. The <securable type>:: syntax is not used for permissions on database objects such as tables, views, and stored procedures. The WITH GRANT OPTION and AS {<group> | <role> } clauses are optional in all cases. The former gives the grantee the ability to grant the permission in turn to other principals, and the latter indicates the name of a database group or role that the granter belongs to and that has the authority to grant permissions on the securable.

The syntax for DENY and REVOKE follows the same basic format.

Managing Permissions

The preceding (necessarily somewhat condensed) information shows how much control the DBA now has in granting permissions on specific objects. A wide range of permissions is available, and with the addition of the schema to the security hierarchy comes a finer degree of control. However, in the real world, permissions don’t occur singly, and managing permissions has two major complications:

  • Many permissions implicitly grant other permissions, and permissions combine so that there may be multiple permissions on the same object.
  • Objects call other objects, on which the principal may or may not have permissions.

Managing Schema Permissions

Schemas provide an extra layer in the authorization hierarchy that didn’t exist in earlier versions of SQL Server. For example, the database-level permission to create tables or other objects doesn’t actually mean anything unless you also have permission to add those objects to a specific schema, as database objects must now exist within a schema in the database. Conversely, as you saw earlier, the owner of a schema automatically has the right to perform select, delete, and other operations from objects within the schema, but not to create new objects in the schema. In order to allow that, you need to give the user the relevant permission at the database level.

USE AdventureWorks

This gives Jane the right to create tables in the database, and assuming she is the owner of the Finance schema created previously, she can now create new tables there. In order to create objects in a schema, a user needs ALTER permission on the schema, so Jane can’t create tables in the Sales schema unless you grant her this permission:

TO Jane

As you would expect, if there’s a conflict in permissions between the schema itself and an object in the schema, DENY overrides GRANT. For example, try to run this T-SQL code:

GRANT SELECT ON HumanResources.Department TO Jane
SELECT * FROM HumanResources.Department

This denies Jane access to the HumanResources schema, but tries to grant access to the Department table within it, and then switches to the Jane user and tries to read from the table. You’ll get the following error message:

SELECT permission denied on object 'Department', database 'AdventureWorks',
schema 'HumanResources'.

This means that there’s no way to explicitly deny access to all but a specific set of objects in a schema. You need to either grant access only to the required object, without configuring access to the remaining objects, or explicitly deny access to each of the objects that you don’t want the principal to access.

Ownership Chaining and Cross-Database Ownership Chaining

If a database object (such as a stored procedure) accesses another object (such as a table), SQL Server will skip permission checking for performance reasons if the owner of the two objects is the same. If the two objects have different owners, the chain of ownership will be broken, and SQL Server will check the permissions on the accessed object. This is the concept of ownership chaining.

While conceptually this works on paper, it is sometimes hard to implement in real life. Forcing the ownership of the table to the user who wrote the stored procedure, using our example, may not be suitable for some organizations. This is where using the EXECUTE AS clause can really be beneficial. The EXECUTE AS clause specifies exactly which security context you want a module, such as a stored procedure or function, to run in, as you saw in the “Impersonation” section earlier in this chapter. It also allows you to continue the chain of ownership, even across database boundaries.

Cross-database ownership chaining is used to allow access to resources on different databases without explicitly granting users access to the target database or tables within the target database. Careful planning is needed when using this feature, as enabling this feature raises some significant security concerns. For more information, read the “Ownership Chains” topic in SQL Server Books Online.

To use cross-database chaining in your SQL Server instance, you can enable it using the sp_configure stored procedure, and then run RECONFIGURE:

sp_configure 'cross db ownership chaining', 1

Let’s kick the tires by creating a new login called William and a couple new databases, with a simple table in each. These tables contain data on actual customers and on recipients who have signed up for a mailing list, for whom you have only an e-mail address. Once you’ve added some data to the tables, you’ll write a view that performs a union query on the two tables. You will also create a user based on the William login, to whom you grant permission to access the view, but neither of the tables directly. Note that the William user must exist in both of the databases, or the query will fail.

CREATE LOGIN William WITH PASSWORD = '?sdj7JS3&*(%sdp_';
USE CustomerData
FirstName nvarchar(255) NOT NULL,
LastName nvarchar(255) NOT NULL,
Email varchar(255) NOT NULL
INSERT INTO Customers VALUES ('John', 'Smith', '');
INSERT INTO Customers VALUES ('Jane', 'Jones', '');
USE MailingList
CREATE TABLE EmailAddresses
Email varchar(255) NOT NULL
INSERT INTO EmailAddresses VALUES('');
INSERT INTO EmailAddresses VALUES('');
CREATE VIEW vGetAllContactEmails
SELECT Email FROM EmailAddresses
SELECT Email FROM CustomerData.dbo.Customers;
GRANT SELECT ON vGetAllContactEmails TO William;
SETUSER 'William'
SELECT * FROM vGetAllContactEmails

When you set the current user to William, you should be able to access the view, even though it accesses data in another database. Because the view and the two tables it selects data from are all owned by the same user, the chain of ownership isn’t broken.

All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd Protection Status

SQL Server 2008 Topics