This blog will no longer be updated.

New content is available on my new blog

Makesafe script - set up security for developers - Piotr Rodak

Makesafe script - set up security for developers

I worked yesterday on a script that is supposed to run on DEV database after it has been restored from production. This script adjusts configuration data and security in the database so developers can work safely on production data without compromising security policies.

The original script has a long history, over 7 or 8 years. I think it's origins reach out as far as to SQL Server 7.0. Parts responsible for creating user accounts for developers were written with old, deprecated now syntax, like sp_grantdbaccess for example. I decided to modify this part to use CREATE USER and other, more contemporary language constructs.

I like to use queries that generate script for me. I wrote about such utility to generate create database snapshot script some time ago. Since the list of developers and potentially their role assignments in the make safe script may change over time, I thought that this will be pretty reasonable approach, the only difference be that instead of returning generated script to output window, saving it in a variable and later executing by sp_executesql.

The script has pretty simple structure. The first part of it contains declarations and initialization of structures that define logins, users and rights that have to be implemented. An example of the script is displayed below:

--This table contains list of logins to be mapped to databases.
declare @DevLogins table
LoginName sysname, --name of login on the server
UserName sysname --name of user mapped to the login in databases

--PR: add or remove WINDOWS logins from here as aproppriate. Remember to modify @PermissionsToAdd table below.
--LoginName is the domain name of the account, UserName is name of the user mapped to the login in all databases
insert into @DevLogins(LoginName, UserName)
select 'DOMAIN\Developer1', 'Developer1' union all --dev account
select 'DOMAIN\Developer2', 'Developer2' union all --dev account
select 'DOMAIN\s-Developer3', 's-Developer3' --application account

--This table contains SQL logins and users used in databases
declare @SQLLogins table
LoginName sysname,
LoginPwd varchar(50),
UserName sysname

insert into @SQLLogins(LoginName, LoginPwd, UserName)
select 'devLogin1', 'pwd1', 'devUser1' union all --application account
select 'devLogin2', 'pwd2', 'devUser2' --application account

I decided to split Windows logins and SQL Logins as they will usually have different rights assigned. SQL logins can be used for example by applications during development stage, for some bulk load operations and so on.

I also added a variable that will control if the generated script is executed or only printed:

 --used to decide if actions should be executed or only printed.
declare @DEBUG char(1)
set @DEBUG = 'N'

 You can see how it works in this piece of script for example:

    raiserror('---## Dropping DEV logins..', 10, 1 ) with nowait;
select @RunSQL = convert(varchar(max),
(select 'if exists(select 1 from sys.server_principals where name = '''+
LoginName+''')' + char(10) +
' drop login ['+LoginName+'];' +
char(10) + char(10)
from @DevLogins for xml path('')))

raiserror(@RunSql, 10, 1) with nowait;

if @DEBUG = 'N'
exec sp_executesql @RunSQL
raiserror('---## DEV logins dropped.', 10, 1 ) with nowait;

As you see, the above script generates drop login statements. It prints out the generated code and executes it only if the value of @DEBUG variable is set to 'N'. The script generates statements for the values defined earlier in the @DevLogins table.

 In my case, there are several databases that have to have security set up. Since the required rights are same in all databases (in development environment), I decided to put the database names into a variable as well:

--This table contains list of databases belonging to the system
declare @APPDatabases table (DBName sysname)

insert into @APPDatabases(DBName)
select 'FEEDS' union all
select 'REPORTING' union all
select 'PROCESSING' union all
select 'ARCHIVE'

 Require drole membership is covered by another table variable:

	-- Verify if all users defined in @DevLogins are refrenced in this table
-- Add one row for each user-role pair. If user belongs to more than one role, two rows have to be added
INSERT INTO @PermissionsToAdd(UserID, DBRole)
select 'Developer1', 'DeveloperRole' union all
select 'Developer2', 'DeveloperRole' union all
select 's-Developer3', 'DeveloperRole'

 Note that if you want particular user to be a member of more than role, each assignment has to be added to the table.

Some developers should have to have special rights assigned, like ability to run profiler or investigate DMVs. These rights can be implemented in the following way:

	--this table contains names of custom grant statements required for all LOGINS.
--DON'T add here execute rights to particular objects in databases
declare @CustomRights table(GrantRight varchar(100))
insert @CustomRights(GrantRight)
select 'GRANT VIEW SERVER STATE ' union all --use dmvs
select 'GRANT ALTER TRACE ' --run profiler

 The rights are assigned in the following script. Note there is also a way to skip certain logins from the assignment. You don't necessarily want to have application logins to have all rights required by developers.

raiserror('---## Adjust DEV login rights..', 10, 1 ) with nowait;

--exclude application account from custom rights
declare @ExcludeAccounts table(LoginName sysname)
insert @ExcludeAccounts(LoginName) values('

select @RunSQL = convert(nvarchar(max), 'use master;' + char(10) + char(10) +
(select GrantRight + ' TO ' + quotename(LoginName) +';' + char(10) from @DevLogins cross join @CustomRights
where LoginName not in(select LoginName from @ExcludeAccounts) for xml path('')
) + char(10))

raiserror(@RunSql, 10, 1) with nowait;

if @DEBUG = 'N'
exec sp_executesql @RunSQL

raiserror('---## DEV logins rights adjusted.', 10, 1 ) with nowait;

And that's about it. You can modify the script to fit your needs for various environments. Even if you have to put some work initially in creating the script and setting up proper security for users, later you will have definitely less problems during the configuration of the environments. In fact, this script can be used to configure QA and UAT users, not only developers. I attach the full script, if you find the idea useful, please download it and adjust it to your needs.















Published Saturday, October 24, 2009 1:35 PM by Piotr Rodak


No Comments