A preface

This article is about the MYSQL database management system on the database user permissions related operations; This article will be based on a series of MYSQL articles published by Knowledge Seeker.

This set of tutorials

  • MYSQL is introduced (1)
  • MYSQL retrieval (2)
  • MYSQL retrieval (3)
  • MYSQL > insert into MYSQL
  • MYSQL things (5)
  • MYSQL data type (6)
  • MYSQL > alter table table_name;
  • MYSQL view (8)
  • MYSQL stored procedure (9)
  • MYSQL cursor and trigger (10)
  • MYSQL > alter table user permissions
  • Introduction to MYSQLl Architecture (12)
  • MYSQL lock wait and deadlock (13)
  • MYSQLl operating json (14)
  • MYSQL Execution Plan (15)
  • MYSQL index (16)

Public account: Knowledge seeker

Inheriting the spirit of open Source, Spreading technology knowledge;

2 User Operations

The user account information is stored in the mysql database. Therefore, if you need to view user information, you need to access the mysql database.

2.2 Viewing User Information

The user table stores all login accounts; Mysql > select * from user;

use mysql;
select `user` from user;
Copy the code

print

user
----
root
mysql.session
mysql.sys
root
Copy the code

2.3 Creating a User

CREATE USER USER name IDENTIFIED BY [PASSWORD] PASSWORD

Example: Create user ZSZXZ and set password to ZSZXZ.

create user zszxz IDENTIFIED by 'zszxz';
Copy the code

2.4 Users with the same Name

Rename user Old user name to new user name;

Example Renaming user ZSZXZ to LSC

rename user zszxz to lsc;
Copy the code

2.5 Deleting a User

Drop user Indicates the user name

Example: Delete user LSC

drop user lsc;
Copy the code

2.6 Changing a Password

SET PASSWORD FOR username = PASSWORD(' PASSWORD ')

Example: Change the password to LSC for user ZSZXZ

SET PASSWORD FOR zszxz = PASSWORD('lsc')
Copy the code

3 Permission Operation

3.1 Viewing User Rights

SHOW GRANTS FOR user name

Example: View the permissions of user ZSZXZ

SHOW GRANTS FOR zszxz
Copy the code

print

Grant for zszxz@%
----------------
GRANT USAGE ON *.* TO 'zszxz'@The '%'
Copy the code

Query a permission, but USAGE indicates no permission at all.

3.2 Granting Permission

GRANT permission ON mysql > IDENTIFIED BY [PASSWORD] 'PASSWORD ']

Common permissions all, create, drop, insert, update, delete,select;

The example assigns user ZSZXZ query permissions on all tables in the ZSZXZ library;

grant select on zszxz.* to zszxz;
Copy the code

Let’s see ZSZXZ becomes 2

Grant for zszxz@%
----------------
GRANT USAGE ON *.* TO 'zszxz'@The '%'
GRANT SELECT ON `zszxz`.* TO 'zszxz'@The '%'
Copy the code

3.3 Revoking Permission

REVOKE permission list ON username FROM username;

Example: cancel user ZSZXZ to ZSZXZ database all table query operations;

revoke select on  zszxz.* from zszxz
Copy the code

3.4 Permission List

To revoke an authorization, refer to the following permission list.

ALL -- All permissions except GRANT OPTION
ALTER -- Use ALTER TABLE
ALTER ROUTINE -- Use ALTER PROCEDURE and DROP PROCEDURE
CREATE -- Use CREATE TABLE
CREATE ROUTINE -- Use CREATE PROCEDURE
CREATE TEMPORARY TABLES --- Use CREATE TEMPORARY TABLE
CREATE USER -- Use CREATE USER, DROP USER, RENAME USER, and REVOKE ALL PRIVILEGES
CREATE VIEW -- Use CREATE VIEW
DELETE - use the DELETE
DROP -- Use DROP TABLE
EXECUTE -- Use CALL and stored procedures
FILE -- Use SELECT INTO OUTFILE and LOAD DATA INFILE
GRANT OPTION -- Use GRANT and REVOKE
INDEX -- Use CREATE INDEX and DROP INDEX
INSERT - use the INSERT
LOCK TABLES -- Use LOCK TABLES
PROCESS -- Run the SHOW FULL PROCESSLIST command
RELOAD - use the FLUSH
REPLICATION CLIENT -- Access to server location
REPLICATION SLAVE -- Used by replication slave
SELECT - use the SELECT
SHOW DATABASES Run the SHOW DATABASES command
SHOW VIEW -- Run the SHOW CREATE VIEW command
SHUTDOWN -- use mysqladmin shutdown to shutdown MySQL
SUPER -- Use CHANGE MASTER, KILL, LOGS, PURGE, MASTER, and SET GLOBAL. You also allow mysqladmin debug logins
UPDATE - use the UPDATE
USAGE -- No access permission
Copy the code