Andrew_Hoying@stripped 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
