April 24, 2008 at 10:33 am
Hi Guys,
I need some advice on a permission issue that I've run into. I have a database to which my QA people have dbo access. However, there's a group of 200 or so tables in the database that I don't want them touching because they're replicated. Changes to these tables should only go through me. I want to give them read access to these tables and full access to all other objects. I can't put these tables in a separate db because the application will break. I need a way to restrict their access to the tables while giving them DBO like privileges to the remaining db objects. Any insights that you can give me would be greatly appreciated.
Lynn
April 24, 2008 at 11:02 am
You could setup a Security Schema with explicit deny permissions on the tables you dont want them to access.
Give them a login/logins to access this schema.
April 24, 2008 at 12:25 pm
Can you elaborate on how creating a schema and denying access to these objects would still allow them to create/alter any other objects under the dbo schema.
Lynn
April 24, 2008 at 7:21 pm
Simpler solution. Set up a DDL trigger which rolls back any changes to the tables you need to protect (you can have it check against another table which has a list of the objects). If the object being modified isn't in your specific table, allow the change.
K. Brian Kelley
@kbriankelley
April 25, 2008 at 9:00 am
I like that option. How do I capture the name of the table involved in the DDL operation?
It seems to me the only way to do this is with Xquery which I'm not up and running on.
I have created a table (repl_tables) and populated it with 220 table names.
Here's how I'd like the trigger to work. I need to know how to get the value for @table.
CREATE TRIGGER protect_repl_tables
ON DATABASE
FOR DROP_TABLE, ALTER_TABLE
AS
IF EXISTS (SELECT tablename from repl_tables WHERE tablename = @table)
PRINT 'You must contact the DBA Team in order to perform drop or alter on table ' + @table
ROLLBACK
Thanks,
Lynn
April 26, 2008 at 8:14 am
For the user do not give any options in the sql logins.
Then on Database set up a Role.
In here you give the tables you want select rights to.
This way they will not see or can update any other table.
April 26, 2008 at 9:04 am
For the user do not give any options in the sql logins.
Then on Database set up a Role.
In here you give the tables you want select rights to.
This way they will not see or can update any other table.
April 26, 2008 at 11:31 am
Lynn (4/24/2008)
I can't put these tables in a separate db because the application will break.
Oh, yes you can... put the tables in a different DB and either make "surrogate views" or synonyms with the same name as the tables in the original database.
--Jeff Moden
April 29, 2008 at 8:48 am
Ok, so I was able to create a DDL trigger to prevent schema changes to the 225 tables. That works well but I no longer want to give them DBO access because they can drop or alter the DDL trigger.
I want to give them permission to create new objects under the dbo schema and restrict their access to readonly for the 225 tables that I want to lock down.
They don't need dbo, just the ability to create new objects in the db.
I want to give them Create, Alter, Drop permission to views, tables, stored procedures, etc.
Restrictions -
I want to give them readonly access to 225 existing tables in the db.
I want to prevent them from dropping or altering DDL triggers
How should I set up the user?
April 29, 2008 at 11:07 am
Can't you give them their own DATABASE ?
April 29, 2008 at 11:07 am
Jeff Moden (4/26/2008)
Lynn (4/24/2008)
I can't put these tables in a separate db because the application will break.Oh, yes you can... put the tables in a different DB and either make "surrogate views" or synonyms with the same name as the tables in the original database.
I have automated this for several databases and I can attest that it works very well. One thing that you do have to look out for is applications that unwittingly use DDL statements. In particular "TRUNCATE TABLE" is a DDL (and not a DML) that many applications nonetheless often include in their SQL code. But, because it is a DDL, it will not work transparently through a View the way that queries and DML's will.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
April 29, 2008 at 1:32 pm
I could but I am trying to stay away from splitting the database into 2 dbs because it will complicate maintenance especially in production.
April 29, 2008 at 5:48 pm
Would you rather have a slightly more complicated database maintenance program or an impossible security situation? 😉
--Jeff Moden
April 30, 2008 at 1:39 pm
Lynn (4/29/2008)
I could but I am trying to stay away from splitting the database into 2 dbs because it will complicate maintenance especially in production.
Yeah, insuring that your restores can be synchronized requires some forethought also. But really, it's just a decision as to which situation is the bigger problem.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
April 30, 2008 at 3:34 pm
Creating a new database would involve going back and making changes to 100+ servers which cannot be done at this time. This particular db exists on all servers which is why I am looking for an alternate way to restrict access to the tables while giving full access to everything else.
Viewing 15 posts - 1 through 15 (of 25 total)
You must be logged in to reply to this topic. Login to reply