August 6, 2015 at 4:28 pm
I am a Infosec guy and don't know a whole lot of details about Database. But as a part of our infosec lockdown activity, we are giving a shared ID to all DBAs and would mandate them them to use that ID only. We have a tool from where they will do this activity.
The situation what I am getting in with this is, what if they add give their own Active directory ID Sysadmin privileges or create a instance level Sysadmin to bypass our process.
Please help, In very difficult situation here and nobody in my contacts seems to know an answer to this.
Thanks in Advance
August 6, 2015 at 4:49 pm
You'll be in a big pile of do, because you won't even know who did it - as you want them all using the same ID.
Perhaps you should just have one person who can type stuff, and anything they want to do, they just ask her?
MM
select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);August 7, 2015 at 4:08 am
I agree; do try to avoid having one shared ID for everybody. Use an AD group if you can.
August 7, 2015 at 9:28 am
ssk7317 (8/6/2015)
I am a Infosec guy and don't know a whole lot of details about Database. But as a part of our infosec lockdown activity, we are giving a shared ID to all DBAs and would mandate them them to use that ID only. We have a tool from where they will do this activity.The situation what I am getting in with this is, what if they add give their own Active directory ID Sysadmin privileges or create a instance level Sysadmin to bypass our process.
Please help, In very difficult situation here and nobody in my contacts seems to know an answer to this.
Thanks in Advance
The larger part of the problem with the "one ID" idea is that you'll lose the respect of the very people you expect to be compliant with that idea, because they are all going to know that they're no longer going to be held responsible for what they do with that id, as there will be no way for you to tell who did what. REALLY BAD IDEA !!! Losing their respect is the fastest road to non-compliance you could possibly take.
You need an AD global group for the purpose of restricting permissions. I don't know if it's possible to stop someone from creating a user with sysadmin priveleges, but you may be able to get a database trigger that can put a stop to it, and a trigger to log all changes or enables or disables of that trigger.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
August 7, 2015 at 9:32 am
It's been said but just to be sure:
One shared ID is an extremely bad idea. Administration doesn't become any easier and compliance to audits only becomes more of a bear.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
August 7, 2015 at 10:06 am
I understand that genrally making them use one ID is a disaster. But we are doing it along with tool called Cyberark which is the conduit for DBAs to access the database servers.
It logs who used the id at what time and also the sessions they create from Cyberark are recorded and can be referenced historically.
So we just want to make sure that DBAs dont outsmart cyberark bh creating new sa ids in database instance
August 7, 2015 at 10:08 am
Never share IDs, including service accounts. That's my philosophy. I don't even like SA available.
Always use groups. If one person needs access, someone else will.
As for preventing new additions, you can't. And you'll need to as people quit/leave/start/etc. Use groups, audit.
August 7, 2015 at 10:11 am
The other problem you will have to contend with (aside from not being able to prevent new sysadmin accounts), is that the quality of your DBAs will decrease as the good ones leave due to unreasonable working conditions.
MM
select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);August 7, 2015 at 10:30 am
ssk7317 (8/7/2015)
I understand that genrally making them use one ID is a disaster. But we are doing it along with tool called Cyberark which is the conduit for DBAs to access the database servers.It logs who used the id at what time and also the sessions they create from Cyberark are recorded and can be referenced historically.
So we just want to make sure that DBAs dont outsmart cyberark bh creating new sa ids in database instance
If they don't have direct access to the DB's without going through a tool how are they DBA's?
August 7, 2015 at 10:38 am
You can't prevent an sa from doing anything in sql server.
Implementing Big Brother wreaks of a lack of trust for your DBAs. Do your Domain Admins have to access AD Users and Groups through CyberArk? How about the Security Admins - are their sessions recorded as well?
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
August 7, 2015 at 12:48 pm
My thoughts on this are:
1. The SA account should be disabled. Enough said.
2. Never share IDs. Period.
3. Hire DBAs you trust.
4. Verify all you want, but the sysadmin role is the sysadmin role and you aren't going to prevent them from doing anything through permissions. For further information, see #3.
5. If you tie the DBA's hands to where they can't do anything, they're no longer able to do what you need them to do.
August 7, 2015 at 1:11 pm
Is this lockdown request coming down from on high or is this a policy your team is implementing yourselves? (Because that changes the answers we give you).
Also, a question people seem to have missed. Are you trying to lock all DBAs out of the system? Or just low level DBAs (as in ones that should only have access to create backups, or stuff like this)?
And if (as everyone assumes) you're locking all DBAs down, what permissions are you giving them? And who will hold the sysadmin keys to the kingdom?
August 7, 2015 at 1:26 pm
i took this post as an attempt to use a third party security audit solution that either has limited scope, or you might have limited experience with it.
it's easier to audit and keep track of one login, sure, so the initial reaction was to try and make everyone use a single login.
In the real world, it just not done.
since using a single login for multiple users is a bad practice, not sox compliant, and could easily get you in trouble with legal issues, you are getting a lot of feedback and kick back from experienced folks on this side of the fence. if you ran this up to a higher authority, they would push back as well; imagine everyone logging into Active Directory as "user!")
if my company wants to audit what i do, i'll help them create an audit solution, no problem.
Try and make the data less secure without thinking things through, and i'll explain why it's wrong.
push it through over my objections, and that's when i get the three envelopes out.
Lowell
August 7, 2015 at 2:40 pm
Real world experience....
We are not a 24-hour on duty DBA shop....after hours all production issues are handled by the on call DBA.
We have to abide by the client's security requirements as well as our own.
Recently we were told to completely lock down DBA access to all databases on a server that had confidential data.
We took time to sit down with all involved and explained the issues with what they wanted done:
1. DBA's need access to do maintenance and keep the databases up and accessible. When there are issues, the DBA needs to be able to access the databases to do their work.
2. Auditing can be put in place to record what is being accessed/done on the database.
3. If you still want DBA's locked out, we won't be able to maintain the databases on that server. Who will do the work instead?
End result, they wanted us all locked out with auditing in place. A specific login (Windows group account) would be created and granted Sysadmin on the databases. No one would be added to that account until it was necessary. There would be approved DBAs (a limited few) who could be granted access. So if something happened on the server's database(s), we would be notified. We would get written (email) permission to access the database(s), the approved DBA would be notified, that DBA would notify the server admins to add his/her login to the special Windows group account. The DBA would then access the database(s), do whatever work was necessary, then request his/her login be removed from the account. The decision makers were told this process would add greatly to the response time: the approved DBA would have to be notified, he/she would have to be available, they would have to notify the server admins to add their access, the update would have to replicate via Active Directory, then the work could start. After business hours, this could mean a one to two hour delay in getting the work STARTED.
The decision makers signed off on this....and it is the way it stands today. Every now and then when they 'complain' about how long it is taking to get the work done, we remind them it is 'because of the hoops you make us go through to do our job'. Resolving issues on that server have gone from around 5 minutes (during business hours) to just under one hour. After hours....from around 15 mins from the time of the page to around 2 hours.
So, if you are going to lock down the databases and reduce what the DBAs can do....expect much longer wait times for issues to be resolved as they work around the lack of permissions or go through hoops to get the proper permission.
-SQLBill
August 7, 2015 at 2:46 pm
Sqlbill, how do they restrict the network people that can grant access to the account/group so that they don't work in cahoots with the villain?
MM
select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);Viewing 15 posts - 1 through 15 (of 35 total)
You must be logged in to reply to this topic. Login to reply