لطفا منتظر باشید ...
Objective 6.3: Create and manage application roles.
An application role is a new functionality in SQL Server 2000. It provides a way to restrict access to a database through a specific application. When an application role is activated for a connection by the application, the connection loses all permissions applied for the connection, including login, user account, roles, and so on. The connection gains the permissions associated with the application role. Application roles are applicable only to the database in which they exist. If an application using an application role needs to access another database, it must do so through the guest account. If a user by default would not have permission to access a table but the application needs to access the table to execute properly, the user permission will be overridden by the application permissions and the user will be able to access the table. Application roles allow the application, rather than SQL Server, to manage user authentication. However, SQL Server still needs to authenticate the application when it accesses the database, so the application must provide a password to authenticate itself. If ad hoc access to the database is not required, Windows NT 4 users and Windows 2000 users and groups do not need to be granted any permissions because all permissions can be assigned by the applications they use to access the database. There are several options for managing application role passwords without hard-coding them into applications. An encrypted key stored in the registry or somewhere else, for which only the application has the decryption code, can be used. The application reads the key, decrypts it, and uses the value to set the application role.
Objective 6.3 Questions
1. | 70-229.06.03.001 You want to switch the current connection to use an application role. How would you accomplish this? In the connection string, you would use the Application Role=True flag. Set the user name equal to the application name and the password equal to the application password. Execute the sp_setapprole stored procedure to activate the permissions for an application role. Execute Setapplicationmode True, "Application Name","Password". This option is available only if the client connects using OLE DB and uses the Application Role=True flag. | |
2. | 70-229.06.03.002 Which connection type supports application role permissions and password encryption? Only OLE DB Only ODBC Only DBLib OLE DB and ODBC | |
3. | 70-229.06.03.003 If a connection is switched to application role permissions, what happens to the user's permissions and application role's permissions? They are fused, and the connection will have both the user's and application role's permissions. The user's permissions will be discarded, and only the application role's permissions will be used. If the user has higher permissions, the user's permissions will be used; otherwise, the application role's permissions will be used. Only those permissions will be available that exist in both the user's permissions and the application's permissions. | |
4. | 70-229.06.03.004 You found out that the application role's password has been compromised. You want to change the application role's password. How would you do it? You cannot do it by just changing the password. You have to drop the current application role and recreate it. You have to execute sp_approlechangepassword to change the application role's password. You have to execute sp_approlepassword to change the application role's password. You have to create a new application role with the new password and copy the permissions over with sp_copyapprolepermissions. | |
5. | 70-229.06.03.005 Can a connection use more than one application role, and is the application role valid in another database in the same SQL Server instance? Connections can use more than one application role but are valid only in the current database. Connections can use only one application role and are valid only in the current database. Connections can use more than one application role and are valid in all databases in the same instance of SQL Server 2000. Connections can use more than one application role and are valid in all databases. |
Answers
1. | B. You can switch an existing connection to application role permissions using the sp_setapprole stored procedure. |
2. | D. Both OLE DB and ODBC support password encryption, whereas DBLib does not. |
3. | B. The application role's permission will be used, and the user-related information will be ignored and discarded from the connection. |
4. | C. The application role's password can be changed without dropping the application role and re-creating it. |
5. | B. SQL Server allows only one application role per connection, and it is valid only in the current database the application role was created in. |
