July 15, 2009 at 2:51 pm
Hi there everyone,
I think I understand that schemas can be used to group logical objects together (which is really cool).
Correct me if i'm wrong, but I read you could also use schemas for security. Would some be able to give me simple example on how to set this up for security. I have always use dbrole and assign permissions to the roles, just wondering how to setup schemas.
Example.
Database: mydb
Tables: myschema.tbl1, myschema.tbl2
Schema: myschema
anyhelp would be much appreciated.
cheers
July 15, 2009 at 4:59 pm
Let's say you have 90 procedures in myschema. You want to grant execute rights to a role all the procedures in that schema. And, you know the developers are going to be creating many more procedures in that schema later on in the process.
So, we can create the role:
CREATE ROLE MySchema_Execute AUTHORIZATION dbo;
Then, we add a couple of users to the role:
Execute sp_addrolemember @rolename = 'MySchema_Execute', @membername = 'userA';
Execute sp_addrolemember @rolename = 'MySchema_Execute', @membername = 'userB';
Execute sp_addrolemember @rolename = 'MySchema_Execute', @membername = 'userC';
Now, we need to grant execute access to all of those procedures to our new role:
GRANT EXECUTE ON schema::MySchema TO MySchema_Execute;
And, we are done...the role will now have execute privileges on any procedure in that schema, whether it is added or edited or dropped and recreated. The execute privilege is for the schema.
Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.
How to post questions to get better answers faster
Managing Transaction Logs
July 15, 2009 at 5:48 pm
thanks for the reply.. that made it clearer..
Could you also, give a schema like DDL access as well? like groups that below in a certain schema can say only create objects.
cheers
July 15, 2009 at 7:15 pm
I am not sure what you are asking - but if you lookup GRANT in books online it has several examples of how to grant security.
Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.
How to post questions to get better answers faster
Managing Transaction Logs
July 15, 2009 at 7:42 pm
Hi there,
I was wondering if you could have maybe 2 schemas:
schemaCreateObjects (allows you to create tables, view, sp in the database)
schemaSelect (allows you select records from the objects in the schema)
cheers
July 15, 2009 at 7:57 pm
I wouldn't use schemas in that way - you really should be looking at using schemas to separate out objects by category/department/etc...
You would still use roles to define the different roles - and each role will have access to only those schemas they should have access to at the level they should have access.
Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.
How to post questions to get better answers faster
Managing Transaction Logs
July 15, 2009 at 10:27 pm
Hi there,
I have 2 groups of developers
Dev1Team
Dev2Team
How would i set it up so each group will there their own schema, and they can create / change / objects in there own schema without affecting the objects in the other group.
cheers
July 16, 2009 at 7:45 am
Free (7/15/2009)
Hi there,I have 2 groups of developers
Dev1Team
Dev2Team
How would i set it up so each group will there their own schema, and they can create / change / objects in there own schema without affecting the objects in the other group.
cheers
Create two separate schema.
Grant ALTER rights to each group of developers to their schema.
Grant the appropriate CREATE object statements to both sets of developers.
K. Brian Kelley
@kbriankelley
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply