EXAM CRAM™ 2 Designing and Implementing Databases with SQL Server 2000 Enterprise Edition [Electronic resources]

Introduction

SQL Server has a built-in object security model that enables the system administrator to grant certain other individuals the rights to read and make changes to databases. Individuals are going to need a variety of access types to data, and this chapter looks at how to keep those folks from inadvertently or maliciously messing things up, while still providing the facilities needed.Appendix B, "Need to Know More?".

The SQL Server Login/User Model

You need to learn some very strict terminology before you'll understand what's really 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.

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 warns against using Standard security, but it's the only way 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. The easiest method for interacting with logins and users is to use the Enterprise Manager, but it is possible to script this process utilizing a set of stored procedures.

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\Danny'

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 'Danny', 'DannysPassword'

After the login has been granted to the server, the user can be added to any of the databases. To add a user to a database, you can use the sp_adduser command or the newer sp_grantdbaccess command:

Use Northwind
sp_grantdbaccess 'Danny'

or

Use Northwind
sp_adduser 'Danny'

Both commands 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 which database you are in before you execute those commands.

In looking at the security process used by SQL Server 2000, you may find yourself asking, "Where have the groups gone?" In particular, if you came up through an older version of SQL Server, such as SQL Server 7.0 or earlier, you might be used to using groups. 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.

Using Security 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. Roles are similar to groups found within a network security framework. There are sets of roles that have predetermined functionality on the server, but you also can define custom 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.

Fixed roles defined can be users assigned to pick up the associated administration permissions of the role. The permissions assigned represent server-wide privilege. The most important of these is the sysadmin role, whose members can perform any activity in SQL Server. You can get a list of the fixed server roles from sp_helpsrvrole, and get the specific permissions for each role from sp_srvrolepermission.

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 roles. 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 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, 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.

Using 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 at which 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 shown here:

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.

There are two reasons you may want to use application roles. 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.

Encryption Can Secure Definitions

Data encryption is a mechanism that can be used to secure data, communications, procedures, and other sensitive information. When encryption techniques are applied, sensitive information is transformed into a nonreadable form that must be decrypted to be viewed. Encryption slows performance, regardless of the method implemented, because extra processing cycles are required whenever encryption or decryption occurs. SQL Server can use data encryption at several levels:

Login information

Application role passwords

Stored procedures

Views

User-defined functions

Triggers

Defaults

Rules

Data sent over the network

Various encryption procedures can be performed by a developer or an administrator, depending on what level of encryption is desired. SQL Server always encrypts login and role passwords within the system tables stored on the server. This automatic encryption of the login information stored on the server can be overridden using sp_addlogin, but this is not recommended. By default, however, application role passwords are not encrypted if they are provided across the network to invoke a role. SQL Server can use SSL (Secure Sockets Layer) encryption across all network libraries, although multiprotocol encryption is still supported for backward-compatibility reasons. A consideration in any SQL Server installation that uses multiple instances installed on the same server is that multiprotocol encryption is not supported by named instances of SQL Server.

Process definition encryptions applied to stored procedures, defaults, rules, user-defined functions, triggers, and view definitions are all implemented in a similar fashion. The definition stored on the server is encrypted to prevent someone from viewing the details of the process. To encrypt these definitions, use the applicable CREATE statement, providing the WITH ENCRYPTION option as illustrated in the following VIEW definition:

CREATE VIEW SampleEncryptedView WITH ENCRYPTION AS
SELECT FirstName, LastName, Wage FROM PayTable

Encryption can also serve the purpose of protecting the copyright that a developer might have over the processes created. In any case, before you encrypt a procedure, make sure you save a copy of the procedure to a file server or another backup mechanism, because future changes are difficult to implement if you do not have the original definition. To update any definition and remove encryption, simply supply the CREATE statement without the WITH ENCRYPTION option. This overwrites the encrypted process with a new version that is not encrypted.

Auditing User Activity

SQL Server provides an auditing facility as a way to trace and record activity. SQL Server 2000 also provides the SQL Profiler for performing more in-depth monitoring. Auditing can be enabled or modified only by system administrators, and every modification of an audit can also be audited to prevent administrative abuse. The capability to create, use, alter, and remove objects can also be tightly controlled through the use of statement and object permissions.

SQL Server can accommodate two types of auditing: standard auditing and C2 auditing. Standard auditing provides some level of auditing but does not require the same number of policies as C2 auditing. C2 auditing requires that you follow very specific security policies. C2 security is more than just a machine standard, but it has aspects to cover the whole computer facility. You can use SQL Profiler to perform both types of auditing.

C2 Security Auditing

Permission sets determine which network identifiers, groups, and roles can work with specific objects, and what degree of interaction is allowed. Permission sets in general are more an administrator concern at this level. For design purposes and more specifically the design exam, you only need to touch on a few considerations. If you are preparing for the companion exam, it is recommended that you learn more about this broad topic.Appendix B.

Security Audits

You use SQL Profiler for auditing events in several categories. A list of these categories and their descriptions is given here:

End user activity Login, SQL, and enabling of application roles.

DBA activity Includes configuration of database and server.

Security events Grant/revoke/deny, add/remove/configure.

Utility events Backup/restore/bulk insert/BCP/DBCC.

Server events Shutdown, pause, start.

Audit events Add audit, modify audit, stop audit.

With the SQL Profiler you can determine the date and time of an event, who caused the event to occur, the type of event, the success or failure of the event, the origin of the request, the name of the object accessed, and the text of the SQL statement.

Auditing can have a significant performance impact. Before you select any object for auditing, balance the trade-off between the likelihood of a security breach and the overhead of the audit. Carefully consider an audit strategy before merely turning on auditing for all objects. If SQL Server is started with the -f flag, auditing does not run.

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, and CREATE FUNCTION, 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 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 this:

GRANT SELECT ON MyTable TO Danny

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

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

To allow a user the right to give other people permissions on the table, you can use the WITH GRANT option as in the following:

GRANT ALL ON MyTable TO Danny WITH GRANT OPTION

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

Now you've given Danny permission to change your table. That's probably not good given that Danny's boss fired him yesterday. Now what are you going to do? Good thing there's the REVOKE command. The following example rescinds any previous permissions given:

REVOKE ALL ON MyTable FROM Danny

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? The following code removes the capability to grant permissions:

REVOKE GRANT OPTION FOR ALL ON MyTable FROM Danny

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

REVOKE GRANT OPTION FOR ALL ON MyTable FROM Danny CASCADE

There are three basic commands to set permissions and five different actions they can control. The commands are GRANT, REVOKE, and DENY. The actions are SELECT, INSERT, UPDATE, DELETE, and DRI. GRANT and DENY allow or disallow access to the view, whereas REVOKE removes a previous GRANT or DENY. SELECT, INSERT, UPDATE, and DELETE should be self-explanatory, and DRI enables users to create references to the view, which would be required to create an object that refers to the view with the WITH SCHEMABINDING clause.

Using Statement and Object Permissions

Activities involved in creating a database or an item in a database, such as a table or stored procedure, require a class of permissions called statement permissions. Careful control over who can create and alter objects is very important. Essentially, statement permissions allow for the creation and alteration of objects. These are the statement permissions:

BACKUP DATABASE

BACKUP LOG

CREATE DATABASE

CREATE DEFAULT

CREATE FUNCTION

CREATE PROCEDURE

CREATE RULE

CREATE TABLE

CREATE VIEW

Because object ownership is established upon creation of objects, and too many owners can add unnecessary overhead, you should ensure that anyone creating objects is doing so under dbo ownership. The dbo internal system user should own all objects regardless of who is actually doing the object creation.

Object permissions determine to what level data can be accessed or whether a procedure can be executed. The list of object permissions is given here:

SELECT, INSERT, UPDATE, and DELETE permissions, which can be applied to data from a table and/or view

SELECT and UPDATE statement permissions, which can be selectively applied to individual columns of a table or view

SELECT permissions, which may be applied to user-defined functions

INSERT and DELETE statement permissions, which affect the entire row, and therefore can be applied only to the table and view and not to individual columns

EXECUTE statement permissions, which affect stored procedures and functions

Setting of permissions can allow audit processes to determine successful and failed attempts to use the permissions.

Using Views to Enhance Security

Views can be used to restrict what data is visible in a database. Because you can restrict what is visible in the view, you can restrict what data can be deleted. Views can also be created with the WITH CHECK OPTION. When the view is created using WITH CHECK OPTION, the view enables data to be added to the database only if the resulting data will be visible in the view.Chapter 3, "Implementing the Physical Database," and Chapter 4, "Advanced Physical Database Implementation." Within views you can utilize many advanced features of the SELECT statement, such as joins and aggregates.

Data modification with views introduced several restrictions that prevent many types of modifications (INSERT, UPDATE, and DELETE) from happening. You were introduced to the WITH CHECK OPTION, which further restricts what you can do. These restrictions serve two purposes: They enable you to validate the data you enter and they enable you to make restrictions to prevent unwanted data modifications.

Displaying the definition of a view may need to be done at some time. To access the exact definition of the view you created, use the sp_helptext system stored procedure. The actual definition of a view is stored in the syscomments system table. The sp_helptext procedure queries the syscomments, organizes the information required, and displays the view definition.

As you've already seen, protecting a view definition is possible if you use the WITH ENCRYPTION option. sp_helptext lets you know what tables and views your view references. This information is helpful if you are having trouble figuring out why your view does not work because you will see what tables or views your malfunctioning view uses.

The syntax is relatively similar to CREATE VIEW. view_name is the name of the view being altered. The WITH ENCRYPTION clause protects the definition of your view. You encrypt the definition of your view because you may not want users to display it, to protect your design from duplication. Encrypting your view using WITH ENCRYPTION ensures that no one can display your view, whether using sp_helptext, viewing it through the Enterprise Manager, or generating it through a Database Creation Script.

Stored Procedures Security Implementations

Stored procedures can be used to modify data rather than allowing users to access data tables directly. When using stored procedures, you are able to write simple or complex logic that is performed when the stored procedure executes. Rather than the user issuing the statements manually, the stored procedure is used to make the modification based on parameters passed to the procedure. Stored procedures can impose rules and policies on the modification, and can perform complex procedures without the possibility of user-induced errors.

To hide the complexity of the logic and the tables being accessed, you can use stored procedures as an additional feature to an overall security plan. In addition, encrypting the procedure definition after it is working adds an additional level of security by keeping the contents of the procedure protected.

Using Defaults

Defaults do not restrict what data is put into a table, but rather they are used to provide values for columns into which you are not inserting data. An example of a default would be a company that is based in the United States with 80% of its client base in the United States. This company might decide to place a default on the Country field in its Customer table, because in most cases it will be correct. This default is used only if the INSERT statement does not provide a Country value.

Constraints to Control Input

There are five basic constraints that can be used to restrict inserts and updates: NOT NULL, CHECK, UNIQUE, PRIMARY KEY, and FOREIGN KEY. These constraints place restrictions on what data can be inserted or updated in the database. FOREIGN KEY constraints can also be used to restrict what data can be deleted from the database.

Constraints offer a fair amount of flexibility but do not possess the level of code or logic that is present in a trigger. Triggers can reference columns in other tables, and can evaluate the change in the data because they can examine the data both before and after modification. In general, constraints should be used when possible because they are more efficient and cost less on the CPU, and triggers should be used whenever constraints are not powerful enough to perform the job. Constraints are executed after INSTEAD OF TRiggers, but before AFTER triggers.

Controlling, Filtering, and Enforcing Data Access

The discussion of AFTER triggers earlier in this chapter included several examples that showed you when and how you might want to implement triggers. Triggers are capable of performing any action you want during their execution, and they execute based on data modification in a table. One task that can be given to triggers is controlling what modifications might be made to the data in a table. This could include restrictions like the following:

We would like only one customer/contact record for each company to exist. When a second customer/contact is added in the form of a new customer record, an INSERT TRigger can check for the existence of that company in the Customer table. If the record exists, it can take the new contact and add it to an Alternate Contact column.

From the deletion perspective, you might allow multiple contact records to exist for each company in the Customer table. When a contact record is deleted, a DELETE trigger can confirm that this is not the last record for that company. If it is, you can have the deletion cancelled and simply remove the contact name from the record.

These are just two examples of how triggers can control what is done to the data in a table. Triggers can be used to restrict or log any and all data modifications made to a table in your database.