You are correct in that each mysql user account only has one set of permissions. Your application talks to the database and it may only need one login. One login = one set of permissions. When I am designing a web-based application, I always create at least two accounts. One will be read-only, the other read-write. All of the pages that do nothing but query the database (no inserts, updates, or deletes) use the read-only login. Those pages that must somehow affect the data use the other one. Neither of my application's mysql accounts will have full admin rights to the entire server.
Each user authenticates with the application. It's your application that must downgrade the user's privileges to use the application's features. Your application will still need access to the data it gets from MySQL so changing your MySQL permissions doesn't make any sense, does it. It's your application that needs to say "no" to the user. You don't want MySQL saying "no" to your application. Do you? Shawn Green Database Administrator Unimin Corporation - Spruce Pine [EMAIL PROTECTED] wrote on 10/12/2005 12:26:54 PM: > > > > > What you describe is how I have it set up today. My thoughts on the matter > are that it makes more sense to maintain a single SQL user account for the > application with on password to manage, rather than two or three or four, > and then allow that SQL user, when the connection is made, what effective > privileges you want MySQL to allow to that connection. That way each > connection can have varying privileges without having multiple MySQL user > accounts. > > Thanks again, > Andrew Hoying > > > [EMAIL PROTECTED] > > 10/12/2005 09:20 To > AM [EMAIL PROTECTED] > cc > mysql@lists.mysql.com > Subject > Re: Downgrade privileges on connect > > > > > [EMAIL PROTECTED] wrote on 10/12/2005 10:56:43 AM: > > > > > > > > > > > Hello, > > > > Is it possible for a MySQL connection to request a downgrade in > privileges? > > What I'd like to be able to do is create one database user account for a > > database application with read and write privileges to the tables, but if > > an application user logs in who only has read access, then to downgrade > the > > permission's on the SQL server for the database user to select only for > > that connection. I want to be able to give an added level of assurance > that > > the user will not be able to write to the database even if the > application > > controls are circumvented. As it is now, I have to create multiple > database > > users and choose which one to connect to the database with based on the > > application users authorization level. > > > > > > So are you saying that you would rather have each user share a direct > database login with other users (granting them certain common rights) or do > you want each of your users to have their own separate database login? I > really don't think you want either. > > As you describe it, your application is in charge of authenticating each > user (not the database). That means that each user only gets the privileges > that the application lets them have. If your application's security > structure is not set up to deny privileges based on their login, then your > application is poorly designed. > > Your application's users should not have ANY direct access to the database > on the backend unless you give them a database login to connect with AND > they have the ability to connect directly to the database. The account your > users use to connect to your application should not be a mysql user > account. That should be something setup as a separate table in a separate > database. Having at least two logins for each application is a wise > precaution. One login can have read-only privileges while the other can > have read-write privileges. Those would be mysql logins. The users should > not have access to that information. > > Your security model should look something vaguely like this: > > ||| ||| > database ||| application ||| user > server ||| ||| > ^ ^ > mysql login application login > > That way the user never "talks" directly to the database. All user > interaction with data is filtered and controlled by the application. The > user should only have the ability to do things in the application that the > user has been given privileges to do. It's the application's responsibility > to act like a firewall between the user and the database. > > Now, don't do something dumb and put mysql logins and passwords as plain > text into an application that you install on the user's system. It is SO > easy to read through a compiled file and get all of the plain-text strings > and try them out. What would be better is if your user's never even had > access to the code that talks to the database (not even as a file on their > system). > > Please write back if any part of this is unclear. I know the Bureau of > Land Management has had problems in the past securing certain user-facing > applications and I, and I am sure many others on the list, would like to > help to keep you as safe as possible. > > Shawn Green > Database Administrator > Unimin Corporation - Spruce Pine > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] >
