You are a database developer on an instance of SQL Server 2008. Your Inventory database contains several tables that contain information related to historical inventory transactions.
You have created a Transact-SQL stored procedure that will accept a component type, query the tables in the Inventory database, and create another table containing all finished good products that have used the specified component. Tables referenced in the stored procedure are owned by different database users.
You want to allow a user, InvUser, to call the stored procedure using Transact-SQL, but you do not want to grant the user access to the underlying tables.
Which action should you take?
A. Create an application role and grant the role the needed permissions to the stored procedure. Grant InvUser membership in the application role.
B. Create a proxy user User1, grant User1 the needed permissions, and re-create the stored procedure including an EXECUTE AS User1 clause. Grant InvUser permission to execute the stored procedure.
C. Create a certificate and associate the certificate with InvUser. Sign the procedure with the certificate.
D. Grant InvUser permission to execute the stored procedure.
Correct Answer: B
Explanation/Reference:
You should create a proxy user User1, grant User1 the needed permissions, and re-create the stored procedure including an EXECUTE AS User1 clause. Then, you should grant InvUser permission to execute the stored procedure. In this scenario, you need the stored procedure to run under the security context of a specific user with elevated privileges who has access to objects owned by multiple database users. You want InvUser to be able to call the stored procedure without granting InvUser permissions on the underlying tables. An ownership chain is created when a database object, such as stored procedure, accesses another database object, such as an underlying table. If a user has access to the stored procedure and the underlying table has the same owner as the stored procedure, then explicit permission to the underlying table is not required. SQL Server will only check permissions on the underlying table if it has a different owner. However, you must note that ownership chaining does not occur when the stored procedure contains DDL.
In this scenario, the underlying tables are owned by multiple users, and the stored procedure contains DDL. Therefore, ownership chaining will not occur. To allow InvUser access, you can use the EXECUTE AS clause to force the stored procedure to execute with additional privileges. When creating a stored procedure, you can include the EXECUTE AS clause to specify the security context under which the stored procedure should execute. You can specify the following values in the EXECUTE AS clause:
SELF: EXECUTE AS SELF is equivalent to EXECUTE AS user_name, where the specified user is the person creating or altering the module. In other words, this will execute under the credentials of the user who last modified the stored procedure. For example, if Bam-Bam modified Fred’s stored proc and Wilma called Fred’s Proc, the proc would run under Bam-Bam.
OWNER: Specifies the stored procedure executes under the security context of the user that owns it. In other words, this will execute under the credentials of the login who owns the stored procedure. As explained previously, Fred’s stored proc will be run under Fred regardless of who executes it and who modified it.
CALLER: Specifies the stored procedure executes under the security context of the user calling it. To execute the stored procedure successfully, the user calling the stored procedure would require permissions on both the stored procedure and on any underlying database objects referenced by the stored procedure. user_name: Specifies the stored procedure executes under the security context of the specified user, regardless of which user called the stored procedure.
By default, a stored procedure executes under the security context of the caller. However, in this scenario you can create a proxy user with additional permissions, User1, and force the stored procedure to run under User1’s security context by creating the stored procedure with the EXECUTE AS User1 clause. Then, you can grant InvUser permission to execute the stored procedure. When InvUser executes the stored procedure, it will run with User1’s permissions.
You should not create an application role with the required permissions and grant InvUser membership in the application role because application roles are not granted directly to users. Application roles can allow users to use the database only through a custom application. The users are not directly assigned permissions, but rather are allowed permissions through the application role only. Using application roles prevents application users from performing any tasks outside the custom application. To implement an application role, you create an application role with a password using the CREATE APPLICATION ROLE statement. An application role does not contain users, only a password. Then, you assign the permissions required by the application to the application role. Finally, you code the application to authenticate users, and activate the appropriate application role using the sp_setapprole system stored procedure. After the application activates the role, the user will have only the role’s permissions, and any other database permissions granted to the user are disregarded.
You should not create a certificate and sign the procedure with the certificate. You can use certificates to provide permissions. To do so, you would create a certificate, associate the certificate with a specific user, and grant the user the needed permissions. Then, you would sign the stored procedure with the certificate.
When the procedure is called, it will execute with the certificate user’s permissions. You use the CREATE CERTIFICATE statement to create a certificate. To create a user associated with the certificate, you can use the CREATE USER statement and include the FROM CERTIFICATE clause. To sign the procedure, you can use the ADD SIGNATURE statement, specifying the stored procedure name, certificate, and certificate password. You should note that each time you alter the stored procedure you must re-sign the stored procedure.
You should not grant InvUser permission to execute the stored procedure. In the given scenario, the stored procedure would run by default the caller’s security context. Ownership chaining would not occur, and access to the underlying tables would be denied because the underlying tables have different owners.
