Database security concerns both designers and administrators. Database designers need to address security issues, which can influence table design or the use of views and stored procedures to restrict user access to the tables. And administrators usually implement security and maintain it as users come and go. The Microsoft certification exam guides show that security questions are probably on the administration exam but not on the design exam. However, the design exam can include questions on how to add logins or assign permissions by using Data Control Language (DCL), a subset of SQL, or on topics such as ownership of objects and ownership chains. These subjects all relate directly to security.
Security-related topics in the online exam guide include:
- Assessing whether to use Windows NT accounts or SQL Server logins.
- Assessing whether to leverage the NT group structure.
- Planning the use and structure of SQL Server roles. Server roles include fixed server, fixed database, and user-defined database.
-
Assessing whether to map NT groups directly into a database or to map them to a role.
Three Steps to Security
SQL Server security has three levels: login, database, and object. Users need permission to connect to the SQL Server and to access a specific database. And within that database, they need specific permissions on individual objects. SQL Server makes the distinction between object permissions, which include permissions to access objects and hence read and modify the data, and statement permissions, which give rights to create and delete objects in the database. Unless all components of the security model are in place, users can't access the data, or they might access data they shouldn't. Let's look at the first of these three levels of security, logging in to the SQL Server. Next month, I'll continue with database access and object permissions.
One benefit of the tight integration between SQL Server and NT is that SQL Server can use the NT security model. NT's security model requires one login and password; then users can access resources, including SQL Server, based on their identity. To give NT users access to the SQL Server, the DBA needs to add the NT logins to the list of users and groups that SQL Server recognizes as approved SQL Server logins. From that point on, users can connect to the SQL Server after they receive NT authentication. This login method applies to both SQL Server 6.x and 7.0, although a Security item in the Enterprise Manager console in SQL Server 7.0 has replaced the Security Manager from SQL Server 6.x. Under the hood, security significantly changed, including the information stored in the tables in the master database.
To add a login from the Enterprise Manager, open the Security item, right-click Logins, and select Add a New Login, which brings up the dialog box that Screen 1 shows. Note that it has no drop-down box to show a list of names—you need to type in the domain and user name. The domains box does offer a drop-down list, which shows the current and trusted domains. Selecting the domain name first is easier because the domain name pops into the Name window; then you can type in the appropriate user name.
You need to specify a default database for each user and grant access. The default database is the database that users connect to when they log in. Note that the users don't yet have any permissions in the database except for those granted to the Public role; they simply connect to it. If you don't specify a default database, the users will connect to Master. Also, you can specify a default language for users if you need to change the default for the SQL Server.
Use Those NT Groups
Also new in SQL Server 7.0 is the ability to add an NT group as a SQL Server login. Instead of adding each user individually, the DBA can grant login permission to give SQL Server access to the entire group. You might think that doing so would make tracking who modifies critical data difficult, but it doesn't. Although users can connect based on group membership, they retain their NT user identification. In organizations where the NT administrator and the DBA aren't the same person, this feature neatly divides responsibilities. The DBA adds the group as a SQL Server security account. As the NT administrator adds people to or removes people from the group, those people acquire or lose rights to connect to the SQL Server. The DBA doesn't need to be involved with who belongs to the group and who doesn't. Suppose a new accountant joins the company. The NT administrator, or even an accounts operator, can add the new hire to the Accountants group in NT. This group has a security account that accesses the SQL Server. Immediately, the new account can log in to the SQL Server. It isn't necessary for the NT administrator to call the DBA and request that the new user be added as a login. Of course, the DBA and the NT administrators need to work together so that the NT administrators know that adding someone to a group effectively gives that person all the group's privileges on the SQL Server.
The reverse is also true. If you remove someone from an NT group, all rights and permissions they had in SQL Server because of their group membership vanish immediately. If they have no individual permissions and don't belong to another group with permissions, they're effectively locked out of the SQL Server.
If someone in the group shouldn't have any access to the SQL Server, you can still use the group strategy. Authorize the group to access SQL Server, then add that user's NT account as an individual security account, but check the Deny Access option. The Deny on the individual account overrides the permissions you granted at the group level.
The local administrators group is automatically granted sa rights when you make the group a member of the sysadmins role. The logins icon in Enterprise Manager lists the group in BUILTIN\Administrators. You could, if you needed to separate responsibilities, create another NT group—for instance, SQLAdmins—then make those members system administrators and remove the NT administrators.
SQL Server Authentication
Any user who has an NT login can use the NT authentication—also called integrated security. Even Windows 9x and Windows 3.1 clients can use it, as long as they log in to the domain. But what about users who don't log in? Perhaps you have users connecting from Macintosh or UNIX systems. You can still let them connect, but they'll need to supply a name and password for the SQL Server to validate. You can choose this mode of authentication from the Login Properties dialog box and supply a password to go with the login name. The term for this type of security is mixed—NT and non-NT logins are each handled appropriately. (Note for SQL Server 6.5 DBAs: SQL Server 7.0 has no standard security, but you can achieve the same result by selecting mixed security and assigning everyone a name and password under the SQL Server authentication mode.)
If you choose mixed security, make sure that you change the server properties to allow the mixed security mode. Remember that you have to stop and restart the SQL Server service after any security mode change for the change to take effect.
Adding Logins with Stored Procedures
From the Query Analyzer, you can use various stored procedures to add logins. If you're adding an existing NT user or group to the SQL Server logins, you use the syntax
sp_grantlogin 'login_name'
which gives permission to that NT login to access the SQL Server. Alternatively, you can use
sp_defaultdb 'login_name', 'default_database'
which not only assigns a default database or changes it if one is already assigned, but also adds the login if the login hasn't yet been granted permission to access the SQL Server.
If you're setting up a SQL Server login for mixed-mode security, you need to use
sp_addlogin 'login_name', 'password', 'default_database'
to set up the name for the new account and the default database.
Server Roles
SQL Server 7.0 introduced the concept of roles, which are similar to NT groups, for the server and database. A server role is similar to the special operator groups in NT, such as Account Operator or Backup Operator. Roles come with predefined privileges and rights, and you can use them to delegate some administrative tasks. You can place a new login in the server roles while you're adding the login, or go back later and edit the login. Select the Server Roles tab of the Login Properties dialog box to see the various server roles, as Screen 2 shows. The sidebar "Server Roles" lists these roles with the internal names in parentheses.
You can't modify the server roles or add new server roles. A login can belong to more than one role and acquires a combination of the privileges of the roles it belongs to. One major difference between server roles and NT groups is that any member of any server role can add any other login to that role. You can see the Server Roles menu item under Security in the Enterprise Manager. Right-click a role, and select Properties. From the Properties dialog box, you can add or remove users. The Enterprise Manager interface lets you look at roles and see who's in that role, or you can look at logins and see what role each login belongs to.
To use stored procedures to add a login to a server role, use the command
sp_addsrvrolemember 'login_name', 'role'
where role is the internal name—for example, sysadmins.
Removing Logins
You'll eventually need to remove logins from the SQL Server. SQL Server prevents you from removing a login if the login is associated with the user name of a user who owns objects in any database. This rule prevents objects from being orphaned, which would be a violation of referential integrity in the database. If you follow good programming practices, only the database owner (dbo) can own objects, so deleting a user and login account won't cause any conflicts. Also, you can't remove a login that's in use.
To prevent users from accessing the server, you can use NT permissions to deny them access, or even delete their NT login. From Enterprise Manager, you can delete a login by selecting it and pressing the delete key. Or you can right-click the login name and select Delete. The SQL syntax to remove a login is
sp_droplogin 'login_name'
Cleaning Up Logins
If the NT administrator removes an NT user login, that login isn't automatically removed from the list of valid SQL Server logins. If you're using integrated security (i.e., only NT logins), this inactive account is harmless because nobody can use it. But you probably want to clean up these loose ends and remove them from the SQL Server. The stored procedure sp_validatelogins shows you these inactive logins so you can delete them.
Next month, I'll look at giving database access to users who connect to the SQL Server. Meanwhile, try the practice questions in the sidebar "Practice Questions: Login Security." (Answers to last month's practice questions are in the sidebar "Answers to December Practice Questions.") For more examples on how to manage logins to SQL Server, see the lab files that accompany this article.
