Introduction to adding new user rights to MySQL database

There are 2 different ways to add users: by using the grant statement or by directly manipulating the MySQL authorization table. The better approach is to use the GRANT statement because they are more concise and seem to have fewer errors.

The following example shows how to install a new user using a MySQL client. These examples assume that permissions are installed according to the defaults described in previous chapters. This means that in order to change, you must be running the same machine on the mysqld, you must be connected as a MySQL root user, and the root user must have insert permissions and reload Admin permissions on the MySQL database. In addition, if you change the root password, you must specify it as the MySQL command below.

You can add a new user by issuing a GRANT statement:

shell> mysql--user=root mysql mysql> GRANT all privileges in *.* to Monty@localhost identified by ' Somethin G ' with GRANT OPTION; Mysql> GRANT all privileges in *.* to monty@ "%" identified by ' something ' with GRANT OPTION; Mysql> GRANT reload,process on *.* to Admin@localhost; Mysql> GRANT USAGE on *.* to Dummy@localhost;

These grant statements install 3 new users:

Monty

You can connect to a full superuser from anywhere, but you must use a password (' Something ' to do this.) Note that we must issue a grant statement to Monty@localhost and monty@ "%". If we add a localhost entry, an entry created by mysql_install_db for the localhost anonymous user entry when we connect from the local host is preferred, because it has a more specific host field value, so it's an earlier arrival in the user table order.

Admin

Users who can connect from localhost without a password and are granted reload and process management privileges. This allows the user to perform mysqladmin reload, mysqladmin refresh and mysqladmin flush-* commands, as well as Mysqladmin processlist. No permissions are granted to the database. They can authorize it later by issuing another grant statement.

Dummy

A user can connect without a password, but only from the local host. Global permissions are set to ' N '--usage permission type allows you to set a user without permission. It assumes that you will grant database-related permissions at a later time.

You can also add the same user access information directly by issuing an INSERT statement, and then tell the server to reload the authorization table again:

shell> mysql--user=root mysql mysql> INSERT into user VALUES (' localhost ', ' Monty ', PASSWORD (' something '), ' y ', ' y ', ' y ', ' y ', ' y ', ' y ', ' y ', ' y ', ' y ', ' y ', ' y ', ' y ', ' y-axis ', ' Y ' mysql> INSERT into user VALUES ('% ', ' Monty ', PASSWORD (' something '), ' y ', ' y ', ' y ', ' y ', ' y ', ' y ', ' y ', ' y ', ' y-axis ', ' y ', ' Y ', ' y ', ' y ', ' y ') mysql> INSERT into user SET host= ' localhost ', user= ' admin ', reload_priv= ' y ', process_priv= ' Y '; Mysql> INSERT into User (Host,user,password) VALUES (' localhost ', ' dummy ', '); mysql> FLUSH privileges;

Depending on your MySQL version, for the above, you may have to use a different number of ' Y ' values (there are fewer rights columns in previous versions of 3.22.11). For admin users, the syntax for only more readable insert extensions with versions that start with 3.22.11.

Note that in order to set up a superuser, you simply create a user table entry with the permission field set to ' Y '. No entries for DB or host tables are required.

The permission columns in the user table are not explicitly set by the last INSERT statement (to the dummy user), so those columns are given the default value ' N '. This is the same thing that grant usage did.

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

To use the GRANT statement to set permissions for a user, run these commands:

shell> mysql--user=root mysql mysql> GRANT select,insert,update,delete,create,drop on bankaccount.* To Custom@localhost Identified by ' stupid '; Mysql> GRANT Select,insert,update,delete,create,drop on expenses.* to custom@whitehouse.gov Identified by ' stupid '; Mysql> GRANT Select,insert,update,delete,create,drop on customer.* to custom@ '% ' identified by ' Stupid ';

To run these commands by directly modifying the authorization table to set user permissions (note that flush privileges at the end):

shell> mysql--user=root mysql mysql> INSERT into user (Host,user,password) VALUES (' localhost ', ' Custom ', PASSWORD (' stupid '); Mysql> INSERT into User (Host,user,password) VALUES (' Server.domain ', ' Custom ', Password (' stupid ')); Mysql> INSERT into User (Host,user,password) VALUES (' whitehouse.gov ', ' Custom ', Password (' stupid ')); Mysql> 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 '); Mysql> 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 '); Mysql> 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 '); mysql> FLUSH privileges;

The first 3 INSERT statements Add User table entries, allowing the user custom to connect from different hosts with a given password, but no license is granted (all permissions are set to the default value ' N '). The 3 INSERT statements Add DB table entries, grant custom permissions to the BankAccount, expenses, and customer database, but only when accessed from the correct host. Typically, when the authorization table is modified directly, the server must be told to mount them again (with flush privileges) in order for the permission modification to take effect.

If you want to give a specific user access to any machine on a given domain, you can issue a grant statement as follows:

Mysql> GRANT ... On *.* to myusername@ "%.mydomainname.com" identified by ' MyPassword ';

To do the same thing by directly modifying the authorization form, do this:

mysql> INSERT into user VALUES ('%.mydomainname.com ', ' myusername ', PASSWORD (' MyPassword '),...); mysql> FLUSH privileges;

You can also use Xmysqladmin, mysql_webadmin, and even xmysql to insert, change, and update values in the authorization table. You can find these utilities in the MySQL contrib directory.