Implementing Security

SQL Server has a built-in object security model that enables you as the system administrator to grant certain other individuals the rights to read and make changes to your databases. After all, you're a programmer or a system administrator ”do you want to be doing all the data entry and reporting for your database? Probably not. Other people are going to have access to your data, and this section looks at how to keep those folks from inadvertently or maliciously messing things up.

The SQL Server Login/ User Model

You need to learn some very strict terminology before you'll understand what's going on with security. This chapter uses terms, such as "individual" or "person," to describe an actual bipedal carbon-based life form. The term "login" describes a SQL Server login, whereas the term "NT Login" describes an operating system login. Finally, the word "user" relates to how a specific login interacts inside a database.

NOTE

Operating System Security This section makes the somewhat large assumption that you or someone in your organization has gone about the task of securing the operating system on the computer your database is on ”simple things, such as putting it behind a firewall, ensuring that the administrator account has a password, and so on. There are a lot of really great books on how to do this; check the "Suggested Reading" section at the end of the chapter.

In SQL Server, there are two security modes. In Integrated security mode, each NT Login is mapped to exactly one SQL Server login. No passwords are stored in SQL Server in this case; instead, SQL Server accepts a token from the operating system, which basically says that the operating system trusts that the login is valid.

Mixed security mode includes Integrated security mode and what used to be called Standard security mode. With Standard mode, each login is created in SQL Server; SQL Server retains passwords for all the logins; and SQL Server is responsible for authenticating users. Microsoft keeps trying to bump off Standard security, but it's the only way that people using other operating systems or certain types of applications can use SQL Server.

Logins are stored in the Master database, complete with their passwords, which are stored encrypted. Each login is associated with one or more users. The user is associated with one database, and you must give each login access to each database it needs access to by creating a user in the database and linking it back to the login.

To add a login for a SQL Server user who has a Windows NT account, you use the sp_grantlogin command, like this:

 sp_grantlogin 'MYDOMAIN\Doug' 

This creates a login for the NT user for integrated security. To add a user who doesn't have a user account, use this:

 sp_addlogin 'doug', 'dougspassword' 

To add a user to a database, you can use the sp_adduser command, or the newer sp_grantdbaccess command:

 sp_grantdbaccess 'doug' go --or sp_adduser 'doug'  -but not both! 

If you do run both commands you'll get an error, because they both do the same thing. They both add a user linked to the login specified as the parameter to the current database. That's important: The user is added to the current database, so you need to make sure you know what database you are in before you execute those commands. Performing these operations in Enterprise Manager is the task of one of the end-of-chapter exercises.

SQL Server implements two types of permissions: the Statement permission and the Object permission. Statement permissions are permissions to execute certain statements, whereas Object permissions are permissions to access certain objects in specified ways.

Statement Permissions

Statement permissions are assigned to users to enable them to do things, such as create databases, define user-defined functions and stored procedures, and back up the database or transaction log. Statement permissions are assigned by using the GRANT statement, like this:

 GRANT <statement> TO <account list> 

The <statement> includes statements that create or destroy objects, such as CREATE DATABASE , DROP DATABASE , CREATE DEFAULT , DROP DEFAULT , CREATE FUNCTION , and so on, along with statements that perform other tasks , such as BACKUP DATABASE and BACKUP LOG . The <account list> is a comma-delimited list of security accounts or roles that you want to grant access to.

Object Permissions

Object permissions are permissions granted to access objects in certain ways. For tables and views, you can grant SELECT , DELETE , UPDATE , and INSERT permissions, and for stored procedure and function objects you can grant EXECUTE permissions. Permissions are granted to users, so the user must exist in the database before you grant permission. To give a user permission to access certain database objects, use the GRANT command, but with a different syntax:

 GRANT <permission> ON <object> TO <account list> 

So, to grant a particular user permissions to read a table called MyTable , you'd execute:

 GRANT SELECT ON MyTable TO Doug 

This grants Doug permission to select data from your table. If you wanted to give Doug permissions to do anything to your table short of dropping it, you could run either of these two statements:

 GRANT ALL ON MyTable TO Doug GRANT SELECT, INSERT, UPDATE, DELETE ON MyTable TO Doug 

That's nice, but you also want to let Doug give other people permissions on the table, because you've gotten too busy. So, do something like this:

 GRANT ALL ON MyTable TO Doug WITH GRANT OPTION 

This enables Doug to grant permissions to other users on that object, up to the level of permissions that Doug has. So, if you give Doug only SELECT permissions, he can grant only SELECT permissions.

Now you've given Doug permission to change your table. That's probably not good given that Doug's boss fired him yesterday . Now what are you going to do? Good thing there's that REVOKE command.

 REVOKE ALL ON MyTable FROM Doug 

The REVOKE command works for statement permissions and object permissions, and it looks just like the GRANT statement in that you can revoke SELECT , INSERT , UPDATE , DELETE , or any combination thereof. What about revoking just the ability to grant permissions to other users?

 REVOKE GRANT OPTION FOR ALL ON MyTable FROM Doug 

That's only necessary if Doug still has permissions to access the table. But what if Doug had been giving people access to a bunch of tables he shouldn't have? You can revoke all the permissions that Doug ever granted on the table by using the CASCADE option.

 REVOKE GRANT OPTION FOR ALL ON MyTable FROM Doug CASCADE 

That's easy enough. But it would be nice to manage all these users as a group , wouldn't it?

User Roles

To provide the capability to grant multiple users access to the same objects the same way, SQL Server provides a mechanism for creating collections of users, called roles .

NOTE

Where Have the Groups Gone? If you came up through an older version of SQL Server, such as SQL Server 7.0 or earlier, you're probably wondering where the groups are. Groups are now called roles. If you use one of the old-style group commands, such as sp_changegroup , you'll actually be changing the user's role. Roles have a lot more functionality than groups, such as enabling a user to belong to several roles.

Fixed Roles

SQL Server provides you with a set of roles you can use to assign different levels of permission to users. There are two types of fixed roles. Fixed server roles are server-wide permissions that can be used regardless of the database you are in. Then there are fixed database roles, which apply to only one database.

There is very little you need to know about the fixed roles for the exam; that is all relegated to the SQL Server Administration exam. For more information about the fixed server roles, you can look in SQL Server Books Online at the overview topic under "Roles."

Defining Your Own Roles

You can also define your own role. To create a new role, use the sp_addrole system-stored procedure, like this:

 sp_addrole 'rolename' 

The rolename is the name of the role, which of course has to meet all of the other restrictions for naming objects in SQL Server, except that roles cannot contain backslash characters. Backslash characters create an empty role with no permissions. To add users to a role, use the sp_addrolemember stored procedure:

 sp_addrolemember 'rolename', 'security_account' 

The 'security account' parameter is the name of the security account that should be added to the role. A security account could include a user, a Windows NT account that has a user associated with it, or a Windows NT group. If a group is specified, then all the members of the Windows NT group who have associated users in the current database are added to the role.

To give the role access to other objects, use the GRANT statement, as described earlier, and use the name of the role in place of the username.

Application Roles

One of the handy features of this security model is the application role . An application role is similar to other roles, but the role has no members associated with it. The GRANT and REVOKE statements work the same way with an application role as with any other role. To create an application role, use the sp_addapprole system-stored procedure:

 sp_addapprole 'AppRoleName', 'Password' 

Yes, there is a password. To activate the application role for a given connection, the connection must execute another stored procedure, sp_setapprole , this way:

 sp_setapprole 'AppRoleName', 'Password' 

This stored procedure causes the connection executing the stored procedure to acquire the permissions granted to the application. In other words, the application has to run that stored procedure and send the password to invoke the correct permissions. At the point that sp_setapprole is used, any roles, permissions, or users associated with the connection are gone, and only the permissions assigned to the application role are valid.

An encryption option can be specified with the sp_addapprole command, which encrypts the password before it is sent across your network. To do this, use sp_setapprole as follows :

 sp_setapprole 'AppRoleName', {Encrypt N'Password'}, 'odbc' 

The little 'odbc' at the end specifies that the password should be encrypted using the standard ODBC encryption function. Otherwise, no encryption will be used.

Why all the bother with application roles? There are two reasons. First of all, you can set up an application role for a user application, and give the role access to all the tables and other objects it needs to access, but users who try to log in to SQL Server with Query Analyzer do not necessarily have a valid password to use to get the same level of access, which prevents them from modifying data incorrectly or running queries that may impede overall server performance.