Grant persmissions to database

  • We have 5 small in-house developed applications whose databases are hosted on a SQL server.

    For each application we have an execute account that is used by application to access back end SQL databases

    So I create the logins in SQL server. Now come to the users security part.

    Shall I simplely grant db_owner for these five accounts for their application databases respectively?

    They may need to read and write and also execute stored procedures in the databases. So I think db_owner should be simple and fine.

    Thanks

  • sqlfriends (3/13/2013)


    ...They may need to read and write and also execute stored procedures in the databases...

    Typically when it comes to security you want to grant the least privileges that the user needs to do its work. If these 5 users are only doing read, write, and execute type of actions, then DB_OWNER seems excessive. If the databases have schemas then a handy trick is to use permissions at the schema level instead of each individual object, for example:

    GRANT SELECT ON SCHEMA::[schemaname] TO

    GRANT INSERT ON SCHEMA::[schemaname] TO

    GRANT UPDATE ON SCHEMA::[schemaname] TO

    GRANT DELETE ON SCHEMA::[schemaname] TO

    GRANT EXECUTE ON SCHEMA::[schemaname] TO

    http://msdn.microsoft.com/en-us/library/ms187940(v=sql.105).aspx

  • If it's the application account, DBOwner may be more expected depending on how it reacts. does it create tables, delete items, insert, update, drop? I agree with least privilege, though the application may actually need DBOwner.

    .

  • Chris Harshman (3/13/2013)


    sqlfriends (3/13/2013)


    ...They may need to read and write and also execute stored procedures in the databases...

    Typically when it comes to security you want to grant the least privileges that the user needs to do its work. If these 5 users are only doing read, write, and execute type of actions, then DB_OWNER seems excessive. If the databases have schemas then a handy trick is to use permissions at the schema level instead of each individual object, for example:

    GRANT SELECT ON SCHEMA::[schemaname] TO

    GRANT INSERT ON SCHEMA::[schemaname] TO

    GRANT UPDATE ON SCHEMA::[schemaname] TO

    GRANT DELETE ON SCHEMA::[schemaname] TO

    GRANT EXECUTE ON SCHEMA::[schemaname] TO

    http://msdn.microsoft.com/en-us/library/ms187940(v=sql.105).aspx

    Thanks all,

    when granting execute on schema, what does execute mean?

    execute stored procedures,

    how about functions and triggers etc?

    Thanks

  • sqlfriends (3/13/2013)...when granting execute on schema, what does execute mean?

    execute stored procedures,

    how about functions and triggers etc?

    Thanks

    EXECUTE privilege is for stored procedures and scalar functions

    SELECT privilege would be for tables, views, and table valued functions

    triggers are events that happen behind the scenes and don't have permissions on them

  • Thanks much.

  • +1 to least privilege. Adding members to db_owner is great for allowing developers access to a development database but beyond that I think it should rarely be handed out, if ever.

    There are no special teachers of virtue, because virtue is taught by the whole community.
    --Plato

  • Assign db_executor, db_datareader, db_datawriter roles to user.

  • sankar276 (3/13/2013)


    Assign db_executor, db_datareader, db_datawriter roles to user.

    db_executor?

    There are no special teachers of virtue, because virtue is taught by the whole community.
    --Plato

  • Chris Harshman (3/13/2013)


    sqlfriends (3/13/2013)


    ...They may need to read and write and also execute stored procedures in the databases...

    Typically when it comes to security you want to grant the least privileges that the user needs to do its work. If these 5 users are only doing read, write, and execute type of actions, then DB_OWNER seems excessive. If the databases have schemas then a handy trick is to use permissions at the schema level instead of each individual object, for example:

    GRANT SELECT ON SCHEMA::[schemaname] TO

    GRANT INSERT ON SCHEMA::[schemaname] TO

    GRANT UPDATE ON SCHEMA::[schemaname] TO

    GRANT DELETE ON SCHEMA::[schemaname] TO

    GRANT EXECUTE ON SCHEMA::[schemaname] TO

    http://msdn.microsoft.com/en-us/library/ms187940(v=sql.105).aspx

    Hi, If I granted permission for above, later if I would like to use a query find out what permission has been granted to this user, is there an easy way to look clearly about it?

    Or simply to say how can I query out what permission has been granted to a specific user on a specific database?

    Thanks much

  • sqlfriends (3/14/2013)


    Chris Harshman (3/13/2013)


    sqlfriends (3/13/2013)


    ...They may need to read and write and also execute stored procedures in the databases...

    Typically when it comes to security you want to grant the least privileges that the user needs to do its work. If these 5 users are only doing read, write, and execute type of actions, then DB_OWNER seems excessive. If the databases have schemas then a handy trick is to use permissions at the schema level instead of each individual object, for example:

    GRANT SELECT ON SCHEMA::[schemaname] TO

    GRANT INSERT ON SCHEMA::[schemaname] TO

    GRANT UPDATE ON SCHEMA::[schemaname] TO

    GRANT DELETE ON SCHEMA::[schemaname] TO

    GRANT EXECUTE ON SCHEMA::[schemaname] TO

    http://msdn.microsoft.com/en-us/library/ms187940(v=sql.105).aspx

    Hi, If I granted permission for above, later if I would like to use a query find out what permission has been granted to this user, is there an easy way to look clearly about it?

    Or simply to say how can I query out what permission has been granted to a specific user on a specific database?

    Thanks much

    I have the following 2 queries that i use:

    SELECT dbuser.name, dbrole.name

    FROM sys.database_principals dbrole

    INNER JOIN sys.database_role_members rm

    ON rm.role_principal_id = dbrole.principal_id

    INNER JOIN sys.database_principals dbuser

    ON dbuser.principal_id = rm.member_principal_id

    ORDER BY dbuser.name

    SELECT pri.name, pro.name

    FROM sys.procedures pro

    INNER JOIN sys.database_permissions per

    ON pro.object_id = per.major_id

    INNER JOIN sys.database_principals pri

    ON pri.principal_id = per.grantee_principal_id

    The first one shows any user roles (db_owner, db_reader, db_writer .....)

    The second shows any stored procedures.

    The way I prefer to do permissions is with SP's being the only way to access the data preferably but if adhoc queries are going to be run from the app give data reader/datawriter (if updates need to be done out side of SP's which personally i never like).

    If i want to go and create a custom level of access i prefer creating a custom DB role for the application put the application user in that role then assign all needed permissions to the role not the user. The benifit of this is that if you want a second user for the application for some reason (or a second app that has the same level of access but a different user) it makes things easier.

    EDIT: You should be able to work with the sys.database_permissions table to get any thing else you may need.


    For faster help in answering any problems Please read How to post data/code on a forum to get the best help - Jeff Moden[/url] for the best way to ask your question.

    For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw[/url]

    Need to Split some strings? Jeff Moden's DelimitedSplit8K[/url]
    Jeff Moden's Cross tab and Pivots Part 1[/url]
    Jeff Moden's Cross tab and Pivots Part 2[/url]

  • For my case, do you think if I use above grant to schema permissions, it is better to create a customized role first?

    Thanks

  • sqlfriends (3/14/2013)


    For my case, do you think if I use above grant to schema permissions, it is better to create a customized role first?

    Thanks

    My preferred method is to use Roles (OR groups in AD) to assign permissions instead of assigning to individual users. Then you document what permissions the role has and if you forget to document adding a user to the role its easy to look at which user is assigned to each DB Role with a simple query.

    For the best way as always it depends on the environment and the people at the top.


    For faster help in answering any problems Please read How to post data/code on a forum to get the best help - Jeff Moden[/url] for the best way to ask your question.

    For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw[/url]

    Need to Split some strings? Jeff Moden's DelimitedSplit8K[/url]
    Jeff Moden's Cross tab and Pivots Part 1[/url]
    Jeff Moden's Cross tab and Pivots Part 2[/url]

  • I like to use Database Roles and only grant permissions to the Roles even if the Role will only have one member initially. Consider that if a person leaves an organization you may delete their Login and Database User but the Role will still be there with the permissions granted to it and the person's replacement can easily be added to that Role and have the same permissions their predecessor had. That is just one benefit of using Roles, there are numerous and there really is no reason not to use them and avoid granting permissions directly to a User.

    There are no special teachers of virtue, because virtue is taught by the whole community.
    --Plato

  • Thanks, I will create roles first.

Viewing 15 posts - 1 through 15 (of 15 total)

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