MS SQL Server - Schema permissions doubt (SQL Server 2005)

Author

Schema permissions doubt (SQL Server 2005)

Alex

2006-02-03, 9:23 am

Hi everyone,
Maybe I have a very simple question ...
I have a requirement that is very apropriate to use schemas.
I understand that one of the advantages is to simplify the permission
management and that I could assign a kind of 'dbo' permission inside a
schema.
I did the following steps:
Schema creation
Login and DB user creation
Default schema assignement to the user
Grant alter (control) permission to the user for the schema created (GRANT
ALTER ON SCHEMA::schema TO user, I've tried CONTROL also)

I would expect that logging with this user it could create a table into that
DB schema, but I receive a deny permission error message.
What's my error? Or have I a bad understand of the concepts?

Thank you for the help!!

Alexandre Calderaro

David Browne

2006-02-03, 8:23 pm

"Alex" <Alex@discussions.microsoft.com> wrote in message
news:24679AFA-A127-4BE4-A7C2- 2530DE922E04@microso
ft.com...
> Hi everyone,
> Maybe I have a very simple question ...
> I have a requirement that is very apropriate to use schemas.
> I understand that one of the advantages is to simplify the permission
> management and that I could assign a kind of 'dbo' permission inside a
> schema.
> I did the following steps:
> Schema creation
> Login and DB user creation
> Default schema assignement to the user
> Grant alter (control) permission to the user for the schema created (GRANT
> ALTER ON SCHEMA::schema TO user, I've tried CONTROL also)
>
> I would expect that logging with this user it could create a table into
> that
> DB schema, but I receive a deny permission error message.
> What's my error? Or have I a bad understand of the concepts?
>

You are using a schema as a "user sandbox" this will work, but there's a
couple of things to keep in mind.

In order to create a table a user must be able to alter the schema, but also
requires the database-level CREATE TABLE permission. In addition the user
MUST own the schema. If you forget to do this it creates a security hole.

Here's how it works: If DBO owns the schema and the user can create
synonyms, views or procedures in the schema then DBO will own the created
items. This will create an ownership chain from the created object to any
referenced objects and the user will be able to access any other database
object owned by DBO in any schema.

To grant ownership of a schema to a user issue:

ALTER AUTHORIZATION ON schema::Bob TO Bob;

So when creating schemas just for logical seperation of objects and easy
administration of user access to those objects, DBO should own the schemas
and all the objects in them. But where users are allowed to create objects
for themselves, each user should own his own schema.

David

Alex

2006-02-04, 8:23 pm

Thanks David,

Sorry, but I don't know if I understood very well your answer.
The first thing is that I don't know the 'user sandbox' expression ...
Sorry, I'm not american or english...
Maybe if I explain what I need to do the things would be simpler:
I need to separate tables by logical areas and each person of a logical area
can be like a owner of it. And for others areas persons could have select
permissions or exec permissions in sp's that update that tables.
I interpreted this scenary apropriated with the schema concept.
I thought that if I create a schema and assign a role as schema owner all
the users that belong to that role automactly could do all operations in that
schema.
I understood that the user has to be permissions in a higher level.
Well, I did other tests and I could note that it works also with roles, that
would be a best practice.
In the tests that I did it works with the create table permission to the
user or role, I mean, the user bob is owner of the schema x and has create
table permission in the db, than he could create tables only in the schema
that he owns.
This is ok, but I yet not understood why this is not true when put bob in
the ddladmin role. I could think in the same way, or not? I mean, bob is
ddladmin of the db, but he owns only the x schema, and then he could use ddl
commands in the x schema and this is not true, bob can create tables in other
schemas.

Thanks a lot

Alexandre Calderaro

"David Browne" wrote:

>
> "Alex" <Alex@discussions.microsoft.com> wrote in message
> news:24679AFA-A127-4BE4-A7C2- 2530DE922E04@microso
ft.com...
>
> You are using a schema as a "user sandbox" this will work, but there's a
> couple of things to keep in mind.
>
> In order to create a table a user must be able to alter the schema, but also
> requires the database-level CREATE TABLE permission. In addition the user
> MUST own the schema. If you forget to do this it creates a security hole.
>
> Here's how it works: If DBO owns the schema and the user can create
> synonyms, views or procedures in the schema then DBO will own the created
> items. This will create an ownership chain from the created object to any
> referenced objects and the user will be able to access any other database
> object owned by DBO in any schema.
>
> To grant ownership of a schema to a user issue:
>
> ALTER AUTHORIZATION ON schema::Bob TO Bob;
>
> So when creating schemas just for logical seperation of objects and easy
> administration of user access to those objects, DBO should own the schemas
> and all the objects in them. But where users are allowed to create objects
> for themselves, each user should own his own schema.
>
> David
>
>
>

David Browne

2006-02-04, 8:23 pm

"Alex" <Alex@discussions.microsoft.com> wrote in message
news:E5A04C83-23BF-4013-B111- E79C44EA5904@microso
ft.com...
> Thanks David,
>
> Sorry, but I don't know if I understood very well your answer.
> The first thing is that I don't know the 'user sandbox' expression ...
> Sorry, I'm not american or english...
> Maybe if I explain what I need to do the things would be simpler:
> I need to separate tables by logical areas and each person of a logical
> area
> can be like a owner of it. And for others areas persons could have select
> permissions or exec permissions in sp's that update that tables.
> I interpreted this scenary apropriated with the schema concept.

Yes. This is what I meant by a "user sandbox" a sandbox is an area where
the user can do whatever he wants.
See

http://en.wikipedia.org/wiki/ Sandb... /> security%29

> I thought that if I create a schema and assign a role as schema owner all
> the users that belong to that role automactly could do all operations in
> that
> schema.

Sounds good.

> I understood that the user has to be permissions in a higher level.
> Well, I did other tests and I could note that it works also with roles,
> that
> would be a best practice.
> In the tests that I did it works with the create table permission to the
> user or role, I mean, the user bob is owner of the schema x and has create
> table permission in the db, than he could create tables only in the schema
> that he owns.
> This is ok, but I yet not understood why this is not true when put bob in
> the ddladmin role. I could think in the same way, or not? I mean, bob is
> ddladmin of the db, but he owns only the x schema, and then he could use
> ddl
> commands in the x schema and this is not true, bob can create tables in
> other
> schemas.
>

I don't understand the problem. Could you post a sript showing what you are
doing.

David

Alex

2006-02-06, 9:23 am

Thanks David,

The situation that I have doubt is the following, with scripts:

CREATE SCHEMA [Schema1] AUTHORIZATION [UserT3]
go
CREATE SCHEMA [Schema2]
go
CREATE LOGIN [UserT3]
WITH PASSWORD='', DEFAULT_DATABASE=[DBTeste], DEFAULT_LANGUAGE=[us_english],
CHECK_EXPIRATION=OFF
, CHECK_POLICY=OFF

go
USE DBTeste
CREATE USER [UserT3] FOR LOGIN [UserT3] WITH DEFAULT_SCHEMA=[Schema1]
go
ALTER SCHEMA [Schema1] AUTHORIZATION [UserT3]
go
GRANT CREATE TABLE TO UserT3
go

SETUSER 'USERT3'
go
create table -- The table is created
territorio.table1
(col int)
go

create table -- Permission denied on schema2
recapito.table1
(col int)
go

This is would expected.
But, if I do the following:
CREATE SCHEMA [Schema1] AUTHORIZATION [UserT3]
go
CREATE SCHEMA [Schema2]
go
CREATE LOGIN [UserT3]
WITH PASSWORD='', DEFAULT_DATABASE=[DBTeste], DEFAULT_LANGUAGE=[us_english],
CHECK_EXPIRATION=OFF
, CHECK_POLICY=OFF

go
USE DBTeste
CREATE USER [UserT3] FOR LOGIN [UserT3] WITH DEFAULT_SCHEMA=[Schema1]
go
ALTER SCHEMA [Schema1] AUTHORIZATION [UserT3]
go
EXEC sp_addrolemember 'db_ddladmin', 'UserT3'
go
SETUSER 'USERT3'
go
create table -- The table is created
schema1.table1
(col int)
go

create table -- The table is create !!! I expected not...
schema2.table1
(col int)
go

In this way I think that I have to give all permissions individualy in
DBTeste. But what I expected is a king of db_owner of a schema, i.e., create,
alter objects in that schema. How could I do this whithout gives these
specific permissions?
Thanks a lot

Alexandre

"David Browne" wrote:

>
> "Alex" <Alex@discussions.microsoft.com> wrote in message
> news:24679AFA-A127-4BE4-A7C2- 2530DE922E04@microso
ft.com...
>
> You are using a schema as a "user sandbox" this will work, but there's a
> couple of things to keep in mind.
>
> In order to create a table a user must be able to alter the schema, but also
> requires the database-level CREATE TABLE permission. In addition the user
> MUST own the schema. If you forget to do this it creates a security hole.
>
> Here's how it works: If DBO owns the schema and the user can create
> synonyms, views or procedures in the schema then DBO will own the created
> items. This will create an ownership chain from the created object to any
> referenced objects and the user will be able to access any other database
> object owned by DBO in any schema.
>
> To grant ownership of a schema to a user issue:
>
> ALTER AUTHORIZATION ON schema::Bob TO Bob;
>
> So when creating schemas just for logical seperation of objects and easy
> administration of user access to those objects, DBO should own the schemas
> and all the objects in them. But where users are allowed to create objects
> for themselves, each user should own his own schema.
>
> David
>
>
>

David Browne

2006-02-06, 8:23 pm

"Alex" <Alex@discussions.microsoft.com> wrote in message
news:724F6BE0-2EAE-4E1D-B92C- A3617480BED8@microso
ft.com...
> Thanks David,
>
> The situation that I have doubt is the following, with scripts:
>
> CREATE SCHEMA [Schema1] AUTHORIZATION [UserT3]
> go
> CREATE SCHEMA [Schema2]
> go
> CREATE LOGIN [UserT3]
> WITH PASSWORD='', DEFAULT_DATABASE=[DBTeste],
> DEFAULT_LANGUAGE=[us_english],
> CHECK_EXPIRATION=OFF
, CHECK_POLICY=OFF
>
> go
> USE DBTeste
> CREATE USER [UserT3] FOR LOGIN [UserT3] WITH DEFAULT_SCHEMA=[Schema1]
> go
> ALTER SCHEMA [Schema1] AUTHORIZATION [UserT3]
> go
> GRANT CREATE TABLE TO UserT3
> go
>
> SETUSER 'USERT3'
> go
> create table -- The table is created
> territorio.table1
> (col int)
> go
>
> create table -- Permission denied on schema2
> recapito.table1
> (col int)
> go
>
> This is would expected.
> But, if I do the following:
> CREATE SCHEMA [Schema1] AUTHORIZATION [UserT3]
> go
> CREATE SCHEMA [Schema2]
> go
> CREATE LOGIN [UserT3]
> WITH PASSWORD='', DEFAULT_DATABASE=[DBTeste],
> DEFAULT_LANGUAGE=[us_english],
> CHECK_EXPIRATION=OFF
, CHECK_POLICY=OFF
>
> go
> USE DBTeste
> CREATE USER [UserT3] FOR LOGIN [UserT3] WITH DEFAULT_SCHEMA=[Schema1]
> go
> ALTER SCHEMA [Schema1] AUTHORIZATION [UserT3]
> go
> EXEC sp_addrolemember 'db_ddladmin', 'UserT3'
> go
> SETUSER 'USERT3'
> go
> create table -- The table is created
> schema1.table1
> (col int)
> go
>
> create table -- The table is create !!! I expected not...
> schema2.table1
> (col int)
> go
>
> In this way I think that I have to give all permissions individualy in
> DBTeste. But what I expected is a king of db_owner of a schema, i.e.,
> create,
> alter objects in that schema. How could I do this whithout gives these
> specific permissions?

You are on the right track, it's just that DB_DDLADMIN has too many
permissions. I would create a new role for schema owners.
Schema Owners should be able to creat objects in schemas they own, and view
the definitions of objects in other schemas.

Something like this:

create role SchemaOwner
grant view definition to SchemaOwner
grant create table to SchemaOwner
grant create view to SchemaOwner
grant create procedure to SchemaOwner
grant create function to SchemaOwner
grant create synonym to SchemaOwner
grant create xml schema collection to SchemaOwner

go
create user Stan without login
create user Bob without login
go
create schema Stan authorization Stan
go
create schema Bob authorization Bob
go
alter user Stan with default_schema=Stan
alter user Bob with default_schema=bob
go
go
exec sp_addrolemember SchemaOwner, Bob
exec sp_addrolemember SchemaOwner, Stan
go

David

Alex

2006-02-07, 9:23 am

Thank you David,

I think that your anwsers clarified my doubts.
In my opinion the schemas doesn't give that easier permission administration
as I would expect.
In my, that is a little bit of your sample, because I don't want to see a
schema as a user, but an area. And in this case I will have to create many
schemaowner roles and give some specific permissions to each one. After I
will put the users that I want to be schema owners in each group.
And a thing that I cannot realize yet is if I have a select permission in a
schema that permits me to select data from all tables from that schema, why
doesn't exist a similar kind of permission to administrate the schema,
instead of give many specific permissions? But this question could be
anwsered only by Microsoft :-)

Regards

Alexandre

"David Browne" wrote:

>
> "Alex" <Alex@discussions.microsoft.com> wrote in message
> news:724F6BE0-2EAE-4E1D-B92C- A3617480BED8@microso
ft.com...
>
> You are on the right track, it's just that DB_DDLADMIN has too many
> permissions. I would create a new role for schema owners.
> Schema Owners should be able to creat objects in schemas they own, and view
> the definitions of objects in other schemas.
>
> Something like this:
>
>
> create role SchemaOwner
> grant view definition to SchemaOwner
> grant create table to SchemaOwner
> grant create view to SchemaOwner
> grant create procedure to SchemaOwner
> grant create function to SchemaOwner
> grant create synonym to SchemaOwner
> grant create xml schema collection to SchemaOwner
>
> go
> create user Stan without login
> create user Bob without login
> go
> create schema Stan authorization Stan
> go
> create schema Bob authorization Bob
> go
> alter user Stan with default_schema=Stan
> alter user Bob with default_schema=bob
> go
> go
> exec sp_addrolemember SchemaOwner, Bob
> exec sp_addrolemember SchemaOwner, Stan
> go
>
>
> David
>
>
>

David Browne

2006-02-07, 9:23 am

"Alex" <Alex@discussions.microsoft.com> wrote in message
news:0B8EC25E-19B1-4823-AF62- 716FBB268F1A@microso
ft.com...
> Thank you David,
>
> I think that your anwsers clarified my doubts.
> In my opinion the schemas doesn't give that easier permission
> administration
> as I would expect.
> In my, that is a little bit of your sample, because I don't want to see a
> schema as a user, but an area. And in this case I will have to create many
> schemaowner roles and give some specific permissions to each one. After I
> will put the users that I want to be schema owners in each group.
> And a thing that I cannot realize yet is if I have a select permission in
> a
> schema that permits me to select data from all tables from that schema,
> why
> doesn't exist a similar kind of permission to administrate the schema,
> instead of give many specific permissions? But this question could be
> anwsered only by Microsoft :-)
>

The CREATE TABLE, etc are database-level permissions. These users already
have the right to administrate their schemas by virtue of owning them.
However just because you own a schema doesn't mean you can create a table in
the schema. Tables reauire storage, and storage is a database-level
resource.

David

Sponsored Links