common_schema

sql_accounts

NAME

sql_accounts: Generate SQL statements to block/release accounts. Provide info on accounts.

TYPE

View

DESCRIPTION

sql_accounts fills in a missing feature in MySQL: the ability to temporarily block user accounts and release them, without tampering with their privileges. It hacks its way by modifying and controlling accounts' passwords in a symmetric way.

To block an account, this view generates a SQL query which changes the account's password into a blocked-password value in such way that:

• It is impossible to log in to the account with the original password
• It is impossible to log in to the account with any other password
• It is possible to detect that the password is a blocked-password
• It is possible to recover the original password

The SQL query to release a blocked account is likewise generated by this view.

In fact, the view provides metadata for each account to explain accounts status: is it blocked? Is it released? Is the password empty? Old?

By generating the SQL statement to modify the account this view fall well onto use of eval() (see examples below).

One should note that blocking accounts does not terminate existing account sessions. To kill existing sessions use the killall() routine.

STRUCTURE

mysql> DESC common_schema.sql_accounts;
+---------------------+--------------+------+-----+---------+-------+
| Field               | Type         | Null | Key | Default | Extra |
+---------------------+--------------+------+-----+---------+-------+
| user                | char(16)     | NO   |     |         |       |
| host                | char(60)     | NO   |     |         |       |
| grantee             | varchar(100) | YES  |     | NULL    |       |
| password            | char(41)     | NO   |     |         |       |
| is_empty_password   | int(1)       | NO   |     | 0       |       |
| is_new_password     | int(1)       | NO   |     | 0       |       |
| is_old_password     | int(1)       | NO   |     | 0       |       |
| is_blocked          | int(1)       | NO   |     | 0       |       |
| sql_block_account   | longtext     | YES  |     | NULL    |       |
| sql_release_account | varchar(163) | YES  |     | NULL    |       |
+---------------------+--------------+------+-----+---------+-------+

SYNOPSIS

Columns of this view:

• user: account user part
• host: account host part
• grantee: grantee name
• password: current password for grantee (as in mysql.user)
• is_empty_password: boolean, 1 if account's password is empty, 0 otherwise.
• is_new_password: boolean, 1 if password is in new format, 0 otherwise.
• is_old_password: boolean, 1 if password is in old format, 0 otherwise. This is the opposite of is_new_password.
  The two columns are mostly informative.
• is_blocked: boolean, 1 if account is currently blocked (by logic of this very view), 0 otherwise.
• sql_block_account: A SQL (SET PASSWORD) query to block the account, making it inaccessible.
  The query has no effect on an account which is already blocked.
  Use with eval() to apply query.
• sql_release_account: A SQL (SET PASSWORD) query to release an account, re-enabling it.
  The query has no effect on an account which is not blocked.
  Use with eval() to apply query.

EXAMPLES

Show info for 'gromit'@'localhost' account:

mysql> SELECT * FROM sql_accounts WHERE USER = 'gromit' \G

               user: gromit
               host: localhost
            grantee: 'gromit'@'localhost'
           password: *23AE809DDACAF96AF0FD78ED04B6A265E05AA257
  is_empty_password: 0
    is_new_password: 1
    is_old_password: 0
         is_blocked: 0
  sql_block_account: SET PASSWORD FOR 'gromit'@'localhost' = '752AA50E562A6B40DE87DF0FA69FACADD908EA32*'
sql_release_account: SET PASSWORD FOR 'gromit'@'localhost' = '*23AE809DDACAF96AF0FD78ED04B6A265E05AA257'

Block all accounts with user 'gromit'. Show info again:

mysql> CALL eval("SELECT sql_block_account FROM sql_accounts WHERE USER = 'gromit'");

mysql> SELECT * FROM sql_accounts WHERE USER = 'gromit' \G

               user: gromit
               host: localhost
            grantee: 'gromit'@'localhost'
           password: 752AA50E562A6B40DE87DF0FA69FACADD908EA32*
  is_empty_password: 0
    is_new_password: 1
    is_old_password: 0
         is_blocked: 1
  sql_block_account: SET PASSWORD FOR 'gromit'@'localhost' = '752AA50E562A6B40DE87DF0FA69FACADD908EA32*'
sql_release_account: SET PASSWORD FOR 'gromit'@'localhost' = '*23AE809DDACAF96AF0FD78ED04B6A265E05AA257'

Note that account's password is modified. It is modified to a value accepted by MySQL, but which can never be generated by the PASSWORD() function. However, it is easily reversible.

Release all blocked accounts. Check:

mysql> CALL eval("SELECT sql_release_account FROM sql_accounts");

mysql> SELECT COUNT(*) AS count_blocked_accounts FROM sql_accounts WHERE is_blocked;
+------------------------+
| count_blocked_accounts |
+------------------------+
|                      0 |
+------------------------+

ENVIRONMENT

MySQL 5.1 or newer

SEE ALSO

eval(), security_audit(), sql_show_grants

AUTHOR

Shlomi Noach

common_schema documentation