Re: sql server 2005 security and schemas: an example please?
- From: "Uri Dimant" <urid@xxxxxxxxxxx>
- Date: Sun, 14 Dec 2008 10:25:31 +0200
Robeito
First of all what vesrion of SQL Server are you using? If it is SQL Server
2005 you can schema object to separate different groups and give them
permission only for schema that belongs to them. It is very huge and
important topic, I'd suggest to spend a few days to study it
---Schemas and metadata permissions
USE dbDemo
GO
-- Create a dbo table
CREATE TABLE dbo.t (id int)
GO
-- new user
CREATE LOGIN Joe WITH password='jghghghj'
CREATE USER Joe FOR LOGIN Joe
GRANT CREATE TABLE TO Joe
GO
-- this fails, because Joe does not have permissions on schema 'dbo'
SETUSER 'Joe'
GO
CREATE TABLE t1 (id int)
GO
-- schema for Joe
SETUSER
GO
CREATE SCHEMA JoeSchema AUTHORIZATION Joe
GO
-- this still fails, because Joe does not have permissions on schema 'dbo'
SETUSER 'Joe'
GO
CREATE TABLE t1 (id int)
GO
-- this works
CREATE TABLE JoeSchema.t1 (id int)
GO
SETUSER
GO
--- Execution context
-- prepare
CREATE LOGIN Joe WITH PASSWORD = 'hj'
CREATE LOGIN Paul WITH PASSWORD = 'fff'
CREATE LOGIN Tom WITH PASSWORD = 'hhhhh'
GO
CREATE USER Joe FOR LOGIN Joe WITH DEFAULT_SCHEMA = Joe
CREATE USER Paul FOR LOGIN Paul WITH DEFAULT_SCHEMA = Paul
CREATE USER Tom FOR LOGIN Tom WITH DEFAULT_SCHEMA = Tom
CREATE SCHEMA JoeSchema AUTHORIZATION Joe
GO
CREATE SCHEMA PaulSchema AUTHORIZATION Paul
GO
CREATE SCHEMA TomSchema AUTHORIZATION Tom
GO
GRANT CREATE TABLE TO Joe, Paul, Tom
GRANT CREATE PROCEDURE TO Joe, Paul, Tom
GO
-- create as Joe
SETUSER 'Joe'
CREATE TABLE JoeSchema.Joetbl(id int)
GO
CREATE PROCEDURE JoeSchema.JoeProcNoDynamic
AS
SELECT * FROM JoeSchema.Joetbl
GO
CREATE PROCEDURE JoeSchema.JoeProcDynamic
AS
EXECUTE ('SELECT * FROM JoeSchema.JoeTab')
GO
GRANT EXECUTE ON JoeSchema.JoeProcNoDynamic TO Paul
GRANT EXECUTE ON JoeSchema.JoeProcDynamic TO Paul
GO
SETUSER
GO
"robeito" <robeito@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:6D16A97F-F013-4036-8D96-21627C282B23@xxxxxxxxxxxxxxxx
Let me establish the following didactic escenario:
There are 1000 users registered in the active directory , 50 of them are
going to use an application that is not developed yet. They belong to
different departments.
Only the people of the TI support department can use the "sa" account of
the
sql server.
The group of developers has 3 members. They must be provided with
privileges for the sql development in the base (create-alter-etc.) for
tables, views, stored procedures, etc.
The final users must not have privileges to do that, they will only read
and
write data in tables, views, and execute stored procedures.
During the development, the developers sould test the functionallity of
the
application by loggin in with privileges of a final user, so they can see
if
it works fine.
At the end of the development, the access to the base must be revoqued to
the developers
Eventually, the number of users can raise, so they must use the tables,
views, stored procedures, etc. without intervention of the developent
team.
The access to the sql server must be "Windows Authentication"
How to resolve this?
How do the queries should look like? (select * from XXXX.myTable)
Do they can be written without the XXXX prefix?
What is the order of creation for: the base, the schema, the login, the
users, etc.?
Could you answer with a step by step script?
Please include a sample of the sql code involved
Thanks in advance
.
- Follow-Ups:
- Re: sql server 2005 security and schemas: an example please?
- From: robeito
- Re: sql server 2005 security and schemas: an example please?
- From: Erland Sommarskog
- Re: sql server 2005 security and schemas: an example please?
- References:
- sql server 2005 security and schemas: an example please?
- From: robeito
- sql server 2005 security and schemas: an example please?
- Prev by Date: Re: Restore Prod DB Into Dev DB Apply Dev Security
- Next by Date: Re: Restore Prod DB Into Dev DB Apply Dev Security
- Previous by thread: sql server 2005 security and schemas: an example please?
- Next by thread: Re: sql server 2005 security and schemas: an example please?
- Index(es):
Relevant Pages
|
