[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]
