Chapter 19 - SQL Server Security

• Learn the difference between logins and users - SQL Server manages server access through Logins and database access through users.

• How to protect your data from unscrupulous browsing - This chapter will introduce you to views and stored procedures as a way of hiding data from users.

• Securing your environment - Get suggestions on how to secure not only SQL Server, but your physical hardware, LAN, WAN and Internet access.

Just about everyone is concerned with the security of their 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, however, 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' needs to use the data.

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

Understanding the Types of Security

Securing your data from internal attacks is probably your primary concern for most corporate environments. This security will involve the monitoring of 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 who 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 things like physical LAN access, fire-walls, and so on.

Microsoft's SQL Server security system can be implemented 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 fig. 19.1).

   Fig. 19.1 - After just 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 SQL Server, Configure. Then activate the Security Options page (see fig. 19.2).

   Fig. 19.2 - To track the successful and unsuccessful login attempts of your users, enable (check) the options in the Audit Level group box.

Using Standard Security

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

Using Integrated Security

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.

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 unfeasible in your environment. MPNL is discussed in the section in this chapter entitled Encrypted Multi-Protocol NetLibrary For more information on the configuration of other communications protocols for clients, see Chapter 21, "Communicating with SQL Server".

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

• Mapped to an existing SQL Server Login if a name match is found

• Connected as the default login (usually guest)

• Connected as SA if the user is the administrator on the NT system

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

Using Mixed Security

Creating and Managing User Accounts

SQL Server uses these distinctions to allow a single user to have different levels of access based on the database that they are connecting to, and yet retain the same password. To support this, a user has a login or connection permission to the server. This login is what SQL Server associates a password to. 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 a SQL Server system login and is described in the following sections.

Using SQL Enterprise Manager to Create a LOGIN

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

2. Select the Server that you want to add a login to and then select Manage, Logins (see fig. 19.3).

   Fig. 19.3 - The Manage Logins dialog box enables you to 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 fig. 19.4).

   Fig. 19.4 - The Manage Logins dialog box shows a new login (swarner) being created with user access to the Pubs and Master databases.

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 (see fig. 19.5).

   Fig. 19.5 - The Confirm Password dialog box requires verification of password information.

When creating 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 stage, 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 19.6.

Fig. 19.6 - The Manage Users dialog box shows a new user (swarner) being created for the Login swarner.

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.

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 Server Enterprise Manager is right-click aware—meaning 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

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

• login_id is the name of the login being added. A login follows standard SQL Server naming conventions.

• password is the password to be assigned the login. Passwords are optional, but are highly recommended as the most basic of security measures.

• defaultdb is the default database that the SQL Server should place the login in after connecting to the database. If left NULL, SQL Server leaves the login in the master database.

• defaultlanguage is the default language that should be assigned to the login. If left NULL, SQL Server assigns the default language for the server.

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.

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

Using sp_adduser to Add New Users to Databases

sp_adduser login_id [, username [, grpname]]

• login_id is the name of the login being added as a user to the database. Invalid logins will not be added to the database.

• username is provided to allow logins to be "aliased" in a database. This allows the same login to connect to different databases on the same server and have different names in each database.

• grpname allows the specification of a user group to which the user will belong. Using groups simplifies security because instead of granting permissions to individual users, the permissions can be granted to the group and then all members of the group receive them.

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

sp_adduser 'Ronald'

sp_droplogin and sp_dropuser

sp_droplogin login_id

sp_dropuser username

Creating and Using Groups

Using SQL Enterprise Manager to Add Groups

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

   Fig. 19.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 (see fig. 19.8).

   Fig. 19.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

Using Permissions and SQL Server

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.

• The database owner (dbo) has full permissions on all objects in the database that he owns.

• The system administrator (SA) has full permissions on all objects in all databases on the server.

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.

Object Permissions

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

• SELECT enables a user to select or read data from a table or view. Note that a SELECT permission can be granted to individual columns within a table or view, not just the entire table.

• INSERT enables a user to add new data to a table or view.

• UPDATE enables a user to change data in a table or view. Note that an UPDATE permission can be granted to individual columns within a table or view, not just the entire table.

• DELETE enables a user to remove data from a table or view.

• EXECUTE enables a user to execute a stored procedure.

• DRI/REFERENCES enables a user to add foreign key constraints on a table.

• DDL/Data Definition Language enables a user to create, alter, or drop objects in the database. Examples are CREATE TABLE, DROP DATABASE, ALTER TABLE.

• ALL enables the user full permissions on the object. Note that only the SA can use ALL when DDL statements are being used.

Using SQL Enterprise Manager to Manage Permissions

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

2. From the Object menu, select Permissions and choose either the By User page or the By Object page (see fig. 19.9).

   Fig. 19.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 fig. 19.10).

   Fig. 19.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.

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

The GRANT Transact-SQL command is used to give a permission or permissions to a user or group in SQL Server. Granting a permission allows the user or group to perform the granted permission.

The syntax for using GRANT is as follows:

GRANT permission_list
ON object_name
TO name_list

The syntax for REVOKE is as follows:

REVOKE permission_list
ON object_name
FROM name_list

object_name is a table, view, or stored procedure for which permissions are being granted or revoked.

name_list is a list of usernames or groups for which permissions are being granted or revoked. Multiple names should be separated by commas. Specifying PUBLIC will include all users.

If WITH GRANT OPTION is appended to a grant statement it will allow the grantee to also grant his rights to other users. This is a nice option, but it should be used very sparingly—if not only by the system administrator, for security reasons.

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

Grant  SELECT, UPDATE
On     AUTHORS
To     PUBLIC
Go

Revoke  DELETE
On      EMPLOYEE
From    PUBLIC
Go

Using Views to Enhance Security

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

Listing 19.1 19_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 Underlying Data Objects and Business Rules

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

Listing 19.2 19_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

Listing 19.3 19_03.SQL—Stored Procedure That Allows Updating of 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

Physical Security

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 in these days of smaller and smaller hardware, the server can be a laptop or similarly small device. This makes it such that it is not so difficult to steal the box.

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

It is assumed that the same level of physical security applied to the SQL Server will also be applied to the following:

• The network file servers

• The network hubs and routers

• Any other shared network device, such as bridges and remote WAN linkup devices.

Local Area Network (LAN) Access

For highly secure environments, provide all users with SecureID cards or similar devices. These devices generate passwords that are authenticated by the Network fileserver 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 utilized. 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 local area network, as follows:

• Enforce password aging with a maximum life of 30 days.

• Require unique passwords.

• Require long (eight-character) passwords that are validated against a list of invalids. (Third party applications exist to ensure that good passwords are being used by a client.)

• Enforce security block outs on logins that fail due to invalid passwords.

Remote or Wide Area Network Access

• Assign IP addresses to all external users and do not allow them to connect with their own addresses. This will enable you to monitor closely all remote connections to your LAN.

• Implement a software and/or hardware based firewall that physically limits external packet traffic on the server's network.

• Enforce routine password changing per the fileserver guidelines outlined above.

• Audit all remote transactions/IP traffic, and scan it for invalid requests.

• Implement secure WAN protocol transport by using hardware based compression on either end of WAN bridges.

Application Security

• Permission trees that allow users access to windows within your application program. You may want to break down access into three levels: view, new, and edit.

• Application-based audit trails that track the changes of fields and the amount of time spent on any given window in the system.

• Application-based limits on the amount of money that can be posted (for financial systems).

Remember, 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 this 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 wish. This would bypass any application-only security that was being enforced.

Encrypted Multi-Protocol NetLibrary

One key advantage of MPNL is that it can be encrypted. The encryption algorithm used can be enabled for individual clients; however, the server 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.

From Here...

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

• See Chapter 13, "Managing Stored Procedures and Using Flow-Control Statements" to learn to create stored procedures to provide users with access to data without giving them access to the actual tables.

• See Chapter 9, "Managing and Using Views" to learn to hide underlying data tables with views that limit data sets available to users.

Table of Contents

18 - SQL Server Administration

20 - Setting up & Managing Replication