SQL Server Magazine

Security in any computing environment is a system of processes, people, and machines. Security for any size organization doesn't just happen; you must plan it. Part of that scheme is database security enabling different types of users to access different levels of data. SQL Server 7.0 has new features to let you plan database security with more precision—and more complexity—than you can with SQL Server 6.5.

Architecture
The basis of the Windows NT and SQL Server security architectures is a system of users and groups. In addition, SQL Server 7.0 introduces several types of roles, including fixed-server and fixed-database roles—sets of permissions associated with a specific type of functionality. Roles are the hottest new part of SQL Server 7.0 security. A group is the unit of security within NT, and the database role performs that function within SQL Server 7.0. Groups can contain NT users and other groups, but roles are more inclusive and might contain SQL Server logins, NT groups and users, and other roles.

Plan your security by arranging users into groups and database roles; this setup lets you allow or deny access to whole ranks of users at once. Together, groups and roles are powerful tools for database security: Users are in an NT group for Windows NT security access needs, and database roles for specific SQL Server access.

Integration with NT
SQL Server 7.0 security is better integrated with NT and provides greater flexibility than earlier versions of SQL Server. To access SQL Server 7.0, a user passes through two levels of security, a process similar to earlier versions. First, users encounter the authentication system. Second, users encounter the permissions system.

The authentication process verifies that the user has the authority to connect to SQL Server, and to use a database. Two server authentication modes exist:

• NT authentication: When users attempt to connect to SQL Server using their Windows NT account, SQL Server verifies that the Windows NT account is authentic, and then verifies that either the Windows NT user (or a group the user is a member of) has been authorized to log in to SQL Server. A connection using a Windows NT account is referred to as a trusted connection because SQL Server trusts that Windows NT has properly verified the user's identity and authenticity.
• Mixed-mode (Windows NT and SQL Server) authentication: Mixed mode lets users connect to SQL Server using either the Windows NT account or a SQL Server account (referred to as SQL Server authentication). Users who connect using a Windows NT account can use trusted connections in either NT or mixed mode. However, when a user connects to SQL Server over a nontrusted connection (meaning they attempt to log in using a SQL Server login account), SQL Server checks to see whether the login account and password are valid. (The documentation states this process enables backward compatibility, but this process is also a requirement for connections from non-NT systems and Internet clients. The phrasing in the documentation suggests Microsoft will remove nontrusted security over time—not a great omen for mixed-platform systems. However, if Kerberos (in Windows 2000, formerly Windows NT 5.0) handles future security validation, non-NT systems providing authenticated Kerberos tickets will be able to access SQL Server—at least theoretically.

After you have successfully logged in to SQL Server, you must switch to a database. You must have a valid user account in each database you wish to connect to. This user account is separate from your login. Every database (except Model) that ships with SQL Server 7.0 has a guest account, so anyone with a valid SQL Server login can connect to them. However, if you create a new database, it won't have a guest account by default, and no one except the database creator will have access. Database access can be granted to a SQL Server login, a Windows NT user, or a Windows NT group.

After security has authenticated the account, you enter the permissions validation stage. There, SQL Server defines the activities the user has permission to perform.

Microsoft is moving in the direction of Windows NT integrated security, and strongly suggests that you use Windows NT integrated security whenever possible. Do note, however, that if you are running the desktop edition of SQL Server on Windows 95 or Windows 98, Windows NT integrated security is not available, and you will have to use SQL Server Authentication security.

Roles
The four types of roles in SQL Server 7.0 are fixed server roles, fixed database roles, user-defined roles, and application roles. Let's examine each one.

Most companies formally conduct database work with distinct functional assignments. SQL Server 7.0 has structured these assignments into roles, from the server level down. The concept is sensible, but many users' work will likely stretch across multiple SQL Server roles, which Screen 1 (page 57) shows.

Roles are a great idea, but they have drawbacks. On the plus side, users can have more than one active role at a time, and a user can belong to more than one role in a database. Roles can contain NT groups and users, SQL Server users, and other roles. Security is more scalable with roles, and aliases are no longer necessary (although they are still supported for backward compatibility).

In earlier versions of SQL Server, an alias lets a user temporarily assume the identity of another user within the database. Thus, the user can perform actions as the aliased user. One purpose of this design was to let a backup DBA temporarily impersonate the dbo if that user were unavailable, or simply to support multiple DBAs in a database. However, in some environments several users have been aliased to dbo permanently, which lets them have complete control of the entire database. This approach has advantages for senior developers who are trusted users of a database. However, roles have replaced aliases. You can use roles to replace this functionality (add the senior developers to the db_owner role if you must), but you can also give them much more granular rights than you could with aliasing.

Many basic maintenance tasks, such as creating databases or managing files, required sa privilege until now. Thus, either the database administrator (DBA) performed daily basic tasks and management, or less experienced or skilled users had to have sa privilege. This arrangement was a problem for both security and human resources. Now, you can assign those users to an appropriate role, restricting security and making it more manageable.

On the negative side, the overhead of complex roles seems too great for small systems. If you have only a few actual database management roles and can match them to SQL Server 7.0's role concepts, the system is easier to control. But for large corporate environments, even planning the roles could be a nightmare. Predefined roles mean almost all users will have several roles most of the time. Keeping track of this security system across hundreds of servers and thousands of users is a major challenge.

   Prev. page   [1] 2     next page