Since I have not blogged in a while, and I saw it was T-SQL Tuesday, I thought I would participate in this FOR THE FIRST TIME EVER! This month SQLBalls asks us if our SQL Servers are on the naughty or nice list. Since I have recently transitioned to a new role, I am still getting familiar with the servers and the environments I am working with. The servers of my past are but a memory, but a fresh one. I cannot even begin to think of all the different “naughty” things that were done on those servers, and how some days it felt like a losing battle. How can you fight a security or code change when you have been over ridden by management, only to have that change come back and bite you weeks or months later? The “I told you so” you might feel like popping off will fall on deaf ears and you will just be stuck fixing the problem…until it gets to be too much and you decide it is time for action.
I have done this before. The day had been a long one and I thought I was finally going home when I got drug into a call on a data issue – something was changed that shouldn’t have been. When and by whom? I didn’t know or have any way of finding this information (days later when I tried to get a backup that was several months old to validate this data from when it was deployed; there was no backup – all all – but that is a completely different issue for another time). I had my suspicions but no proof. Could have been a naughty developer elf logging in with an elevated SQL account they knew the password for. Changing the password? “Out of the question” they say. It is everywhere.
If this were the only event that had happened THAT DAY. It wasn’t. This one was production (hence the conference call). The others (yes, more than one) were pre-production. Messes that had to be cleaned up because the developer elves thought they knew better than the DBA and that they could do it on their own. This production issue was the last straw. Elves were running a muck and had to be reigned in, and they weren’t going to like it.
All the elevated permissions in the pre-production servers – gone. I didn’t care if it was a DEV server. Am I the meanest DBA in the world? So says some. Scrooge? Well, if you are into name calling and want to go there, then ok, but I get to call you names too. In this case I did not care – I was fixing things that were only broken because someone abused a privilege. It should also be said that there was some relation in the names off all the tables involved with all the issues that occurred on this day.
If I had to wag my finger and any naughty part of the SQL Server instances it would have to be at security…and I am partially to blame. It can be difficult to keep up with all the changes that happen across a large environment when it comes to assigning permissions, and if you have more than one DBA, the situation is compounded by the fact that you might not always know what the other is doing and vice versa. They might grant something that you would otherwise veto for cause. You might take care of a permissions issue one way when they would handle it differently.
While I worked on some Powershell code to pull back users from specific AD groups and incorporate alerts for some of those groups, sadly the bandwidth was not there to fully roll this out. I did however create some triggers that would send email alerts when a change was made at the server and database levels, and I made them nameless and encrypted.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 |
/*************ALERTS FOR SERVER ROLES***************/ ALTER TRIGGER [ ] ON ALL SERVER WITH execute as 'sa',encryption FOR ADD_SERVER_ROLE_MEMBER AS BEGIN DECLARE @event XML, @rolename VARCHAR(50),@user_added VARCHAR(50),@adding_user VARCHAR(50) ,@mailbody VARCHAR(8000) ,@mailsubject VARCHAR(200) ,@servername VARCHAR(50) SET @event = EVENTDATA() SELECT @rolename = @event.value('data(/EVENT_INSTANCE/RoleName)[1]', 'SYSNAME') ,@user_added = @event.value('data(/EVENT_INSTANCE/ObjectName)[1]', 'SYSNAME') ,@adding_user = @event.value('data(/EVENT_INSTANCE/LoginName)[1]', 'SYSNAME') SET @servername = @@SERVERNAME SET @mailsubject = 'USER ADDED TO ROLE - '+@servername SET @mailbody = 'The following change has been made to the '+@rolename+' on '+@servername+':'+CHAR(13)+CHAR(10) +'User Added: '+@user_added+CHAR(13)+CHAR(10) +'Added by: '+@adding_user+CHAR(13)+CHAR(10) EXEC msdb.dbo.sp_send_dbmail @profile_name = 'DBMailProfile', @recipients = 'alert@yourcompany.com', @body = @mailbody, @subject = @mailsubject; END /*************DATABASE LEVEL ALERTS**************/ CREATE TRIGGER [ ] ON ALL SERVER WITH execute as 'sa',encryption FOR DDL_DATABASE_SECURITY_EVENTS AS BEGIN DECLARE @event XML, @rolename VARCHAR(50),@user_added VARCHAR(50),@adding_user VARCHAR(50) ,@mailbody VARCHAR(8000) ,@mailsubject VARCHAR(200) ,@servername VARCHAR(50) ,@event_type varchar(100) ,@database varchar(50) ,@datetime varchar(100) ,@command varchar(4000) SET @event = EVENTDATA() SELECT @adding_user = @event.value('data(/EVENT_INSTANCE/LoginName)[1]', 'SYSNAME') ,@event_type = @event.value('data(/EVENT_INSTANCE/EventType)[1]', 'SYSNAME') ,@database = @event.value('data(/EVENT_INSTANCE/DatabaseName)[1]', 'SYSNAME') ,@servername = @event.value('data(/EVENT_INSTANCE/ServerName)[1]', 'SYSNAME') ,@datetime = @event.value('data(/EVENT_INSTANCE/PostTime)[1]', 'SYSNAME') ,@command = @event.value('data(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]', 'SYSNAME') SET @servername = @@SERVERNAME SET @mailsubject = 'DATABASE SECURITY CHANGE - '+@servername SET @mailbody = 'The following change has been made to the '+@database+' database on '+@servername+':'+CHAR(13)+CHAR(10) +'Change Made By: '+@adding_user+CHAR(13)+CHAR(10) +'Event Type: '+@event_type+CHAR(13)+CHAR(10) +'Server Name: '+@servername+CHAR(13)+CHAR(10) +'Database Name: '+@database+CHAR(13)+CHAR(10) +'Date/Time: '+@datetime+CHAR(13)+CHAR(10) +'SQL Statement: '+@command+CHAR(13)+CHAR(10) EXEC msdb.dbo.sp_send_dbmail @profile_name = 'DBMailProfile', @recipients = 'alert@yourcompany.com', @body = @mailbody, @subject = @mailsubject; END |
The lack of a name for each of these is intentional, as is the encryption. The last thing I wanted was someone seeing these ans what they were doing, and if they had permissions to do so, disabling or dropping them to avoid having their nefarious behavior tracked. Even better would have been to put additional triggers in place to prevent the dropping of these no matter what, but I decided not to go there.
Note there is nothing there for the name for each of these – this is courtesy of the devious mind of Rob Volk. He might have too much time on his hands but this is pretty darn crafty. What you name these triggers is up to you but you have to MAKE NOTE OF WHAT THE NAME ARE!!! When I did these they were a combination of a few tabs and spaces – like “space space space tab tab” but with those actual characters. The result looks like this:
It should go without saying use this code at your own risk and always thoroughly vet and test anything before applying it to a production environment.
If this helps further cement my meanest DBA creds then I guess I am doing it right. Sometimes the elves developers can get out of hand and it is up to Santa the DBA to make sure they know they are being watched.
