Article

A quick review of SQL Server security concepts with enhancements

Bob Beauchemin, Niels Berglund and Dan Sullivan

The following excerpt, courtesy of Addison-Wesley, is from Chapter 6 of the book "A First Look at SQL Server 2005 for Developers" written by Bob Beauchemin, Niels Berglund and Dan Sullivan. Click for the complete book excerpt series

    Requires Free Membership to View

or purchase the book.



A quick review of SQL Server security concepts with enhancements

SQL Server 2005 extends the SQL Server security system to handle .NET assemblies and the calls that these assemblies might make. With a .NET assembly, we are concerned with both internal calls (calls to other assemblies, or "ordinary" stored procedure and table access) and external calls (calls from a .NET assembly to the world outside the SQL Server process) in SQL Server 2005. Because the new security is based on the original SQL Server security system, and there have been many enhancements to SQL Server security in SQL Server 2005, it helps to start with a quick review of how SQL Server security works in general.

Authentication and Authorization -- Principals and Permissions

Any security system is concerned with two main concepts: authentication and authorization. Authentication is the process of identifying a principal; authorization is determining what operations a principal can perform. In SQL Server a login is a principal, and authorization is done by SQL Server when a user logs in to it. For example, once a user -- let's call him Bob -- has identified himself to the SQL Server by logging in (that is, SQL Server is aware the user session "5" is actually "bob"), he can do whatever operations, such as add records to tables or query tables, he has authorization to do.

A SQL Server process is called an instance, as in "an instance of the service process sqlservr.exe." One or more SQL Server instances can run on one machine running Windows. Authentication is done per instance of SQL Server. Principals -- that is, logins -- that can log in to an instance, are originally identified to SQL Server by using DDL and stored in a system table, syslogins. In SQL Server 2005 this information is exposed through sys.server_principals and sys.sql_logins.

SQL Server supports Windows users and SQL Server users as logins, but it may be configured to support only Windows users as logins, which is recommended practice. Windows users may be local to the machine running SQL Server or users in the Active Directory for the enterprise to which SQL Server belongs. SQL Server no longer supports permitting only SQL Server users.

In SQL Server 2000, the DBA defines Windows logins to SQL Server using the sp_grantlogin system stored procedure. SQL Server -- defined users are assigned their login name, password, and (optionally) language and default database using the sp_addlogin system stored procedure. These system stored procedures still exist in SQL Server 2005 but should be considered deprecated; the equivalent functionality is exposed in the CREATE LOGIN DDL statement in SQL Server 2005, which should be used instead.

Authenticated logins of a particular SQL Server instance can be mapped to principals called users in a particular database. Each login can have a default database; logging in will drop the user into this database. Logins may also be allowed to access databases other than their default database, as defined by the administrator. In SQL Server, the term "user" refers to a "login's" identity in a particular database. Depending on how users are mapped to logins by the administrator, the user ID for a particular user in a particular database may not be the same as the login ID. However, a login can have only a single user in a given database.

CREATE LOGIN bob WITH PASSWORD='m8b#5pL'
GO

USE PUBS
GO

CREATE USER pubsbob FOR LOGIN bob
GO

--the following will fail, login bob
-- may only have a single user in a database
CREATE USER otherPubsBob FOR LOGIN bob

Figure 6-1 shows the relationship between logins and users.
Figure 6-1 shows an instance of SQL Server with two logins: bob and niels. The login bob is mapped to the user bob in the pubs database. The login bob has a default database of pubs, so when bob logs in, he will, by default, be referencing the pubs database as user bob. The login niels is


mapped to the user nielsB on the database pubs, but the default database for this login is nwnd. The login niels will have to use a three-part name to reference pubs, and when he does, he will be referencing it as the user nielsB. The login niels is also mapped to the nwnd database as user niels, which is the default database for that login. When niels logs in, he will, by default, reference the nwnd database and do so as the user niels. Note that login niels has a different user name on pubs than on nwnd.

If a user is granted permission to access a database, but not assigned a user identity for the database, he accesses the database as a user called "guest."

A SQL Server user in a particular database can be permitted to perform DDL and DML operations; therefore, a database in SQL Server (rather than an instance) is usually the authorization domain. Users are granted permissions using the GRANT DDL statement, such as the following:

GRANT SELECT ON SOMESCHEMA.SOMETABLE TO SOMEUSER

In SQL Server 2005, permissions can also be granted on instance-level objects like ENDPOINTs and even commands like SHUTDOWN.

Permissions on a database table, view, or stored procedure, as well as other database object permissions, are granted to a user by the object's owner. A permission can be granted to each user, but this would be cumbersome and unwieldy as many users are added to the database. Therefore, SQL Server users can be grouped into roles. In addition to custom roles -- which can be defined, for example, for users of a specific application -- a few special, system-defined roles exist. The only two special roles we'll mention here are DBO, the database owner role, and public, the general public. Multiple users can be assigned to the DBO role for a particular database, and this role gives the user unlimited privilege inside that database. The role "public" refers to anyone who has access to a particular database. In addition to roles, SQL Server authorization is aware of Windows groups, if database users were derived from Windowsbased logins.

In addition to user-based security, SQL Server also has the notion of application-based security. Some administrators might wish to permit or deny access based on the current application, rather than the user. For this purpose, SQL Server permits definition of application roles. Application roles are different from roles in that they contain no users. In fact, when a SQL Server user assumes an application role, because code in an application (for example, an inventory control application) issues a statement such as the following:

sp_setapprole @role='inventoryrole',
                   @password= {Encrypt N 'inventorypassword'}, 'Odbc'

the user gives up all the permissions granted him. The user can only access those database objects that the role has permission to access.

The following object-level permissions can be granted to a user, a role, an application role, or a Windows group in SQL Server 2000:

  • SELECT

  • UPDATE

  • DELETE

  • INSERT

  • EXECUTE

  • REFERENCES

In SQL Server 2000, SELECT, INSERT, UPDATE, and DELETE are permissions that can be granted on SQL Server tables and views. EXECUTE permission can be granted on a stored procedure or user-defined function. REFERENCES permission means that a user who is the owner of table A can define a foreign key on table B, which she does not own, if she has REFERENCES permission on table B. REFERENCES permission is also used to allow a user to define a VIEW or UDF using the SQL WITH SCHEMABINDING option if the VIEW or UDF uses a table or view that is owned by a different owner. You can also use GRANT to grant a permission and give the user permission to grant access to others; this is called GRANT WITH GRANT OPTION.

SQL Server 2005 adds the following new object-level permissions:

  • ALTER

  • CONTROL

  • RECEIVE (service broker queues)

  • TAKE OWNERSHIP

  • VIEW DEFINITION

If a user is permitted to create objects like tables, views, or stored procedures, when the user issues the CREATE statement, she becomes the owner of the object. Object owners have unlimited permissions on objects they create, including the permission to grant other users permissions. One special case is that if the user is a member of the DBO role, objects created by that user are considered owned by DBO. Because different users can define objects having the same name -- that is, BOB.SOMETABLE and MARY. SOMETABLE -- many SQL Server installations prefer to have all database objects defined and owned by DBO, although this can be done differently in SQL Server 2005 with the introduction of schemas (discussed later in the chapter). This simplifies determining permissions, which can become quite complicated, as we'll see later in this chapter.

As an aside, when a Windows system administrator or the SQL Server system administrator account, known as "sa," logs on to a SQL Server instance, she is automatically granted the DBO role in every database by default. Programmers should always test their programs by running the program when logged on to Windows as nonadministrators, to prevent surprises at deployment time. Obviously, security makes SQL DML statements act differently depending on the user that executes them. In addition, because changing databases inside a SQL Server instance using the USE SOMEOTHERDATABASE statement is an expensive operation, a best practice when using SQL Server from an application server (where like sessions will be pooled using connection pooling) is to define a special user for each distinct application in the application server, and give that user a default database that is appropriate for that application. Of course, when users must be distinctly identified inside SQL Server -- for example, for auditing -- this is not possible, although each user should have a default database that corresponds to the database he will be working in most often.

Execution Context and Ownership Chaining

When you execute a stored procedure or user-defined function or use a view prior to SQL Server 2005, access to objects inside the stored procedure occurs using the identity of the caller of the stored procedure. Many people don't recognize this at first glance, because permissions are only checked when the owner of the stored procedure is different from the owner of the object the stored procedure is accessing. This permits giving users access to database tables only through sanctioned stored procedures, while denying them access to the underlying tables directly.

In the following example, let's say the same user, FRED, owns both the employee table and the update_salary stored procedure. FRED does not grant BOB permission to access the employee table but does grant BOB permission to execute the update_salary stored procedure. BOB can now update the employee table, but only through the stored procedure.

--Logged in as FRED
CREATE TABLE employee (
  -- other fields elided for clarity
  emp_id INT,
  name VARCHAR(20),
  address VARCHAR(50),
  phone VARCHAR(15),
  salary_grade INT,
  salary MONEY
)
go

-- procedure for update
CREATE PROCEDURE update_salary(
 @EMP_ID INT,
 @NEW_SALARY MONEY)
AS
UPDATE employee SET salary = @NEW_SALARY
  WHERE emp_id = @EMP_ID
go

-- BOB can only execute the procedure
GRANT EXECUTE ON update_salary to BOB
go

If the same user (FRED) owns both the stored procedure and the table, permissions are never checked when the stored procedure accesses the table. Because BOB has EXECUTE permission on the stored procedure, the stored procedure works.

However, if FRED alters the store procedure to also access another table, owned by a different user (say, ALICE), inside the stored procedure, the ability of BOB (not FRED) to access the salary_audit table is checked.

-- procedure for update
-- FRED owns the PROCEDURE
ALTER PROCEDURE update_salary(
 @EMP_ID INT,
 @NEW_SALARY MONEY)
AS
-- FRED owns the employee table
UPDATE employee SET salary = @NEW_SALARY
  WHERE emp_id = @EMP_ID

-- But ALICE owns the salary_audit table
INSERT INTO alice.salary_audit values(@EMP_ID, @NEW_SALARY)
go


Figure 6-2 illustrates ownership chaining.
Notice two things about this diagram.

  • Because BOB is invoking a stored procedure owned by FRED, BOB must have EXECUTE permission on the stored procedure. He need not have access to the underlying tables as long as FRED also owns the tables.

  • Because FRED is the owner of the stored procedure but does not own the alice.employee table, permission of the caller (BOB) on the alice.employee_table is checked.

In general, SQL Server checks permissions only when an object accesses an object that is not owned by the same accessor. If the same user owns two objects and one object accesses the other, permissions are not checked at all, at any level. This is known as an ownership chain. If, at any level, an object accesses another object that is not owned by the accessor, permissions are checked because the ownership chain has been broken.

Until SQL Server 2000 SP3, ownership chains were permitted across databases. After SP3, this is no longer the default, but you can enable crossdatabase ownership chains on a per-database basis as an option, using the following statements.

USE master
EXEC sp_dboption MyDatabase, 'db chaining', 'true'
GO

In addition, permissions are always checked when you are using dynamic SQL inside a stored procedure. Adifferent implementation of the update_salary stored procedure is shown in the following code.

-- procedure for update
-- FRED owns the PROCEDURE
ALTER PROCEDURE update_salary(
 @EMP_ID INT,
 @NEW_SALARY MONEY)
AS
-- FRED owns the employee table
-- table is accessed through dynamic SQL
execute ('UPDATE employee SET salary = @NEW_SALARY
  WHERE emp_id = @EMP_ID')
go

In this version, access is always checked when dynamic SQL is invoked, regardless of the owner of the object that the dynamic SQL statement accesses. Because we're using dynamic SQL, when BOB executes the stored procedure, BOB's access to the employee table is checked. Because BOB does not have access to the table, the stored procedure fails.

SQL Server 2005 refines the concept of ownership chaining to deal with the concept of schemas and introduces the notion of execution context other than "current user." Setting execution context can solve the dynamic SQL problem just described but must be managed carefully. We'll discuss it later in this chapter.

Click for the next excerpt in this series: SQL Server password policies and credentials


Click for the book excerpt series or visit here to obtain the complete book.

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to: