CommitDBA

Database Management Simplified

Ross Group Inc  MemberzPlus  Blogs  |  News  |  Careers  |  Contact    Twitter CommitDBA LinkedIn CommitDBA

CommitDBA Blogs


See What we are Talking About!

CommitDBA Blog


Frank Wright, CommitDBA staff Database Administrator

User Based Security

By: Frank Wright, CommitDBA staff Database Administrator

October 28, 2011


Situation

You are a busy DBA who wears multiple hats. Wherever possible you try to automate and/or delegate any repetitive maintenance or action items. The most recent situation is a new application that utilizes Windows Authentication to connect to the SQL database. This application is quickly becoming high maintenance because it is used by a department with very high turnover. Every time there is a new hire or leave/termination you are tasked with handling the respective security in the database. You want to be able to turn this over to the manager of the department. You might ask why not just create an AD group, and one time assign database privileges to that group login, as that would be the simplest solution. But that now puts the burden on the Network Administrator who is a contractor and only works 1-2 days a week and granting AD admin access to the department manager is NOT something you want to do.

Technical Review

Overview

We will examine security fundamentals and what is necessary to accomplish the stated objective. If all I need to do is allow a user to perform a controlled insert into a table then a simple stored procedure will do. If a user who has permissions to insert into the specified table (and create a procedure) creates a procedure to do just that, then all that is necessary is to grant execute permissions on that procedure to another user. If this other user does not have privileges to directly insert into the table they will be able to accomplish it via this procedure. The owner of the procedure is checked by the SQL engine to make sure they have the correct privileges to perform these actions. If I wanted to make sure that the calling user had to have the privileges directly as well in order to run the procedure, then I would add an EXECUTE as CALLER clause(see syntax below) to the specification of the procedure. The SQL engine would then check the privileges of the caller instead of the procedure owner.

Where this breaks down is when the procedure needs to do something besides basic query and DML activity. If the procedure needs to perform any DDL or Dynamic SQL(built and evaluated at runtime) then the privileges of the caller are checked for those statements and NOT the owner. If the procedure is marked to run as SELF (the user who created the proc) or an explicit username then that user is the one whose permissions are checked at runtime by the SQL engine. This will allow a procedure with DDL or Dynamic SQL to be executed successfully by another user.

Now if the procedure needed to perform instance level activity, such as login security tasks, then even the EXECUTE AS SELF/explicit_username is not sufficient as I have found this only works within the scope of the database not the instance. There are a couple other things that need to be done. The user who will execute the procedure or commands must given IMPERSONATE privileges on the login/user that does have permissions to perform the instance level activity.

Syntax and Examples

Generic Syntax:

CREATE PROCEDURE dbo.myProc
[WITH EXECUTE AS { OWNER | CALLER | SELF | <explicit_username> } ]
AS
BEGIN

END

CREATE PROCEDURE dbo.myProc
AS
BEGIN
EXECUTE AS LOGIN=’<explicit_loginname>’

REVERT
END

GRANT IMPERSONATE ON LOGIN::[<login1>] TO [<login2>]
GRANT IMPERSONATE ON USER::[<user1>] TO [<user2>]

Solution(s)

Setup script to be run by a SQL DBA or a login with sysadmin. This script will setup a login that will have the ability to create logins but is prevented from being able to login directly. The permissions to impersonate this login will be granted to the department manager. Finally the procedure that will handle the appropriate login maintenance will be created along with granting permissions on the procedure to the department manager.

USE [master]
GO
CREATE LOGIN [dbAutoLogin] WITH PASSWORD=N'abc123$%^', DEFAULT_DATABASE=[<userdb>], CHECK_EXPIRATION=ON, CHECK_POLICY=ON
GO
EXEC master..sp_addsrvrolemember @loginame = N'dbAutoLogin', @rolename = N'securityadmin'
GO
DENY CONNECT SQL TO [dbAutoLogin]
GO
GRANT IMPERSONATE ON LOGIN::[dbAutoLogin] TO [<deptmgr>]
GO

USE [<userdb>]
GO
CREATE USER [dbAutoLogin] FOR LOGIN [dbAutoLogin]
GO
EXEC sp_addrolemember N'db_securityadmin', N'dbAutoLogin'
GO
GRANT IMPERSONATE ON USER::[dbAutoLogin] TO [<deptmgr>]
GO

CREATE PROCEDURE [dbo].[sp_loginproc] (@action varchar(1), @userID varchar(50))
AS
BEGIN
EXECUTE AS LOGIN=’dbAutoLogin’
DECLARE @execSQL varchar(255)
IF @action = ‘A’ AND
NOT EXISTS(SELECT 1 FROM master.sys.syslogins WHERE [name] = @userID)
BEGIN
SET @execSQL = 'use master
CREATE LOGIN ['+@userID+'] FROM WINDOWS WITH DEFAULT_DATABASE=[<userdb>];'
EXEC (@execSQL)
SET @execSQL = 'use <userdb>
CREATE USER ['+@userID+'] FOR LOGIN ['+@userID+'];'
EXEC (@execSQL)
EXEC sp_addrolemember N'<userdbrole>', @userID
END
ELSE IF @action = ‘D’ AND
EXISTS(SELECT 1 FROM master.sys.syslogins WHERE [name] = @userID)
BEGIN
EXEC sp_droprolemember N'<userdbrole>', @userID
SET @execSQL = 'use <userdb>
DROP USER ['+@userID+'];'
EXEC (@execSQL)
SET @execSQL = 'use master
DROP LOGIN ['+@userID+'];'
EXEC (@execSQL)
END
ELSE RAISERROR(‘action must be in (‘‘A’’,‘‘D’’)
and login existence must not conflict’,11,1)

REVERT
END
GO
GRANT EXECUTE ON [dbo].[sp_loginproc] TO [<deptmgr>]

This solution should be made more robust and for the experienced DBAs they ought to be able to see the security hole/flaw that exists with this implementation. I will follow up with another article that will show a more secure implementation.

From database assessments to complete database management, CommitDBA can assist your company today. For more information about how CommitDBA can work for you – give us a call at (800) 734-9304.



Got database support needs?
Professional and Affordable DBA and Data Services

Contact us Today