July 24, 2008 at 6:13 am
Hi All,
I am having some problems here, its to do with SQL logins for a web application, the way the web applications work is that it runs on Java and needs to make database calls using stored procedures, the login details to the database needs to be embedded in the java code.
The problem is that, if one assigns individual permissions to the table, i.e grant all selects, and execute procedures etc, if an object is dropped and re-created, it means that the permission will be lost as well, and for critical applications the last thing one needs it for it to be failing because it hasn’t got permissions to the database.
I would like to know what solution other professionals have successfully used, I have head about application roles, but don’t quite know how this works and whether it may be useful in a web setting, the other thing is that I want to prevent users from using the web logon for their own daily use, I normally find developers lazy and they just tend to use the application login details instead of asking for their own individual logins.
Looking forward to your replies.
July 24, 2008 at 6:51 am
I would put all the source code (sps, functions etc) in a source code control system with 1 sp or function etc per file and then add appropriate grant statements at the end of each file.
Mike John
July 24, 2008 at 7:31 am
Other than the default roles, you are going to have to explicitly do the grant when you create an object. You could give the users db_datareader / db_datawriter and they would have read/write to all tables, but if you need more restrictive permissions, you will have to remember to include the permissions.
Object drops and re-creates in your production application database should rpetty much never happen. Using ALTER statements is what you really should be doing - these retain permissions. If your schema is going to change so often that you need to be concerned about automating something for these permissions, you probably have a design issue. Excluding temporary tables, schema in your database should be relatively stable. So, you may be trying to solve a non-problem.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply