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]
