Roles provide a consistent yet flexible model for security administration. Roles are similar to the groups used in administering networks. Permissions are applied to the role, and then members are added to the role; therefore, any member of the role has all the permissions that the role has. A user can be a member of multiple roles and, with the exception of application roles, all permissions of all roles will apply. This makes possible a modular model for permission administration. For example, all permissions required for sales personnel can be assigned to the "sales" role. All permissions required for the managers could be assigned to the "managers" role. To implement security in the sales department, assign the sales users to the "sales" role and the sales managers to both the "sales"and "managers" roles. Roles are also useful to group permissions required for a particular application, as opposed to function or department. If a user is required to use the application, he can simply be added to its associated role. SQL Server uses roles in three ways:
Fixed-Server RolesAs the name implies, fixed-server roles are used to manage permissions at the server level and cannot be modified. Fixed-server roles are used to delegate various levels of administrative privileges by adding logins as members of the roles. This concept of delegation is important, as you can maintain security while distributing the administrative workload. A typical use of this would be to assign the network account administrators to the securityadmin role, allowing them to create logins on SQL Server. The fixed-server roles are listed in Table 15.1. Table 15.1. Fixed-Server Roles
Managing Fixed-Server RolesTo change membership in a fixed-server role, you can use the system-stored procedures sp_addsrvrolemember and sp_dropsrvrolemember. For example, to add the Windows user domain1\bobw to the sysadmin role and drop the SQL login bwhite, the syntax would be as follows : EXEC sp_addsrvrolemember 'domain1\bobw', 'sysadmin' Go EXEC sp_dropsrvrolemember 'bwhite', 'sysadmin' If you prefer to use Enterprise Manager, in the Security folder, use either the Server Roles tab of a login's Properties dialog box or select a server role and from its Properties dialog box, click Add and select the appropriate login(s). Figure 15.2 shows the Properties page for a fixed-server role. Figure 15.2. The Server Role Properties page.
Fixed-Database RolesEach database also has a predefined set of roles used to delegate permissions at the database level. As in the fixed-server roles, the permissions that apply to these roles are preset and can't be altered . The exception to this rule is the public role. The public role cannot be deleted, but permissions to access database objects can be applied to it. As all users are members of the public role, any permissions applied to the role will affect all users. Table 15.2 shows the fixed-database roles and their permissions. Table 15.2. Fixed-Database Roles
Managing Fixed-Database RolesTo change membership in a fixed-database role with T-SQL, use the system-stored procedures sp_addrolemember and sp_droprolemember. The following statement adds the database user bob to the db_owner role and drops the user tom. EXEC sp_addrolemember 'db_owner' , 'bob' GO EXEC sp_droprolemember 'db_owner', 'tom' If you prefer to use Enterprise Manager, select the database you want to administer, select a user from the Users folder, and from the General tab of the User Properties dialog box, check the appropriate roles. Alternatively, select the database role from the Roles folder, and from its Properties dialog box, click Add and select the appropriate login(s). Figure 15.3 shows the Properties page for a fixed database role. Figure 15.3. The Database Role Properties page.
User-Defined RolesUser-defined roles, or database roles, are used to provide a common set of permissions to a group of users. Whereas fixed roles are used to provide statement-level permissions (with the exception of public), user-defined roles are used to provide object permissions in the database. Roles can contain users and other roles, which can be useful for designing a modular security model. An example of this would be to create a role for an application ”such as HR_App ”and assign the permissions to it that are required to run the HR application. You could then create roles called HR_Users and HQ_Users and add the appropriate users to these roles. Now add the HR_Users and HQ_Users to the HR_App role to provide all users in those roles the needed permissions to run the application. Note that if you are using NT groups to manage users, they too could be added to the application role to facilitate permission management. For a full discussion on creating and managing user-defined roles, see the later section, "Managing Database Roles." Application RolesUnlike other roles, application roles contain no database users. When an application role is created (see the later section, "Managing Database Roles"), rather than adding a list of users who belong to the role, a password is specified. To obtain the permissions associated with the role, the connection must "set" the role and supply the password. This is done using the stored procedure sp_setapprole . The syntax to set the role to the sales application role (with a password of "qwerty") is as follows: EXEC sp_setapprole 'sales', 'qwerty' You can also encrypt the password: EXEC sp_setapprole 'sales', {ENCRYPT N 'qwerty'}, 'odbc' When an application role is "set," all permissions from that role apply and all permissions inherited from roles other than public are suspended until the session is ended. So why is it called an application role? The answer is in how it is used. An application role is used to provide permissions on objects through an application, and only through the application. Remember that you must use sp_setapprole and provide a password to activate the role; this statement and password are not given to the users; rather, they are imbedded in the application connect string. This means that the only way the user gets the permissions associated with the role is if they are running the application. The application can have checks and balances written into it to ensure that these permissions are being used for the forces of good and not evil. |