May 18, 2011 at 7:11 am
I inherited a database where the db users were granted db_ownership. After spending hours on the phone yesterday explaining the db change management process, the developer was given permission by a manager to make a change directly in production without my consent. Now, it seems like I have no choice but to lock down the database in production. What is the best way to go about doing this without breaking the application? Deny alter and create rights on the user? Would that affect anything but being able to create/alter objects? I know this is backward thinking, since "db_owners" are just that, owners of the database, who should be able to do anything.
Thanks in advance for your help! 🙂
May 18, 2011 at 7:15 am
DBAgal (5/18/2011)
I inherited a database where the db users were granted db_ownership. After spending hours on the phone yesterday explaining the db change management process, the developer was given permission by a manager to make a change directly in production without my consent. Now, it seems like I have no choice but to lock down the database in production. What is the best way to go about doing this without breaking the application? Deny alter and create rights on the user? Would that affect anything but being able to create/alter objects? I know this is backward thinking, since "db_owners" are just that, owners of the database, who should be able to do anything.
Obviously you do not have support from upper management which leads to me think you have two alternatives:
a) deal with it and keep a bottle of brandy by your desk.
b) update your resume and find a job in a company that will appreciatte you being a good professional.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.May 18, 2011 at 7:21 am
DBAgal (5/18/2011)
I inherited a database where the db users were granted db_ownership. After spending hours on the phone yesterday explaining the db change management process, the developer was given permission by a manager to make a change directly in production without my consent. Now, it seems like I have no choice but to lock down the database in production. What is the best way to go about doing this without breaking the application? Deny alter and create rights on the user? Would that affect anything but being able to create/alter objects? I know this is backward thinking, since "db_owners" are just that, owners of the database, who should be able to do anything.Thanks in advance for your help! 🙂
what I think i would do is create a new role, Say "DEVUsers", and create it like this:
CREATE ROLE [DEVUsers]
--allow executing of stored procs
GRANT EXECUTE TO [DEVUsers]
--allow them to VIEW object definitions, but not change them
GRANT VIEW DEFINITION TO 'DEVUsers'
--allow data read
EXEC sp_addrolemember N'db_datareader', N'DEVUsers'
--allow data write
EXEC sp_addrolemember N'db_datawriter', N'DEVUsers'
after that, i'd remove the dbowner role from their login, and add this one instead.
if anyone ever comes back and says they need to ALTER a VIEW for example, I'd kicke it back and say it has to go through the proper database change procedures.
Lowell
May 18, 2011 at 7:27 am
Lowell (5/18/2011)
DBAgal (5/18/2011)
I inherited a database where the db users were granted db_ownership. After spending hours on the phone yesterday explaining the db change management process, the developer was given permission by a manager to make a change directly in production without my consent. Now, it seems like I have no choice but to lock down the database in production. What is the best way to go about doing this without breaking the application? Deny alter and create rights on the user? Would that affect anything but being able to create/alter objects? I know this is backward thinking, since "db_owners" are just that, owners of the database, who should be able to do anything.Thanks in advance for your help! 🙂
what I think i would do is create a new role, Say "DEVUsers", and create it like this:
CREATE ROLE [DEVUsers]
--allow executing of stored procs
GRANT EXECUTE TO [DEVUsers]
--allow them to VIEW object definitions, but not change them
GRANT VIEW DEFINITION TO 'DEVUsers'
--allow data read
EXEC sp_addrolemember N'db_datareader', N'DEVUsers'
--allow data write
EXEC sp_addrolemember N'db_datawriter', N'DEVUsers'
after that, i'd remove the dbowner role from their login, and add this one instead.
if anyone ever comes back and says they need to ALTER a VIEW for example, I'd kicke it back and say it has to go through the proper database change procedures.
Yes, that came to my mind, but I would want to test the role in a dev system, but there is no dev application server for this app. I'm always afraid that 3rd party applications will do something funky and I would end up breaking something that I would never have thought of.
May 18, 2011 at 7:46 am
ugh;
no dev area and developers making direct changers to production(since it;'s the only work area?); Paul's advice about liquor and updating the resume sounds even better now....
how about a DDL audit trigger instead, so you can simply report to management "the following changes were made without authorization"
Lowell
May 18, 2011 at 7:57 am
DBAgal (5/18/2011)
Lowell (5/18/2011)
DBAgal (5/18/2011)
I inherited a database where the db users were granted db_ownership. After spending hours on the phone yesterday explaining the db change management process, the developer was given permission by a manager to make a change directly in production without my consent. Now, it seems like I have no choice but to lock down the database in production. What is the best way to go about doing this without breaking the application? Deny alter and create rights on the user? Would that affect anything but being able to create/alter objects? I know this is backward thinking, since "db_owners" are just that, owners of the database, who should be able to do anything.Thanks in advance for your help! 🙂
what I think i would do is create a new role, Say "DEVUsers", and create it like this:
CREATE ROLE [DEVUsers]
--allow executing of stored procs
GRANT EXECUTE TO [DEVUsers]
--allow them to VIEW object definitions, but not change them
GRANT VIEW DEFINITION TO 'DEVUsers'
--allow data read
EXEC sp_addrolemember N'db_datareader', N'DEVUsers'
--allow data write
EXEC sp_addrolemember N'db_datawriter', N'DEVUsers'
after that, i'd remove the dbowner role from their login, and add this one instead.
if anyone ever comes back and says they need to ALTER a VIEW for example, I'd kicke it back and say it has to go through the proper database change procedures.
Yes, that came to my mind, but I would want to test the role in a dev system, but there is no dev application server for this app. I'm always afraid that 3rd party applications will do something funky and I would end up breaking something that I would never have thought of.
Sounds like the perfect opportunity for a discussion with the management team about *why* they need a dev application server, and a clarification of both what your role is and what is expected of you. The scenario you are describing sounds like one where either you're in a no-win situation, or people just aren't communicating.
Unfortunately, these are also the types of situations that can easily end up with folks being defensive rather than working together, so I'd approach it from the perspective of how changing the way the team works will provide value to the organization, rather than just from a "best practices" model.
Good luck, however you choose to handle this. It's a difficult situation to find yourself in.
-Ki
May 18, 2011 at 8:10 am
Two things, first, if you make a change to security, you might break the app. It doesn't sound like it's been terribly well designed. Inevitably these types of apps require 'sa' privileges (not really, but that's how they're built). You'll want to introduce changes in very, very small increments in order to ensure up time.
Second, do one of two things. Make the developers responsible for on-call duties, or at least make sure that their cell phones are pinged each and every time there's an issue in production. I'd consider adding management to the distribution list too. If you're getting up at 3AM on a regular basis because of ignorant decisions, may as well wake up everyone until they realize the error of their ways.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
May 18, 2011 at 11:09 pm
Run a profiler trace on the DB. Find out what it's really doing. If it never really issues any ddl commands, then create the new group as discussed above and move everyone to it. If it issues ddl on a regular basis, at least you'll know what it's doing and may have a better scheme for locking out any additional unwanted behavior.
It's been my experience that several vendors used to 'require' that their users be given DBO rights just because they were too lazy to figure out what they really needed to run the app successfully.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply