Data Control Language


This is by far the simplest subset of the SQL language. The goal of Data Control Language (DCL) is to manage users' access to database objects. After the database has been designed and objects are created using DDL, a security plan should be implemented to provide users and applications with an appropriate level of access to data and database functionality, while protecting the system from intrusion. Access privileges can be controlled at the server or database level and groups of privileges can be assigned to individual users and to groups of users who are assigned role membership. Although database security involves simple concepts, it is not a task to be approached in a haphazard manner. It's important to devise a comprehensive plan and to consider all of the business requirements and the organization's security standards when devising a database security plan.

SQL Server recognizes two separate security models. These include SQL Server Security, where roles and users are managed entirely within the database server, and Integrated Windows Security, which maps privileges to groups and users managed in a Windows-based network system. This topic is discussed in greater detail in Chapter 10, but some of the basic principles are explained in this section.

The easiest way to think about permissions is in layers. Because users can have memberships to multiple roles, they may have a mixed set of privileges for different database objects. Like placing multiple locks on a door, a user can only gain access to an object if all restrictive permissions are removed and they have been granted access through at least one role membership. Using the lock analogy, if you had a key to one of three locks, you would not be able to open the door. Likewise, if a user is a member of three roles, two of which are denied access to an object, access won't be allowed even if it is explicitly granted. The user must be either removed from the restrictive roles or these permissions must be revoked.

In short, DCL consists of three commands that are used to manage security privileges for users or roles on specific database objects:

  • The GRANT command gives permission set to a user or role.

  • The DENY command explicitly restricts a permission set.

  • The REVOKE command is used to remove a permission set on an object.

Revoking permissions removes an explicit permission (GRANT or DENY) on an object so that permissions that may have been applied at a less-specific level are used. Before permissions can be applied to objects, users and roles are defined. SQL Server provides a set of standard roles for both the database server and for each database. You learn how to manage permissions for roles and users in Chapter 10.

Following are some examples. This statement grants SELECT permission to the user Paul on the Product table:

 GRANT SELECT ON Product TO Paul 

Tables and views have permissions to allow or restrict the use of the four DML statements: Select, Insert, Update, and Delete. Stored procedures and functions recognize the EXECUTE permission. On tables, views, and functions, permissions can also be given or restricted on a user's ability to implement referential integrity, using the DRI permission.

This example grants EXECUTE permission to members of the db_datawriter built-in role but denies this permission to a user named Martha:

 GRANT EXECUTE ON spAddProduct TO db_datawriter  DENY EXECUTE ON spAddProduct TO Martha 

Multiple permissions can be applied on an object by placing permissions in a comma-delimited list, as in the following:

 GRANT SELECT, INSERT, UPDATE ON Product TO Paul 

An important aspect to remember about SQL Server security is that SQL Server does not enforce logical combinations of permissions. For example, assume that user Paul is a member of a security role called Authors and the following DCL scripts are executed:

 GRANT UPDATE ON PublishedBooks TO Authors  DENY SELECT ON PublishedBooks TO Paul 

Because Paul is a member of the Authors role he inherits the UPDATE permission granted to that role. He was also specifically denied the SELECT permission on the table PublishedBooks. The logical assumption would be that Paul could not update the PublishedBooks table, but this assumption would be wrong. Paul cannot update any specific rows due to this permission combination so the following command would fail:

 UPDATE PublishedBooks SET Author = 'Paul Turley' WHERE BookID = 222 

However this command would succeed:

 UPDATE PublishedBooks SET Author = 'Paul Turley' 

Because the WHERE expression is in essence a select command that is processed prior to the update, Paul is prevented from making the change. Unfortunately, Paul is a savvy SQL user and he knows that by updating all the rows in the table he circumvents the denied select permission and changes all the published book records to show that he is the author. The moral to this story is to use care and planning when applying permissions.

This short discourse should have provided a cursory introduction to the concepts and practices of DCL. As previously mentioned, like database design, security is a matter that should be carefully planned and implemented in a uniform and standard approach. It's usually best to have a small number of database administrators charged with the task of security to keep tight reigns over how privileges are applied for users of a database.




Beginning Transact-SQL with SQL Server 2000 and 2005
Beginning Transact-SQL With SQL Server 2000 and 2005
ISBN: 076457955X
EAN: 2147483647
Year: 2006
Pages: 131
Authors: Paul Turley

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net