Security and schemas: an example please?

  • Let me establish the following didactic scenario:

    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, functions, 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 should test the functionality of the

    application by login 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 development team.

    The access to the sql server must be "Windows Authentication"

    How to resolve all this situation?

    How many schemas are needed? one for developing and other for the final users?

    What differences are between a schema and a role?

    How do the queries should look like? (select * from XXXX.myTable)

    Do they can be written without the XXXX prefix by the developers?

    If so, will they be accessible also for the users?

    What is the order of creation for: the database, the schema(s), the logins, the

    users, the roles, etc.?

    Please include a sample of the sql code involved

    Is it convenient to create a group in the active directory with the 50 users?

    Do the 3 developers can own the "dbo" schema? If so, is this enough to allow them

    to create-alter-view-select-insert-execute...?

    How will they login for testing as final users? (windows authentication is going to be used)

    Could you answer with a step by step script? (create this thing first, then grant that...)

    Thanks in advance

  • This sounds an awful lot like a classroom assignment. Even if it's not, can you post what your thoughts are so we know kind of where your starting point is, so we can walk you through from there?

    For instance, one basic question: do you have separate production and development environments?

    K. Brian Kelley
    @kbriankelley

  • I develop systems using MS Access projects (*.ADP), I know almost nothing of SQL Server 2005

    The DBA is migrating SQL databases from 2000 to 2005 and the use of schemas even is not clear to him, so I decided to ask for help for increase my knowledge about this theme.

    Actually my applications could gain access to the databases by this ways:

    1.- Using an sql account that logs in with dbo permissions. That is the account that I use during the development to create and manipulate tables, views, etc. in the sql server database, directly from MS Access.

    2.- The users connect with the same application, but they use a sql account with privileges of datareader and datawriter, and each stored procedure must be granted with exec permissions for this account too.

    But now the login must done with windows authentication, and this changes everything.

    I believe that the 50 users must be attached to a group in the domain (myAppUsers or something like that), and grant datareader datawriter privileges to the database for this group

    But, for the beginning, ยฟwhat is the order of creation for schemas, roles, logins, access, users, etc?

  • The thing is there are still a lot of options and different ways to go here and it is hard to say which may be "best" for you.

    For instance, who is a "User" in your app & database and who is a developer can be controlled either through Windows AD or through SQL Server security admins. You use a different setup for each approach, but which is better depends on your environment and circumstances.

    [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
    Proactive Performance Solutions, Inc.
    [/font]
    [font="Verdana"] "Performance is our middle name."[/font]

  • Only the people of the TI support department can use the "sa" account of the

    sql server.

    You should create individual Logins for each member of this department and add them to the sysadmin group rather than sharing 'sa' password amongst them.

    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, functions, stored procedures, etc.

    I'd rather create a role called developer, grant all rights that the developer needs and then grant that role's permission to developers..

    During the development, the developers should test the functionality of the

    application by login in with privileges of a final user, so they can see if

    it works fine.

    For this you must have a dev server. You should not test things on the production.

    How many schemas are needed? one for developing and other for the final users?

    developement should ideally be done on a dev database(on a dev server).

    Is it convenient to create a group in the active directory with the 50 users?

    not much idea on domain controller but yes, you can create groups.

    Do the 3 developers can own the "dbo" schema? If so, is this enough to allow them

    to create-alter-view-select-insert-execute...?

    anyone can own the dbo schema(if you allow them). The question is do they really need to own the schema or will specific permissions will suffice. that you need to decide upon.

    Ideally developers are not the owners ๐Ÿ™‚

    refer to BOL and msdn for more details...



    Pradeep Singh

  • Thanks ps

    It was really usefull your information

  • Quite informative. Thanks

    Thanks a lot,
    Hary

  • Thank You ๐Ÿ™‚



    Pradeep Singh

  • Is it convenient to create a group in the active directory with the 50 users?

    Yes. I'd create a security group in AD and add users there. In SQL, grant access to the group. The group's rights should not change, but its membership will over time... In AD, work with groups whenever possible.

  • Thanks for clarification Christopher :). I also searched few posts here on AD and groups and found this to be quite informative.

    http://www.sqlservercentral.com/Forums/Topic498389-146-1.aspx



    Pradeep Singh

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply