|
|
SQL Server provides a number of role-based approaches for authorization. These revolve around the following thee types of roles supported by SQL Server:
User-defined Database Roles. These are used to group together users who have the same security privileges within the database. You create SQL Server logins and then map these to specific database users. You then add the database users to database roles and establish permissions on individual database objects (stored procedures, tables, views, and so on) using the roles.
Application Roles. These are similar to user database roles in that they are used when establishing object permissions. However, unlike user database roles, they do not contain users or groups. Instead, they must are activated by an application using a built-in stored procedure. Once active, the permissions granted to the role determine the data access capabilities of the application.
Application roles allow database administrators to grant selected applications access to specified database objects. This is in contrast to granting permissions to users.
Fixed Database Roles. SQL Server also provides fixed server roles such as db_datareader and db_datawriter. These built-in roles are present in all databases and can be used to quickly give a user read specific (and other commonly used) sets of permissions within the database.
For more information about these various role types (and also fixed server roles which are similar to fixed database roles but apply at the server level instead of the database level), see SQL Server Books Online (http://www.microsoft.com/sql/techinfo /productdoc/2000/books.asp).
If your application has multiple categories of users, and the users within each category require the same permissions within the database, your application requires multiple roles. Each role requires a different set of permissions within the database. For example, members of an Internet User role may require read-only permissions to the majority of tables within a database, while members of an Administrator or Operator role may require read/write permissions.
In this scenario, you can use multiple user-defined SQL Server database roles. These are used to assign permissions to database objects for the groups of users who share the same permissions in the database. With this approach, you must:
Create multiple service accounts to use for database access.
Create database users to grant the login access to the database.
Add each database user to a user-defined database role.
Establish the necessary database permissions for each role within the database.
Authorize users within your application (ASP.NET Web application, Web service, or middle tier component) and then use application logic within your data access layer to determine which account to connect to the database with. This is based on the role-membership of the caller.
Declaratively, you can configure individual methods to allow only those users that belong to a set of roles. You then add imperative role-checks within method code to determine precise role membership, which determines the connection to use.
Figure 12.4 illustrates this approach.
To use the preferred Windows authentication for this scenario, you develop code (using the LogonUser API) in an out of process serviced component to impersonate one of a set of Windows identities.
With SQL authentication, you use a different connection string (containing different user names and passwords) depending upon role-based logic within your application.
|
|
