May 20, 2013 at 7:12 am
Hello all,
There are accounts that were created well before my time in which the users (developers) know the passwords. These accounts are being used to elevate access and perform tasks that they should not be doing.
Is there a way to create an alert that will notify me when user access is elevated or changed?
Thanks all who respond to this in advance, any input is greatly appreciated.
May 21, 2013 at 6:09 am
1. What sort of tasks are they performing?
2. Can anyone in the development team get you the passwords?
As bad as this might sound, you may even need to deactivate the accounts one by one and wait for someone to complain. The reason being is that over time, as applications become upgraded or obsolete, the accounts that belong to them are not removed. To that end, you could have active accounts that aren't being used, with elevated privileges that are a big security hole for you to fill.
It will take time but you need to begin to reduce the level of permission and privilege to each necessary and active account.
Check the logs to find out which accounts are running what jobs and then check the jobs to see exactly what they are doing. Here the main thing is to proactively search out the accounts being used rather than use an alert to tell you when they are being used. By then it may be too late.
May 21, 2013 at 11:11 am
When you say elevate access, do you mean people are logging in with these accounts and making changes they can't make from their own account?
I've disabled these in the past, as suggested above, and wait for the phone to ring. Ultimately this is an administrative and security issue.
May 21, 2013 at 1:03 pm
Yes, they use the accounts to do things they cannot do in the prod environment.
The SQL accounts have been coded into the apps.....
What I mean by "elevate" is the devs are using the accounts to grant or elevate other users access... So I was trying to find a way to send me a notification of when this occurrs so I can work from the "top down" to get a handle on this issue.
If I turn off the accounts an entire line of business or applications will be down...
May 21, 2013 at 3:09 pm
You still haven't explained what elevate means. Are they using the EXECUTE AS syntax? Or are they just connecting with these accounts?
May 21, 2013 at 3:46 pm
Do you mean, for example, that the sa password in mixed mode is coded into apps so it can never be changed and other users know what this is and can therefore do what they like?
.
May 21, 2013 at 6:43 pm
Tim Walker. (5/21/2013)
Do you mean, for example, that the sa password in mixed mode is coded into apps so it can never be changed and other users know what this is and can therefore do what they like?
Yes this is what I mean. I thought I was being clear. They are logging in with these accounts and adding access for other users, adding linked servers, creating SQL accounts etc. Basically the wild west.
May 22, 2013 at 4:57 am
For SQL2008+ I'd look into the Audit functionality first.
For SQL2005 or lower, SQL trace is your friend. See how you can create server-side traces on startup. 🙂
Cheers,
JohnA
MCM: SQL2008
May 23, 2013 at 5:54 am
As you say, "these accounts". For any account that is not specifically the SA account, can you reduce the permissions to simple read/write? What essential feature do the applications have beyond that?
Mike Hinds Lead Database Administrator1st Source BankMCP, MCTS
May 23, 2013 at 10:32 am
Mike Hinds (5/23/2013)
As you say, "these accounts". For any account that is not specifically the SA account, can you reduce the permissions to simple read/write? What essential feature do the applications have beyond that?
If the applications were sensibly designed the only access they would need would be execute on some stored procedures - why allow them read and write privileges when you have a bunch of what are clearly cowboy developers here? They clearly won't be sensibly designed if they were produced in the wild west environment described, but they may well need some execute permissions as well as read and write.
As someone said, auditing the creating of accounts and the adding or permissions to existing accounts is the way to go to get information about when this happens if you are on anything from SQL 2008 onwards. Also killing off the accounts that have been created that should not exist is something you can do - and when someone complains, you have discovered a person who was taking advantage of the poor security, and that information can be passed up to someone sufficiently senior to have some weight in dissuading that offender from continuing the practise.
But why are you not talking to whoever is in charge of the developers? You should do that before killing off any accounts anyway, and if he isn't happy for you to take action you will need to go up the management chain until you find someone who is. Of course if you find no such person you have a real problem.
Tom
May 23, 2013 at 1:08 pm
Definitely talk to the applications manager and inform them of the situation. I would approach this from the stance that the accounts hard-coded into the applications need to change and they need to change quickly. This could be an opportunity to examine the whole approach to security and grant permissions to roles and then grant the roles to the users who can run the applications instead, but that's another discussion.
As you continue up the food chain, someone will eventually care about this enough to take it seriously. There's some real damage that can be done if applications are logging in as 'sa' or other accounts that shouldn't be used from applications. I would really try and don't get discouraged by the push-back you're likely to receive - you're probably going to save the company from a disaster down the road if nobody does take action.
May 23, 2013 at 7:29 pm
L' Eomot Inversé (5/23/2013)
Mike Hinds (5/23/2013)
But why are you not talking to whoever is in charge of the developers? You should do that before killing off any accounts anyway, and if he isn't happy for you to take action you will need to go up the management chain until you find someone who is. Of course if you find no such person you have a real problem.
I have a real problem because the people in charge are not interested in paying off the technical debt they have created through "just getting it done" best practice and security be damned.....
May 24, 2013 at 5:18 am
Sorry to hear that. Just like the public debt, there is a price to be paid for this technical debt eventually. One thing about debt is that if you don't address it, it won't just go away. It could involve a little pain or a lot, but it will be paid eventually. Good luck.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply