April 28, 2016 at 11:30 am
Hello,
I want an user to be able to grant select right on all its tables.
So I grant him select right on the schema with grant option.
But he still cannot give the select right.
I create two users with there schemas:
create schema MY_SCHEMA1;
create login MY_LOGIN1 with password = 'MY_PASSWORD1';
create user MY_USER1 for login MY_LOGIN1 with default_schema=MY_SCHEMA1;
create schema MY_SCHEMA2;
create login MY_LOGIN2 with password = 'MY_PASSWORD2';
create user MY_USER2 for login MY_LOGIN2 with default_schema=MY_SCHEMA2;
GRANT CREATE TABLE, CREATE VIEW, CREATE PROCEDURE, CREATE FUNCTION TO MY_USER1
GRANT ALTER, CREATE SEQUENCE, DELETE, EXECUTE, INSERT, REFERENCES, SELECT, TAKE OWNERSHIP, UPDATE, VIEW CHANGE TRACKING, VIEW DEFINITION ON SCHEMA :: MY_SCHEMA1 TO MY_USER1 with grant option;
Then I connect using MY_LOGIN1 then:create table MY_TABLE(MY_COLUMN int)
select * from MY_TABLE;
grant select on MY_TABLE to MY_USER2;
I have the error:
Error: Cannot find the object 'MY_TABLE', because it does not exist or you do not have permission.
SQLState: S0001
ErrorCode: 15151
April 29, 2016 at 5:28 am
MY_USER1 also requires CONTROL on schema to be able to define a users permissions.
GRANT ALTER, CREATE SEQUENCE, DELETE, EXECUTE, INSERT, REFERENCES, SELECT, CONTROL, TAKE OWNERSHIP, UPDATE, VIEW CHANGE TRACKING, VIEW DEFINITION ON SCHEMA :: MY_SCHEMA1 TO MY_USER1 with grant option;;
Go
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
April 29, 2016 at 6:37 am
Yes, CONTROL does the trick, but I tried to avoid it because it embeds all other permissions.
GRANT ALTER, CREATE SEQUENCE, DELETE, EXECUTE, INSERT, REFERENCES, SELECT, CONTROL, TAKE OWNERSHIP, UPDATE, VIEW CHANGE TRACKING, VIEW DEFINITION ON SCHEMA :: MY_SCHEMA1 TO MY_USER1;
Go
appears to be equivalent to:
GRANT CONTROL ON SCHEMA :: MY_SCHEMA1 TO MY_USER1;
Go
I don't understand why "grant select with grant option" doesn't seem to work on a schema while it is perfectly working on a table.
I am investigating another approach, making MY_USER1 owner of MY_SCHEMA1:
ALTER AUTHORIZATION ON SCHEMA :: MY_SCHEMA1 TO MY_USER1;
It appears to be closer of what we want -> 2 schemas, one user each, one of these users that is allowed to select in some tables of the other.
Plus this approach may prevent us from ownership chaining issue.
Not sure, but if stored procedures of MY_USER2 are owned by default user dbo, they may be able to modify tables of MY_USER1 because they are also owned by dbo user.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply