SQL Server Magazine

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.

   Prev. page   [1] 2     next page