Friday, September 13, 2013

How to Avoid the 'Abuse' of SysAdmin by Applying User Defined Roles in SQL 2012 (and keep Exec.s and Auditors Happy)

This will not be a typical post, just a dive right away into Data Access Language Code, which provides a method to avoid the SysAdmin fixed Server role in SQL Server for DBAs and Monitoring/Auditing Accounts, thanks to extensive explicit permissions and taking full advantage of SQL 2012-4 User Define Server Roles or Flexible Server Roles.

-- This script would be a required step to do post instance install and to apply flexible server roles
-- We are to apply this as a security policy in production environments, and then perform validation 
-- Could be applied on some Dev/UAT servers

-- start with a rollback / back-out - or clean out roles to start again (alternatively skip to line 50)
USE [master]
GO
Drop Server Role DBAs;
Drop Server Role Monitoring;
go
-- Add DBAs back to fixed server role sysadmin, unless on servers that will not be managed by DBAs
ALTER SERVER ROLE [sysadmin] ADD MEMBER [Group1]
ALTER SERVER ROLE [sysadmin] ADD MEMBER [MonitoringAccount1]
ALTER SERVER ROLE [sysadmin] ADD MEMBER [MonitoringAccount2]
ALTER SERVER ROLE [sysadmin] ADD MEMBER [AuditingAccount]
GO
-- drop explicit rights in master, model and msdb also
USE [msdb]
GO
ALTER ROLE [ServerGroupAdministratorRole] DROP MEMBER [DBAGroup2]
ALTER ROLE [ServerGroupAdministratorRole] DROP MEMBER [DBAGroup1]
GO
ALTER ROLE [SQLAgentOperatorRole] DROP MEMBER [DBAGroup2]
ALTER ROLE [SQLAgentOperatorRole] DROP MEMBER [DBAGroup1]
GO-- not supposed to exist, but just in case
ALTER ROLE [db_owner] DROP MEMBER [DBAGroup2]
ALTER ROLE [db_owner] DROP MEMBER [DBAGroup1]
GO
use model
go
GRANT select, insert, TAKE OWNERSHIP, view definition, update, execute, CONTROL, REFERENCES
         on schema::dbo to [DBAGroup2]
GRANT select, insert, TAKE OWNERSHIP, view definition, update, execute, CONTROL, REFERENCES
         ON SCHEMA::[dbo] TO [DBAGroup1]
GO -- don't forget you can easily enough do DENY permissions too to prevent data modificaci√≥n 
use master
go
-- All sections of this Security Hardening should correspond to a master Document/ed procedure
ALTER LOGIN [sa] enable
GO
use [Master]
go
drop USER [AuditingAccount] FOR LOGIN [AuditingAccount]
drop USER [MonitoringAccount1] FOR LOGIN [MonitoringAccount1]
drop USER [MonitoringAccount2] FOR LOGIN [MonitoringAccount2]
--- END CLEAN UP / Rollback of Role Security hardening


-- BEGIN SQL Security Hardening
--- disable SA, but do not drop it, maybe needed for service packs or for backout in Startup -m option (single-user mode)
ALTER LOGIN [sa] disable  -- ALTER LOGIN [sa] enable
GO
-- before applying any security policy, ensure BUILTIN\Administrators not there
USE MASTER
GO
IF EXISTS (SELECT * FROM sys.server_principals
WHERE name = NBUILTIN\Administrators)
DROP LOGIN [BUILTIN\Administrators]
GO


-- New in SQL 2012 - User Defined Server Roles, begin to take advantage of them
-- using roles rather than granting access to individuals, is a best practice in itself,
-- and the flexibility of user defined roles has become essential for many reasons
-- not limited to but including auditing, compliance, best management practices
Create Server Role DBAs Authorization [securityadmin]; 
-- where SecurityAdmin contains just a few who have FireCall IDs (elevated accounts, for rare use)

-- example with a few DBAs
IF NOT EXISTS (SELECT * FROM sys.server_principals
WHERE name = N’SeniorSQLDBA1)
CREATE LOGIN [SeniorSQLDBA1] FROM WINDOWS WITH DEFAULT_DATABASE=[master]

IF NOT EXISTS (SELECT * FROM sys.server_principals
WHERE name = N’SeniorSQLDBA2)
CREATE LOGIN [SeniorSQLDBA2] FROM WINDOWS WITH DEFAULT_DATABASE=[master]
go
-- add a few senior dbas to [securityadmin] only for now (unless Super User/System Admin account exists)
ALTER SERVER ROLE [securityadmin] ADD MEMBER [SeniorSQLDBA2]
ALTER SERVER ROLE [securityadmin] ADD MEMBER [SeniorSQLDBA1]
-- these senior dbas should backup each other in case of role issues and revised grants have to be applied
GO

IF NOT EXISTS (SELECT * FROM sys.server_principals
WHERE name = N’DBAGroup1)
CREATE LOGIN [DBAGroup1] FROM WINDOWS WITH DEFAULT_DATABASE=[master]

IF NOT EXISTS (SELECT * FROM sys.server_principals
WHERE name = N’DBAGroup2)
CREATE LOGIN [DBAGroup2] FROM WINDOWS WITH DEFAULT_DATABASE=[master]

CREATE
USER [DBAGroup1] FOR LOGIN [DBAGroup1]
CREATE USER [DBAGroup2] FOR LOGIN [DBAGroup2]
GO
-- add DBA groups to the role
Alter server role DBAs add member [DBAGroup2]
-- we could use a subset of the following grants for different 'levels' of DBAs
-- (i.e. a JuniorDBA flexible server role)
Alter server role DBAs add member [DBAGroup1]

-- now lock down for operations we as DBAs should not be doing anyway
-- do not forget to give WITH GRANT rights when necessary
-- (please validate in the GUI afterwards that DBAs role has the correct DAL)

-- (all necessary permissions to do DBA job, minus unnecessary privileges)
GRANT ADMINISTER BULK OPERATIONS TO [DBAs] WITH GRANT OPTION
GRANT ALTER ANY CONNECTION TO [DBAs] WITH GRANT OPTION
GRANT ALTER ANY CREDENTIAL TO [DBAs] WITH GRANT OPTION
GRANT ALTER ANY DATABASE TO [DBAs] WITH GRANT OPTION
GRANT Shutdown to DBAs
GRANT control server TO [DBAs]
-- Luckily Control Server permission respects the following DENYs
-- which is not the case for sysadmin fixed role
GRANT ALTER ANY EVENT NOTIFICATION TO [DBAs] WITH GRANT OPTION
GRANT ALTER ANY EVENT SESSION TO [DBAs] WITH GRANT OPTION
GRANT ALTER RESOURCES TO [DBAs]
GRANT ALTER SERVER STATE TO [DBAs]
GRANT ALTER SETTINGS TO [DBAs]
GRANT AUTHENTICATE SERVER TO [DBAs]
-- Grants or denies the ability to use a particular signature across all databases on the server
-- when impersonation is used.
GRANT CONNECT SQL TO [DBAs] WITH GRANT OPTION
-- Grants or denies the ability to connect to the SQL Server.
-- All logins, when newly created, are granted this permission automatically
GRANT CREATE ANY DATABASE TO [DBAs] WITH GRANT OPTION
-- GRANT CREATE AVAILABILITY GROUP TO [DBAs] (if you have Av. Groups at all)
GRANT CREATE DDL EVENT NOTIFICATION TO [DBAs]
GRANT CREATE TRACE EVENT NOTIFICATION TO [DBAs]
GRANT VIEW ANY DATABASE TO [DBAs] WITH GRANT OPTION
GRANT VIEW ANY DEFINITION TO [DBAs]
GRANT VIEW SERVER STATE TO [DBAs]
GRANT ALTER ANY EVENT NOTIFICATION TO [DBAs]
GRANT ALTER ANY EVENT SESSION TO [DBAs]
GRANT ALTER ANY LOGIN TO [DBAs]  -- some may want this as a DENY
--- Now the explicit denys (options)
DENY ALTER ANY AVAILABILITY GROUP TO [DBAs]
DENY ALTER ANY ENDPOINT TO [DBAs]
DENY ALTER ANY LINKED SERVER TO [DBAs]
-- debatable regarding linked servers
DENY ALTER ANY SERVER ROLE TO [DBAs] 
--obviously, we want control on number or roles
DENY ALTER TRACE TO [DBAs]
DENY CREATE ENDPOINT TO [DBAs]
Deny impersonate on login::sa to DBAs
-- add any other accounts that are individual users on the server with elevated rights,
-- and the Service Account(s)

Deny impersonate on login::[SERVICEaccountNameHere] to [DBAs]
Deny Alter any Server Audit to DBAs
Deny Unsafe Assembly to DBAs;
GO
-- resolve master grants
USE [master]
GO
GRANT select, view definition, execute, CONTROL, REFERENCES on schema::dbo to [DBAGroup2]
GRANT select, view definition, execute on schema::sys to [DBAGroup2]
GRANT EXECUTE ON xp_readerrorlog TO [DBAGroup2]  -- helpful to get DBAs to query the error log
GRANT EXECUTE ON sp_readerrorlog TO [DBAGroup2]
ALTER ROLE [db_datareader] ADD MEMBER [DBAGroup2]
GO
GRANT select, view definition, execute, CONTROL, REFERENCES on schema::dbo to [DBAGroup1]
GRANT select, view definition, execute on schema::sys to [DBAGroup1]
GRANT EXECUTE ON xp_readerrorlog TO [DBAGroup1]
GRANT EXECUTE ON sp_readerrorlog TO [DBAGroup1]
ALTER ROLE [db_datareader] ADD MEMBER [DBAGroup1]
GO
-- resolve MSDB grants
USE [msdb]
GO
CREATE USER [DBAGroup1] FOR LOGIN [DBAGroup1]
CREATE USER [DBAGroup2] FOR LOGIN [DBAGroup2]
go
ALTER ROLE [ServerGroupAdministratorRole] ADD MEMBER [DBAGroup1]
ALTER ROLE SQLAgentOperatorRole ADD MEMBER [DBAGroup1]
GO 
ALTER ROLE [ServerGroupAdministratorRole] ADD MEMBER [DBAGroup2]
ALTER ROLE SQLAgentOperatorRole ADD MEMBER [DBAGroup2]
GO -- after roles, grant explicit rights to be sure nothing is missing
GRANT select, execute, CONTROL, REFERENCES on schema::dbo to [DBAGroup2]
GO
GRANT select, execute, CONTROL, REFERENCES on schema::dbo ON SCHEMA::[dbo] TO [DBAGroup1]
GO -- allow DBAs to be part of msdb ownership if you like
--ALTER ROLE [db_owner] ADD MEMBER [DBAGroup1]
GO -- not necessary since in above Admnistrator and Operator roles
ALTER ROLE [db_ssisadmin] ADD MEMBER [DBAGroup2]
go

-- resolve issues for all new databases created - fix MODEL database to include DBAs.
Use Model
GO
-- setup DenyData reader role by default for groups to cover Prod data constraint
CREATE USER [DBAGroup1] FOR LOGIN [DBAGroup1]
CREATE USER [DBAGroup2] FOR LOGIN [DBAGroup2]
-- all user databases for production, will have deny read on the data
GO
-- use roles even at the database level
CREATE ROLE [DBAs] AUTHORIZATION [dbo]
GRANT VIEW DATABASE State, execute, view definition TO [DBAs]
-- remove read access when necessary, but allow administration
-- add appropriate groups
ALTER ROLE [DBAs] ADD MEMBER [DBAGroup2]
ALTER ROLE [DBAs] ADD MEMBER [DBAGroup1]
-- and finally, requirements for some prod. environments
Deny select, insert, update TO [DBAs] 
GO
-- or deny select in prod user dbs this way
ALTER ROLE [db_denydatareader] ADD MEMBER [DBAGroup2]
ALTER ROLE [db_denydatareader] ADD MEMBER [DBAGroup1]
GO


-- WE DO NOT STOP THERE, CONTINUE with Monitoring Accounts
-- which traditionally have way too many Privileges
-- and the goal is to adhere to the principle of least privileges
use master
GO
-- create monitoring role and add accounts the monitor the servers
Create Server Role [Monitoring] Authorization [securityadmin]; 
-- the right logins in the container
CREATE LOGIN [MonitoringAccount1] FROM WINDOWS WITH DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english]
CREATE LOGIN [MonitoringAccount2] FROM WINDOWS WITH DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english]
CREATE LOGIN [AuditingAccount] FROM WINDOWS WITH DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english]
ALTER SERVER ROLE [Monitoring] ADD MEMBER [MonitoringAccount1]
ALTER SERVER ROLE [Monitoring] ADD MEMBER [MonitoringAccount2]
ALTER SERVER ROLE [Monitoring] ADD MEMBER [AuditingAccount]
-- minimum WMI permissions required, so add to local admins on the server too
GRANT CONNECT SQL TO [Monitoring]
GRANT CONTROL Server TO [Monitoring]
GRANT ALTER TRACE TO [Monitoring]
GRANT VIEW ANY DATABASE TO [Monitoring]
GRANT VIEW ANY DEFINITION TO [Monitoring]
GRANT VIEW SERVER STATE TO [Monitoring]
Grant CREATE DDL EVENT NOTIFICATION TO [Monitoring]
GRANT CREATE TRACE EVENT NOTIFICATION TO [Monitoring]
-- because we have granted control server, we must apply these DENY statements
DENY ALTER ANY AVAILABILITY GROUP TO [Monitoring]
DENY ALTER ANY ENDPOINT TO [Monitoring]
DENY ALTER ANY LINKED SERVER TO [Monitoring]
DENY ALTER ANY LOGIN TO [Monitoring]
DENY ALTER ANY SERVER ROLE TO [Monitoring] 
--obviously, we want control on number or roles
DENY CREATE ENDPOINT TO [Monitoring]
Deny impersonate on login::sa to [Monitoring]
-- add any other accounts that are individual users on the server that have Sysadmin rights
Deny impersonate on login::[SERVICEaccountNameHere] to [Monitoring]
Deny Alter any Server Audit to [Monitoring]
Deny Unsafe Assembly to [Monitoring];

-- Monitoring users need to be in Master and Model, MSDB as DataReader
Go
CREATE
USER [MonitoringAccount1] FOR LOGIN [MonitoringAccount1]
CREATE USER [MonitoringAccount2] FOR LOGIN [MonitoringAccount2]
CREATE USER [AuditingAccount] FOR LOGIN [AuditingAccount]
--
ALTER ROLE [db_datareader] ADD MEMBER [MonitoringAccount1]
ALTER ROLE [db_datareader] ADD MEMBER [MonitoringAccount2]
ALTER ROLE [db_datareader] ADD MEMBER [AuditingAccount]
-- or more explicitly
grant select, view definition, execute on schema::dbo to [AuditingAccount]
grant select, view definition, execute on schema::sys to [AuditingAccount]
GRANT EXECUTE ON xp_readerrorlog TO [AuditingAccount]
GRANT EXECUTE ON sp_readerrorlog TO [AuditingAccount]
GO
grant
select, view definition, execute on schema::dbo to [MonitoringAccount1]
grant select, view definition, execute on schema::sys to [MonitoringAccount1]
GRANT EXECUTE ON xp_readerrorlog TO [MonitoringAccount1]
GRANT EXECUTE ON sp_readerrorlog TO [MonitoringAccount1]
GO
grant select, view definition, execute on schema::dbo to [MonitoringAccount2]
grant select, view definition, execute on schema::sys to [MonitoringAccount2]
GRANT EXECUTE ON xp_readerrorlog TO [MonitoringAccount2]
GRANT EXECUTE ON sp_readerrorlog TO [MonitoringAccount2]
GO
USE
[msdb]
GO -- the options here for system databases are to either continue to create user defined roles, or used fixed if possible
CREATE USER [MonitoringAccount1] FOR LOGIN [MonitoringAccount1]
CREATE USER [MonitoringAccount2] FOR LOGIN [MonitoringAccount2]
CREATE USER [AuditingAccount] FOR LOGIN [AuditingAccount]
GO
grant
execute on schema::dbo to [MonitoringAccount1]
grant
execute on schema::dbo to [MonitoringAccount2]
grant
execute on schema::dbo to [AuditingAccount]
GO
-- improve this by adding appropriate role?
ALTER ROLE [db_datareader] ADD MEMBER [AuditingAccount] -- may need to use more elevate fixed roles here
ALTER ROLE [db_datareader] ADD MEMBER [MonitoringAccount1]
ALTER ROLE [db_datareader] ADD MEMBER [MonitoringAccount2]
GO
-- add model for defaults similar DBA groups
Use Model
GO
CREATE USER [AuditingAccount] FOR LOGIN [AuditingAccount]
CREATE USER [MonitoringAccount1] FOR LOGIN [MonitoringAccount1]
CREATE USER [MonitoringAccount2] FOR LOGIN [MonitoringAccount12]
-- all user databases for production, will have deny read on the data
GO  -- create  monitoring database role
CREATE ROLE [Monitoring] AUTHORIZATION [dbo]
GRANT VIEW DATABASE State, execute, view definition TO [Monitoring]
-- remove read access when necessary, but allow administration
Deny select, insert, update TO [Monitoring]  -- requirements for some prod. environments
-- add appropriate groups or accounts for auditing or monitoring
ALTER ROLE [Monitoring] ADD MEMBER [AuditingAccount]
ALTER ROLE [Monitoring] ADD MEMBER [MonitoringAccount1]
ALTER ROLE [Monitoring] ADD MEMBER [MonitoringAccount1]

GO

USE [master]
GO
-- cleanup those who were in sysadmin
ALTER SERVER ROLE [sysadmin] DROP MEMBER [DBAGroup2]
ALTER SERVER ROLE [sysadmin] DROP MEMBER [DBAGroup1]
ALTER SERVER ROLE [sysadmin] DROP MEMBER [AuditingAccount]
ALTER SERVER ROLE [sysadmin] DROP MEMBER [MonitoringAccount1]
-- you can truly state that you have reduced the SysAdmin elevate account access to your auditors
GO
-- validate that only those who you want are noe in sysadmin

use master
go
exec sp_helpsrvrolemember 'sysadmin'
-- reduce members to bare minimum !!


-- new for sql 2012: check which services are listed:
select * from sys.dm_server_services


-- for each new application added, one should verify the wanted/identifiable
-- (and not unwanted) logins are viewed
select * from sys.dm_exec_connections


--final review of permissions 
SELECT * FROM sys.fn_builtin_permissions('SERVER')
ORDER BY permission_name;

-- again but at a different level
SELECT * FROM sys.fn_builtin_permissions('default')
ORDER BY permission_name;

-- database securable
SELECT * FROM sys.fn_builtin_permissions('database')
ORDER BY permission_name;