MySQL account creation, authorization, deletion

Source: Internet
Author: User
Tags create index

One, create the user:

Command: CREATE USER ' username ' @ ' host ' identified by ' password ';

Description: Username-the user name that you will create, host-Specifies which host the user can log on to, if localhost is available to local users, you can use the wildcard% if you want the user to be able to log on from any remote host. Password-The user's login password, the password can be empty, if it is empty, the user can not require a password to log on to the server.

Example: CREATE USER ' dog ' @ ' localhost ' identified by ' 123456 ';
CREATE USER ' pig ' @ ' 192.168.1.101_ ' idendified by ' 123456 ';
CREATE USER ' pig ' @ '% ' identified by ' 123456 ';
CREATE USER ' pig ' @ '% ' identified by ';
CREATE USER ' pig ' @ '% ';

Two, Authorization:

Command: GRANT privileges on databasename.tablename to ' username ' @ ' host '

Description: Privileges-user's operation permissions, such as SELECT, INSERT, UPDATE, etc. (see the last side of the article for a detailed list). Use all if you want to grant the permission. DatabaseName-database name, tablename-table name, if you want to grant the user the appropriate operation permissions on all databases and tables, the * representation, such as *. *.

Example: GRANT SELECT, INSERT on Test.user to ' pig ' @ '% ';
GRANT all on * * to ' pig ' at '% ';

Note: A user authorized with the above command cannot authorize another user, and if you want the user to be authorized to do so, use the following command:
GRANT privileges on Databasename.tablename to ' username ' @ ' host ' with GRANT OPTION;

three. Setting and changing user passwords

Command: Set PASSWORD for ' username ' @ ' host ' = PASSWORD (' NewPassword '), if current user is logged in with set PASSWORD = PASSWORD ("NewPassword");

Example: SET PASSWORD for ' pig ' @ '% ' = PASSWORD ("123456");

four. Revoke user rights

Command: REVOKE privilege on databasename.tablename from ' username ' @ ' host ';

Description: Privilege, DatabaseName, TableName-With the authorization section.

Example: REVOKE SELECT on * * from ' pig ' @ '% ';

Note: If you are giving the user ' pig ' @ '% ' authorization (or similar): Grant SELECT on Test.user to ' pig ' @ '% ', then use revoke select On * * from ' pig ' @ '% '; A select operation for the user table in the test database. Conversely, if the authorization is to use the grant SELECT on * * to ' pig ' @ '% ', then the revoke select on Test.user ' pig ' @ '% '; Revoke the user's SELECT permission to the Users table in the test database.

Specific information can be used with the command show GRANTS for ' pig ' @ '% '; View.

Five. Delete a user

Command: DROP USER ' username ' @ ' host ';

Schedule: Permissions for operations in MySQL

Alter Allows use of ALTER TABLE.
ALTER ROUTINE Alters or drops stored routines.
CREATE Allows use of CREATE TABLE.
CREATE ROUTINE Creates stored routines.
CREATE Temporary TABLE Allows use of CREATE temporary TABLE.
CREATE USER Allows use of the CREATE user, DROP user, RENAME user, and REVOKE all privileges.
CREATE VIEW Allows use of CREATE VIEW.
DELETE Allows use of DELETE.
DROP Allows use of DROP TABLE.
EXECUTE Allows the user to run stored routines.
FILE Allows use of SELECT... Into OUTFILE and LOAD DATA INFILE.
INDEX Allows use of CREATE index and DROP index.
INSERT Allows use of INSERT.
LOCK TABLES Allows use of LOCK TABLES on TABLES for which the user also have SELECT privileges.
PROCESS Allows use of the SHOW full processlist.
RELOAD Allows use of FLUSH.
REPLICATION Allows the user to ask where slave or master
CLIENT Servers is.
REPLICATION SLAVE Needed for replication slaves.
SELECT Allows use of SELECT.
SHOW DATABASES Allows use of SHOW DATABASES.
SHOW VIEW Allows use of SHOW CREATE VIEW.
SHUTDOWN Allows use of mysqladmin shutdown.
SUPER Allows use of change master, KILL, PURGE master LOGS, and SET GLOBAL SQL statements. Allows mysqladmin debug command. Allows one extra connection to be made if maximum connections is reached.
UPDATE Allows use of UPDATE.
USAGE Allows connection without any specific privileges.

MySQL account creation, authorization, deletion

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.