Application / Database user management
-
Hello All, When designing an application, desktop or web, what is the best way to manage users that must access a database? Is it better to have a single group that all users belong to and let the application manage all the security? Or setup different groups with different permissions on the database and then associate users with that and whatever security the database can't manage handle that in the application? Or is there a third way that I am just not seeing?
Thanks, Koshbert
-
Hello All, When designing an application, desktop or web, what is the best way to manage users that must access a database? Is it better to have a single group that all users belong to and let the application manage all the security? Or setup different groups with different permissions on the database and then associate users with that and whatever security the database can't manage handle that in the application? Or is there a third way that I am just not seeing?
Thanks, Koshbert
You will get answers for both methods. 1) Create users and handle security in the database 2) Create users in the application and connect with one user to the database. There are pluses and minuses for both situations, however, consider this ... Given a website like Facebook, you can create an account and have some set of permissions. Do you really think it is creating a database user on the backend? Hmmm ... Probably not. The real answer is up to you. You can do it either way. I personally create one user in the database and handle all the security in the application. Good luck. :thumbsup:
-
Hello All, When designing an application, desktop or web, what is the best way to manage users that must access a database? Is it better to have a single group that all users belong to and let the application manage all the security? Or setup different groups with different permissions on the database and then associate users with that and whatever security the database can't manage handle that in the application? Or is there a third way that I am just not seeing?
Thanks, Koshbert
I agree with David. I usually create users/rights as structures in the database and use one SQL user/password to access the database. I do break this rule when security is a one shot item -> You either have full access or you have zero access. In this case I won't go to the trouble to create user/rights structures and just use standard group security on the database.
-
I agree with David. I usually create users/rights as structures in the database and use one SQL user/password to access the database. I do break this rule when security is a one shot item -> You either have full access or you have zero access. In this case I won't go to the trouble to create user/rights structures and just use standard group security on the database.
So then you guys have a table with user ids and a hash for the password that is used for logging into the application and everyone is associated with the one database user id. That makes the database management piece pretty easy. So what is the downside to this approach?
Thanks, Koshbert
-
I agree with David. I usually create users/rights as structures in the database and use one SQL user/password to access the database. I do break this rule when security is a one shot item -> You either have full access or you have zero access. In this case I won't go to the trouble to create user/rights structures and just use standard group security on the database.
Follow up question: For desktop applications how do you keep the single user id / password in sync? Alot of people for good reason want to change the supplied default password so hard coding it would be a bad idea. Is this kind of design pattern covered in a blog, article or book anywhere online?
Thanks, Koshbert
-
So then you guys have a table with user ids and a hash for the password that is used for logging into the application and everyone is associated with the one database user id. That makes the database management piece pretty easy. So what is the downside to this approach?
Thanks, Koshbert
The only downside is adding development complexity. You need to manage rights in the application which requires the development of objects manage users and thier rights.
-
Follow up question: For desktop applications how do you keep the single user id / password in sync? Alot of people for good reason want to change the supplied default password so hard coding it would be a bad idea. Is this kind of design pattern covered in a blog, article or book anywhere online?
Thanks, Koshbert
I work in a closed environment and have no applications in third party hands so changing the db password is not an issue. Not sure how I would handle an application at other sites. Even encrypting a connection string would require a hardcoded key somewhere along the line.