MySQL - When Privilege Changes Take Effect

When mysqld starts, all grant table contents are read into memory and become effective at that point.

Modifications to the grant tables that you perform using GRANT, REVOKE, or SET PASSWORD are noticed by the server immediately.

If you modify the grant tables manually (using INSERT, UPDATE, etc.), you should execute a FLUSH PRIVILEGES statement or run mysqladmin flush-privileges or mysqladmin reload to tell the server to reload the grant tables. Otherwise your changes will have no effect until you restart the server. If you change the grant tables manually but forget to reload the privileges, you will be wondering why your changes don't seem to make any difference!

When the server notices that the grant tables have been changed, existing client connections are affected as follows:

Table and column privilege changes take effect with the client's next request.

Database privilege changes take effect at the next USE db_name command.

Global privilege changes and password changes take effect the next time the client connects.

Setting Up the Initial MySQL Privileges

After installing MySQL, you set up the initial access privileges by running scripts/mysql_install_db. The mysql_install_db script starts up the mysqld server, then initializes the grant tables to contain the following set of privileges:

The MySQL root user is created as a superuser who can do anything. Connections must be made from the local host. NOTE: The initial root password is empty, so anyone can connect as root without a password and be granted all privileges. An anonymous user is created that can do anything with databases that have a name of 'test' or starting with 'test_'. Connections must be made from the local host. This means any local user can connect without a password and be treated as the anonymous user.

Other privileges are denied. For example, normal users can't use mysqladmin shutdown or mysqladmin processlist.

NOTE: The default privileges are different for Windows.

Because your installation is initially wide open, one of the first things you should do is specify a password for the MySQL root user. You can do this as follows (note that you specify the password using the PASSWORD() function):

shell> mysql -u root mysql

mysql> UPDATE user SET Password=PASSWORD('new_password')

WHERE user='root';

mysql> FLUSH PRIVILEGES;

You can, in MySQL Version 3.22 and above, use the SET PASSWORD statement:

shell> mysql -u root mysql

mysql> SET PASSWORD FOR root=PASSWORD('new_password');

Another way to set the password is by using the mysqladmin command:

shell> mysqladmin -u root password new_password

Only users with write/update access to the mysql database can change the password for others users. All normal users (not anonymous ones) can only change their own password with either of the above commands or with SET PASSWORD=PASSWORD('new password').

Note that if you update the password in the user table directly using the first method, you must tell the server to re-read the grant tables (with FLUSH PRIVILEGES), because the change will go unnoticed otherwise.

Once the root password has been set, thereafter you must supply that password when you connect to the server as root.

You may wish to leave the root password blank so that you don't need to specify it while you perform additional setup or testing. However, be sure to set it before using your installation for any real production work.

See the scripts/mysql_install_db script to see how it sets up the default privileges. You can use this as a basis to see how to add other users.

If you want the initial privileges to be different than those just described above, you can modify mysql_install_db before you run it.

To re-create the grant tables completely, remove all the `.frm', `.MYI', and `.MYD' files in the directory containing the mysql database. (This is the directory named `mysql' under the database directory, which is listed when you run mysqld --help.) Then run the mysql_install_db script, possibly after editing it first to have the privileges you want.

NOTE: For MySQL versions older than Version 3.22.10, you should NOT delete the `.frm' files. If you accidentally do this, you should copy them back from your MySQL distribution before running mysql_install_db.

Adding New User Privileges to MySQL

ou can add users two different ways: by using GRANT statements or by manipulating the MySQL grant tables directly. The preferred method is to use GRANT statements, because they are more concise and less error-prone.

The examples below show how to use the mysql client to set up new users. These examples assume that privileges are set up according to the defaults described in the previous section. This means that to make changes, you must be on the same machine where mysqld is running, you must connect as the MySQL root user, and the root user must have the insert privilege for the mysql database and the reload administrative privilege. Also, if you have changed the root user password, you must specify it for the mysql commands below.

You can add new users by issuing GRANT statements:

shell> mysql --user=root mysql

mysqlgt; GRANT ALL PRIVILEGES ON *.* TO monty@localhost

IDENTIFIED BY 'some_pass' WITH GRANT OPTION;

mysqlgt; GRANT ALL PRIVILEGES ON *.* TO monty@"%"

IDENTIFIED BY 'some_pass' WITH GRANT OPTION;

mysqlgt; GRANT RELOAD,PROCESS ON *.* TO admin@localhost;

mysqlgt; GRANT USAGE ON *.* TO dummy@localhost;

These GRANT statements set up three new users:

monty

A full superuser who can connect to the server from anywhere, but who must use a password 'some_pass' to do so. Note that we must issue GRANT statements for both monty@localhost and monty@"%". If we don't add the entry with localhost, the anonymous user entry for localhost that is created by mysql_install_db will take precedence when we connect from the local host, because it has a more specific Host field value and thus comes earlier in the user table sort order.

admin

A user who can connect from localhost without a password and who is granted the reload and process administrative privileges. This allows the user to execute the mysqladmin reload, mysqladmin refresh, and mysqladmin flush-* commands, as well as mysqladmin process list . No database-related privileges are granted. (They can be granted later by issuing additional GRANT statements.)

dummy

A user who can connect without a password, but only from the local host. The global privileges are all set to 'N' -- the USAGE privilege type allows you to create a user with no privileges. It is assumed that you will grant database-specific privileges later.

You can also add the same user access information directly by issuing INSERT statements and then telling the server to reload the grant tables:

shellgt; mysql --user=root mysql

mysqlgt; INSERT INTO user VALUES('localhost','monty',PASSWORD('some_pass'),

'Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y')

mysqlgt; INSERT INTO user VALUES('%','monty',PASSWORD('some_pass'),

'Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y')

mysqlgt; INSERT INTO user SET Host='localhost',User='admin',

Reload_priv='Y', Process_priv='Y';

mysqlgt; INSERT INTO user (Host,User,Password)

VALUES('localhost','dummy','');

mysqlgt; FLUSH PRIVILEGES;

Depending on your MySQL version, you may have to use a different number of 'Y' values above (versions prior to Version 3.22.11 had fewer privilege columns). For the admin user, the more readable extended INSERT syntax that is available starting with Version 3.22.11 is used.

Note that to set up a superuser, you need only create a user table entry with the privilege fields set to 'Y'. No db or host table entries are necessary.

The privilege columns in the user table were not set explicitly in the last INSERT statement (for the dummy user), so those columns are assigned the default value of 'N'. This is the same thing that GRANT USAGE does.

The following example adds a user custom who can connect from hosts localhost, server.domain, and whitehouse.gov. He wants to access the bankaccount database only from localhost, the expenses database only from whitehouse.gov, and the customer database from all three hosts. He wants to use the password stupid from all three hosts.

To set up this user's privileges using GRANT statements, run these commands:

shellgt; mysql --user=root mysql

mysqlgt; GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP

ON bankaccount.*

TO custom@localhost

IDENTIFIED BY 'stupid';

mysqlgt; GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP

ON expenses.*

TO custom@whitehouse.gov

IDENTIFIED BY 'stupid';

mysqlgt; GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP

ON customer.*

TO custom@'%'

IDENTIFIED BY 'stupid';

To set up the user's privileges by modifying the grant tables directly, run these commands (note the FLUSH PRIVILEGES at the end):

shellgt; mysql --user=root mysql

mysqlgt; INSERT INTO user (Host,User,Password)

VALUES('localhost','custom',PASSWORD('stupid'));

mysqlgt; INSERT INTO user (Host,User,Password)

VALUES('server.domain','custom',PASSWORD('stupid'));

mysqlgt; INSERT INTO user (Host,User,Password)

VALUES('whitehouse.gov','custom',PASSWORD('stupid'));

mysqlgt; INSERT INTO db

(Host,Db,User,Select_priv,Insert_priv,Update_priv,Delete_priv,

Create_priv,Drop_priv)

VALUES

('localhost','bankaccount','custom','Y','Y','Y','Y','Y','Y');

mysqlgt; INSERT INTO db

(Host,Db,User,Select_priv,Insert_priv,Update_priv,Delete_priv,

Create_priv,Drop_priv)

VALUES

('whitehouse.gov','expenses','custom','Y','Y','Y','Y','Y','Y');

mysqlgt; INSERT INTO db

(Host,Db,User,Select_priv,Insert_priv,Update_priv,Delete_priv,

Create_priv,Drop_priv)

VALUES('%','customer','custom','Y','Y','Y','Y','Y','Y');

mysqlgt; FLUSH PRIVILEGES;

The first three INSERT statements add user table entries that allow user custom to connect from the various hosts with the given password, but grant no permissions to him (all privileges are set to the default value of 'N'). The next three INSERT statements add db table entries that grant privileges to custom for the bankaccount, expenses, and customer databases, but only when accessed from the proper hosts. As usual, when the grant tables are modified directly, the server must be told to reload them (with FLUSH PRIVILEGES) so that the privilege changes take effect.

If you want to give a specific user access from any machine in a given domain, you can issue a GRANT statement like the following:

mysql> GRANT ...

ON *.*

TO myusername@"%.mydomainname.com"

IDENTIFIED BY 'mypassword';

To do the same thing by modifying the grant tables directly, do this:

mysqlgt; INSERT INTO user VALUES ('%.mydomainname.com', 'myusername',

PASSWORD('mypassword'),...);

mysqlgt; FLUSH PRIVILEGES;

You can also use xmysqladmin, mysql_webadmin, and even xmysql to insert, change, and update values in the grant tables. You can find these utilities in the Contrib directory of the MySQL Website.