Securing Database Objects

SQL Server's security mechanism is both elegant and flexible. When SQL Server first came to be, it included a role-based security model where all roles and logins were defined within the database server. Users are similar to logins but defined at the database level. Users could be made members of a role and then permissions for various database objects could be assigned at the individual or role level. This approach met all of the necessary requirements except that it duplicated much of the security assignments managed by the network system. As SQL Server was integrated into the Windows platform, it made sense to integrate the existing Windows security model. Today, you have the option to use either Windows Integrated Security or both Windows and SQL Server security mechanisms to secure database objects. Whether or not you choose to utilize the Windows security integration is up to you, but this option is always enabled.

Typically, it makes sense to use Windows Integrated Security if you have the luxury of managing the network security as well. This is convenient considering you don't have to create duplicate login names and groups. There are some situations where it may not be feasible to use integrated security. On a departmental database server, where the server is managed separately from the corporate network, this can be a challenge. Another common exception is the Internet service provider that creates accounts for its customers to manage their databases on a common server. In this scenario, there may be no reason to allow access to any other network resources.

The coverage of this topic focuses on the language rather than the administrative tasks and practices. In brief, SQL Server defines eight fixed server roles that can be used to map various server and database object permissions. Logins defined at the server level may have membership in these roles. Logins can be defined using the SQL Server security model or can map to a user or group in Windows. At the database level, custom roles can be defined that may also be used to grant or deny object permissions. Users are defined at the database level that map to a login at the server level. This may seem a little complicated at first, but it really isn't. The short version is that users, in one form or another, are grouped into roles so that you don't have to assign permissions for every individual user. Ideally, all permissions are assigned to a role with the occasional exception for the user who needs to have special permissions or restrictions.

New in SQL Server 2005 is the ability to enforce complex passwords and password expiration on SQL Server logins. SQL Server retrieves the password policy from the local security policy on the server and uses it. If complex passwords are required on the server, then, by default, they will also be required for SQL Server logins. The same goes for password expirations.

Managing Security Objects

In SQL Server 2005, logins, users, and roles have their own corresponding CREATE and DROP statements. In SQL Server 2000, this administrative task is handled by system stored procedures.

The syntax for creating a new login is slightly different depending on whether the new login is a Windows login or a SQL Server login, as the following examples illustrate.

SQL Server 2005 SQL Server login:

 /************************************************** *    ********SQL SERVER 2005************** * Creates a new SQL Server login and then maps  * that login to a new database user ***************************************************/ USE Master GO CREATE LOGIN Paul WITH Password = ‘P@ssword1’ USE AdventureWorks2000 GO CREATE USER Paul FOR Login Paul SQL Server 2005 Windows login./************************************************** *    ********SQL SERVER 2005************** * Creates a new Windows login and then maps  * that login to a new database user ***************************************************/ USE Master GO CREATE LOGIN Adventureworks\Paul  USE AdventureWorks2000 GO CREATE USER WindowsPaul FOR Login AdventureWorks\Paul 

SQL Server 2000 SQL Server login:

 /************************************************** *    ********SQL SERVER 2000************** * Creates a new SQL Server login and then maps  * that login to a new database user ***************************************************/ USE Master GO sp_addlogin ‘Paul’, USE AdventureWorks2000 GO sp_grantdbaccess ‘Paul’, 

SQL Server 2000 Windows login:

 /************************************************** *    ********SQL SERVER 2000************** * Creates a new Windows login and then maps  * that login to a new database user ***************************************************/ USE Master GO sp_grantlogin  USE AdventureWorks2000 GO sp_grantdbaccess  

In SQL Server 2000, Windows logins are removed with the sp_revokelogin system stored procedure. Users are removed with the sp_revokedbaccess procedure. In SQL Server 2005, the DROP LOGIN and DROP USER commands are used.

Data Control Language

Three SQL statements are used to control permission to all database objects and securable user resources (that is, users and roles). Each statement accepts the permission type (Select, Insert, Update, Delete, Execute, and so on), the object name, and the user or role to which the setting applies.

GRANT

To grant permission is to give or allow permission to perform a type of operation on an object. The following are examples:

 GRANT INSERT ON Product TO Paul GRANT EXEC ON spDeletePurchaseOrders TO Paul 

DENY

The DENY statement is used to explicitly prohibit a user or role members from performing a specific action on an object. Even if a user is a member of a role or is otherwise granted permission, they will not be able to perform the action if they are denied permission explicitly or through any role membership:

 DENY INSERT ON Product TO Paul DENY EXEC ON spDeletePurchaseOrders TO Paul  

REVOKE

This statement is often misunderstood, as the term revoke means to take away. Revoking a permission doesn't necessarily mean that a user loses the ability to perform an action. To revoke permission means to remove the current set of permissions for an object and user or role. This could have the effect of removing an explicit GRANT or DENY, if either exists. This would cause the permission set for a user to revert to those applied through a role membership or to the default permissions.

 REVOKE INSERT ON Product TO Paul REVOKE EXEC ON spDeletePurchaseOrders TO Paul