snippetsqlModerate
Create table permission for a user in specific schema
Viewed 0 times
createuserpermissionschemaforspecifictable
Problem
I want to give Create , alter and drop permission to user A on a schema1 of a database.
I guess this question has been already asked, and what i have found is to Grant Alter to the schema and also grant create table to the User A:
If this is right then , will the userA will be able to create table on other schemas too?
I guess this question has been already asked, and what i have found is to Grant Alter to the schema and also grant create table to the User A:
GRANT ALTER, DELETE, EXECUTE, INSERT, SELECT, UPDATE ON SCHEMA::schema1 TO user A;GRANT CREATE TABLE TO User A;If this is right then , will the userA will be able to create table on other schemas too?
Solution
I want to give Create , alter and drop permission to user A on a schema1 of a database
The safe way to do this is to make A owner of that Schema.
Granting a user the ability to alter another user's schema gives that user the ability to SELECT, INSERT, UPDATE and DELETE rows in any table owned by the owner of that schema. This is called "Ownership Chaining" and it's what makes Views and Stored Procedures really simple ways to control security, as a user who has permissions on a View or Stored Procedure does not need to be granted permissions on the underlying Table, so long as the View/Proc is has the same owner as the Table.
However in this scenario you can easily create a security hole with Ownership Chains. Generally you never want a user to be able to create objects that will be owned by a different (and especially a privileged) user.
EG, just granting CREATE TABLE, ALTER, and INSERT creates a security hole:
outputs
The safe way to do this is to make A owner of that Schema.
Granting a user the ability to alter another user's schema gives that user the ability to SELECT, INSERT, UPDATE and DELETE rows in any table owned by the owner of that schema. This is called "Ownership Chaining" and it's what makes Views and Stored Procedures really simple ways to control security, as a user who has permissions on a View or Stored Procedure does not need to be granted permissions on the underlying Table, so long as the View/Proc is has the same owner as the Table.
However in this scenario you can easily create a security hole with Ownership Chains. Generally you never want a user to be able to create objects that will be owned by a different (and especially a privileged) user.
EG, just granting CREATE TABLE, ALTER, and INSERT creates a security hole:
use master
--drop database security_test
go
create database security_test
go
use security_test
go
create schema schema1 authorization dbo
go
create user A without login
go
grant create table to A
grant alter, insert on schema::schema1 to A
go
create table dbo.secret(id int, msg varchar(200))
insert into dbo.secret(id,msg) values (1, 'secret data')
go
execute as user='A'
create table schema1.foo(id int)
go
create trigger t on schema1.foo after insert
as
begin
select * from dbo.secret
end
go
insert into schema1.foo(id) values (1)
go
revertoutputs
id msg
----------- -----
1 secret dataCode Snippets
use master
--drop database security_test
go
create database security_test
go
use security_test
go
create schema schema1 authorization dbo
go
create user A without login
go
grant create table to A
grant alter, insert on schema::schema1 to A
go
create table dbo.secret(id int, msg varchar(200))
insert into dbo.secret(id,msg) values (1, 'secret data')
go
execute as user='A'
create table schema1.foo(id int)
go
create trigger t on schema1.foo after insert
as
begin
select * from dbo.secret
end
go
insert into schema1.foo(id) values (1)
go
revertid msg
----------- -----
1 secret dataContext
StackExchange Database Administrators Q#177285, answer score: 13
Revisions (0)
No revisions yet.