July 28, 2008 at 2:36 am
Hi Everyone,
We normally advise our clients to grant DB_owner permissions to the database behind our software application. However a new client has stated this is against their internal policy and would like to grant just DB_DataWriter and DB_DataWriter. This is fine however there are well over 100 stored procedures that the login will need to execute for the application to work. Is there a role I can apply along with DB_DataWriter and DB_DataWriter that will provide the privileges to run the stored procedures associated with the database or do I have to visit each stored procedure and explicitly grant Execute permissions for the user?
Many thanks for your help
Dave
July 28, 2008 at 2:45 am
There is no standard database role for executing stored procedurees, but if you're using SQL 2005 you can grant execute permission on the schema which contains the stored procedures.
[font="Verdana"]Markus Bohse[/font]
July 28, 2008 at 3:14 am
Hi Markus,
Many thanks for your help…
Would I grant DB_DataWriter and DB_DataWriter permissions and then run something like…
GRANT EXECUTE ON SCHEMA :: dbo TO DavidN;
To grant the permission to execute stored procedures?
Regards
David
July 28, 2008 at 3:53 am
db_datareader gives the right to run SELECT queries on any table or view. db_datawriter allows INSERT, UPDATE and DELETE on any tyable or view.
If you do all your data access through stored procedures granting Execute permission on the schema might be enough, you won't even need the other two roles. But if you application also does SELECT or modification of data directly on the tables (without using a SP), then you must give the users the appropriate permissions.
[font="Verdana"]Markus Bohse[/font]
July 28, 2008 at 4:07 am
I think our application code does directly hit the tables (as well as via the stored procedures) so I will grant the datareader and datawriter priviledges too.
Many thanks for your excellent help
Regards
David
July 29, 2008 at 8:46 am
Thanks Marcus. That information was useful for me as well.
July 30, 2008 at 6:14 am
If you grant datawriter, how do you prevent users from making changes to data outside the scope of a program? If you grant datawriter, can't a user just connect with Access and make any desired changes? I may be wrong on this, but it seems like a gaping hole.
July 30, 2008 at 6:20 am
Hi Ross,
The application uses SQL Authentication with the SQL Login password stored in an encrypted file. Users would need to know this password to access the database outside of the application.
David
July 30, 2008 at 6:37 am
OK, it makes sense to grant a SQL login used only by an application the ability to change data. We do that by making the SQL login dbo. I was reading the original note as implying the users themselves would have datawriter, which would be a bad thing.
How do you handle password changes to the SQL login and the ensuing requirement to change the stored encrypted password? Teh SQL login password change has to be performed by someone with admin rights to the SQL server. Once the password is changed, the app won't be able to connect until the encrypted password is changed. Loads of fun trying to coordinate that process when your SQL support is on the other side of the world.
July 30, 2008 at 7:09 am
Ross McMicken (7/30/2008)
OK, it makes sense to grant a SQL login used only by an application the ability to change data. We do that by making the SQL login dbo. I was reading the original note as implying the users themselves would have datawriter, which would be a bad thing.How do you handle password changes to the SQL login and the ensuing requirement to change the stored encrypted password? Teh SQL login password change has to be performed by someone with admin rights to the SQL server. Once the password is changed, the app won't be able to connect until the encrypted password is changed. Loads of fun trying to coordinate that process when your SQL support is on the other side of the world.
Ross,
of course every time you give a login datawriter or any over change permission, this could be used to change data from outside the application as well, but your practice of making the application login dbo is even worse, because this means that not only can you change the data, but also you could change the database objects as well.
Yes, users usually don't know the password, but I've seen to many applications which stored passwords in plain text either in a config file.
If you want to make sure that data is only changed through one application you should create an application role.
If you want to be able to audit changes, use Windows authentification or give each user it's own SQL login with a strong password. I've seen too many situations where data or database objects had been changed and even with logreader tools we couldn't find who did it, because the application used one standard login.
[font="Verdana"]Markus Bohse[/font]
January 29, 2009 at 12:55 am
Hi, Is there a way to solve this problem on SQL 2000?
January 30, 2009 at 7:29 am
I would personally create a user defined role that has the exact permissions you want and add the desired logins into that role. That way you can be more specific on what users can do.
If all the users run the same application and need the some rights, than just one user role is needed but if you have multiple applications, you can assign a role for each and add the users to the role that they need permissions for.
Steve
January 31, 2009 at 4:46 pm
the following can be used to generate the scripts to grant execute on all SP's in a database if required
select 'grant execute on ' + s.name + '.' + o.name + ' to [mysqlacctorrole]'
from sys.objects o inner join sys.schemas s
on o.schema_id = s.schema_id where o.type = 'P' and o.is_ms_shipped = 0
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply