Can someone please help me with a simple schema example for security?

  • 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

  • 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
    “We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

    ― Charles R. Swindoll

    How to post questions to get better answers faster
    Managing Transaction Logs

  • 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

  • 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
    “We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

    ― Charles R. Swindoll

    How to post questions to get better answers faster
    Managing Transaction Logs

  • 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

  • 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
    “We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

    ― Charles R. Swindoll

    How to post questions to get better answers faster
    Managing Transaction Logs

  • 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

  • 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