‫ SQL Server Security- 10th Section- Granting Row-Level Permissions and Creating Application Roles

Number: IRCAR201409231
Date: 2014-09-09
1.1.        Granting Row-Level Permissions in SQL Server
In some scenarios, there is a requirement to control access at a more granular level than that allowed by simply granting, revoking, or denying permissions on the data. For example, a hospital database application may store patient information in a single table. Doctors may need to be restricted to viewing information related to their own patients. Similar scenarios exist in many environments, including finance, law, government, and military applications. However, SQL Server does not have support for implementing row-level security. You must create additional columns in your tables that define row filtering mechanisms.

Row-level permissions are used for applications that store information in a single table. Each row has a column that defines a differentiating parameter, such as a user name, label or other identifier. You then create parameterized stored procedures, passing in the appropriate value. Users can see only rows that match the supplied value.
The following steps describe how to configure row-level permissions based on a user or login name.
  • Create the table, adding an additional column to store the name.
  • Create a view that has a WHERE clause based on the user name column. This will restrict the rows returned to those with the specified value. Use one of the built-in functions to specify a database user or login name. This eliminates the need to create different views for different users.
    ' Returns the login identification name of the user.
    WHERE UserName = SUSER_SNAME()
    ' USER_NAME or CURRENT_USER Return the database user name.
  • Create stored procedures to select, insert, update, and delete data based on the view, not the base tables. The view provides a filter that restricts the rows returned or modified.
  • For stored procedures that insert data, capture the user name using the same function specified in the WHERE clause of the view and insert that value into the UserName column.
  • Deny all permissions o÷n the tables and views to the public role. Users will not be able to inherit permissions from other database roles, because the WHERE clause is based on user or login names, not on roles.
  • Grant EXECUTE on the stored procedures to database roles. Users can only access data through the stored procedures provided.
1.2.        Creating Application Roles in SQL Server
Application roles provide a way to assign permissions to an application instead of a database role or user. Users can connect to the database, activate the application role, and assume the permissions granted to the application. The permissions granted to the application role are in force for the duration of the connection.


Security Note
Application roles are activated when a client application supplies an application role name and a password in the connection string. They present a security vulnerability in a two-tier application because the password must be stored on the client computer. In a three-tier application, you can store the password so that it cannot be accessed by users of the application.

Application roles have the following features:
  • Unlike database roles, application roles contain no members.
  • Application roles are activated when an application supplies the application role name and a password to the sp_setapprole system stored procedure.
  • The password must be stored on the client computer and supplied at run time; an application role cannot be activated from inside of SQL Server.
  • The password is not encrypted. The parameter password is stored as a one-way hash.
  • Once activated, permissions acquired through the application role remain in effect for the duration of the connection.
  • The application role inherits permissions granted to the public role.
  • If a member of the sysadmin fixed server role activates an application role, the security context switches to that of the application role for the duration of the connection.
  • If you create a guest account in a database that has an application role, you do not need to create a database user account for the application role or for any of the logins that invoke it. Application roles can directly access another database only if a guest account exists in the second database
  • Built-in functions that return login names, such as SYSTEM_USER, return the name of the login that invoked the application role. Built-in functions that return database user names return the name of the application role.

Application roles should be granted only required permissions in case the password is compromised. Permissions to the public role should be revoked in any database using an application role. Disable the guest account in any database you do not want callers of the application role to have access to.

The execution context can be switched back to the original caller after activating an application role, removing the need to disable connection pooling. The sp_setapprole procedure has a new option that creates a cookie, which contains context information about the caller. You can revert the session by calling the sp_unsetapprole procedure, passing it the cookie.

Application roles depend on the security of a password, which presents a potential security vulnerability. Passwords may be exposed by being embedded in application code or saved on disk.
You may want to consider the following alternatives.
  • Use context switching with the EXECUTE AS statement with its NO REVERT and WITH COOKIE clauses. You can create a user account in a database that is not mapped to a login. You then assign permissions to this account. Using EXECUTE AS with a login-less user is more secure because it is permission-based, not password-based.
  • Sign stored procedures with certificates, granting only permission to execute the procedures.



بدون نظر
شما برای نظر دادن باید وارد شوید


تاریخ ایجاد: 7 مهر 1393



امتیاز شما
تعداد امتیازها:0