only select, view definition on schema, but otherwise grant alter on database

Question

only select, view definition on schema, but otherwise grant alter on database

jjturner

Right there with Babe

Points: 788
More actions
May 6, 2016 at 2:37 pm

#326113

For a given role 'dev_role', I'm trying to be restrictive to SELECT and VIEW DEFINITION for a particular schema 'foo', but otherwise, I wish to confer GRANT ALTER to dev_role.
How can I make schema 'foo' restrictive for dev_role while retaining ALTER permissions across the database apart from 'foo'?
grant alter to dev_role;
grant select, view definition on schema::foo to dev_role;
This lets me do the following, which I don't want:create table foo.bar (i int);
If I subsequently use: deny control on schema::foo to dev_role...then I'm rather unsurprisingly unable to run the following:select * from foo.mytableI must be missing something basic, but thus far no joy...
- John

Viewing 7 posts - 1 through 6 (of 6 total)

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

jjturner Right there with Babe Points: 788 More actions · Answer 1

Me think know ansur:

Not quite what I was going for to begin with, but these 2 solutions are probably more sensible than [font="Courier New"]GRANT ALTER to dev_role[/font]:

Solution 1 (no explicit GRANTs to schema 'foo' necesssary):

GRANT SELECT, VIEW DEFINITION to dev_role

GRANT CONTROL on schema::dbo to dev_role

Solution 2 (only schema-specific permissions):

ALTER AUTHORIZATION on schema::foo TO db_ddladmin

GRANT SELECT, VIEW DEFINITION on schema::foo to dev_role

GRANT CONTROL on schema::dbo to dev_role

AFAICS, either of these will lock down schema 'foo' as initially intended, while permitting liberal permissions on the default 'dbo' schema.

Took some head-scratching and brick-wall acquaintances to get there, but these are making sense to me unless anyone sees holes with this approach...

Jeff Moden SSC Guru Points: 1004318 More actions · Answer 2

Looks like those may work. Have you tried them, yet?

--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)

jjturner Right there with Babe Points: 788 More actions · Answer 3

Thanks for eyeballing this Jeff.

Yes, when executing within the context of the 'dev_role' role the following 2 statements fail as intended:create table foo.bar (i int);

execute as user = 'dbo';

And the following 2 succeed as intended:create table dbo.bar (i int);

select * from foo.readonlytable;

So, short of an exhaustive security audit or setting up a full-blown array of tests, I think dev_role meets the requirements. Permissioning schemes tax the old noggin right quick!

Cheers,

John

Jeff Moden SSC Guru Points: 1004318 More actions · Answer 4

Very cool, John. That's awesome. Thanks for posting what you tried and done. Really appreciate it.

--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)

Jeff Moden SSC Guru Points: 1004318 More actions · Answer 5

Hey! As a bit of a sidebar, something like this would make a great "SQL SPACKLE" article. You should take up the pen and submit an article.

--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)

jjturner Right there with Babe Points: 788 More actions · Answer 6

Thanks for the vote of confidence Jeff - taking up the pen on the topic of permissions is something I'll have to deliberate over. Hopefully I can find some time to follow through on that and come up with a draft that's spackle-worthy : )

Cheers,

John