Chapter 9: Securing the Database Engine


In this chapter, you learn how to secure your data and the core database engine. As you can imagine, security is a huge topic and can easily turn into an entire book by itself. This chapter covers core topics such as setting up your logins and users for the minimal data access to do their job. This is called the principle of least access. Also, we cover how to do encryption inside T-SQL, which is now part of SQL Server 2005.

Logins

Logins are the essential element for any person who would like to connect to your SQL Server instance. There is a key differentiating factor when a DBA uses the word login versus the word user. A login grants you instance-level rights to an instance, and a user gives you database-level rights to a database. It's similar to the login giving you the key to the front door with no access to any room, unless you have the user key. Certain types of rights at server-level roles will grant you rights to do anything you'd like to do, almost like a skeleton key in a building. We'll cover those server roles in a moment.

Windows vs. SQL Server Authentication

There are two types of logins you can enable in SQL Server: Windows and SQL Server authentication. Windows authentication is the most secure solution for most environments, if you have the luxury of using this type of authentication. With Windows authentication, you would grant the rights to the database to the user's Windows login. A better solution would be to create a group that the Windows users can be added to for the application and then grant the Windows group rights to the SQL Server. This way, you have only to create the SQL login and assign rights a single time, and the group will delegate the user administration to the Active Directory administrator.

This will simplify laws such as Sarbanes-Oxley that your company must comply with, since access to the database is controlled through a single mechanism.

The best thing about Windows authentication is the user will have a single user name and password to remember. When the user wishes to log in to the database, his or her Windows credentials are passed to the server automatically, and the user is never prompted for a user name and password. Additionally, the domain policies that you set up for changing the user's login and lockouts apply automatically for SQL Server. You could also use domain policies to lockout the user during certain hours.

With all that said, there are some drawbacks to using Windows authentication. If your server is in a different domain from your users and the two domains don't trust each other, you have to use SQL Server authentication. You now have the ability in SQL Server authentication to lock out logins and force them to change their passwords. Some of these features are only available in Windows 2003 or later.

Creating the Login

Granting a person rights to your SQL Server and the various databases is vastly different from how it was in SQL Server 2000, but the screens do look similar to those of SQL Server 2000. To create a login in the Management Studio interface, connect to the server you wish to add the login to and click the Security group. Right-click Logins and select New Login.

You are first faced with the decision of whether to use Windows or SQL Server authentication. If you want Windows authentication, type the Windows local or domain user account name or the group you wish to grant access to. Type the domain name first and then the user name, separated by a backslash, like this: DomainName\AccountName. Alternatively, you can search for the login by clicking Search. If you wish to add a SQL Server authenticated login, just select that radio box and type the login name and password.

If you have chosen to use SQL Server authentication, as shown in Figure 9-1, you have the option to enforce password policies and expiration. You can also force the user to change his or her password the first time the user connects. If you check Enforce Password Policy, your outcome may vary based on the operating system that you have installed. In Windows 2003, this checkbox enforces the policy using the Windows 2003 NetValidatePasswordPolicy API, which ensures that the password meets the following criteria by default:

  • Must not contain all or part of the login name

  • Must be at least eight characters long

  • Must contain three of the following strong password options:

    • Uppercase letters

    • Lowercase letters

    • Numbers 0–9

    • Special characters like ! or ^.

image from book
Figure 9-1

In Windows 2000 and Windows XP, the criteria for passwords are weaker. The check ensures that your password is not blank and that it does not contain the login or computer name. The last check it makes is to ensure that your password does not contain any obvious passwords like "admin," "administrator," "password," "sa," or "sysadmin." If you type a weak password when setting up the login or when changing the password, you will receive this error:

 Msg 15118, Level 16, State 1, Line 1 Password validation failed. The password does not meet Windows policy requirements because it is not complex enough. 

It is important to note that passwords are case sensitive in SQL Server 2005 but user names are not. In Windows 2003, you adjust the password policy in the Local System Settings console that can be found in Administrative Tools (shown in Figure 9-2). Most DBAs may not be familiar with the console, but SQL Server password and lockout policies are tied to the console. For example, the default configuration may not have the Account Lockout setting enabled. You set this setting to the number of failed logins that will cause a SQL Server login or Windows login to be locked out of your server. You can then also set how long the account will be locked out by adjusting the Account Lockout Duration setting in the Account Lockout Policy folder, as shown in Figure 9-2.

image from book
Figure 9-2

As a DBA, you can force the user to be unlocked by going to the Status page for the login. The point of the account-lockout feature is to deter hackers from performing a brute-force attack against your system where they rotate through a list of passwords and attempt to break your password. Previously in SQL Server 2000, you could log that you were being brute-force attacked but couldn't do anything about it other than manually remove the login from having access.

In the Password Policy folder of the Local Security Setting console in Windows 2003, you can also enable complex passwords for SQL Server logins. In this folder, you can also specify how long your password must be, how often the password must be reset, and how long password history is kept.

Back in the General page of the Login Properties screen, you can set a default database for the login by selecting the database from the Default Database drop-down box. Even though the default database is set to master by default, you should always make it a practice to change this database to the database where the user will spend most of his or her time. This will make it easier for your users, since they will not have to change databases once they log in to the server. It also helps keep clutter out of your master database. Oftentimes, a DBA or user will connect to SQL Server and run a script that they think they're running against the proper database. Instead, they may run the script in the master database, potentially creating objects in that database by accident.

The downside to setting the database to something other than master is that if the login does not have access to the database you selected, the user will not be able to login. For example, consider a user named bknight who connected by default to the database called Reports. If you were to delete the Reports database, or remove bknight's access to the Reports database, he would receive the error shown as follows:

 Cannot connect to <instance name> Cannot open user default database. Login failed. Login failed for user '<user name>'. (Microsoft SQL Server, Error: 4064) 

The last option on the General page is to set the login's default language. The default language is set to the server's default language by default, but you can change that by selecting the new language from the drop-down box. This option will essentially change the error and informational messages that the SQL Server returns to the user's native language (if available). The SQL Server tools will still have all their menus in English unless you have installed the multilingual SQL Server.

There are other options on this page that relate to certificates and keys, but you can't add those through this screen. Those options give the login a certificate to use for encrypting and decrypting data and can be set through the CREATE LOGIN command, which we'll discuss in a moment.

Defining Server Roles

Technically, you could click OK at this point, and the login would be created. At this stage, though, the login has no rights to do anything in the SQL Server or in the database, since all you've given the user is the key to the door. Next, click the Server Roles page. Server roles give a user the right to perform a universal function across the entire server. For example, the sysadmin role is the equivalent of the skeleton key mentioned earlier. This role gives the user the right to do anything he or she would like to do. If you have this option checked, there's no reason to check any other option, since the sysadmin role trumps all other permissions, including explicit denial of rights. The following table shows all the roles and the rights you give a user by checking each role:

Open table as spreadsheet

Server Role

Rights Given

bulkadmin

Can run the BULK INSERT statement

dbcreator

Can create, alter, restore, and drop databases

diskadmin

Can manage the disk file

processadmin

Can terminate sessions connecting to your SQL Server

securityadmin

Can create logins and grant logins rights to a database. Can also reset passwords and alter the login.

serveradmin

Can shut down the SQL Server and alter the instance's configuration

setupadmin

Can add and remove linked servers

sysadmin

Can do anything on the server

Granting Database Rights

In the User Mappings page, you grant the login rights to the individual database. To do this, check each database that you wish grant login rights to. Then click the database role that you wish to give the user, if any. Once you check the database to assign the login rights to, the login is automatically granted access to the public role. The public role is special in that every user in the database belongs to it. You can also see in Figure 9-3 that when you assign the login rights to the database, user-defined roles show in the role membership list below the list of databases, as in the role called Test. We'll talk much more about this in a moment. The list of system roles that appears gives users various rights to the database. As you check the database, you can also assign the user a default schema. This, too, will be discussed in this chapter a little later. The following table lists how these rights work.

image from book
Figure 9-3

Open table as spreadsheet

Database Role

Rights Given

db_accessadmin

Can add or remove access for a login

db_backupoperator

Can back up the specified database

db_datareader

Can read from every table in the database unless the access is explicitly denied

db_datawriter

Explicitly grants the user permission to run an UPDATE, DELETE, or INSERT statement for the database

db_ddladmin

Can run any DDL statement inside the database, including creating stored procedures or tables

db_denydatareader

Explicitly prevents the user from reading data

db_denydatawriter

Explicitly prevents the user from running an UPDATE, DELETE, or INSERT statement for the database

db_owner

Is an administrator of the database and can perform any function

Defining Access to Objects

Oftentimes, you need to delegate security to a login without giving them overarching security like sysadmin. For example, you may want a user to be able to run Profiler but not be a sysadmin, which you had to do in SQL Server 2000. You can do this in the Securables page of the Login dialog box (shown in Figure 9-4), where you can secure to a very granular level on almost any type of server-level function that the login can perform.

image from book
Figure 9-4

To grant or deny a login explicit rights, click Add; then select the type of object that you'd like to list in the below grid. For example, if you select "All Objects of the Types," all securables will display. You can then check Grant or Deny to give rights or take rights away from the login. You can also check With Grant. The With Grant option not only grants the login rights but also allows the user to delegate those rights to someone else.

Note

As of service pack 1 of SQL Server 2005, you will not see what was already checked in the Securables page by default. You will have to search for the object again and it will then display as already checked.

The Status Page

The final page is the Status page. In this page, you can unlock someone's login. You can also disable the login from accessing your system temporarily without having to delete the account. You cannot lockout someone's account as a sysadmin. This can only be done by the SQL Server instance. You can, however, unlock an account as a sysadmin by unchecking "Login is locked out."

Logins with T-SQL

You can take the same actions that you applied in Management Studio for creating a login and apply them by using T-SQL. It's often very important to know how to do these T-SQL commands in order to script a reproducible installation. Only the essential commands are covered in this section, rather than each minor detail. First, to create a login, use the CREATE LOGIN command. The command has changed since SQL Server 2000 to handle the additional options. To create a SQL Server authenticated login called LoginName, you can use the following syntax:

 CREATE LOGIN LoginName WITH PASSWORD = 'StRonGPassWord1', CHECK_POLICY=ON, CHECK_EXPIRATION=ON; 

Ultimately, the CHECK_POLICY piece of this syntax (which checks the password's strength) is not required, since it's on by default if not specified. The CHECK_EXPIRATION option, which enables the password to be expired, is not on by default and is required if you want to enable the feature. If you wish to grant access for a Windows account to access your SQL Server, you can use the following syntax:

 CREATE LOGIN [Domain\AccountName] FROM WINDOWS; 

To add the login to a given server role, you can use the sp_addsrvrolemember stored procedure. The stored procedure accepts two parameters: the login name and the server role. The following example shows you how to add the login bknight into the sysadmin role.

 EXEC sp_addsrvrolemember 'bknight', 'sysadmin'; 

Adding a login to a database role and a database as a user is discussed in the "Users" section of this chapter.

Another handy new function in SQL Server 2005 is the LOGINPROPERTY function. With this function, you can determine which properties are turned on for the given login. Developers can use these checks to build functionality into their application to simplify the administration of an application's users. You can use the function with the following syntax:

 LOGINPROPERTY ('login_name' ,                  { 'IsLocked' | 'IsExpired' | 'IsMustChange'                    | 'BadPasswordCount' | 'BadPasswordTime'                    | 'HistoryLength' | 'LockoutTime'                    | 'PasswordLastSetTime' | 'PasswordHash' } 

For example, if you wish to determine if the login name bknight must change his password the next time he logs in, you can use the following code:

 SELECT LOGINPROPERTY('bknight', 'IsMustChange'); 

If the login needs to be changed, a boolean 1 will be returned.

The Security Hierarchy

Security can be confusing when you have a conflict between an explicit denial of rights conflicting with an explicit grant of rights. Granting access to an object or a role gives the user rights to a database unless the user has been denied that same access. The main thing to point out is that a denial of access always outweighs a grant of access, with the exception of the sysadmin role at a server. The other type of right that you can perform is a revoke. Revoke essentially removes any rights (grant or deny) from the user and is a neutral position.

For example, say that John has a login into SQL Server called DomainName\John. He is also a part of an Active Directory group called Accounting. John has been granted access to the Salary table, but the Accounting group has explicitly been denied access to the table. In this scenario, he would be denied access to the table. The following table takes this example a bit further and shows you what combinations of permissions would result in denial of granting of access.

Open table as spreadsheet

Accounting Group

John

Effective Rights for John

Granted access to table

Denied access to table

Denied

Denied access to table

Granted access to table

Denied

db_datareader

No rights

Read access to all tables

db_datawriter

Db_denydatareader

Can write but not read

db_denydatareader

Db_owner

Can do anything except read

db_denydatareader

sysadmin

Can do anything on server

Sysadmin

Denied all access from tables

Can do anything on server

Additionally, it's important to note that if you grant a user rights to the db_datareader database role, he will not have rights to any stored procedures or functions. Typically, if you grant a user access to stored procedures, he will not need access to the underlying tables. The reason we use the word typically is that there are exceptions to the rule. The main exception is in the case of dynamic queries inside the stored procedure. In that case, you will need to grant access to the underlying tables. The way around this from a security perspective is context switching, which we will cover momentarily.

The Guest Account

The guest account is a special user in SQL Server that does not exist as a login. Essentially, if you grant the guest account access to your database, anyone who has a login into SQL Server will implictly have access to your database and be given any rights that the guest account has been granted. Granting the guest account access to your database creates a security hole in your database and should never be done. The only tables that should ever have the guest account enabled are the master, msdb, and tempdb databases. This allows users access to create jobs, create temporary objects, and connect to SQL Server.

BUILTIN\Administrators Login

Another special type of login that could pose some security issues for you is BUILTIN\Administrators. This login grants implicit sysadmin rights to your database server to anyone in the local Administrators group for the Windows machine. In some environments, this may be fine, but in most environments, you don't want a Windows administrator to have access to sensitive data, like salary information. This creates a challenge if you're trying to become HIPAA or Sarbanes-Oxley compliant as well, since you have someone not explicitly given rights to become a sysadmin on your system.

If this is a problem for you, you're better off dropping the login. Before you do this, though, ensure that the SQL Server service accounts have a login to SQL Server and have been given sysadmin rights. These logins should be granted access by default through groups named something like BKNIGHT\SQLServer 2005MSSQLUser$BKNIGHT$MSSQLSERVER (where BKNIGHT is the machine name).

Granting Profiler Access

Previously in SQL Server 2000, you had to be a sysadmin to use the Profiler application. This created a bottleneck for DBAs, since access was not given away lightly and Profiler was sometimes the only tool that could be used to debug certain problems. In SQL Server 2005, you can allow a login access to create a Profiler trace without having to give them high-level authority by granting the user ALTER TRACE rights in the Securables page. You can also use this syntax to grant them the right:

 GRANT ALTER TRACE TO [LoginName] 



Professional SQL Server 2005 Administration
Professional SQL Server 2005 Administration (Wrox Professional Guides)
ISBN: 0470055200
EAN: 2147483647
Year: 2004
Pages: 193

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net