Modifying the SQL Server 2005 execution Environment

Source: Internet
Author: User
Tags add end execution insert log sql return access

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.



Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.