Managing Users and Permissions
|
|
MySQL has a well-designed, flexible, and easy-to-manage permissions system. Permissions are what allow or disallow certain users or host machines from connecting to the database server and from performing certain operations on the databases, tables, or even certain columns in the tables.
For example, take some possible scenarios:
-
A news website includes a database server, a web server, and an intranet where staff update the news. Connections from the web server should only have permission to perform SELECT queries on the database, and connections from the intranet would allow UPDATE and INSERT queries for the staff.
-
A financial transactions system has one database containing a log of records and one database containing customer balances. UPDATEs are permitted on the customer balance database, but not on the log database.
-
A booking system has ordinary users who can only insert records to a particular table and an administrator who can update this table.
The mysql Database
When MySQL is installed, the mysql database is one of those automatically created. A thorough knowledge of the tables in this database is vital to be able to effectively administer security on the system (see Table 14.1). Six tables in the mysql database affect system access:
mysql> USE mysql; Database changed mysql> SHOW TABLES; +-----------------+ | Tables_in_mysql | +-----------------+ | columns_priv | | db | | func | | host | | tables_priv | | user | +-----------------+ 6 rows in set (0.00 sec)
| Table | Description |
|---|---|
| user | Lists users and the associated hosts and passwords that may access the server, as well as theglobal permissions they have. It's best to disallow any global permissions and instead specifically allow them access in one of the other tables. |
| db | Lists databases that users may access. Permissions granted here apply to all tables in the database. |
| host | Together with the db table allows a more controlled form of access based on the particular host. |
| tables_priv | Lists access to specific tables. Permissions granted here apply to all columns in the table. |
| columns_priv | Lists access to specific columns. |
| func | Not yet used. |
What Fields the Tables Contain
Let's take a look at the tables in the mysql database. Your distribution may contain some slight differences.
mysql> SHOW COLUMNS FROM user; +-----------------------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-----------------------+--------------+------+-----+---------+-------+ | Host | varchar(60) binary | | PRI | | | | User | varchar(16) binary | | PRI | | | | Password | varchar(16) binary | | | | | | Select_priv | enum('N','Y')| | | N | | | Insert_priv | enum('N','Y')| | | N | | | Update_priv | enum('N','Y')| | | N | | | Delete_priv | enum('N','Y')| | | N | | | Create_priv | enum('N','Y')| | | N | | | Drop_priv | enum('N','Y')| | | N | | | Reload_priv | enum('N','Y')| | | N | | | Shutdown_priv | enum('N','Y')| | | N | | | Process_priv | enum('N','Y')| | | N | | | File_priv | enum('N','Y')| | | N | | | Grant_priv | enum('N','Y')| | | N | | | References_priv | enum('N','Y')| | | N | | | Index_priv | enum('N','Y')| | | N | | | Alter_priv | enum('N','Y')| | | N | | | Show_db_priv | enum('N','Y')| | | N | | | Super_priv | enum('N','Y')| | | N | | | Create_tmp_table_priv | enum('N','Y')| | | N | | | Lock_tables_priv | enum('N','Y')| | | N | | | Execute_priv | enum('N','Y')| | | N | | | Repl_slave_priv | enum('N','Y')| | | N | | | Repl_client_priv | enum('N','Y')| | | N | | | ssl_type | enum('','ANY' ,'X509' ,'SPECIFIED') | | | | | | ssl_cipher | blob | | | | | | x509_issuer | blob | | | | | | x509_subject | blob | | | | | | max_questions | int(11) unsigned | | | 0 | | | max_updates | int(11) unsigned | | | 0 | | | max_connections | int(11) unsigned | | | 0 | | +-----------------------+--------------+------+-----+---------+-------+ 31 rows in set (0.00 sec) mysql> SHOW COLUMNS FROM db; +-----------------+-----------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-----------------+-----------------+------+-----+---------+-------+ | Host | char(60) binary | | PRI | | | | Db | char(64) binary | | PRI | | | | User | char(16) binary | | PRI | | | | Select_priv | enum('N','Y') | | | N | | | Insert_priv | enum('N','Y') | | | N | | | Update_priv | enum('N','Y') | | | N | | | Delete_priv | enum('N','Y') | | | N | | | Create_priv | enum('N','Y') | | | N | | | Drop_priv | enum('N','Y') | | | N | | | Grant_priv | enum('N','Y') | | | N | | | References_priv | enum('N','Y') | | | N | | | Index_priv | enum('N','Y') | | | N | | | Alter_priv | enum('N','Y') | | | N | | +-----------------+-----------------+------+-----+---------+-------+ 13 rows in set (0.01 sec) mysql> SHOW COLUMNS FROM host; +-----------------+-----------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-----------------+-----------------+------+-----+---------+-------+ | Host | char(60) binary | | PRI | | | | Db | char(64) binary | | PRI | | | | Select_priv | enum('N','Y') | | | N | | | Insert_priv | enum('N','Y') | | | N | | | Update_priv | enum('N','Y') | | | N | | | Delete_priv | enum('N','Y') | | | N | | | Create_priv | enum('N','Y') | | | N | | | Drop_priv | enum('N','Y') | | | N | | | Grant_priv | enum('N','Y') | | | N | | | References_priv | enum('N','Y') | | | N | | | Index_priv | enum('N','Y') | | | N | | | Alter_priv | enum('N','Y') | | | N | | +-----------------+-----------------+------+-----+---------+-------+ 12 rows in set (0.01 sec) mysql> SHOW COLUMNS FROM tables_priv; +-------------+----------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------------+----------------------+------+-----+---------+-------+ | Host | char(60) binary | | PRI | | | | Db | char(64) binary | | PRI | | | | User | char(16) binary | | PRI | | | | Table_name | char(60) binary | | PRI | | | | Grantor | char(77) | | MUL | | | | Timestamp | timestamp(14) | YES | | NULL | | | Table_priv |set('Select','Insert', 'Update', 'Delete', 'Create', 'Drop', 'Grant', 'References', 'Index', 'Alter') | | | | | | Column_priv |set('Select','Insert', 'Update','References')| | | | | +-------------+----------------------+------+-----+---------+-------+ 8 rows in set (0.01 sec) mysql> SHOW COLUMNS FROM columns_priv; ; +-------------+-----------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------------+-----------------------+------+-----+---------+-------+ | Host | char(60) binary | | PRI | | | | Db | char(64) binary | | PRI | | | | User | char(16) binary | | PRI | | | | Table_name | char(64) binary | | PRI | | | | Column_name | char(64) binary | | PRI | | | | Timestamp | timestamp(14) | YES | | NULL | | | Column_priv | set('Select','Insert', 'Update','References') | | | | | +-------------+-----------------------+------+-----+---------+-------+ 7 rows in set (0.01 sec) mysql> SHOW COLUMNS FROM func; +-------+------------------------------+------+-----+----------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+------------------------------+------+-----+----------+-------+ | name | char(64) binary | | PRI | | | | ret | tinyint(1) | | | 0 | | | dl | char(128) | | | | | | type | enum('function','aggregate') | | | function | | +-------+------------------------------+------+-----+----------+-------+ 4 rows in set (0.01 sec) Table 14.2 describes the various privileges.
| Column | Description |
|---|---|
| Host | The host machine from which the user connects. |
| User | The username supplied for the connection (the -u option). |
| Password | The password the user connects as (the -p option). |
| Db | The database on which the user is trying to perform the operation. |
| Select_priv | Permission to return data from a table (a SELECT statement). SELECT results thatcan be calculated without needing to access a table still return a result even if the user does not have SELECT privileges. |
| Insert_priv | Permission to add new records to the table (an INSERT statement). |
| Update_priv | Permission to modify data in a table (an UPDATE statement). |
| Delete_priv | Permission to remove records from a table (a DELETE statement). |
| Create_priv | Permission to create databases and tables. |
| Drop_priv | Permission to drop databases or tables. |
| Reload_priv | Permission to reload the database (a FLUSH statement or a reload, refresh, or flush issued from mysqladmin). |
| Shutdown_priv | Permission to shut down the server. |
| Process_priv | Permission to view the current MySQL processes or kill MySQL processes (for SHOW PROCESSLIST or KILL SQL statements). |
| File_priv | Permission to read and write files on the server (for LOAD DATA INFILE or SELECT INTO OUTFILE statements). Any files that the MySQL user can read are readable. |
| Grant_priv | Permission to grant privileges available to the user to other users. |
| References_priv | Not currently used by MySQL. |
| Index_priv | Permission to create, modify, or drop indexes. |
| Alter_priv | Permission to change the structure of a table (an ALTER statement). |
| Show_db_priv | Permission to see all databases. |
| Super_priv | Permission to connect, even if the maximum number of connections is reached, and perform the CHANGE MASTER, KILL thread, mysqladmin debug, PURGE MASTER LOGS, and SET GLOBAL commands. |
| Create_tmp_table_priv | Permission to create a temporary table (CREATE TEMPORARY TABLE). |
| Lock_tables_priv | Permission to lock a table for which the user has SELECT permission. |
| Execute_priv | Permission to run stored procedures (scheduled for MySQL 5). |
| Repl_client_priv | Permission to ask about replication slaves and masters. |
| Repl_slave_priv | Permission to replicate (see Chapter 12, "Database Replication"). |
| ssl_type | Permission to connect is only granted if Secure Sockets Layer (SSL) is used. |
| ssl_cipher | Permission to connect is only granted if a specific cipher is present. |
| x509_issuer | Permission to connect is only granted if the certificate is issued by a specific issuer. |
| x509_subject | Permission to connect is only granted if the certificate contains a specific subject. |
| max_questions | Maximum number of queries the user can perform per hour. |
| max_updates | Maximum number of updates the user can perform per hour. |
| max_connections | Maximum number of times the user can connect per hour. |
How MySQL Examines Permissions to Allow Access
When a user tries to connect, MySQL examines the user table first to confirm that the particular user, host, and password combination is listed. If not, the user is denied access. When a user is trying to connect directly to a database, the db table will also be examined if the user gets through the other checks. If the user does not have permission to connect to that database, access is denied.
When a connected user tries to perform an administrative operation (for example, mysqladmin shutdown), MySQL examines the column related to the operation from the user table. If permission for the required operation is granted, the operation goes ahead. If not, the operation fails.
When a connected user tries to perform a database-related operation (SELECT, UPDATE, and so on), MySQL examines the related field from the user table. If permission for the required operation (for the SELECT, UPDATE, and so on) is granted, the operation is permitted. If not, MySQL goes to the next step.
The db table is examined next. MySQL looks for the database on which the user is performing the operation. If this does not exist, permission is denied, and the operation fails. If the database does exist, and the host and user match, the field relating to the operation is examined. If permission is granted for the required operation, the operation succeeds. If permission is not granted, MySQL proceeds to the next step. If the database and user combination does exist, and the host field is blank, MySQL examines the host table to see whether the host can perform the required operation. If the host and database are found in the host table, the related field on both the host and db tables determines whether the operation succeeds. If permission is granted on both tables, the operation succeeds. If not, MySQL proceeds to the next step.
MySQL examines the tables_priv table taking into account the table(s) on which the operation is being performed. If the host, user, db, and table combination do not exist, the operation fails. If they do exist, the related field is examined. If permission is not granted, MySQL proceeds to the next step. If permission is granted, the operation succeeds.
Finally, MySQL examines the columns_priv tables, taking into account the table columns being used in the operation. If permission related to the required operation is granted here, the operation succeeds. If not, if fails.
The order of precedence for the MySQL permission tables is shown in Figure 14.1.
Figure 14.1: Precedence for MySQL permission tables
How to Populate the Permission Tables
The permission tables are populated with some default values:
mysql> SELECT * FROM user; +---------------------------+------------------+------------------+--- ---------+-------------+-------------+-------------+-------------+----- ------+-------------+---------------+--------------+-----------+------- -----+-----------------+------------+------------+--------------+------ ------+-----------------------+------------------+--------------+------ -----------+------------------+----------+------------+-------------+-- ------------+---------------+-------------+-----------------+ | Host | User | Password | Select_priv | Insert_priv | Update_priv | Delete_priv | Create_priv | Drop_priv | Reload_priv | Shutdown_priv | Process_priv | File_priv | Grant_priv | References_priv | Index_priv | Alter_priv | Show_db_priv | Super_priv | Create_tmp_table_priv | Lock_tables_priv | Execute_priv | Repl_slave_priv | Repl_client_priv | ssl_type | ssl_cipher | x509_issuer | x509_subject | max_questions | max_updates | max_connections | +---------------------------+------------------+------------------+---- ---------+-------------+-------------+-------------+-------------+----- ------+-------------+---------------+--------------+-----------+------- -----+-----------------+------------+------------+--------------+------ ------+-----------------------+------------------+--------------+------ -----------+------------------+----------+------------+-------------+-- ------------+---------------+-------------+-----------------+ | localhost | root | | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | | | | | 0 | 0 | 0 | | test.testhost.co.za | root | | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | | | | | 0 | 0 | 0 | | localhost | | | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | | | | | 0 | 0 | 0 | | test.testhost.co.za | | | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | | | | | 0 | 0 | 0 | +---------------------------+------------------+------------------+---- ---------+-------------+-------------+-------------+-------------+----- ------+-------------+---------------+--------------+-----------+------- -----+-----------------+------------+------------+--------------+------ ------+-----------------------+------------------+--------------+------ -----------+------------------+----------+------------+-------------+-- ------------+---------------+-------------+-----------------+ 4 rows in set (0.05 sec) mysql> SELECT * FROM db; +------+---------+------+-------------+-------------+-------------+---- ---------+-------------+-----------+------------+-----------------+---- --------+------------+ | Host | Db | User | Select_priv | Insert_priv | Update_priv | Delete_priv | Create_priv | Drop_priv | Grant_priv | References_priv | Index_priv | Alter_priv | +------+---------+------+-------------+-------------+-------------+---- ---------+-------------+-----------+------------+-----------------+---- --------+------------+ | % | test | | Y | Y | Y | Y | Y | Y | N | Y | Y | Y | | % | test\_% | | Y | Y | Y | Y | Y | Y | N | Y | Y | Y | +------+---------+------+-------------+-------------+-------------+---- ---------+-------------+-----------+------------+-----------------+---- --------+------------+ 2 rows in set (0.01 sec) mysql> SELECT * FROM host; Empty set (0.00 sec) mysql> SELECT * FROM tables_priv; Empty set (0.00 sec) mysql> SELECT * FROM columns_priv; Empty set (0.00 sec)
Notice that the default settings are not secure. Anyone can connect from the local host as the root user and have total authority. An anonymous user (where no username is supplied) can connect from the local host to the default test database and to any database where the name begins with test.
| Note | In Unix, MySQL uses the Unix login username if no username is supplied. This means that someone logged in as root can simply enter MySQL without specifying a username, and they will have full permissions. |
One of the first tasks to perform in a new installation is to set new permissions and at least a new password for the root user.
Manipulating the Permission Tables Directly
There are two ways to set permissions—by using the MySQL GRANT and REVOKE statements or by directly changing the values in the tables. The easiest and most convenient way is with the GRANT and REVOKE statements, but it's important to understand how the tables affect permissions. For now, you're going to look at changing permissions by changing the values in the tables with the basic INSERT, UPDATE, and DELETE SQL statements. You'll see the other method later in the section titled "Using GRANT and REVOKE to Manipulate the Permission Tables." To add a password for the root user, you would write the following:
mysql> UPDATE user SET password=PASSWORD('g00r002b') WHERE user='root'; Query OK, 2 rows affected (0.00 sec) Rows matched: 2 Changed: 2 Warnings: 0 Notice the use of the PASSWORD() function. You must use this function when updating the tables directly. It encrypts the password so that it cannot be read simply by viewing the contents of the tables. For example:
mysql> SELECT host,user,password FROM user; +---------------------+------+------------------+ | host | user | password | +---------------------+------+------------------+ | localhost | root | 43b591f759a842a9 | | test.testhost.co.za | root | 43b591f759a842a9 | | localhost | | | | test.testhost.co.za | | | +---------------------+------+------------------+ 4 rows in set (0.00 sec)
| Warning | Be careful when directly changing permissions. Neglecting the WHERE clause would mean that all passwords change, and suddenly no existing users would be able to connect. |
Changes to the permissions do not take effect immediately when made directly to the MySQL tables. MySQL needs to reread the grant tables. You can force it to do this by issuing FLUSH PRIVILEGES, mysqladmin flush-privileges, or mysqladmin reload.
mysql> INSERT INTO user (Host,User,Password) VALUES ('localhost', 'administrator', PASSWORD('admin_pwd')); Query OK, 1 row affected (0.09 sec) Before the permissions are flushed, this data does not take effect. You can connect as the administrator user without any password:
% mysql -uadministrator; Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 6 to server version: 4.0.1-alpha-max-log Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
Before the database is reloaded, connecting as administrator is accepted, because, not finding the specific name, connecting is the same as for an anonymous user where no password is needed. You can see this by looking at the third and fourth records from the user table. After the flush, the administrator user can no longer connect without a password.
mysql> FLUSH PRIVILEGES; Query OK, 0 rows affected (0.00 sec) mysql> exit Bye % mysql -uadministrator; ERROR 1045: Access denied for user: 'administrator@localhost' (Using password: NO)
It's never good to use the root user for anything but administration. Day-to-day connections should be through users with permissions developed especially for the tasks that user performs. For this sales system, you're going to add two users—an administrator and a regular user. The administrator will have full permissions to do anything, and the regular user will have certain limitations. To add the administrator, you could simply add a record to the user table, giving a full set of permissions to the administrator. But that would mean the administrator of the sales rep system would have full access to any other database that gets developed on the system. It's almost always better to limit permissions at a user level and then activate permissions on a lower level. You're going to add a record to the user and to the database table to do this. I use an INSERT statement without specifying fields (for ease of typing) with the db table example, in case you're following these examples. Be sure that the fields match the fields in the tables from your distribution, in case they have changed:
mysql> INSERT INTO user (host,user,password) VALUES('localhost','administrator',PASSWORD('l3tm31n')); Query OK, 1 row affected (0.00 sec) mysql> INSERT INTO db VALUES('localhost','firstdb','administrator','y','y','y','y','n','n', 'n','n','n','n'); Query OK, 1 row affected (0.01 sec) The tables now contain the following:
mysql> SELECT * FROM user; +---------------------------+------------------+------------------+---- ---------+-------------+-------------+-------------+-------------+----- ------+-------------+---------------+--------------+-----------+------- -----+-----------------+------------+------------+--------------+------ ------+-----------------------+------------------+--------------+------ -----------+------------------+----------+------------+-------------+-- ------------+---------------+-------------+-----------------+ | Host | User | Password | Select_priv | Insert_priv | Update_priv | Delete_priv | Create_priv | Drop_priv | Reload_priv | Shutdown_priv | Process_priv | File_priv | Grant_priv | References_priv | Index_priv | Alter_priv | Show_db_priv | Super_priv | Create_tmp_table_priv | Lock_tables_priv | Execute_priv | Repl_slave_priv | Repl_client_priv | ssl_type | ssl_cipher | x509_issuer | x509_subject | max_questions | max_updates | max_connections | +---------------------------+------------------+------------------+---- ---------+-------------+-------------+-------------+-------------+----- ------+-------------+---------------+--------------+-----------+------- -----+-----------------+------------+------------+--------------+------ ------+-----------------------+------------------+--------------+------ -----------+------------------+----------+------------+-------------+-- ------------+---------------+-------------+-----------------+ | localhost | root | | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | | | | | 0 | 0 | 0 | | test.testhost.co.za | root | | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | | | | | 0 | 0 | 0 | | localhost | | | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | | | | | 0 | 0 | 0 | | test.testhost.co.za | | | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | | | | | 0 | 0 | 0 | | localhost | administrator | 26981a09472b4835 | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | | | | | 0 | 0 | 0 | +---------------------------+------------------+------------------+---- ---------+-------------+-------------+-------------+-------------+----- ------+-------------+---------------+--------------+-----------+------- -----+-----------------+------------+------------+--------------+------ ------+-----------------------+------------------+--------------+------ -----------+------------------+----------+------------+-------------+-- ------------+---------------+-------------+-----------------+ 5 rows in set (0.05 sec) mysql> SELECT * FROM db;; +-----------+---------+---------------+-------------+-------------+---- ---------+-------------+-------------+-----------+------------+-------- ---------+------------+------------+ | Host | Db | User | Select_priv | Insert_priv | Update_priv | Delete_priv | Create_priv | Drop_priv | Grant_priv | References_priv | Index_priv | Alter_priv | +-----------+---------+---------------+-------------+-------------+---- ---------+-------------+-------------+-----------+------------+-------- ---------+------------+------------+ | % | test | | Y | Y | Y | Y | Y | Y | N | Y | Y | Y | | % | test\_% | | Y | Y | Y | Y | Y | Y | N | Y | Y | Y | | localhost | firstdb | administrator | Y | Y | Y | Y | N | N | N | N | N | N | +-----------+---------+---------------+-------------+-------------+---- ---------+-------------+-------------+-----------+------------+-------- ---------+------------+------------+ 3 rows in set (0.01 sec)
The administrator can connect to the database with the password but only perform data manipulation on the firstdb database.
Remember to flush the tables before these permissions take effect:
% mysqladmin reload -u root -p Enter password: % mysql mysql; ERROR 1045: Access denied for user: 'root@localhost' (Using password: NO)
If you were not logged in as root, you'd get an error indicating that the anonymous user does not have permission:
% mysql mysql; ERROR 1044: Access denied for user: '@localhost' to database 'mysql'
Using GRANT and REVOKE to Manipulate the Permission Tables
Rather than updating the tables directly and having to flush the database, an easier way is to use the GRANT and REVOKE statements to manage permissions. The basic GRANT syntax is as follows:
GRANT privilege ON table_or_database_name TO user_name@hostname IDENTIFIED BY 'password'
To add a regular user for this sales system, you could do the following:
mysql> GRANT SELECT ON sales.* TO regular_user@localhost IDENTIFIED BY 'l3tm37n_2'; Query OK, 0 rows affected (0.00 sec) mysql> SELECT * FROM user; +---------------------------+------------------+------------------+---- ---------+-------------+-------------+-------------+-------------+----- ------+-------------+---------------+--------------+-----------+------- -----+-----------------+------------+------------+--------------+------ ------+-----------------------+------------------+--------------+------ -----------+------------------+----------+------------+-------------+-- ------------+---------------+-------------+-----------------+ | Host | User | Password | Select_priv | Insert_priv | Update_priv | Delete_priv | Create_priv | Drop_priv | Reload_priv | Shutdown_priv | Process_priv | File_priv | Grant_priv | References_priv | Index_priv | Alter_priv | Show_db_priv | Super_priv | Create_tmp_table_priv | Lock_tables_priv | Execute_priv | Repl_slave_priv | Repl_client_priv | ssl_type | ssl_cipher | x509_issuer | x509_subject | max_questions | max_updates | max_connections | +---------------------------+------------------+------------------+---- ---------+-------------+-------------+-------------+-------------+----- ------+-------------+---------------+--------------+-----------+------- -----+-----------------+------------+------------+--------------+------ ------+-----------------------+------------------+--------------+------ -----------+------------------+----------+------------+-------------+-- ------------+---------------+-------------+-----------------+ | localhost | root | | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | | | | | 0 | 0 | 0 | | test.testhost.co.za | root | | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | | | | | 0 | 0 | 0 | | localhost | | | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | | | | | 0 | 0 | 0 | | test.testhost.co.za | | | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | | | | | 0 | 0 | 0 | | localhost | administrator | 26981a09472b4835 | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | | | | | 0 | 0 | 0 | | localhost | regular_user | 1bfcf83b2eb5e59 | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | | | | | 0 | 0 | 0 | +---------------------------+------------------+------------------+---- ---------+-------------+-------------+-------------+-------------+----- ------+-------------+---------------+--------------+-----------+------- -----+-----------------+------------+------------+--------------+------ ------+-----------------------+------------------+--------------+------ -----------+------------------+----------+------------+-------------+-- ------------+---------------+-------------+-----------------+ 6 rows in set (0.05 sec) mysql> SELECT * FROM db; +-----------+---------+---------------+-------------+-------------+---- ---------+-------------+-------------+-----------+------------+-------- ---------+------------+------------+ | Host | Db | User | Select_priv | Insert_priv | Update_priv | Delete_priv | Create_priv | Drop_priv | Grant_priv | References_priv | Index_priv | Alter_priv | +-----------+---------+---------------+-------------+-------------+---- ---------+-------------+-------------+-----------+------------+-------- ---------+------------+------------+ | % | test | | Y | Y | Y | Y | Y | Y | N | Y | Y | Y | | % | test\_% | | Y | Y | Y | Y | Y | Y | N | Y | Y | Y | | localhost | firstdb | administrator | Y | Y | Y | Y | N | N | N | N | N | N | | localhost | sales | regular_user | Y | N | N | N | N | N | N | N | N | N | +-----------+---------+---------------+-------------+-------------+---- ---------+-------------+-------------+-----------+------------+-------- ---------+------------+------------+ 4 rows in set (0.01 sec)
The password is automatically encrypted when issued with GRANT, so there is no need to use the PASSWORD() function to encrypt it. In fact, if you do, you'll re-encrypt the password! You can change the password by reissuing the same permissions with a new password.
You can also revoke permissions in the same way as you grant them:
mysql> REVOKE SELECT ON sales.* FROM regular_user@localhost; Query OK, 0 rows affected (0.01 sec) mysql> SELECT * FROM user; +---------------------------+------------------+------------------+---- ---------+-------------+-------------+-------------+-------------+----- ------+-------------+---------------+--------------+-----------+------- -----+-----------------+------------+------------+--------------+------ ------+-----------------------+------------------+--------------+------ -----------+------------------+----------+------------+-------------+-- ------------+---------------+-------------+-----------------+ | Host | User | Password | Select_priv | Insert_priv | Update_priv | Delete_priv | Create_priv | Drop_priv | Reload_priv | Shutdown_priv | Process_priv | File_priv | Grant_priv | References_priv | Index_priv | Alter_priv | Show_db_priv | Super_priv | Create_tmp_table_priv | Lock_tables_priv | Execute_priv | Repl_slave_priv | Repl_client_priv | ssl_type | ssl_cipher | x509_issuer | x509_subject | max_questions | max_updates | max_connections | +---------------------------+------------------+------------------+---- ---------+-------------+-------------+-------------+-------------+----- ------+-------------+---------------+--------------+-----------+------- -----+-----------------+------------+------------+--------------+------ ------+-----------------------+------------------+--------------+------ -----------+------------------+----------+------------+-------------+-- ------------+---------------+-------------+-----------------+ | localhost | root | | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | | | | | 0 | 0 | 0 | | test.testhost.co.za | root | | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | | | | | 0 | 0 | 0 | | localhost | | | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | | | | | 0 | 0 | 0 | | test.testhost.co.za | | | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | | | | | 0 | 0 | 0 | | localhost | administrator | 26981a09472b4835 | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | | | | | 0 | 0 | 0 | | localhost | regular_user | 1bfcf83b2eb5e59 | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | | | | | 0 | 0 | 0 | +---------------------------+------------------+------------------+---- ---------+-------------+-------------+-------------+-------------+----- ------+-------------+---------------+--------------+-----------+------- -----+-----------------+------------+------------+--------------+------ ------+-----------------------+------------------+--------------+------ -----------+------------------+----------+------------+-------------+-- ------------+---------------+-------------+-----------------+ 6 rows in set (0.05 sec) mysql> SELECT * FROM db; +-----------+---------+---------------+-------------+-------------+---- ---------+-------------+-------------+-----------+------------+-------- ---------+------------+------------+ | Host | Db | User | Select_priv | Insert_priv | Update_priv | Delete_priv | Create_priv | Drop_priv | Grant_priv | References_priv | Index_priv | Alter_priv | +-----------+---------+---------------+-------------+-------------+---- ---------+-------------+-------------+-----------+------------+-------- ---------+------------+------------+ | % | test | | Y | Y | Y | Y | Y | Y | N | Y | Y | Y | | % | test\_% | | Y | Y | Y | Y | Y | Y | N | Y | Y | Y | | localhost | firstdb | administrator | Y | Y | Y | Y | N | N | N | N | N | N | +-----------+---------+---------------+-------------+-------------+---- ---------+-------------+-------------+-----------+------------+-------- ---------+------------+------------+ 3 rows in set (0.00 sec)
Notice that all trace of the user has been removed from the db table but that the user still exists in the user table. There is no way to remove this from the table without directly deleting it. A user with no permissions (called USAGE permission) can still connect to the server and access some information, such as, for early versions of MySQL 4, viewing the existing databases! For example:
mysql> exit Bye % mysql -uregular_user -pl3tm37n_2 Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 21 to server version: 4.0.1-alpha -max-log Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql> SHOW DATABASES; +----------+ | Database | +----------+ | firstdb | | mysql | | test | +----------+ 3 rows in set (0.00 sec)
To remove all traces of the user, delete them from the user table directly (while connected as root):
mysql> exit Bye % mysql mysql -uroot -pg00r002b Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 22 to server version: 4.0.1-alpha -max-log Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql> DELETE FROM user WHERE user='regular_user'; Query OK, 1 row affected (0.00 sec) mysql> SELECT * FROM user; +---------------------------+------------------+------------------+---- ---------+-------------+-------------+-------------+-------------+----- ------+-------------+---------------+--------------+-----------+------- -----+-----------------+------------+------------+--------------+------ ------+-----------------------+------------------+--------------+------ -----------+------------------+----------+------------+-------------+-- ------------+---------------+-------------+-----------------+ | Host | User | Password | Select_priv | Insert_priv | Update_priv | Delete_priv | Create_priv | Drop_priv | Reload_priv | Shutdown_priv | Process_priv | File_priv | Grant_priv | References_priv | Index_priv | Alter_priv | Show_db_priv | Super_priv | Create_tmp_table_priv | Lock_tables_priv | Execute_priv | Repl_slave_priv | Repl_client_priv | ssl_type | ssl_cipher | x509_issuer | x509_subject | max_questions | max_updates | max_connections | +---------------------------+------------------+------------------+---- ---------+-------------+-------------+-------------+-------------+----- ------+-------------+---------------+--------------+-----------+------- -----+-----------------+------------+------------+--------------+------ ------+-----------------------+------------------+--------------+------ -----------+------------------+----------+------------+-------------+-- ------------+---------------+-------------+-----------------+ | localhost | root | | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | | | | | 0 | 0 | 0 | | test.testhost.co.za | root | | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | | | | | 0 | 0 | 0 | | localhost | | | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | | | | | 0 | 0 | 0 | | test.testhost.co.za | | | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | | | | | 0 | 0 | 0 | | localhost | administrator | 26981a09472b4835 | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | | | | | 0 | 0 | 0 | +---------------------------+------------------+------------------+---- ---------+-------------+-------------+-------------+-------------+----- ------+-------------+---------------+--------------+-----------+------- -----+-----------------+------------+------------+--------------+------ ------+-----------------------+------------------+--------------+------ -----------+------------------+----------+------------+-------------+-- ------------+---------------+-------------+-----------------+ 5 rows in set (0.05 sec)
Table 14.3 describes all the privileges available.
| Privilege | Description |
|---|---|
| ALL | Grants all the basic permissions. |
| ALL PRIVILEGES | Same as ALL. |
| ALTER | Permission to change the structure of a table (an ALTER statement), excluding indexes. |
| CREATE | Permission to create databases or tables, excluding indexes. |
| CREATE TEMPORARY TABLES | Permission to create a temporary table (CREATE TEMPORARY TABLE statement). |
| DELETE | Permission to remove records from a table (a DELETE statement). |
| DROP | Permission to drop databases or tables, excluding indexes. |
| EXECUTE | Permission to run stored procedures (scheduled for MySQL 5). |
| FILE | Permission to read and write files on the server (for LOAD DATA INFILE or SELECT INTO OUTFILE statements). Any files that the MySQL user can read are readable. |
| GRANT | Permission to grant permissions owned by the user to another user. |
| INDEX | Permission to create, modify, or drop indexes. |
| INSERT | Permission to add new records to the table (an INSERT statement). |
| LOCK TABLES | Permission to lock a table for which the user has SELECT permission. |
| PROCESS | Permission to view the current MySQL processes or kill MySQL processes (for SHOW PROCESSLIST or KILL SQL statements). |
| REFERENCES | Not currently used by MySQL. |
| RELOAD | Permission to reload the database (a FLUSH statement or a reload, refresh, or flush issued from mysqladmin). |
| REPLICATION CLIENT | Permission to ask about the replication slaves and masters. |
| REPLICATION SLAVE | Permission to replicate from the server (slaves need this to replicate). |
| SHOW DATABASES | Permission to see all databases. |
| SELECT | Permission to return data from a table (a SELECT statement). |
| SHUTDOWN | Permission to shut down the server. |
| SUPER | Permission to connect, even if the maximum number of connections is reached, and perform the CHANGE MASTER, KILL thread, mysqladmin debug, PURGE MASTER LOGS, and SET GLOBAL commands. |
| UPDATE | Permission to modify data in a table (an UPDATE statement). |
| USAGE | Permission to connect to the server and perform statements available to all (for early versions of MySQL 4 this included SHOW DATABASES). |
The earlier example granted permissions for all tables in the sales database. You can easily manipulate this by changing the database and table names you grant on (see Table 14.4).
| Name | Description |
|---|---|
| *.* | All tables in all databases |
| * | All tables in the current database |
| databasename.* | All tables in the database databasename |
| databasename.tablename | The table tablename in the database databasename |
For example:
mysql> GRANT SELECT ON *.* TO regular_user@localhost IDENTIFIED BY 'l3tm37n_2'; Query OK, 0 rows affected (0.00 sec)
Because permission is granted on all databases, there is no need for an entry in the database table; just the user table, with the field select_priv set to Y:
mysql> SELECT * FROM user; +---------------------+---------------+------------------+------------- +-------------+-------------+-------------+-------------+-----------+-- -----------+---------------+--------------+-----------+------------+--- --------------+------------+------------+----------+------------+------ -------+--------------+ | Host | User | Password | Select_priv | Insert_priv | Update_priv | Delete_priv | Create_priv | Drop_priv | Reload_priv | Shutdown_priv | Process_priv | File_priv | Grant_priv | References_priv | Index_priv | Alter_priv | ssl_type | ssl_cipher | x509_issuer | x509_subject | +---------------------+---------------+------------------+------------- +-------------+-------------+-------------+-------------+-----------+-- -----------+---------------+--------------+-----------+------------+--- --------------+------------+------------+----------+------------+------ -------+--------------+ | localhost | root | 43b591f759a842a9 | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | NONE | | | | | test.testhost.co.za | root | 43b591f759a842a9 | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | NONE | | | | | localhost | | | N | N | N | N | N | N | N | N | N | N | N | N | N | N | NONE | | | | | test.testhost.co.za | | | N | N | N | N | N | N | N | N | N | N | N | N | N | N | NONE | | | | | localhost | administrator | 74126e0c6742d7e9 | N | N | N | N | N | N | N | N | N | N | N | N | N | N | NONE | | | | | localhost | regular_user | 1bfcf83b2eb5e591 | Y | N | N | N | N | N | N | N | N | N | N | N | N | N | NONE | | | | +---------------------+---------------+------------------+------------- +-------------+-------------+-------------+-------------+-----------+-- -----------+---------------+--------------+-----------+------------+--- --------------+------------+------------+----------+------------+------ -------+--------------+ 6 rows in set (0.00 sec)
Using SET to Set User Passwords
Another way to change passwords is with the SET PASSWORD statement. Any nonanonymous user can change their own password in this way. (This is another reason to assign users carefully. It's not unheard of for a user to change their password, denying others access, because they do not realize they share this username!)
You can set a password for the user you're connected as follows:
mysql> SET PASSWORD=PASSWORD('g00r002b2'); Query OK, 0 rows affected (0.00 sec) A user with access to the user table in the mysql database can set passwords for other users too, by specifying the user:
mysql> SET PASSWORD FOR root=PASSWORD('g00r002b'); Query OK, 0 rows affected (0.00 sec) Remember to use the PASSWORD() function to encrypt the password. If you don't, the password is stored in the user table as plain text, but because on connection the given password is automatically encrypted before being compared to the password in the user table, you will not be able to connect (if you try this, you'll need to refer to the section titled "What to Do If You Can't Connect..." afterward to continue):
mysql> SET PASSWORD FOR root='g00r002b'; Query OK, 0 rows affected (0.00 sec)
Now, after exiting you won't be able to reconnect as root:
% mysql -uroot -pg00r002b2 ERROR 1045: Access denied for user: 'root@localhost' (Using password: YES)
Using mysqladmin to Set User Passwords
When using mysqladmin, as with the GRANT statement, you should not use the PASSWORD() function:
% mysqladmin -uroot -pg00r002b password g00r002b
Wildcard Permissions
There's no need to enter 1,001 hosts if that's how many hosts to which you need to grant access. MySQL accepts wildcards in the host table. For example, the following allows a user to connect from a host ending with marsorbust.co.za:
mysql> GRANT SELECT ON sales.* TO regular_user@"%. marsorbust.co.za" IDENTIFIED BY 'l3tm37n'; Query OK, 0 rows affected (0.03 sec) mysql> SELECT * FROM user WHERE host LIKE '%mars%'; +---------------------------+------------------+------------------+---- ---------+-------------+-------------+-------------+-------------+----- ------+-------------+---------------+--------------+-----------+------- -----+-----------------+------------+------------+--------------+------ ------+-----------------------+------------------+--------------+------ -----------+------------------+----------+------------+-------------+-- ------------+---------------+-------------+-----------------+ | Host | User | Password | Select_priv | Insert_priv | Update_priv | Delete_priv | Create_priv | Drop_priv | Reload_priv | Shutdown_priv | Process_priv | File_priv | Grant_priv | References_priv | Index_priv | Alter_priv | Show_db_priv | Super_priv | Create_tmp_table_priv | Lock_tables_priv | Execute_priv | Repl_slave_priv | Repl_client_priv | ssl_type | ssl_cipher | x509_issuer | x509_subject | max_questions | max_updates | max_connections | +---------------------------+------------------+------------------+---- ---------+-------------+-------------+-------------+-------------+----- ------+-------------+---------------+--------------+-----------+------- -----+-----------------+------------+------------+--------------+------ ------+-----------------------+------------------+--------------+------ -----------+------------------+----------+------------+-------------+-- ------------+---------------+-------------+-----------------+ | %.marsorbust.co.za | regular_user | 1bfcf83b2eb5e591 | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | | | | | 0 | 0 | 0 | +---------------------------+------------------+------------------+---- ---------+-------------+-------------+-------------+-------------+----- ------+-------------+---------------+--------------+-----------+------- -----+-----------------+------------+------------+--------------+------ ------+-----------------------+------------------+--------------+------ -----------+------------------+----------+------------+-------------+-- ------------+---------------+-------------+-----------------+ 1 row in set (0.05 sec)
The quotes in the GRANT statement allow wildcards, or any special characters, to be used. You can of course also insert wildcards into the MySQL tables directly.
What to Do If You Can't Connect or Have No Permissions
It's not impossible. Maybe you revoke yourself into oblivion, use DELETE where you shouldn't have, or even damage the files holding the permission tables, and now you can't connect at all, even as root. Fear not; there is a solution!
First, stop MySQL completely. As the root user on Unix, if you run MySQL out of /init.d, you may be able to run the following:
% /etc/rc.d/init.d/mysql stop Killing mysqld with pid 5091 Wait for mysqld to exit\c .\c .\c .\c .\c 020612 01:14:41 mysqld ended done
If not, still as root, you'll need to kill the specific MySQL-related processes:
% ps -ax |grep mysql 5195 pts/0 S 0:00 sh /usr/local/mysql/bin/mysqld_safe --datadir=/usr/lo 5230 pts/0 S 0:00 /usr/local/mysql-max-4.0.1-alpha-pc-linux-gnu-i686/bi 5232 pts/0 S 0:00 /usr/local/mysql-max-4.0.1-alpha-pc-linux-gnu-i686/bi 5233 pts/0 S 0:00 /usr/local/mysql-max-4.0.1-alpha-pc-linux-gnu-i686/bi 5234 pts/0 S 0:00 /usr/local/mysql-max-4.0.1-alpha-pc-linux-gnu-i686/bi 5235 pts/0 S 0:00 /usr/local/mysql-max-4.0.1-alpha-pc-linux-gnu-i686/bi 5236 pts/0 S 0:00 /usr/local/mysql-max-4.0.1-alpha-pc-linux-gnu-i686/bi 5237 pts/0 S 0:00 /usr/local/mysql-max-4.0.1-alpha-pc-linux-gnu-i686/bi 5238 pts/0 S 0:00 /usr/local/mysql-max-4.0.1-alpha-pc-linux-gnu-i686/bi 5239 pts/0 S 0:00 /usr/local/mysql-max-4.0.1-alpha-pc-linux-gnu-i686/bi 5240 pts/0 S 0:00 /usr/local/mysql-max-4.0.1-alpha-pc-linux-gnu-i686/bi 5241 pts/0 S 0:00 /usr/local/mysql-max-4.0.1-alpha-pc-linux-gnu-i686/bi % kill 5195 5230 5232 5233 5234 5235 5236 5237 5238 5239 5240 5241 mysqld ended
In the eventuality that this doesn't work, you may have to use kill -9 (followed by the process ID) to really kill the process.
On Windows, you can simply use the task manager to close MySQL.
Then, restart MySQL without the grant tables (this ignores any permission restrictions):
% mysqld_safe --skip-grant-tables
And now you should be able to add a root password, either through directly manipulating the tables directly, with GRANT, or with mysqladmin:
% mysqladmin -u root password 'g00r002b'
Don't forget to stop the server, and restart it without --skip-grant-tables, to activate your root password.
What to Do If the User Table Becomes Corrupted
Sometimes it can happen that the user table has become corrupted, so you still cannot change the password with mysqladmin. This happened to me once after a crash and could conceivably happen if someone tampers with the files directly. If you want to follow this example, you can simulate the loss of the user table by renaming it, and then flushing the tables (the original will probably still be cached otherwise):
% mv user.MYD user_bak.olddata % mysql -uroot -pg00r002b; Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 6 to server version: 4.0.1-alpha- max-log Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql>FLUSH TABLES; mysql> SELECT * FROM user; ERROR 1016: Can't open file: 'user.MYD'. (errno: 145)
If this is the case, you should still start MySQL without the grant tables and then try dropping the table:
mysql> DROP TABLE user; Query OK, 0 rows affected (0.01 sec) CREATE TABLE user ( Host varchar(60) binary NOT NULL default '', User varchar(16) binary NOT NULL default '', Password varchar(16) binary NOT NULL default '', Select_priv enum('N','Y') NOT NULL default 'N', Insert_priv enum('N','Y') NOT NULL default 'N', Update_priv enum('N','Y') NOT NULL default 'N', Delete_priv enum('N','Y') NOT NULL default 'N', Create_priv enum('N','Y') NOT NULL default 'N', Drop_priv enum('N','Y') NOT NULL default 'N', Reload_priv enum('N','Y') NOT NULL default 'N', Shutdown_priv enum('N','Y') NOT NULL default 'N', Process_priv enum('N','Y') NOT NULL default 'N', File_priv enum('N','Y') NOT NULL default 'N', Grant_priv enum('N','Y') NOT NULL default 'N', References_priv enum('N','Y') NOT NULL default 'N', Index_priv enum('N','Y') NOT NULL default 'N', Alter_priv enum('N','Y') NOT NULL default 'N', Show_db_priv enum('N','Y') NOT NULL default 'N', Super_priv enum('N','Y') NOT NULL default 'N', Create_tmp_table_priv enum('N','Y') NOT NULL default 'N', Lock_tables_priv enum('N','Y') NOT NULL default 'N', Execute_priv enum('N','Y') NOT NULL default 'N', Repl_slave_priv enum('N','Y') NOT NULL default 'N', Repl_client_priv enum('N','Y') NOT NULL default 'N', ssl_type enum('','ANY','X509','SPECIFIED') NOT NULL default '', ssl_cipher blob NOT NULL, x509_issuer blob NOT NULL, x509_subject blob NOT NULL, max_questions int(11) unsigned NOT NULL default '0', max_updates int(11) unsigned NOT NULL default '0', max_connections int(11) unsigned NOT NULL default '0', PRIMARY KEY (Host,User) ) TYPE=MyISAM COMMENT='Users and global privileges'; Query OK, 0 rows affected (0.00 sec) This probably still won't give you permission, however:
mysql> GRANT SELECT ON sales.* TO regular_user@localhost IDENTIFIED BY 'l3tm37n'; ERROR 1047: Unknown command mysql> exit Bye [root@test mysql]# mysqladmin -uroot password 'g00r002b' mysqladmin: unable to change password; error: 'You must have privileges to update tables in the mysql database to be able to change passwords for others'
You'll need to insert some values into the table once more. Here you add the default values. Be sure they match the columns for the user table you created, if it is different:
[root@test mysql]# mysql mysql Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 5 to server version: 4.0.1-alpha -max-log Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql>INSERT INTO user VALUES ('localhost', 'root', '', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', '', '', '', '', 0, 0, 0); Query OK, 1 row affected (0.01 sec) mysql>INSERT INTO user VALUES ('%', 'root', '', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', '', '', '', '', 0, 0, 0); Query OK, 1 row affected (0.00 sec) mysql> INSERT INTO user VALUES ('localhost', '', '', 'N', 'N' , 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', '', '', '', '', 0, 0, 0); Query OK, 1 row affected (0.00 sec) mysql> INSERT INTO user VALUES ('localhost', '', '', 'N', 'N' , 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', '', '', '', '', 0, 0, 0); Query OK, 1 row affected (0.00 sec) You'll need to reload (or flush the privilege tables) in order to activate the permissions, and then once again you can start issuing commands:
mysql> exit Bye [root@test mysql]# mysqladmin reload [root@test mysql]# mysql Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 7 to server version: 4.0.1-alpha-max-log Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql> GRANT SELECT ON sales.* TO regular_user@localhost IDENTIFIED BY 'l3tm37n'; Query OK, 0 rows affected (0.00 sec)
Other Grant Options
By default, MySQL does not allow a user to pass their permissions onto someone else. And, being a control freak, I don't suggest you allow your users to do this. You've probably got a good reason for not granting them the permission in the first place, and you wouldn't want another user to override this. But, if you must—perhaps in a situation where there are multiple trusted users—there is a way. The WITH GRANT OPTION will allow a user to grant any permissions that they have to another user.
The following demonstrates this in action, using two databases: sales and customer. The administrator creates a regular_user2, with permission to perform SELECT queries on the sales database, and then grants the GRANT option to the first regular_user, who has permission to SELECT on the customer database, who will then in turn grant the same rights to regular_user2:
mysql> GRANT SELECT ON sales.* TO regular_user2@localhost IDENTIFIED BY 'l3tm37n'; Query OK, 0 rows affected (0.01 sec) mysql> GRANT SELECT ON customer.* TO regular_user@localhost IDENTIFIED BY 'l3tm37n' WITH GRANT OPTION; Query OK, 0 rows affected (0.00 sec) mysql> exit Bye % mysql -u regular_user2 -pl3tm37n Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 3 to server version: 4.0.1-alpha- max-log Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql> GRANT SELECT ON customer.* TO regular_user@localhost IDENTIFIED BY 'l3tm37n' WITH GRANT OPTION; ERROR 1044: Access denied for user: 'regular_user2@localhost' to database 'customer'
Regular_user2 cannot grant anything to another user:
mysql> exit Bye [root@test /root]# mysql -u regular_user -pl3tm37n Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 4 to server version: 4.0.1-alpha-max-log Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql> GRANT SELECT ON customer.* TO regular_user@localhost IDENTIFIED BY 'l3tm37n' WITH GRANT OPTION; Query OK, 0 rows affected (0.00 sec)
However, regular_user can grant permissions to regular_user2.
There are some other useful options that help avoid the possibility of one user hogging connections. These limit queries, updates, or connections to a certain number per hour. The three options are as follows:
MAX_QUERIES_PER_HOUR n MAX_UPDATES_PER_HOUR n MAX_CONNECTIONS_PER_HOUR n
Without these three options, the only limit on user activity is the max_user_connections variable. But this is global, and you cannot limit one kind of user from an activity.
Where Could User Limitation Be Useful?
Limiting database-intensive queries, such as searches that involve many joins of large tables, can be useful. The application could connect as a different user with this limit. The application is still a better place to do this limiting, but an hourly limit may be useful in some situations—for example, situations where there is potential for denial of service attacks, or situations where one user could make multiple connections and cause the performance of the database server to degrade.
Which Type of User Limitation to Use?
Connections usually have the lowest impact on the database, but there is still the possibility that
max_user_connections could be taken up by one user. Setting MAX_CONNECTIONS_PER_HOUR is the most cautious option.
Sometimes you may not be worried about the number of connections, but rather about a user performing multiple queries at the same time or performing extraneous queries. You could set
MAX_QUERIES_PER_HOUR to avoid users performing unnecessary queries and wasting resources or performing too many of a possible heavy query in a short space of time.
UPDATEs have a higher performance impact than SELECTs and a very marked impact where table locking is used (such as the default MyISAM table type). You may use MAX_ UPDATES_PER_HOUR to limit updates for performance reasons or where users need not make many updates.
| Tip | It pays to be paranoid. Users can abuse the database, intentionally or not. If you cannot see a use for them to have certain permissions, do not grant them. It's always easier to add permissions than to take away permissions once they've been granted. I've encountered large systems where the security consisted only of one user and one password. When this was compromised, it proved impossible to gracefully add limitations. |
As an example of user limitations, you can limit regular_user2 to two connections per hour:
mysql> GRANT SELECT ON sales.* TO regular_user2@localhost IDENTIFIED BY 'l3tm37n' WITH MAX_CONNECTIONS_PER_HOUR 2; Query OK, 0 rows affected (0.00 sec)
If regular_user2 exceeds the number of connections, they'll get an error such as follows:
ERROR 1226: User 'regular_user2' has exceeded the 'max_connections' resource (current value: 2)
Similarly, if MAX_QUERIES_PER_HOUR was assigned and exceeded, the error message would be as follows:
ERROR 1226: User 'regular_user2' has exceeded the 'max_questions' resource (current value: 4)
| Tip | Be reasonable with your limitations. If a user should only perform one query an hour, realize that they could enter an incorrect query and have to perform a second one. |
A Strategy for Managing Users Securely
The more complex your needs, the more complex your strategy will have to be. Simple websites can often suffice with two users: an administration user who can update data and a user for the website application who can only perform SELECTs from certain tables, for example. The general principle is to grant the user only the privileges they need and no more. If they need more at a later stage, it is easy to grant them additional ones. But taking them away is another matter!
MySQL users are mostly of three kinds: There are individual people (for example, Anique or Channette), applications (for example, a salary system or the news website), and roles (for example, updating the news or updating the salaries). These may overlap to various degrees—for example, Anique may update both salaries and news, using both applications and performing both roles. The DBA needs to decide whether to issue Anique and Channette their own passwords, issue passwords to the news and salary systems, or create a user based upon whether the news or salaries are being updated.
If you opt for users as individuals and issue Anique her own password, she only has to remember one login to the database. But then she needs to be given access to update both the salary and news databases. If she, or the application she is using, makes a mistake, there is the possibility of her damaging data on which she should not even be working. For example, if the salary and news databases both have a days_data table—with the news database version growing continually until it is archived and the salary data being manually removed after it has been processed—there is the possibility of her removing the news table when she meant to remove the salary table.
If you opt for users as applications, you solve some of these problems. However, it seems a user now has to remember two passwords. Also, you cannot track which user has made which changes to the database. You have a solution, however, because where security is necessary, it's likely that the individual will have to log into the application (potentially allowing you to track the changes an individual makes to the database), and the application then logs into the database. The user could have the same username and password to both applications, but they could never destroy news data when connected as the salary application (as you'd not have given the salary user permission to update the news database).
Applications, though, often have many roles, with many levels of user. Perhaps anyone may view their own salary details, but only an administrator can update them. Giving the application permission to update data potentially allows an ordinary user to update the data. Consider also the development process: A trusted senior developer builds the salary administration component of the application, and a team of junior developers builds the salary-viewing component. Issuing the same password to the application allows the junior developers to update the data when they don't need to and probably shouldn't be allowed to update it. In this case, you could issue usernames based upon a combination of role and application (salary administration, salary viewing, news administrator, news viewing).
Some principles to keep in mind include the following:
-
Never issue a user the root password. They should always be connecting with another username and password.
-
Always issue the minimum permissions you can. (But be reasonable! You'll always get some sadists who take great glee in granting permission on a query-by-query basis. For example, allowing the user to read the surname column, then forcing them to come begging for more permission when they need to read the first name shortly afterward.) The global permissions assigned in the user table should always be N, though, and then access to specific databases granted in the db table.
-
For critical data, it must be possible to trace changes made by individuals. In general, people interact with the database through an application. The burden for managing access on an individual level then usually falls on the application.
Avoiding Granting Dangerous Privileges
Although you should always issue the minimum privileges required, there are some privileges that are particularly dangerous, where the security risk may outweigh the convenience factor. Remember that you should never grant access on a global level.
The following privileges in particular could be security risks:
Any privileges on the mysql database A malicious user can still gain access even after being able to only view the encrypted passwords.
ALTER A malicious user could make changes to the privilege tables, such as renaming them, which renders them ineffective.
DROP If a user can DROP the mysql database, the permission limitations will no longer be in place.
FILE Users with the FILE privilege can potentially access any file that is readable by all. They can also create a file that has the MySQL user privileges.
GRANT This allows users to give their privileges to others who may not be as trustworthy as the original user.
PROCESS Queries that are running can be viewed in plain text, which includes any that change or set passwords.
SHUTDOWN It's unlikely a DBA will be fooled into granting this privilege easily, and it should go without saying that users with the SHUTDOWN privilege can shut down the server and deny access to everyone.
|
|
