March 13, 2013 at 12:09 pm
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
March 13, 2013 at 12:21 pm
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
March 13, 2013 at 12:25 pm
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.
.
March 13, 2013 at 1:13 pm
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] TOGRANT 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
March 13, 2013 at 1:50 pm
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
March 13, 2013 at 1:53 pm
Thanks much.
March 13, 2013 at 9:40 pm
+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
March 13, 2013 at 9:54 pm
Assign db_executor, db_datareader, db_datawriter roles to user.
March 13, 2013 at 10:43 pm
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
March 14, 2013 at 11:24 am
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] TOGRANT 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
March 14, 2013 at 11:35 am
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] TOGRANT 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 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]
March 14, 2013 at 12:08 pm
For my case, do you think if I use above grant to schema permissions, it is better to create a customized role first?
Thanks
March 14, 2013 at 1:17 pm
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 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]
March 14, 2013 at 1:30 pm
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
March 14, 2013 at 1:33 pm
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