| Author |
Topic |
|
Kristen
Test
22859 Posts |
 Posted - 2006-06-28 : 05:24:29
|
| We've got a client who now has two databases - for two different applications. That mucks up our normal naming conventions(!) and creates a risk that one set of developers will accidentally use the wrong database (DEV) or Admins (PROD)Can I Deny these users - at least in part? (They will have SA rights, but their own User ID etc.). I was wondering if DenyRead and DenyWrite would help - although they'll be trying to do Create Proc stuff mostly, and ideally that's what I'd want to prevent.Kristen |
|
|
rockmoose
SQL Natt Alfen
3279 Posts |
Posted - 2006-06-28 : 06:15:43
|
| You can't deny rights to sysadmin accounts.What about 2 instances of Sql Server?rockmoose |
 |
|
|
jen
Master Smack Fu Yak Hacker
4110 Posts |
Posted - 2006-06-28 : 10:31:04
|
and i thought this was some post joke from Kristen  if they only need to create sproc, can you provide them with dbo instead? do they really need SA?--------------------keeping it simple... |
|
|
|
rockmoose
SQL Natt Alfen
3279 Posts |
Posted - 2006-06-28 : 10:47:59
|
| better still give them db_ddladmin..but no read rights |
|
|
|
jen
Master Smack Fu Yak Hacker
4110 Posts |
Posted - 2006-06-28 : 11:00:29
|
db_ddladmin + db_securityadmin so they can grant permissions on the objects  --wait... what were you trying to prevent again Kristen?  --------------------keeping it simple... |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2006-06-28 : 11:56:59
|
| I know its a FAQ and the answer is "You can't keep SA folk out of your private data", etc.But what I'm looking for is something to save us from ourselves - for that rainy day after little sleep when we pick the wrong DB from the pull-down in QA and run our script ... bang!It doesn't actually have to keep the SA's out of the database, just has to alert them or somesuch ...The 2 instances thing would work, we're just not very well tooled up to implement that - a whole second set of maintenance tasks etc. But it would work, that's for sure.Our clients have two databases each - a TEST and a LIVE - (they are both "Production", but Test is for experiments they want to do ...) so it would work well for that too.I changed the password on the wrong database yesterday ... because of this issue. Today I had the hear-rendering task of fired myself If I (as SA) also had denyread/denywrite on the database, and created an SProc on it would that get denyread, or would it get SA?Could I deny myself on the System tables in that DB?Just thinking out loud really!Kristen |
|
|
|
rockmoose
SQL Natt Alfen
3279 Posts |
Posted - 2006-06-28 : 13:00:08
|
| This reminds me of some of the discussions here on this forum.How do we deny rights to the sa account?, how do we prevent the dba to access the database...Well you can't!Hardware is cheap, buy a new (TEST) server?rockmoose |
|
|
|
X002548
Not Just a Number
15586 Posts |
|
|
Kristen
Test
22859 Posts |
Posted - 2006-06-28 : 16:04:36
|
| My problem isn't with TEST, its with two production DBs for the same client with very similar names (because we are used to only having one database / client, and thus there is a significant risk of Administrator-Cock-up in this instance!)Our TEST databases are copies of the Product database where users will create new web pages and trial them before putting them live. They represents no threat to the health of the server. The TEST system is there so that the user can "rehearse" and approve new web pages without the customers being able to see them.But I do think that another Instance would keep them segregated in a way that might well prevent an accidental cock-up by DBAs - give that TEST and LIVE are two variants for a given user, there is a risk of a DBA-cock-up - whereas separate instances would mean that the DBAs routinely connect to TEST to do stuff, but rarely connect to LIVE, so less risk of doing something accidentally - or perhaps more correctly a heightened awareness when having a need to connect to a LIVE database.But I'm stuck with two similar database names being a risk from the Administration perspective -I'm inclined to rename them both to something with unusual Prefixes - so that they aren't in the normal position in the Select List, and thus less likely to be wrongly selected by accident.Kristen |
|
|
|
rockmoose
SQL Natt Alfen
3279 Posts |
Posted - 2006-06-28 : 17:18:04
|
| DBA's don't cock up.Kristen, x$ of investment will make you sleep x times better.rockmoose |
|
|
|
jen
Master Smack Fu Yak Hacker
4110 Posts |
Posted - 2006-06-28 : 21:52:02
|
| well you can invoke an approle, issue yourself an ordinary user then invoke an approle for superuser, the approle name should be atleast close to the database name, so if you get deny permissions, it means you used the wrong approle, wrong databaseor, were you tinkering with enteprise manager? I always put a use [databasename] before any query just to make sure i'm running the query on the right database--------------------keeping it simple... |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2006-06-30 : 01:43:17
|
Good thoughts Jen, thanks. We have [databasename] on all our data transfer scripts, but not on a CREATE PROCEDURE - we keep them generic so that they can be run on any database ... but running it on the wrong database is a bad idea, of course!I was using E.M. yes. I ran my Create Database script which creates all the users and stuff and then realised I had left my 'PutPassWordHere' in the script - so I went to change the password in E.M.Now, every datahase has MyDataBase_IIS as a use, and I had two databases because that client already had one (another department, so I don't need access) - and you guessed it, I got the wrong one!I thought we used to have a solution where Admins had a high enough security clearance to give THEMSELVES db_owner to a database, but did not have sysadmin - is that possible?Because if so I could have an overnight script that removed all the db_owner explicits (with an exception list where people are working long-term in a specific database), and then admins would have to re-add themselves when they wanted to work on that DB - any merit in that?"Kristen, x$ of investment will make you sleep x times better."Ok, I'll employ you Rocky ... how much?  Kristen |
|
|
|
jen
Master Smack Fu Yak Hacker
4110 Posts |
Posted - 2006-06-30 : 02:02:56
|
try the securityadmin server fixed role, i haven't tested it, my laptop got wiped out (2nd time now) due to an OS upgrade  -- but i smell frustration, i mean as a DBA you have super user rights, then you want to take it away? not fair or, create a new control apps with all the alerts needed for fail safe transactions, use this apps only when running scripts so your DBA can just click on the database first then run the script, no database specified = script will not run--------------------keeping it simple... |
|
|
|
rockmoose
SQL Natt Alfen
3279 Posts |
Posted - 2006-06-30 : 03:49:32
|
| "Ok, I'll employ you Rocky ... how much?"Let me put it his way, What's the weather like?I don't see an easy solution to your problem really.Just make sure as few people as possible have elevated permissions, if someone needs sysadmin add them on a "need to sa" basis.Run your "remove all from sa - except select superusers" at the end of the day.rockmoose |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2006-06-30 : 04:10:47
|
| "Let me put it his way, What's the weather like?"Damn, you're new employer failed on the first interview question!"if someone needs sysadmin add them on a "need to sa" basis"All the admins need sysadmin, but only on selected databases ... that's db_owner, right?"or, create a new control apps"One of our users has an application we integrate to; their people just send them an XML file for an upgrade, and they "execute" it through their front end. That does all the DDL changes, any DML rearrangement of data, and insertion of new settings etc. I'm very jealous!"so your DBA can just click on the database first then run the script, no database specified or insufficient permissions= script will not run"Kristen |
|
|
|
rockmoose
SQL Natt Alfen
3279 Posts |
Posted - 2006-06-30 : 04:58:52
|
| *LOL*If you have sysadmin rights you have complete permissions in all db's.And the whole sql server for that matter.A sysadmin is not a member of the db_owner role in the database, but can be added to that role.The effective permissions will still be the same though, the only difference is that if the sysadmin account is a user in the database, then it can own objects.You may DENY to a sysadmin login, but it will have no effect!If you DENY to a "regular" login who is db_owner in a database it will have effect. But of course since he is db_owner he can revoke his own DENY.One caveat, you can't deny to the user who owns the object.A bit messy, I know.rockmoose |
|
|
|
jen
Master Smack Fu Yak Hacker
4110 Posts |
Posted - 2006-06-30 : 05:10:13
|
| or you can issue new policies and stick it on the DBAs door or somewhere visible:1. if you are not yourself today, do not touch the server2. if you have hang over, do not touch the server3. if you are oncall, make sure you are wide-awake before issuing instructions4. before starting your daily tasks, take a strong cup of coffee to wake you up5. and finally, do not make any changes: a. at the end of your shift b. if you're under stress due to tight deadlines c. if you're sleepy d. if you had a fight with your significant half e. if it's FridayPS. If you have eye problem (poor eyesight, a pinkeye or anything that can affect clear vision), please rectify (wear specs, wipe your eye, wash your face, etc)--------------------keeping it simple... |
|
|
|
rockmoose
SQL Natt Alfen
3279 Posts |
Posted - 2006-06-30 : 05:17:25
|
| *LOL*, that's good Jen!I'm mailing that list to my team straight away!!!rockmoose |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2006-07-01 : 06:09:57
|
| "1. if you are not yourself today, do not touch the server"I love that one, but the trouble is I never know what I am not myself!"e. if it's Friday"Nah, that's just one of those Feeble Philippines rules, isn't it? Don't Strong Singapore rules apply now? Kristen |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2006-07-01 : 06:13:45
|
| "A bit messy, I know."Thanks Rocky, thought provoking ...... so how about I had a process that would elevate a user to db_owner. Then they can do their work on that database, but not accidentally elsewhere.And then I can take that away each night (except for intended long-term development type DBs.Then the "admins" don't have to have sysadmin - is that right?If so can I have an SProc that evaluates the current user to db_owner, or do they need to be sysadmin themselves for the Sproc to be able to make that change?We can also have two logins:KristenKristenSAso that they have SA logins for exception needs.Kristen |
|
|
|
jen
Master Smack Fu Yak Hacker
4110 Posts |
Posted - 2006-07-01 : 10:50:37
|
in a fighting mood then? hope this doesn't trigger a war quote:
Originally posted by Kristen
Then the "admins" don't have to have sysadmin - is that right?
--------------------keeping it simple... |
|
|
|
Next Page
|
Deny login to SA