Restrict DBO user

Question

Post reply

Restrict DBO user

Lynn-266641

SSC-Addicted

Points: 486
More actions
April 24, 2008 at 10:33 am

#185774

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

Viewing 15 posts - 1 through 15 (of 25 total)

You must be logged in to reply to this topic. Login to reply

steveb. SSC-Forever Points: 46733 More actions · Answer 1

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.

Lynn-266641 SSC-Addicted Points: 486 More actions · Answer 2

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

K. Brian Kelley SSC Guru Points: 114642 More actions · Answer 3

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

Lynn-266641 SSC-Addicted Points: 486 More actions · Answer 4

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

TRACEY-320982 SSChampion Points: 13653 More actions · Answer 5

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.

TRACEY-320982 SSChampion Points: 13653 More actions · Answer 6

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.

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 7

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

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Lynn-266641 SSC-Addicted Points: 486 More actions · Answer 8

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?

TRACEY-320982 SSChampion Points: 13653 More actions · Answer 9

TRACEY-320982

SSChampion

Points: 13653

April 29, 2008 at 11:07 am

#808552

Can't you give them their own DATABASE ?

RBarryYoung SSC Guru Points: 143329 More actions · Answer 10

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]

Lynn-266641 SSC-Addicted Points: 486 More actions · Answer 11

I could but I am trying to stay away from splitting the database into 2 dbs because it will complicate maintenance especially in production.

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 12

Would you rather have a slightly more complicated database maintenance program or an impossible security situation? 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

RBarryYoung SSC Guru Points: 143329 More actions · Answer 13

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]

Lynn-266641 SSC-Addicted Points: 486 More actions · Answer 14

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.