Special Edition Using Microsoft SQL Server 6.5 Second Edition

Previous chapterNext chapterContents


- 20 -
SQL Server Security

SQL Server manages server access through logins and database access through users.
This chapter introduces you to views and stored procedures and how to use them to hide data from users.
This chapter gives suggestions on how to secure not only SQL Server, but also your physical hardware, LAN, WAN, and Internet access.

Just about everyone is concerned with the security of data. If you're not, then you may not have considered how easy it is to get access to sensitive data on your server. One thing to remember is that sometimes too much security can get in the way of productivity. Make sure that you achieve a balance between your need to manage access to data and monitor users and the users' need to use the data.

No document can categorically define every possible security option. This chapter's purpose is to illustrate the features that SQL Server offers and to provide suggestions on what you can do to secure your environment from unauthorized access.

Understanding the Types of Security

Securing your data from internal and external attacks is an important job for you as a database administrator. It is important that you can control who and how data is accessed on your server. Security in SQL Server will help you manage the access that you give to your users.

Securing your data from internal attacks is probably your primary concern for most corporate environments. This security will involve the monitoring and management of corporate databases at the direction of the managers of your company. Security is often designed to limit the sorts of data that your employees can see and when they can see it.

Securing your data from external attacks, such as over the Internet, is much more complicated and is generally only applicable to those companies that are beginning to have an Internet presence with their SQL Server databases.

This chapter will focus more on internal security. This security will act in a layered approach, starting with logins and user permissions that secure the basic access to the server. The second layer adds views and stored procedures that limit data access. Finally, the third layer is an external security through methods like physical LAN access, firewalls, and so on.

You can implement SQL Server's security system in three ways on any server: standard, integrated, and mixed. These security methods control how SQL Server manages user accounts on the server and how it interacts with Windows NT's own security system.

To configure a database server's security type for standard, integrated, or mixed security, follow these steps:

1. Run SQL Enterprise Manager from the Microsoft SQL Server 6.5 group (see Figure 20.1).

FIG. 20.1
After being started, SQL Enterprise Manager shows that no server is selected.

2. Select the server that is going to be managed and, from the Server menu, select S_QL Server, Configure. After doing so, activate the Security Options page shown in Figure 20.2.

FIG. 20.2
To track the successful and unsuccessful login attempts of your users, enable, or check, the options in the Audit Level group box.


NOTE: When you install SQL Server, three users are installed automatically. The system administrator account is set up for the user SA, the system performance monitoring account is set up for the user PROBE, and the GUEST user is set up for default access to the system.

If you set up your server for use with Replication, two additional users, REPL_PUBLISHER and REPL_ SUBSCRIBER, are installed. These two user IDs manage the communications in the publisher/subscriber mode. For more information on Replication, see Chapter 17, "Setting Up and Managing Replication."

As you've seen throughout this book, the SA account is a special account with which you can fully control the different aspects of your system. For this reason, it is extremely important that you change the default blank password to one that is more secure. You should never leave a production system with a blank password.


Using Standard Security

In standard security mode, SQL Server is wholly responsible for managing and maintaining accounts on the server. In this case, SQL Server is responsible for authenticating a user and for enforcing password/login restrictions. This is the most common way of configuring SQL Server because it behaves identically to Sybase on any hardware platform and to SQL Server 4.2 on OS/2. The majority of the rest of this chapter will discuss the features of standard security. For more information on Windows NT's integrated security system, refer to Que's Special Edition Using Windows NT Server.


NOTE: You should use standard security when no Windows NT servers are being used for file server duties. In this case, NT's integrated security mechanisms provide no benefit to the SQL Server. Also, you should use standard security when you expect that several different protocols will be used to attach to the server.

Using Integrated Security

Because SQL Server runs only on Windows NT, Microsoft could take advantage of, and integrate into, Windows NT's excellent security system. When operating in integrated security mode, Windows NT is responsible for managing user connections through its Access Control List (ACL). The advantages of integrated security include single-password access to all resources on a Windows NT domain and password aging and encryption across the network.

A login to the Windows NT server is either granted or denied connection to the SQL Server based on attributes of the user's login account to the NT server. This granting of permission or authentication between client and server creates a trusted login to the server. At this point, NT only validates that the login name is valid for accessing any particular resource available on the network or server.


NOTE: Trusted connections are only available via the Multi-Protocol NetLibrary (MPNL) or via Named Pipes communications protocols, so there may be networking reasons that make integrated security infeasible in your environment. MPNL is discussed in the section "Encrypted Multi-Protocol NetLibrary" later in this chapter. For more information on the configuration of other communications protocols for clients, see Chapter 22, "Developing Applications to Work with SQL Server."

When a user establishes a trusted connection to the SQL Server, the user is:

SQL Server manages all other database-based permissions, such as permissions on tables, views, and other objects, in the same way as a server running in standard security mode. These security permissions are discussed next.

Using Mixed Security

Mixed security, as its name implies, is a combination of both standard and integrated security and means that users can log into the server in either way. When a user connects to a SQL Server in mixed security mode, the server validates the login by first checking whether the login name has already established a trusted connection to the NT server. If no connection is found, SQL Server then performs its own validation of the login name and password supplied. If the requested login is not a known SQL Server login, access is denied.

Creating and Managing User Accounts

SQL Server has two levels of users that are important to understand. The first level of user is a login. A login is the ability to attach to the SQL Server itself. SQL Server manages logins on a server-wide basis. All logins are stored in the SYSLOGINS table of the master database. The second level of user is a user. Users are SQL Server's way of managing who has permissions to interact with resources, such as tables and stored procedures, in a given database. A user can be in one or many databases. All users are stored in the SYSUSERS table of each database for which they have permission to access.

SQL Server uses these distinctions so that a single user can have different levels of access based on the database to which they are connecting and yet retain the same password. To support this, a user has a login or connection permission to the server. It is this login to which SQL Server associates a password. Without a valid login to the server, a user will not have access to any of the server's databases, with the possible exception of remote systems using remote stored procedures.

Once a login is created, it is then necessary to create a user of a database on that server. This process is very similar to creating an SQL Server system login and is described in the following sections.

Using SQL Enterprise Manager to Create a Login

The SQL Enterprise Manager provides a simple way of creating a login to the database. Perform the following steps:

1. Run SQL Enterprise Manager from the SQL Server 6.5 group.

2. Select the Server to which you want to add a login then select Manage, Logins. This is shown in Figure 20.3.

FIG. 20.3
With the Manage Logins dialog box, you can grant access to all the databases on the server by selecting the required access level in the table at the bottom of the dialog box.

3. Enter the information for the new login and, optionally, indicate the databases that the login will be allowed to access (see Figure 20.4).

4. Click Add to verify that the information is correct and to create the login. Enter the password and verify the password assigned to the user to ensure that the information was entered correctly. This screen is shown in Figure 20.5.

FIG. 20.4
The Manage Logins dialog box shows a new login being created with user access to the PUBS and MASTER databases.

FIG. 20.5
In the Confirm Password dialog box, you must verify password information.


TIP: When you create a new login, set the password to be the same as the login name so that it is easy to remember.

If, at a later time, it is necessary to add a user to a database, highlight the database in the SQL Enterprise Manager and then select Users from the Manage menu. The Manage Users dialog box will appear, as shown in Figure 20.6.

FIG. 20.6
The Manage Users dialog box shows a new user being created.

Select the user to be added to the database from the Login drop-down list box and enter the name by which it is to be identified. You should not change the name if you do not want to create an alias. Finally, click Add.


NOTE: Use the Aliases group of the Manage Users dialog box to specify that other server logins can also use the currently selected database. When those logins connect to the server, if they use the currently selected database, they will not be governed by their own name but instead will be known and managed by the system under the alias name. Note that the only logins available for selection in the Aliases group will be those logins that have not already been created as a user of the currently selected database.

Dropping Logins and Users with SQL Enterprise Manager

SQL Enterprise Manager's Manage Logins and Manage Users dialog boxes both provide a Drop button. Click this button to drop any user or login that you no longer want to have access to the database or server.


NOTE: SQL Server Enterprise Manager is right-click aware, which means that you can right-click just about anything in the tree and bring up a context-sensitive menu about the object. To quickly drop a user or login, find the user or login in the tree and right-click it. Click Delete to remove the user or login.

Using sp_addlogin to Add Logins to a Server

The sp_addlogin stored procedure is provided to add a login to the server using Transact-SQL statements. The syntax for sp_addlogin is as follows:

sp_addlogin login_id [, password [, defaultdb [, defaultlanguage]]]

The elements of the statement are:


TIP: The user can change his or her password at any time using the sp_password stored procedure. For example, sp_password `Agent99', `MaxwellSmart' changes the currently connected user's password from Agent99 to MaxwellSmart. It's a good idea for the user to change his or her password after the first login and regularly thereafter.

The following is an example of creating a login to the server with the default database of pubs and a password of Allen:

sp_addlogin `Ronald', `Allen', pubs


NOTE: SQL Server 6.5 has a new variation of the sp_addlogin stored procedure with which a user can be added to a database while adding that user to the master.dbo.syslogins table. This new procedure takes an additional parameter that identifies the login ID. Only system administrators may use this feature.

Using sp_adduser to Add New Users to Databases

sp_adduser is similar in style to the sp_addlogin procedure in that it takes an existing login and adds it to the currently active database. Note that you must issue a use command and be in the required database to add a user to before running the sp_adduser stored procedure.

sp_adduser login_id [, username [, grpname]]

The elements of the statement are:

Following is an example of adding a user to the currently active database. Because no user name is supplied, the login_id is assumed for the user name.

sp_adduser `Ronald'

sp_droplogin and sp_dropuser

To remove a login or user from the server or database, execute the system procedures sp_droplogin or sp_dropuser. Their syntax is very similar, especially when the user name chosen for a given login to a database is the same as the login_id:

sp_droplogin login_id

and

sp_dropuser username

Creating and Using Groups

SQLServer provides the ability to create groups of users so that security permissions granted to all members are the same. This is simpler to use and is a more practical approach to security than granting individual users specific permissions on any particular set of tables.

When SQL Server is installed, it installs with PUBLIC, a single group. All users that are created in your system will belong to the PUBLIC group and it will drive the default permissions sets of your users.

Users can belong to one, and only one, additional group on your system so that you can associate custom rights with their account. You'll want to carefully create your groups to encompass the different functionality "classes" of users on your system.

Also, remember that groups are defined on a database-by-database basis, which means that when you define a group in one database, it's not available in others. You'll need to create the group anew in each database as needed. To get around this, you can use the MODEL database. Create the groups you need in that database, then again when it's used as the template for new databases.

Using SQL Enterprise Manager to Add Groups

SQL Enterprise Manager provides an easy method for adding groups to the database. Perform the following steps:

1. Start SQL Enterprise Manager and highlight the database in the server tree for which you want to create a group (see Figure 20.7).

FIG. 20.7
The SQL Enterprise Manager shows the PUBS database highlighted.

2. From the Manage menu, select Groups and enter the information/name of the new group. Select any users that are required members of the group, as shown in Figure 20.8.

FIG. 20.8
In the Manage Groups dialog box, a new group (grp_me) is created with swarner as its only member.

3. Click Add to add the group to the database.

Dropping Groups with SQL Enterprise Manager

Dropping security groups with SQL Enterprise Manager involves performing the same steps as creating them. You can use the Manage Groups dialog box, shown previously in Figure 20.8, to drop any unneeded groups from the server. Removing a group will not remove any users associated with those groups. Any permissions granted to users because they were members of the groups will be revoked.

Using Permissions and SQL Server

Permissions are the rights to access an object, such as a table, in the database. Permissions are granted to a user or group so that user or group can perform functions such as select data, insert new rows, and update data.

Permissions are implicitly granted to the owner or creator of an object. The owner can then decide to grant permissions to other users or groups as that user sees fit. Several permissions exist on objects in the database:

SQL Server provides the GRANT and REVOKE commands to give or take away permission from a user. SQL Enterprise Manager also provides an easy way to add and remove permissions. These commands are discussed in the following sections.

Object Permissions

Object permissions are the permissions to act on tables and other objects, such as stored procedures and views, in the database.

The following is a list of permissions available on tables and their descriptions:

Using SQL Enterprise Manager to Manage Permissions

SQL Enterprise Manager provides an easy way of managing permissions for users and groups in a database. Perform the following steps:

1. Start SQL Enterprise Manager and highlight the database in the server tree for which you want to manage permissions (refer to Figure 20.7).

2. From the Object menu, select Permissions and choose either the By User page or the By Object page. This is shown in Figure 20.9.

FIG. 20.9
Changing permissions by checking any of the columns does not take effect until you click the
Set button.

3. Use the Object Permissions dialog box to specify the permissions required for the user, and then click Set to apply the changes (see Figure 20.10).

FIG. 20.10
Use the Object Filters options to limit the types of objects that are displayed in the table at the lower-left part of the dialog box.


TIP: Use the Grant All and Revoke All buttons to grant or revoke all the permissions on a given table or view to save time.

Using GRANT and REVOKE

SQL Server's Transact-SQL interface to permissions is through the GRANT and REVOKE statements. The GRANT Transact-SQL command is used to give a permission or permissions to a user or group in SQL Server. Granting a permission enables the user or group to perform the granted permission. The syntax for using GRANT follows:

GRANT permission_list
ON object_name
TO name_list

Use REVOKE to revoke permissions from a user. The opposite of GRANT, REVOKE is designed to undo or remove any permissions granted from a user or group. The syntax for REVOKE follows:

REVOKE permission_list
ON object_name
FROM name_list

The parameters for the REVOKE command are:


NOTE: If WITH GRANT OPTION is appended to a grant statement, the grantee will be able also to grant his rights to other users. This is a nice option, but it should be used very sparingly. It probably is best if it is used only by the system administrator because of security reasons. n

The following example grants SELECT and UPDATE permissions on the AUTHORS table:

Grant  SELECT, UPDATE
On     AUTHORS
To     PUBLIC
Go

The following example revokes DELETE permissions on the EMPLOYEE table:

Revoke  DELETE
On      EMPLOYEE
From    PUBLIC
Go

Using Views to Enhance Security

Views provide a great way to enhance security because they limit the data that is available to a user. For example, you can have a group of users in grp_junior_emp that is not allowed to view any of the authors who receive more than 50 percent royalties because this amount is only available to the senior managers or other employees within the company. In Listing 20.1, the Transact-SQL shows how you can achieve this.


See Chapter 10, "Managing and Using Views," to learn more about creating views with SQL Server.

Listing 20.1 20_01.SQL--Using Groups and Views to Create a Well-Secured
Environment

/* First add the group */
sp_addgroup grp_junior_emp
go
/* now revoke select on the base tables from the public group */
Revoke Select on TitleAuthor from public
go
Revoke Select on Authors from public
go
/* now create the view that limits access */
Create View Vie_Authors
As
      Select      *
      From  AUTHORS
      Where AU_ID in (Select AU_ID
                  From TITLEAUTHOR
                  Where ROYALTYPER <= 50)
Go
/* grant select on the view to the members of the group */
grant select on Vie_Authors to grp_junior_emp
go

Using Stored Procedures to Conceal Objects and Business Rules

You can use stored procedures in a very similar fashion to using views to provide a level of security on the data that completely conceals the data available to a user or the business processes involved in manipulating the data.

In Listing 20.2, you can see the same data concealment as demonstrated in using the view in Listing 20.1 except that it is achieved through using a stored procedure.

Listing 20.2 20_02.SQL--Using Groups and Stored Procedures to Conceal Data Structures on the Server

/* First add the group */
sp_addgroup grp_junior_emp
go
/* now revoke select on the base tables from the public group */
Revoke Select on TitleAuthor from public
go
Revoke Select on Authors from public
go
/* now create the stored procedure that limits access */
Create Procedure up_SelectAuthors
As
      Select      *
      From  AUTHORS
      Where AU_ID in (Select AU_ID
                  From TITLEAUTHOR
                  Where ROYALTYPER <= 50)
Go
/* grant execute on the view to the members of the group */
grant execute on up_SelectAuthors to grp_junior_emp
go

In Listing 20.3, the junior employees are allowed to update the contract flag on the AUTHORS table without having permission to update anything else on the table. This is the sort of procedure that enables you to hide data manipulation from the users while still giving them limited power to work on the data available to them in the server.

Listing 20.3 20_03.SQL--Stored Procedure that Enables Users to Update the AUTHORS Table

/* First add the group */
sp_addgroup grp_junior_emp
go
/* now revoke select on the base table from the public group */
Revoke Update, Delete, Insert on Authors from public
go
/* now create the stored procedure that limits access */
Create Procedure up_SetContractForAuthor
      @nAu_Id id,
      @bContract bit
As
      Update      AUTHORS
      Set   CONTRACT = @bContract
      Where AU_ID = @nAu_Id
      Print "Author's contract flag set."
Go
/* grant execute on the view to the members of the group */
grant execute on up_SetContractForAuthor to grp_junior_emp
go

Using Security Beyond SQL Server

You can take a number of steps to provide a more secured environment in which SQL Server will operate. Some of the following sections may seem obvious but are worth thinking about. It is recommended that you designate a person to be responsible for system security at your workplace. This person will live, breathe, and eat security and should be clearly empowered to implement any of the steps outlined in the following sections. System Security Officers (SSO) are becoming more and more common within organizations due to the highly accessible nature of public access networks, such as the Internet. Their roles are that of company custodians.

Physical Security

Often overlooked when the security of a system is being designed is the physical security of the server itself. Granted, it is unlikely that the average hacker will spend all day sitting on the system console hacking into a server, trying various passwords without being noticed. If the server can be removed physically from its location, however, many unscrupulous users will be prepared to spend more time in the comfort of their homes. This would also include its mass data storage devices, such as tapes and hard drives.

Ensure that physical access to the server is limited. Provide locked doors, preferably with electronic locks, that secure the server, and optionally bolt the server to the structure on which it resides. Remember that in these days of smaller hardware, the server can be a laptop or similarly small device, which makes it easier to steal.

Because Windows NT provides excellent remote administration capabilities, you can remove monitors and keyboards from servers that must be placed in high-access areas. Doing so will prevent an idle person from walking by and examining the server. As an alternative, plenty of hardware manufacturers provide secure casings for server boxes that you can use to provide better security for your server.

It is assumed that you will apply the same level of physical security to the SQL Server as to the following:

Local Area Network (LAN) Access

A common mistake on LANs is to have unmonitored network nodes that allow access. Ensure that all nodes on the network that do not have computers actually attached to them have been disconnected from the hub so that no one can bring in a laptop and access the LAN at a physical level.

For highly secure environments, provide all users with SecureID cards or similar devices. These devices generate passwords that are authenticated by the network file server and change constantly. This will stop users without valid identification cards from having access to the LAN, even if they have physical access to a node.

At a LAN software level, ensure that all the features of the LAN's software are being used. Most network operating systems provide at least government-approved C2 level of security, but only if you turn it on. Unlike the B2 standard of security, C2 provides the features but does not enforce their use. Windows NT, NetWare 4.1, and some versions of UNIX support C2 security. Make sure that you are doing all the basics of good user management on your LAN, as follows:

Remote or Wide Area Network Access

It's much harder to control WAN or remote access to a network than the local access provided through the LAN. Some steps that you can take include:

Application Security

You can take a number of steps to make your applications independently secure of the security applied at the SQL Server level. Some ideas to consider are:

Remember that if the security of your database is important to you, you should always ensure that the database itself is secure with or without application programs. You must do so because sophisticated users on your network and on the Internet, if you are connected, will always be able to use a different application to work with your data if they want, bypassing any application-only security that was being enforced.

Encrypted Multi-Protocol NetLibrary

If security is a serious concern in the environment in which SQL Server is being used, then it is possible to implement the SQL Server Multi-Protocol NetLibrary (MPNL). This feature is available in versions 6.0 and higher. MPNL provides a Remote Procedure Call (RPC)-based interface from clients to the SQL Server. MPNL requires adding the protocol as a listener service to the engine, though MPNL is not actually a listener because it is RPC-based.

One key advantage of MPNL is that it can be encrypted. The encryption algorithm used can be enabled for individual clients. The server, however, must be enabled for encrypted traffic. Support for clients varies. Check your SQL Server documentation for the client support available in your version.

Server enumeration via the dbserverenum call in NetLibrary is not supported on servers that are MPNL-enabled. Clients must know the name of servers that are operating in this mode.

Reality Check

Security is one of those things that changes from installation to installation. What works best for you may not be the best thing for others, so you'll have to carefully consider before you select integrated, mixed, or standard security for your SQL Server installation. The most common installation choice is mixed security, which gives you the most flexibility but still enables you to use the features of the NT user base as the foundation for your SQL Server users.

Leaving the SA user with no password is, by far, the most common mistake system administrators make. Take the time immediately after installation to put a password on this account. Never allow your developers to use the account for standard maintenance; they can use a permission-based account in the database or databases as they need to make necessary changes to their project databases. No matter how small your shop is, it's just not a good idea to use the SA account for anything other than administration.

SQL Server security is a complex matrix of options. You have the ability to control database access on many different levels. Remember that you can control access to information with views as well as security implemented as outlined in this chapter. Combine the different techniques to make the system as secure, or as open, as you need. When you're designing your system, keep in mind what types of access are possible, even when they're not probable. This means that if your server resides on a system that is also connected to the Internet, be sure to take into account that you should enhance security to prevent unknown users from accessing the system. Your security should be extremely tight if your system is available to the Internet or other outside sources in any way.

From Here...

Having discovered the many facets of SQL Server security, it is most likely that you will spend the next few months trying to fill the holes that you now know exist. If you are lucky enough to be reading this book before you implement SQL Server in your environment, take advantage of what you have learned and apply as many security features as necessary to provide the appropriate control needed.

Take a look at the following chapters for more information that may be useful in creating a secure environment:


Previous chapterNext chapterContents


Macmillan Computer Publishing USA

© Copyright, Macmillan Computer Publishing. All rights reserved.