Add a new user to the MySQL database using the GRANT Statement (1)

Source: Internet
Author: User

The following example shows how to use a MySQL client to install a new user. These examples assume that the permission has been installed by default. This means that, in order to change, you must be on the same machine that MySQL is running, you must be connected as the MySQL root user, and the root user must have the insert permission and reload management permission on the MySQL database. In addition, if you change the root user password, you must specify it using the following MySQL command.

You can add new users by issuing the GRANT statement:

shell> mysql --user=root mysql
mysql> GRANT ALL PRIVILEGES ON *.* TO monty@localhost
IDENTIFIED BY 'something' WITH GRANT OPTION;
mysql> GRANT ALL PRIVILEGES ON *.* 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 three new users:

Monty: A full super user who can connect to the server from anywhere, but must use a password ('something' to do this. Note: You must issue a GRANT statement to monty @ localhost and monty @ "%. If we add a localhost entry, the entry created by mysql_install_db for the anonymous user entry of localhost takes priority when we connect from the local Host, because it has a more specific Host field value, therefore, the user table is arranged in the order of users.

Admin: a user who can connect from localhost without a password and is granted reload and process management permissions. This allows you to run the mysqladmin reload, mysqladmin refresh, mysqladmin flush-* commands, and mysqladmin processlist commands. No database-related permissions are granted. They can GRANT permissions in the future by issuing another GRANT statement.

Dummy: you can connect to a user without a password, but only from the local host. The global permission is set to 'n' -- the USAGE permission type allows you to set a user without permission. It assumes that you will grant database-related permissions in the future.

You can also directly add the same user access information by issuing an INSERT statement, and then tell the server to load 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','Y')
mysql> INSERT INTO user VALUES('%','monty',PASSWORD('something'),
'Y','Y','Y','Y','Y','Y','Y','Y','Y','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 (versions earlier than 3.22.11 have fewer permission columns ). For admin users, only the INSERT extension syntax that is more readable in version 3.22.11.

Note: To set a Super user, you only need to create a user table entry with the permission field set to 'y '. No db or host table entries are required.

The permission columns in the user table are not explicitly set by the last INSERT statement (for dummy users), so those columns are assigned the default value 'n '. This is the same thing grant usage does.

In the following example, add a User custom, which can be connected from the host localhost, server. domain, and whitehouse.gov. He only wants to access the bankaccount database from localhost, the expenses database from whitehouse.gov, and the customer database from all three hosts. He wants to use the password stupid from all three hosts.

To use the GRANT statement to set permissions for individual users, 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';

Run these commands by directly modifying the authorization table to set user permissions (Note: 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;


Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.