October 4, 2012 at 6:53 am
I am preparing MCITIP test, I got a question below. Can anyone give me a code example to demonstrate this? I have been a SQL Server developer for years but never touched anything other than dbo.
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, invllser, 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?
The answer is:
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.
This is the explanation:
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.
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: specifies the stored procedure executes under the security context of the current user. owner: specifies the stored procedure executes under the security context of the user that owns it. caller: specifies the stored procedure executes under the security context of the user calling it.
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 underuser1'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.
October 4, 2012 at 7:02 am
here's a couple of examples i made from another forum post.
this one was just a basic example, where something normally done by a sysadmin, can be delegated tro a proc that an end user can access.
--the EXECUTE AS must be a user in the database...not a login
CREATE procedure pr_CallBoostedSecurityProcess
WITH EXECUTE AS 'superman'
AS
BEGIN
dbcc freeproccache
END
this bigger example has the assumption that an end user needs to restore a database, but we don't want to give the end user dbbackupoperator priviledges, nore give the user dbo rights on a specific database.
instead, a proc was create dto allow that user the limited ability to do it.
USE MASTER;
--the basic setup: we assume we have a user and a user database:
IF NOT EXISTS(SELECT 1 from master.sys.databases where name = 'ClientX')
CREATE DATABASE ClientX;
IF NOT EXISTS (SELECT * FROM sys.server_principals WHERE name = N'ClientXAdmin' AND type = 'S') --'S' = SQL login
--create our super user
CREATE LOGIN [ClientXAdmin]
WITH PASSWORD=N'NotTheRealPassword',
DEFAULT_DATABASE=[ClientX],
CHECK_EXPIRATION=ON,
CHECK_POLICY=ON
--first we need a sysadmin role with no login, which will be used
--for execution context in the DDL triggers or special elevated permissions functions.
IF NOT EXISTS (SELECT * FROM sys.server_principals WHERE name = N'superman' AND type = 'S') --'S' = SQL login
BEGIN
--create our super user
CREATE LOGIN [superman]
WITH PASSWORD=N'NotTheRealPassword',
DEFAULT_DATABASE=[master],
CHECK_EXPIRATION=ON,
CHECK_POLICY=ON
--make our special user a sysadmin
EXEC master..sp_addsrvrolemember @loginame = N'superman', @rolename = N'sysadmin'
--noone will ever login with this, it's used for EXECUTE AS, so disable the login.
ALTER LOGIN [superman] DISABLE
END
GO
CREATE PROCEDURE sp_RestoreFullFileBackupForClientX(@PathToBackupFile nvarchar(1000))
WITH EXECUTE AS 'superman' --a user in master with the ability to restore databases.
AS
BEGIN --PROC
--get exclusive access to that database right now.
ALTER DATABASE [ClientX] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
--restore the requested database for the specific user.
RESTORE DATABASE [ClientX]
FROM DISK = @PathToBackupFile
WITH FILE = 1,
REPLACE,
NOUNLOAD,
STATS = 10
--after restore make sure specific roles must exist:
IF NOT EXISTS(SELECT 1
FROM ClientX.sys.database_principals
WHERE name = N'AlmostOwners' AND type = 'R')
EXEC('USE ClientX;
CREATE ROLE [AlmostOwners];
EXEC sp_addrolemember N''db_ddladmin'', N''AlmostOwners''
EXEC sp_addrolemember N''db_datareader'', N''AlmostOwners''
EXEC sp_addrolemember N''db_datawriter'', N''AlmostOwners''
--can the users EXECUTE procedures? comment out if false
GRANT EXECUTE TO [AlmostOwners]
--allow the users to see view proc and function definitions
Grant View Definition ON SCHEMA::[dbo] To [AlmostOwners]
');
IF NOT EXISTS(SELECT 1
FROM ClientX.sys.database_principals
WHERE name = N'ClientXAdmin' AND type = 'S')
EXEC('USE ClientX;
CREATE USER [ClientXAdmin] FOR LOGIN [ClientXAdmin];
EXEC sp_addrolemember N''AlmostOwners'', N''ClientXAdmin''
');
--just in case, set it to multi_user.
ALTER DATABASE [ClientX] SET MULTI_USER;
END --PROC
GO
--create a user in master for ClietnX
CREATE USER [ClientXAdmin] for LOGIN [ClientXAdmin];
GRANT EXECUTE ON sp_RestoreFullFileBackupForClientX TO ClientXAdmin;
GO
--the test harness
EXECUTE AS LOGIN = 'ClientXAdmin'
select suser_name();
--I'm ClientXAdmin!
--i KNOW this is a path to the database.
declare @path nvarchar(1000) = N'C:\data\backups\PERFECT1100_01052012.BAK'
exec sp_RestoreFullFileBackupForClientX @path
--change back into superman
REVERT;
--cleanup
/*
USE master;
drop database ClientX;
drop user ClientXAdmin;
drop login ClientXAdmin;
drop procedure sp_RestoreFullFileBackupForClientX;
*/
Lowell
October 4, 2012 at 7:45 am
Thank you Lowell, saw you almost every time. I will have a look at the code.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply