Revoke viewing of metadata even with SA account
-
I know that I can revoke metadata access in SQL 2005 by using revoke view any database/ view server state/ view definitions but is there any way I can deny viewing of databases and it's tables and columns even if the user is a Sys Admin or SA?
Thanks!
Dex
-
Not sure, but if you deny the SA or Sys Admin from view database objects (databases, tables, etc), doesn't that mean you don't need a Sys Admin or SA? A DBA needs access to these objects to support the database and perform necessary maintenance. They may not need Sys Admin for all activities, but there are times Sys Admin rights are required. DBA should have 2 logins, one for general use with sufficient rights to do day to day monitoring, and a seperate Sys Admin login for those times that it is needed.
-
I agree on you but I was looking for any functionality on 2005 that would disallow access to SA and give specific logins/access to DBAs. We are trying to release a product and one of the parameters are that we don't want DBA's or anyone of our client to see database architecture (tables/columns and if possible the database from the list). Thanks.
-
"We are trying to release a product and one of the parameters are that we don't want DBA's or anyone of our client to see database architecture (tables/columns and if possible the database from the list). "
Why?
-
Well some of our clients don't have full-time DBA and we just want to safeguard the clients from doing harm on the DB.
To safeguard the data, we're implementing encryption on a key fields. We wanted to take it to another level so that they won't be able to look at what's on the database (tables/columns). Is this possible to disable sysadmin so that they can't take a look at tables/columns on a particular DB? Or is it the same problem with 2000 where SA/Sysadmin has full rights to the DB?
-
First, if you don't want someone to muck up the database, don't give them access to it. Period.
We have a call center where I work and we use some proprietary stuff that runs off Oracle. They maintain the db and the only access I can get is read.
Which is probably a better route for you.
Another thing you need to think of is the future. What happens when your customer doesn't want the product anymore? Your app may have stored the data, but they do have rights to it, encrypting it could open up a whole can of worms that you don't even want to get into.
So back to the first part, why are you giving them access in the first place if they don't need it?
If you are giving them access for maintenance, you need to look at the roles and give them rights to do the few things on the box that they need to do.
For god's sake, don't give them sa.
-
I truely agree on you but how can I control it when the DB is setting on the client's environment and have no control over it. Clients build the server, install SQL, do the install on the app which creates DB/security/roles/etc. For us to protect our product, we don't want SysAdmin to look into our schema of the DB and possibly do any damage by deleting tables, columns, and/or data. I'm just asking if there's any functionality in 2005 that will let me do this functionality. If not then I would like to know how you guys go around this issue?
Thanks!
-
Well if someone has SA, there isn't a ton you can do.
maybe triggers on DDL's?
What does your agreement with them state? Are mods to the database allowed?
What support is in the agreement? If they have a data corruption issue, do you fix it? Will they have to do it themselves?
Have you looked into hosting it? Is that a possibility?
Another idea is to create a seperate instance and do it that way.
Of course there are caveats to all of this, so you can't make those decisions lightly.
-
Thanks Don. Client shouldn't have the right to change anything on the DB and app. If they change anything that has abnormal effect then we'll not support it.
We are currently looking to host it ourselves but there might be some compliance issue we have to address before we can even go to that route.
-
Dexter, you said they don't have any database people right?
Do you know how well the existing database server is running?
If it's performance blows, it could affect your database as well.
I think a seperate instance is something worth looking at.
-
I just came on board with this company so I don't really know the health of those dbs. Thanks!
-
If the database isn't running on your server then you have a problem as any user to the local admins will get to view the data.
I agree with the comment about who owns what - the data belongs to your customer, I'd take a dim view of not being able to access my own data. DDL triggers and such will help you audit, but even they could be removed. I usually remove permissions to syscomments and have no table access - this at least means users can't see stuff. But removing sa / sysadmin rights would be tricky.
If you have control of the server ( e.g. dedicated server ) remove builtin admins and set a very complex sa pwd , remove public rights to syscomments and other system tables - that'll lock down the server hard.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/ -
You should be very forward about how you're setting up the database server.
You should also have the various possibilities spelled out in the agreements between both parties.
-
If a vendor tried to sell me something where I couldn't see the structure of the data I wouldn't even talk to them.
-
Both of you have a good idea of the metadata, but I have a pratcical security issue along the same line.
I have a several production databases. We have client execuitives who support the customers. The CEs have access to the database ( db_datareader ). What the CEs also had in 2000 was the ability to view the stored procedures, but not execute them. This helps them track down issues with bad code.
The problem I am having is that I can grant them execute on all procedures ( much like giving out sa access, or in this case DBO, NO ) or they can look at what is in source control ( sometimes dosent match the database)
Any Ideas here?
Eric
Viewing 15 posts - 1 through 15 (of 21 total)
You must be logged in to reply to this topic. Login to reply