Lesson 3: Designing an Access and Permissions Strategy

3 4

Now that you understand the tools available to you for securing your data within SQL Server 2000, you must design an access and permissions strategy that works for your organization. In this lesson, you will learn access and permissions strategies incorporating these tools, and the strengths and weaknesses of each strategy in different environments.


After this lesson, you will be able to

  • Understand the different access and permissions strategies available to you
  • Evaluate and select an appropriate access and permissions strategy

Estimated lesson time: 15 minutes


Permitting Administrator Access

First, you need to determine which users will need full access to SQL Server 2000. In general, limit the number of users to whom you grant full access. To grant these users full access, you have a number of choices. If these users are currently members of the local Administrators group on the SQL Server 2000 computer (by default, domain administrators are members of the local Administrators local group), they already have full access as members of the sysadmin server role. If they are not members of this local group, you can do one of the following:

  • Add them individually to the local Administrators group.
  • Create a login for each user in SQL Server 2000 using his or her Windows 2000 user account and add this login to the sysadmin server role.
  • Create a Windows 2000 global group containing these users and make the SQL Server administrator group a member of the local Administrators group.
  • Create a Windows 2000 global group containing these users, create a login for this global group, and add this login to the sysadmin server role.

In general, as the number of Windows 2000 users who will be SQL Server 2000 system administrators increases and as the number of computers running SQL Server 2000 increases, the more useful a dedicated Windows 2000 global security group becomes. Using a Windows group allows you to grant a user administrator access merely by adding him or her to this Windows group.

In addition, you might not want all members of the local Administrators group to have full access to SQL Server 2000. In this case, remove the login for the local BUILTIN\Administrators group. However, before you remove this login, make make sure you have provided alternate administrator access (generally with a dedicated SQL Server Administrator group login).

Using Windows Groups and SQL Server 2000 Server Roles

For users that require administrator access to SQL Server 2000, but for whom you do not want to grant full administrator access, use server roles to grant them the rights they absolutely require. Use combinations of server roles to grant sufficient permissions. Consider using built-in groups and creating Windows groups in which to place users and map these Windows groups to SQL Server 2000 server roles to simplify administration. Remember that adding a user or group to a server role automatically grants them login access to SQL Server 2000. Also remember that you must use BUILTIN rather than the domain or computer name to add a built-in group.

Providing SQL Server 2000 Access

First, enable Mixed Mode authentication only if absolutely necessary. If you must use Mixed Mode authentication, consider enabling SSL encryption for all communication. Create SQL Server 2000 security accounts for each SQL Server login required. Add these SQL Server logins to server roles as required, using the minimum permissions necessary.

Next, grant login access to the Windows users that will need access to SQL Server 2000. Consider creating and granting login access to one Windows 2000 group that will contain all Windows 2000 users requiring access to SQL Server 2000. Also consider creating a second Windows 2000 group that will contain all users that are specifically denied access to SQL Server 2000. Create a login for this Windows 2000 group and specifically deny login access to any members of this group. Using only these two groups will allow you to quickly grant or deny any Windows user access to SQL Server 2000.

Providing Database Access

First, if one user will have responsibility for a particular database, make that user the owner of the database. Next, create user accounts in the database for users that require database access. If you use SQL Server Enterprise Manager, you can only add users who have SQL Server 2000 logins. If you use Transact-SQL, you can add any Windows 2000 user or group. Consider creating Windows groups for each type of user requiring database access with different levels of permissions.

Using Fixed Database Roles for Administrative Access

Add Windows users and groups, and SQL Server logins (if any) to fixed database roles to provide access rights to perform specific tasks. Create Windows groups as appropriate and map these to fixed database roles.

If you plan to use certain fixed database roles in all new databases, consider adding the roles to the model database and then adding the appropriate users. For example, consider creating a Windows group for security operators and then adding that group to the db_securityadmin fixed database role in the model database. Each new database created will then automatically have the db_securityadmin role automatically populated with this Windows group.

Providing Data Access

There are several levels to consider in providing data access. First, determine whether you want to provide guest access to the database. You can create a guest user account in the database and grant the guest user limited rights to view certain tables, views, columns, and stored procedures. You have no way to audit the activities of a specific guest user because the user is not accessing the database through his or her own user account (although you can audit guest user activity). Any rights granted or denied the guest user account have no impact on permitted users.

Next, determine the data access rights that are required by all permitted users in the database. Grant these rights to the public role. This gives you a single place to grant these rights and makes administration easier. All permitted users acquire these rights by default upon connecting to the database.

Next, if certain users or groups require additional access rights to tables, views, stored procedures, and functions, you have a number of options.

  • If the users or groups require access to all tables, views, and functions, you can add each of these users or groups to the db_datawriter and/or db_datareader fixed database roles.
  • If the users or groups require access to certain tables, views, functions, and stored procedures, you can grant those object permissions individually to each user or group. If there are numerous users or groups who require these permissions, this might not be a good choice.
  • If the users or groups require access to certain tables, views, functions, and stored procedures, you can grant those object permissions to a user-defined database role and add each user or group to the role. If you are using SQL Server logins and Windows logins, this allows you to set permissions in a single location, and then simply add and remove users from the role.
  • If the users or groups require access to most (but not all) tables, views, and functions, you can add each user or group to the db_datawriter and/or db_datareader fixed database roles and then specifically deny each of these users and groups specific permissions on the restricted objects.
  • If the users or groups require access to most (but not all) tables, views, and functions, you can add each user or group to the db_datawriter and/or db_datareader fixed database roles and then place each of these users and groups in a user-defined role and deny specific permissions to this role. Again, if you are using SQL Server logins and Windows logins, this allows you to set permissions in a single location, and then simply add and remove users from the role.

Note


You cannot use the db_datareader and db_datawriter fixed database roles to grant EXECUTE permissions.

Finally, if you need to limit access to tables based on columns, consider using views or stored procedures rather than restricting individual columns. This will generally result in improved performance.

Lesson Summary

SQL Server 2000 provides a myriad of ways to implement security. Choosing the method most appropriate to your environment depends upon fully understanding the options available to you with Windows 2000 (or Windows NT) and SQL Server 2000. In general, choose an access and permissions strategy that enables you to set security in as few places as possible. This will simplify the task of updating and modifying permissions, as well as debugging security problems should they occur.



MCSE Microsoft SQL Server 2000 System Administration(c) Training Kit (c) Exam 70-228
MCSE Microsoft SQL Server 2000 System Administration(c) Training Kit (c) Exam 70-228
ISBN: N/A
EAN: N/A
Year: 2001
Pages: 126

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net