Impersonating a server level permission12
February 26, 2014 by Kenneth Fisher
Warnings up front, this has some serious security implications. The method I’m going to use minimizes that somewhat but it’s really easy to shoot yourself in the foot here, so be careful!
Impersonation allows you to grant a user the ability to mimic another user and gain access to all of the permissions that the impersonated user has. However if you have worked with this much you will know that you can only impersonate database level permissions. Or can you?
To start with data base level impersonation. There are 2 users, UserA and UserB. UserA is dbo and UserB can impersonate UserA. UserB can do anything that dbo can by impersonating UserA. We want to apply the principal of least privilege wherever possible so we start by only granting UserA those privileges that UserB needs to impersonate. However, we will probably soon have a UserC that needs a subset of those permissions. We don’t want to have to create yet another user to be impersonated, and we don’t want to grant UserC the extra privileges that UserA has. The solution is to create stored procedures that do the work. These stored procedures then use the EXECUTE AS clause to have the stored procedure run as if another user is actually running it. Then we grant execute access to that stored procedure.
An excellent example of this is creating a stored procedure that truncates a table.
-- Create table to truncate CREATE TABLE TruncateMe (Id int NOT NULL IDENTITY(1,1)) GO
-- User that has permission to truncate the table CREATE USER Imp_TruncateMe WITHOUT LOGIN -- Grant user ALTER permission so it can truncate the table GRANT ALTER ON TruncateMe TO Imp_TruncateMe GO
-- Create procedure to do the truncate impersonating Imp_TruncateMe CREATE PROCEDURE dbo.Truncate_TruncateMe WITH EXECUTE AS 'Imp_TruncateMe' AS TRUNCATE TABLE TruncateMe GO
Now in order to give someone permission to truncate our table we don’t have to grant the IMPERSONATE permission, or even the ALTER TABLE permission, we can just grant EXECUTE to the stored procedure.
That’s great but we want to impersonate a server level permission. To start with we need the TRUSTWORTHY setting of databases. So what does TRUSTWORTHY do? If the TRUSTWORTHY database setting is set to ON then the instance trusts EVERYTHING in that database. This means that any impersonated user in the database will have the ability to use the permissions of the associated login. This can have some pretty serious security implications. Personally I don’t know every implication of using TRUSTWORTHY but I think this one is pretty significant on it’s own.
Here is the scenario, you want to grant a junior DBA access to run DBCC HELP. Unfortunately this DBCC command requires membership in the sysadmin server role. You aren’t quite ready to give your junior DBA sysadmin permissions so you need a work around.
FYI if this seems contrived, it is. I couldn’t come up with a good server level permission on the fly. This should be good enough to get the point across though.
As a method of minimizing risk I put my “impersonation” stored procedures in a separate database when I’m setting TRUSTWORTHY ON. And even more particularly when I’m using a permission that requires sysadmin. Why? Because no matter how careful you are mistakes happen. Application databases tend to have fairly complicated permissions and eventually someone is granted db_owner and you have forgotten that they can now generate stored procedures that can impersonate a login with sysadmin permissions. I want a database where the users only have CONNECT to the database and EXECUTE to specific SPs. And those are the only users in the DB. That means that the SPs have to be created by a sysadmin, but I’m ok with that restriction.
In general the process runs like this:
- We create a new database and set the TRUSTWORTHY flag on
- We create a login with the permissions we want
- Set the login as the owner of the new database
- We create a stored procedure that does the work we want within the new database.
- We add the EXECUTE AS OWNER clause to the SP
I ran several tests here and the only way I could get it to work was by using EXECUTE AS OWNER. EXECUTE AS ‘UserName’ would not work even with TRUSTWORTHY ON. I did not try anything other than setting up the “OWNER” (schema of the SP) as dbo. It might work with a different schema but I suspect not.
-- Create a login to be the owner of our impersonation database USE master GO -- Make the password as obnoxious as possible because -- no one ever needs to or should log in as this login. CREATE LOGIN Imp_DBO WITH PASSWORD = 'VeryStrongPassword', CHECK_EXPIRATION = OFF, CHECK_POLICY = OFF; GO
-- Create database with TRUSTWORTHY set ON -- to contain our impersonation SP CREATE DATABASE ImpTest WITH TRUSTWORTHY ON; GO -- Change owner to login created for the purpose ALTER AUTHORIZATION ON DATABASE::ImpTest TO Imp_DBO; GO
-- Grant the login the permissions needed USE master; GO -- In this case sysadmin is required but only use it -- if it is REQUIRED! ALTER SERVER ROLE sysadmin ADD MEMBER Imp_DBO; GO
-- Create stored procedure to mimic DBCC HELP USE ImpTest GO -- SP must be in the dbo schema for this to work. CREATE PROCEDURE dbo.MyDBCCHelp (@dbcc_param varchar(50)) WITH EXECUTE AS OWNER AS DBCC HELP (@dbcc_param) GO
-- Create a login to test with USE master GO CREATE LOGIN Imp_User WITH PASSWORD = 'VeryStrongPassword', CHECK_EXPIRATION = OFF, CHECK_POLICY = OFF; GO
-- Create user in the ImpTest database and -- grant it execute to dbo.MyDBCCHelp USE ImpTest GO CREATE USER Imp_User FROM LOGIN Imp_User GO GRANT EXECUTE ON dbo.MyDBCCHelp TO Imp_User GO
Open a connection using the new login and test.
EXEC ImpTest.dbo.MyDBCCHelp 'CHECKDB'
Now our new login can run a sysadmin only DBCC command and the only permissions it has are to connect to the new database and to execute a stored procedure. I could even put additional controls into the new stored procedure if I wanted to, logging for example. I should note that you want to use ORIGINAL_LOGIN if you are logging user information when using impersonation in order to get the original login name.
Obviously this won’t work with views and functions as there is no EXECUTE AS clause.
Last time, when using this method of impersonation I create a separate database that has the absolute minimal security inside of it. The only users are those that need to execute the stored procedure(s) and they only have execute on the specific stored procedure(s) that they need. This avoids giving any access I don’t intend. You can create a world of problems if you aren’t careful.
Category: Impersonation, Microsoft SQL Server, Security, SQLServerPedia Syndication, T-SQL | Tags: code language, database permissions, Impersonation, language sql, microsoft sql server, security, server permissions, sql statements, T-SQL
12 thoughts on “Impersonating a server level permission”
Leave a Reply Cancel reply
This site uses Akismet to reduce spam. Learn how your comment data is processed.
Excellent tip! I have used this very successfully in the past by creating a stored procedure that will allow users to update only their password for only particular linked servers, something they wouldn’t normally have the permission to do unless I granted them way too many permissions.
Absolutely. I’m using it right now to create some SPs that allow a specific user the ability to clone a certain database and do COPY_ONLY backups to a specific directory to a group of databases.
Great post. personally, i user impersonate for Login and database user creating. So, with your example, how can i execute DBCC on another database.
I’m honestly not sure. I’ll play with it soon and let you know.
Cool tip! I was struggling with option to set actual user as db_owner for his newly restored DB – as he had no rights on the original DB. This little ‘trick’ helped to achieve that thing.
[…] will allow you to impersonate server level permissions. This has some scary potential if you aren’t careful about […]
[…] https://sqlstudies.com/2014/02/26/impersonating-a-server-level-permissions/ […]
[…] SQL Studies: Impersonating a server level permission […]
[…] only risk is if the database becomes TRUSTWORTHY. Then you can create stored procedures that can act as sa. It’s honestly not a huge risk even then since you have to have impersonate permissions to […]
[…] Impersonating a server level permission | SQL Studies […]
[…] link https://sqlstudies.com/2014/02/26/impersonating-a-server-level-permissions/ goes into more detail about using EXECUTE AS in production code. Needless to say, use this with […]