Chapter 3 Protecting the Data (4): Creating and Using Application roles

原文出处：http://blog.csdn.net/dba_huangzj/article/details/39927713，专题目录：http://blog.csdn.net/dba_huangzj/article/details/37906349

No person shall, without the consent of the author, publish in the form of "original" or be used for commercial purposes without any liability.

Last article: http://blog.csdn.net/dba_huangzj/article/details/39639365

Preface:

Database roles are used to manage access and permissions within the database. Database role members are database users who can connect to SQL Server through client software such as SSMS, but you may want to grant some privileges to a particular user, but only one application is limited to SSMs, and the first solution is to use a dedicated SQL account to log in as an application. However, the disadvantage of this scenario is that you need to use SQL Server authentication and that you cannot identify which user is connecting to SQL Server. However, you can use the same application login name and use Windows Authentication to better identify the users of your application so that you can elevate permissions when necessary.

implementation:

1. Open SSMs, under security → role for a specific database, right-click application role, and select New application role:

2. Enter the role name, password, and optional default schema, or dbo if null:

3. On the Security Objects page, you can manage the permissions of an application role like a database role:

4. You can also use T-SQL to implement this operation:

CREATE application role marketingreports with PASSWORD = N ' A complex PASSWORD please ';

5. Use the sp_setapprole system stored procedure to modify the context of a session when the application role is used in a program:

EXEC sp_setapprole @rolename = ' marketingreports ',     @password = N ' A complex password please ';

After this stored procedure is executed, the current session will run under the context of the application role and can be authorized to override the permissions of the original database user.

principle:

Application roles can be used in code and can only use sp_setapprole stored procedures, which require encrypted transport links, such as SSL, because the password transfer is plaintext. After the context is switched, the context of the application role persists to a disconnected server, and if recovery is required but the connection is open, you can use Sp_unsetapprole:

DECLARE @cookie varbinary (8000); 
EXEC sp_setapprole @rolename = ' marketingreports ', 
     @password = N ' A complex password please ', 
     @fCreateCookie = Tru E, @cookie = @cookie OUTPUT; 
-do something, then revert: 
EXEC sp_unsetapprole @cookie;

MORE:

The following C # fragment allows you to use the application role immediately after the connection:

using (SqlConnection cn = new SqlConnection (connectionString)) 
{ 
    SqlCommand cmd = new SqlCommand (); 
    Cmd. Connection = cn; 
    Cmd. CommandType = CommandType.Text; 
    Cmd. CommandText = 
               "EXEC sp_setapprole @rolename = ' marketingreports ',  
@password = N ' A complex password please '"; 
    cn. Open (); 
    int res = cmd. ExecuteNonQuery (); 
}

Next article: