Security and Auditing on SQL Server

Security Features

Integrity and security are critical requirements with on-line applications that involve data access by dozens, hundreds, or even thousands of users. Integrity refers to the accuracy and consistency of data. Security means authorizing and controlling access to data. SQL Server addresses these requirements by enforcing integrity and security centrally, rather than in each application that accesses the data.

This approach provides important benefits:

• High application reliability. Centralized data integrity and security cannot be circumvented by any application, user, or tool.

• Reduced development costs. The development cycle is significantly shortened because integrity and security logic and business rules are coded only once and stored in the database, instead of being written over and over again for each application.

• Increased database security. Rules and transactions, as well as tables, views, and fields, are protected from unauthorized access.

• User logins and data access can be audited by SQL Server.

A separate Sybase product, Secure SQL Server, is targeted at the Class B1 requirements. It adds mandatory access controls based on sensitivity labels associated with data and users. Granularity of access can be as fine as an individual data row.

This chapter describes the SQL Server security features that protect the system and its data against unauthorized use. It also describes the mechanisms available for identifying authorized users and keeping track of the use of system resources.This chapter contains the following sections:

• ``Security Features''

• ``User Identification''

• ``User Roles''

• ``Ownership Types''

• ``Managing User Identifications and Permissions''

• ``Managing Users: An Overview''

• ``User Permissions''

• ``Views and Stored Procedures as Security Mechanisms''

• ``Auditing in SQL Server''

• ``Establishing Auditing''

User Identification

• Minimum password length of six bytes, which makes passwords more secure

• Passwords stored in encrypted form

• Optional system-wide password expiration

• Optional client-side password encryption

• Recovery mechanisms in case of lost or expired passwords

• Login account locking, which makes it possible to lock a user's account without dropping the user and the user's objects from all databases

The System Administrator

• Installing SQL Server

• Managing disk storage

• Granting permissions to SQL Server users

• Modifying, dropping, and locking server login accounts

• Monitoring SQL Server's automatic recovery procedure

• Diagnosing system problems and reporting them as appropriate

• Fine-tuning SQL Server by changing the configurable system parameters

• Creating and granting ownership of user databases

• Granting and revoking the System Administrator role

There are several commands and system procedures that only a System Administrator can execute, and on which permissions cannot be transferred to other users.

System Security Officer

• Creating server login accounts

• Granting and revoking the System Security Officer and Operator roles

• Changing the password of any account

• Setting the password expiration interval

• Managing the audit system

The Operator

Managing Roles

• Grant the appropriate roles to individual login accounts

• Lock the "sa" login

• Establish auditing of all commands that require a special role

• Display the roles for a specific login system procedure

• Limit execution of stored procedures to logins with a specific role

Ownership Types

• Owners of user databases

• Owners of database objects

The owner of a database may:

• Allow other SQL Server users access to the database

• Grant other users permission within the database to create objects and execute commands

• Set up groups for convenience in granting and revoking permissions

A database object owner is automatically granted all permissions on that object. He or she can see and modify the data, or drop the object. Other users must be granted explicit access permission on that object by its owner in order to select or modify the data. Even the Database Owner cannot use an object directly unless the object owner grants him or her the appropriate permission.

Managing User Identifications and Permissions

• Create the login account on the server: this allows users to log in, but does not give them access to any information.

• Add the user to the database(s) he or she needs to access: this allows the users to "use" a database, where they can execute any commands or procedures which "public" can perform.

• Add the user to a group in the database, if desired: groups provide a convenient way of controlling the permissions of many users with a single grant or revoke command.

• Grant the user permission to access specific data or to create database objects, if the permissions are different from "public" permissions or the user is not part of a group.

The following sections summarize the system procedures used in managing user accounts. The permission column in the following tables specifies the lowest default permission level.

Managing Users: An Overview

Adding Login Accounts, Users, and Groups

Dropping Logins, Users, and Groups

Locking SQL Server Logins

Changing User Information

Using Aliases in Databases

For example, say several vice presidents want to use a database with identical privileges and ownerships. One way to accomplish this is to add a login named "vp" to SQL Server and the database; each vice president logs in as "vp". The problem with this method is that there is no way to tell the individual users apart. The other approach is to alias all the vice presidents, each of whom has his or her own SQL Server account, to the database user name "vp".

The following system procedures manage aliases:

Table 10-5: System procedures for managing aliases

System procedure	Task	Executed by	Where
sp_addalias	Add an alias for a user	DBO or SA	User database
sp_dropalias	Drop an alias	DBO or SA	User database

Getting Information About Users

By default, all users can execute these system procedures.

User Permissions

Some Transact-SQL commands can be issued at any time by any user, with no grant permission required. Other Transact-SQL commands can be used only by users of certain status (for example, only by the System Administrator). Permission to use certain commands is not transferable with grant.

By using the with grant option clause to the grant command, the Database Owner or System Administrator can give other users the ability to grant permission to access database objects. For example, if the Database Owner grants select permission on the titles table to user "mary" specifying with grant option, user "mary" can grant the same permission to other users. A similar option for the revoke command, cascade, revokes permissions from "mary" and from everyone to whom "mary" granted permission.

Permissions to Create and Access Objects

• Permissions for the commands select, update, insert, delete, and execute are called object access permissions because these commands always specify database objects, and allow users to access or change data.

  These are the objects to which the object access permissions can apply:

  Table 10-7: Object access permissions

  Command	Objects
  select	table, view, columns
  update	table, view, columns
  insert	table, view
  delete	table, view
  execute	stored procedure

  Here is an example:

  grant select on titles to mary
  
  

  revoke select on titles(advance) from mary
  
  

  This sequence of commands gives user "mary" access to all of the columns in the titles table except the advance column.

• Permissions for other commands are called object creation permissions because they give the user permission to create database objects. They can be granted only by a System Administrator or a Database Owner, and do not take the with grant option clause. These commands are:
  • create database

  • create default

  • create procedure

  • create rule

  • create schema

  • create table

  • create view

    For example:

    grant create table to mary
    
    

If you try to use a command or database object for which you have not been assigned permission, SQL Server responds with an error message.

The Permission Hierarchy

Database Owners are next in the hierarchy. System Administrators and Database Owners can grant and revoke object creation permissions to other users (so that they become the next level: database object owners).

At the next lower level are the owners of database objects (tables, views, and stored procedures), who control access permission on their objects. For example, a user who creates (and therefore owns) a table automatically has all of the object access permissions that apply to that table¯select, insert, update, and delete. No other users have any permissions on the table until the owner specifically grants them with the grant command.

At the bottom of the hierarchy are the other database users (also known as "public"). Permissions are granted to or revoked from them by object owners, Database Owners, and/or the System Administrator. These users are specified by user name, by group name, or as members of "public".

Views and Stored Procedures as Security Mechanisms

Views

Permission to access data in a view must be explicitly granted or revoked, regardless of the set of permissions in force on the view's underlying table(s). Data in an underlying table that is not included in the view is hidden from users who are authorized to access the view but not the underlying table.

By defining different views and selectively granting permissions on them, an owner can restrict a user (or any combination of users) to different subsets of data:

• Access can be restricted to a subset of the rows of a base table (a value-dependent subset). For example, you might define a view that contains only the rows for business and psychology books, in order to keep information about other types of books hidden from some users.

• Access can be restricted to a subset of the columns of a base table (a value-independent subset). For example, you might define a view that contains all the rows of the titles table, but omits the royalty and advance columns, since this information is sensitive.

• Access can be restricted to a row-and-column subset of a base table.

• Access can be restricted to the rows that qualify for a join of more than one base table. For example, you might define a view that joins the titles, authors, and titleauthor tables in order to display the names of the authors and the books they have written. This view would hide personal data about authors and financial information about the books.

• Access can be restricted to a statistical summary of data in a base table. For example, you might define a view that contains only the average price of each type of book.

• Access can be restricted to a subset of another view, or to some combination of views and base tables.

• Control over data that can be inserted or updated through a view can be controlled by the with check option clause of the create view command.

Auditing in SQL Server

SQL Server can be configured to provide an audit trail for events such as:

• Server logins and logouts

• Use of any commands that require a special role

• Use of commands that reference a specified object or database

• Deletion of objects

• Execution of stored procedures and triggers

• Any actions performed by a specified user

• Support for multiple audit tables. This capability enables installations to archive and process audit data with no manual intervention and with no loss of audit records.

• The capability to audit additional events, including the creation of objects; the binding and unbinding of rules, defaults, and messages; all actions by individual users; and all actions performed with a particular role active.

• The addition of a single flag for auditing a set of server-wide security-relevant events.

• A new, streamlined, user interface available for the System Security Officer to control auditing. In the new interface, which will replace the current auditing interface, a single system procedure provides the capability to set all auditing options.

The Audit System

• The sybsecurity database

• System procedures that set the various auditing options

• The audit queue, to which audit records are sent before they are written to the audit trail

• sysaudits ­ the audit trail

• sysauditoptions ­ contains the global auditing choices

The Audit Queue

Establishing Auditing

• Execute any of the auditing system procedures (with the exception of sp_addauditrecord)

• Read the audit trail

• Access the audit database

Auditing can be established at two levels:

• At the server level, to audit specific activities on the entire server

• At the database level, to audit specific events within a specific database

• Logins

• Logouts

• Actions by individual login accounts in all databases on the server, including failed and/or successful accesses to views and tables and the text of the user's commands

• Server boots

• Remote procedure calls

• Use of the set role command

• Commands that require special server roles, such as the System Administrator role, the System Security Officer role, or the Operator role

• Fatal errors

• Ad hoc additions to the audit trail

• Use of the drop, grant, revoke, and truncate table commands within a database

• Use of the drop database and use commands on a database

• Execution of SQL commands from within another database that reference the audited database

• Activate auditing of accesses to specified tables or views

• Set default audit settings that will be applied to all future tables and views in the database

• Audit successful or failed attempts to access an object, or both successful and failed attempts

• Specify what kind of queries to audit: selects, inserts, updates and deletes, in any combination

• Establish auditing for stored procedures and triggers

update_acct 23475, 200.00

create proc update_acct  @acct int,

                         @amount smallmoney

as

update acct set amount = amount + @amount 

where acct = @acct

sp_auditsproc update_acct, pubs2, ok

select eventtime, loginame, extrainfo

from sysaudits

where objname = "upd_acct"

order by eventtime

eventtime           loginname     extrainfo

------------------- ------------- --------------------

Oct 19 1993  4:44PM henry         23475, 200.00

Oct 19 1993  4:49PM lulu          22376, -30.89

Oct 19 1993  4:49PM lulu          22376, 1057.23

Oct 19 1993  4:55PM carol         67892, 98.73