List:General Discussion« Previous MessageNext Message »
From:Andrew_Hoying Date:October 12 2005 4:26pm
Subject:Re: Downgrade privileges on connect
View as plain text  

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

Thanks again,
Andrew Hoying

             10/12/2005 09:20                                           To 
             AM                        Andrew_Hoying@stripped               
                                       Re: Downgrade privileges on connect 

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
> 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
> 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
> the user will not be able to write to the database even if the
> controls are circumvented. As it is now, I have to create multiple
> 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

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

Downgrade privileges on connectAndrew_Hoying12 Oct
  • Re: Downgrade privileges on connectSGreen12 Oct
    • Re: Downgrade privileges on connectAndrew_Hoying12 Oct
      • Re: Downgrade privileges on connectSGreen12 Oct
        • Re: Downgrade privileges on connectAndrew_Hoying12 Oct
          • Re: Downgrade privileges on connectJoerg Bruehe13 Oct
        • Re: Downgrade privileges on connectScott Gifford12 Oct
  • Re: Downgrade privileges on connectScott Gifford12 Oct