Modifying the SQL Server 2005 execution Environment

The execution environment is a way to authenticate user rights in SQL Server, for example, when you log on to SQL Server, the login account is given certain permissions, which may include the ability to log on, access the database, and perform certain operations in the database.

SQL Server 2005 contains the EXECUTE AS statement, and by using the EXECUTE AS statement, you can execute the environment for batch and procedure transformations, so that users who invoke the batch or procedure can operate with different permissions.

Ownership chain

Before I take a formal look at the problem of executing an environment in SQL Server 2005, let's simply say how the ownership chain works.

When a user executes a stored procedure (assuming that the user has permission to execute the stored procedure), SQL Server compares the owner of the stored procedure with the owner of the object involved in the stored procedure, and if their owners are the same, then the permissions of those referencing objects do not have to be evaluated.

So, if the user Tim has access to the stored procedure Usp_procedurechain, and the Usp_procedurechain stored procedure owner is dbo, then if the DBO also owns the USP_ Procedurechain Other stored procedures that are invoked, then Tim does not have an error executing the stored procedure.

The transformation of the execution environment

In SQL Server 2000, you can use the SETUSER command to simulate the execution environment of a SQL user, but the problem is that only the system administrator or the owner of the database can use this command, and the Windows account cannot use the command.

In SQL Server 2005, the EXECUTE AS statement can replace SETUSER to alter the execution environment of a stored procedure, trigger, batch, or function. If the execution environment becomes another user, SQL Server checks the user's permissions. If you need to specify the EXECUTE AS statement when you create or modify a stored procedure or function, you need to have IMPERSONATE permissions and permissions to create the object.

Instance

As I've just described, changing the execution environment of stored procedures is useful, and I'll explain how to do that with an example. In this example, you'll see how to use EXECUTE as to simulate a user who has no exact permissions to insert a table for the owner.

In the first line of statements, I used the revert command so that you can return to the example completely without worrying about the need to clear any objects.

REVERT GO

In line seventh of the following code, I used the purge statement so that I could check if the object I was using in the subsequent example already exists, and if it already exists, clear it.

IF object_id (' usp_insertmytable ', ' P ') >0 DROP PRO Cedure usp_insertmytable Go IF object_id (' tableownerschema.mytable ', ' U ') >0 DROP TABLE Tableownerschema.mytable Go IF EXISTS (SELECT * from sys.schemas WHERE name = N ' Tableownerschema ') DROP SCHEMA [Tableownerschema] IF EXISTS (SELECT * from sys.database_principals WHERE name = N ' Baseuser ') DROP USER B Aseuser If EXISTS (SELECT * from sys.server_principals WHERE name = N ' Baseuser ') DROP LOGIN baseuser IF EXIST S (SELECT * from sys.database_principals WHERE name = N ' Tableowner ') DROP USER tableowner IF EXISTS (SELECT * From sys.server_principals WHERE name = N ' Tableowner ') DROP LOGIN tableowner

The following scripting statement creates two login names and user accounts for the database, note that the check_expiration and Check_policy statements are new in SQL Server 2005. These statements tell SQL Server not to enforce a password expiration policy on this user account, and not to perform any type of password policy check, which is a very effective way to enforce security policies.

CREATE LOGIN [BaseUser] WITH PASSWORD=N'baseuser', DEFAULT_DATABASE=[TRS], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF GO CREATE USER [BaseUser] FOR LOGIN [BaseUser] GO CREATE LOGIN [TableOwner] WITH PASSWORD=N'tableowner', DEFAULT_DATABASE=[TRS], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF GO CREATE USER TableOwner FOR LOGIN TableOwner GO

In SQL Server 2005, schemas are no longer the same thing as database users, and they are in a completely different namespace for the included objects. The separation of users and patterns is a major step in SQL Server 2005, which allows the ownership of objects to be detached and easier to manage than SQL Server 2000, which creates the database schema that we will use:

CREATE SCHEMA [TableOwnerSchema] AUTHORIZATION [TableOwner] GO Now I enable logins so they can be used: ALTER LOGIN [TableOwner] ENABLE ALTER LOGIN [BaseUser] ENABLE GO GRANT CREATE TABLE TO TableOwner GO

First, I used the EXECUTE AS command, and I set the current execution environment to Tableowner, and after running this command, all the rights evaluations will run in Tableowner, and the previous system administrator permissions will no longer apply.

EXECUTE AS USER = 'TableOwner' GO

Running this statement can indicate that the current execution environment is Tableowner:

SELECT SESSION_USER GO

This script will create a table named MyTable in Tableownerschema mode, because I have given the user the permission to create TABLE, so Tableowner can execute this statement.

CREATE TABLE TableOwnerSchema.MyTable ( Field1 INT ) GO

When I run the revert statement, you can step back in the execution environment chain, in SQL Server 2005, where the execution environment can be nested, so if you have many users running in the same database connection, you may need to execute the statement multiple times to return to the original login environment.

REVERT GO SELECT SESSION_USER GO

Now I'm going to make a quick selection of the new table to make sure it's there:

SELECT * FROM TableOwnerSchema.MyTable GO

The following script creates a procedure to insert a new tableownerschema.mytable table, noting that I used the with EXECUTE as ' in the process definition Tableowner ' statement, which means that when the process is executed, it will be executed in the Tableowner execution environment.

CREATE PROCEDURE usp_InsertMyTable WITH EXECUTE AS 'TableOwner' AS BEGIN INSERT INTO TableOwnerSchema.MyTable(Field1)VALUES(8) END GO

I can also assign execution permissions to a user account, in which case I use the previously created user named Baseuser.

GRANT EXEC ON usp_InsertMyTable TO BaseUser GO

Next, I convert the execution environment to Baseuser and try to run the stored procedure:

EXECUTE AS USER = 'BaseUser' GO EXEC usp_InsertMyTable GO

Now I can add a record to the Tableschema.mytable table because Tableowner allows me to do this in the process, and Baseowner does not have explicit permissions to add records to the table, so any attempt by that user can cause errors to occur. To illustrate this issue, you can run the following script, which changes the process we just made and runs in the caller's execution environment instead.

REVERT GO ALTER PROCEDURE usp_InsertMyTable AS BEGIN INSERT INTO TableOwnerSchema.MyTable(Field1)VALUES(8) END GO EXECUTE AS USER = 'BaseUser' GO EXEC usp_InsertMyTable GO REVERT

Developers and database administrators will find it useful to convert permissions when executing stored procedures, especially when you are working with TRUNCATE TABLE statements, because TRUNCATE TABLE does not have the permissions to specify. You can assign permissions to the user who will be intercepting the table, and then restore the original permissions at the end of the operation.