Sql server 2005 Security Best Practices Operational and Administrative Tasks

Authorization

In SQL Server, authorization is accomplished via Data Access Language (DAL) rather than DDL or DML. In addition to the two DAL verbs, GRANT and REVOKE, mandated by the ISO-ANSI standard, SQL Server also contains a DENY DAL verb. DENY differs from REVOKE when a user is a member of more than one database principal. If a user Fred is a member of three database roles A, B, and C and roles A and B are GRANTed permission to a securable, if the permission is REVOKEd from role C, Fred still can access the securable. If the securable is DENYed to role C, Fred cannot access the securable. This makes managing SQL Server similar to managing other parts of the Windows family of operating systems.

SQL Server 2005 makes each securable available by using DAL statements and makes permissions more granular than in previous versions. For example, in SQL Server 2000 and earlier versions, certain functions were available only if a login was part of the sysadmin role. Now sysadmin role permissions are defined in terms of GRANTs. Equivalent access to securables can be achieved by GRANTing a login the CONTROL SERVER permission.

An example of better granularity is the ability to use SQL Server Profiler to trace events in a particular database. In SQL Server 2000, this ability was limited to the special dbo user. The new granular permissions are also arranged in a hierarchy; some permissions imply other permissions. For example, CONTROL permission on a database object type implies ALTER permission on that object as well as all other object-level permissions. SQL Server 2005 also introduces the concept of granting permissions on all of the objects in a schema. ALTER permission on a SCHEMA includes the ability to CREATE, ALTER, or DROP objects in that SCHEMA. The DAL statement that grants access to all securables in the payroll schema is:

A best practice for authorization is to encapsulate access through modules such as stored procedures and user-defined functions. Hiding access behind procedural code means that users can only access objects in the way the developer and database administrator (DBA) intend; ad hoc changes to objects are disallowed. An example of this technique would be permitting access to the employee pay rate table only through a stored procedure "UpdatePayRate." Users that need to update pay rates would be granted EXECUTE access to the procedure, rather than UPDATE access to the table itself. In SQL Server 2000 and earlier versions, encapsulating access was dependent on a SQL Server feature known as ownership chains. In an ownership chain, if the owner of stored procedure A and the owner of table B that the stored procedure accesses are the same, no permission check is done. Although this works well most of the time, even with multiple levels of stored procedures, ownership chains do not work when:

• 
  The database objects are in two different databases (unless cross-database ownership chaining is enabled).
  
• 
  The procedure uses dynamic SQL.
  
• 
  The procedure is a SQLCLR procedure.
  

A login only can only be granted authorization to objects in a database if a database user has been mapped to the login. A special user, guest, exists to permit access to a database for logins that are not mapped to a specific database user. Because any login can use the database through the guest user, it is suggested that the guest user not be enabled.

SQL Server 2005 contains a new type of user, a user that is not mapped to a login. Users that are not mapped to logins provide an alternative to using application roles. You can invoke selective impersonation by using the EXECUTE AS statement (see Execution Context later in this paper) and allow that user only the privileges needed to perform a specific task. Using users without logins makes it easier to move the application to a new instance and limits the connectivity requirements for the function. You create a user without a login using DDL:
CREATE USER mynewuser WITHOUT LOGIN
Best practices for database object authorization

• 
  Encapsulate access within modules.
  
• 
  Manage permissions via database roles or Windows groups.
  
• 
  Use permission granularity to implement the principle of least privilege.
  
• 
  Do not enable guest access.
  
• 
  Use users without logins instead of application roles
  

Catalog Security

SQL Server 2005 makes all metadata views secured by default. This includes:

• 
  The new metadata views (for example, sys.tables, sys.procedures).
  
• 
  The compatibility metadata views (for example, sysindexes, sysobjects).
  
• 
  The INFORMATION_SCHEMA views (provided for SQL-92 compliance).
  

Some applications present lists of database objects to the user through a graphic user interface. It may be necessary to keep the user interface the same by permitting users to view information about database objects while giving them no other explicit permission on the object. A special permission, VIEW DEFINITION, exists for this purpose.

Best practices for catalog security

• 
  The catalog views are secure by default. No additional action is required to secure them.
  
• 
  Grant VIEW DEFINITION selectively at the object, schema, database, or server level to grant permission to view system metadata without conferring additional permissions.
  
• 
  Review legacy applications that may depend on access to system metadata when migrating the applications to SQL Server 2005.