Re: Downgrade privileges on connect
Hi Andrew, all! [EMAIL PROTECTED] wrote: [[...]] but I believe in security in depth and so for read-only users I connect to the database with a SQL user that only has the select privilege, and for read/write users I connect to the database with a user with select,insert,update,delete privileges. Well done! This all works fine, the only reason to change it would be to reduce the number of SQL users an application requires. Then I could create one user with select,insert,update,delete, for example, but when the connection is established a SQL command could be issued requesting a downgrade of privileges to select only. It all comes down to having fewer application passwords to change on a regular basis to stay current with security requirements. Ok, I understand that goal. However, ... I realize that there is probably no way to do that with the current MySQL API, but perhaps it could be a feature request for future releases. ... "the SQL way" of handling privileges is to associate them with user accounts. I know some DBMSs have "roles", but did not yet look into these, I assume this is a way of defining a set of privileges and then provide several users with the same set. There is also the concept of "user groups", also doing this. (AIUI, both roles and groups are introduced to manipulate the privilege set only once, but still support distinct users with their own names and passwords - opposite to your goal.) I have never heard of a "privilege downgrade" while maintaining the user id, so I fear you have little chances in following such a direction. Regards, Jörg -- Joerg Bruehe, Senior Production Engineer MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Downgrade privileges on connect
[EMAIL PROTECTED] writes: [...] > 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? Having multiple layers of security is generally a good design (often called "Security in depth" or "Defense in depth"). That way if there's a flaw in your application, the damage is limited. If you think that's unlikely to happen, then you're not paying attention: http://cve.mitre.org/cgi-bin/cvekey.cgi?keyword=sql+injection ScottG. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Downgrade privileges on connect
[EMAIL PROTECTED] wrote on 10/12/2005 10:46:46 AM: > > 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. Yes, and that is how the application is coded, as that has always been the only way to do it. > 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? Again, my concern is not normal operations, but the 'what if a user fuzzes the data in a way that is completely new and unexpected in an attempt to circumvent the controls of the application?' For example, I have a database table editor, with some users having read access to see the data, and some users having write access to add, modify and delete the data. They use the same application, but users who are not allowed to write do not get access to those functions, and the server code verifies that a user has write access again before actually making any changes in case of a modified post query, or other malicious activity. It should be bulletproof, but I believe in security in depth and so for read-only users I connect to the database with a SQL user that only has the select privilege, and for read/write users I connect to the database with a user with select,insert,update,delete privileges. This all works fine, the only reason to change it would be to reduce the number of SQL users an application requires. Then I could create one user with select,insert,update,delete, for example, but when the connection is established a SQL command could be issued requesting a downgrade of privileges to select only. It all comes down to having fewer application passwords to change on a regular basis to stay current with security requirements. I realize that there is probably no way to do that with the current MySQL API, but perhaps it could be a feature request for future releases. > 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]
Re: Downgrade privileges on connect
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
Re: Downgrade privileges on connect
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 Un
Re: Downgrade privileges on connect
[EMAIL PROTECTED] writes: > 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 don't know of a way to do exactly what you describe, but you can get the same effects by creating two accounts, one with only SELECT privileges and one with whatever write privileges you need. Then you application can simply decide which account to authenticate with. > 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. That sounds like an excellent design decision. Scott. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
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
