Grant Create View Ability Without Over Permissioning

Question

Post reply

Grant Create View Ability Without Over Permissioning

Mark Simmerman

Old Hand

Points: 381
More actions
September 1, 2009 at 9:46 am

#133343

Thanks in Advance.

I have a need to provide a user who has read-only access the ability to create and alter views.

We are on MS SQL Server 2005 and use an ERP system that locks the schema so that most objects cannot be altered, yet we can supplement. All objects are part of the dbo schema.

I provided the user Grant Create View Access, but he received the following message:

"The specified schema name "dbo" either does not exist or you do not have

permission to use it. "

I later ran the following command:

GRANT ALTER ON SCHEMA::dbo TO "DOMAIN\user"

The user was then over permissioned and could drop tables.

Could someone point out how I can provide more granular access?

Mark S

Napa, CA

Viewing 5 posts - 1 through 4 (of 4 total)

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

SQLRNNR SSC Guru Points: 281324 More actions · Answer 1

Try this article.

Grant Create View to ViewUser;

There is additional info explained in the article about using the "as" option.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Mark Simmerman Old Hand Points: 381 More actions · Answer 2

Thanks, Jason.

The problem is not the command you shared, which by itself will not allow the user to create a view. Below is an excerpt from BOL:

Permissions

To execute CREATE VIEW, at a minimum, CREATE VIEW permission in the database and ALTER permission on the schema in which the view is being created are required.

As you can see, it also requires ALTER permission on the schema, which in our case is DBO. That user can then drop objects like tables, which is too much permission.

Another forum post suggested assigning the views to a different schema that DBO owned and give the user ALTER on the subordinate schema. This is the closest thing that I have to an answer.

Mark S

SQLRNNR SSC Guru Points: 281324 More actions · Answer 3

This has me interested. I am checking some things and testing to see if they will work.

From MSDN:

Caution:

A user with ALTER permission on a schema can use ownership chaining to access securables in other schemas, including securables to which that user is explicitly denied access. This is because ownership chaining bypasses permissions checks on referenced objects when they are owned by the principal that owns the objects that refer to them. A user with ALTER permission on a schema can create procedures, synonyms, and views that are owned by the schema's owner. Those objects will have access (via ownership chaining) to information in other schemas owned by the schema's owner. When possible, you should avoid granting ALTER permission on a schema if the schema's owner also owns other schemas.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

SQLRNNR SSC Guru Points: 281324 More actions · Answer 4

After some poking around at it, here is what I came up with:

When Granting alter, it grants alter any schema by default. This could be just as bad in the separate schema setup as the default schema setup.

Create Schema CreateView Authorization dbo

Create TestViewUser login and specify the CreateView schema and add the user to the db_datareader role

Grant Create View to TestViewUser

Grant Alter on SCHEMA::CreateView to TestViewUser

And then for good measure I did:

revoke alter,delete,control,execute,references to testviewuser

revoke ALTER ANY Schema to testviewuser

And then:

create table dbo.testdrop (id int)

grant select on dbo.testdrop to testviewuser

And then I logged in as the TestViewUser and did the following:

create view testview as select * from sys.all_columns --Successful

select * from testview -- Successful

select * from testdrop -- Returns column headers as expected (didn't bother with data entry)

drop table dbo.testdrop --Msg 3701, Level 14, State 20, Line 1

Cannot drop the table 'testdrop', because it does not exist or you do not have permission.

drop view testview -- successful

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events