Hi Andrew, all!
Andrew_Hoying@stripped 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
