MySQL add user, give user authorization

Source: Internet
Author: User
Tags create index

Create a user?
1 CREATEUSER ‘username‘@‘host‘ IDENTIFIED BY‘password‘;

Description

username– the user name you will create

host– specifies on which host the user can log on, if localhost is available to the local user, if you want the user to be able to log in from any remote host, you can use the wildcard%

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:

?
12345 CREATEUSER‘javacui‘@‘localhost‘IDENTIFIED BY‘123456‘CREATEUSER‘javacui‘@‘172.20.0.0/255.255.0.0‘ IDENDIFIED BY‘123456‘CREATEUSER‘javacui‘@‘%‘IDENTIFIED BY‘123456‘CREATEUSER‘javacui‘@‘%‘IDENTIFIED BY‘‘CREATEUSER‘javacui‘@‘%‘;

Authorized?
1 GRANTprivileges ON databasename.tablename TO‘username‘@‘host‘;

Description

privileges– the 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 permissions

databasename– database name

tablename-the table name, if you want to grant the user the appropriate operation permissions on all databases and tables, the * representation, such as *. *

Example:

?
12 grant  select insert  on   test. user  to   Javacui ' @ '% ' ;   grant  all  on  *.*&NBSP; to  < Code class= "SQL string" > ' javacui ' @

Note: The user authorized by the above command cannot authorize other users, if you want to allow the user to authorize, to use the command

?
1 GRANTprivileges ON databasename.tablename TO ‘username‘@‘host‘ WITH GRANTOPTION;

setting and changing user passwords

?
1 SETPASSWORD FOR ‘username‘@‘host‘PASSWORD(‘newpassword‘);

If the user is currently logged in

?
1 SETPASSWORDPASSWORD("newpassword");

Revoke user Rights?
1 REVOKEprivilege ON databasename.tablename FROM‘username‘@‘host‘;

Description: Privilege, DatabaseName, tablename– with authorization section

Example

?
1 REVOKESELECT ON *.* FROM‘javacui‘@‘%‘;

Attention:

If you are authorizing the user ' javacui ' @ '% ' (or similar): Grant SELECT on Test.user to ' javacui ' @ '% ', then use REVOKE SELECT on *. * from ' Javacui ' @ '% '; the command does not revoke the user's select operation on the Users table in the test database

Conversely, if the authorization is to use the grant SELECT on * * to ' javacui ' @ '% ', then REVOKE Select on the test.user from ' javacui '% '; the command cannot revoke the user table in the test database The Select permission

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

Delete User

?
1 DROPUSER‘username’@‘host’;

Remember to refresh the database after operation

?
1 flush privileges;

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 add user, give user authorization

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.