User and Rights Management of MySQL

MySQL Rights Management

Permission Category:

management class

program class

database level

table level

field-level

MySQL User and Rights Management

Management category:

CREATE TEMPORARY TABLES

CREATE USER

FILE

SUPER

SHOW DATABASES

RELOAD

SHUTDOWN

REPLICATION SLAVE

REPLICATION CLIENT

LOCK TABLES

PROCESS

Program classes: FUNCTION, PROCEDURE, TRIGGER

CREATE

ALTER

DROP

EXCUTE

Library and table levels: DATABASE, TABLE

ALTER

CREATE

CREATE VIEW

DROP

INDEX

SHOW VIEW

GRANT OPTION: You can transfer your acquired permissions to other users, be careful

Data manipulation:

SELECT

INSERT

DELETE

UPDATE

Field Level:

SELECT(col1,col2,...)

UPDATE(col1,col2,...)

INSERT(col1,col2,...)

All permissions: ALL PRIVILEGES or ALL

authorized

Reference: dev.mysql.com/doc/refman/5.7/en/grant.html

GRANT priv_type [(column_list)],... ON [object_type] priv_level TO 'user'@'host' [IDENTIFIED BY 'password'] [WITH GRANT OPTION];

priv_type: ALL [PRIVILEGES]

object_type:TABLE | FUNCTION | PROCEDURE

priv_level: *(all libraries)| *.* | db_name.* | db_name.tbl_name |tbl_name(table of current library)| db_name.routine_name(specify library functions, stored procedures, triggers)

with_option: GRANT OPTION

| MAX_QUERIES_PER_HOUR count

| MAX_UPDATES_PER_HOUR count

| MAX_CONNECTIONS_PER_HOUR count

| MAX_USER_CONNECTIONS count

Example: GRANT SELECT (col1), INSERT (col1,col2) ON mydb.mytbl TO 'someuser'@'somehost';

REVOKE priv_type [(column_list)] [, priv_type [(column_list)]]... ON [object_type] priv_level FROM user [, user] ...

Examples:

REVOKE DELETE ON testdb.* FROM 'testuser'@'%'

To view the authorizations granted to a specified user:

Help SHOW GRANTS

SHOW GRANTS FOR 'user'@'host';

SHOW GRANTS FOR CURRENT_USER[()];

Note: MariaDB service process will read all authorization tables in mysql library into memory when it starts

(1)Execute permission operations such as GRANT or REVOKE are stored in system tables, and MariaDB service processes usually automatically reread authorization tables to take effect

(2)For commands that cannot or cannot reread the authorization table in time, manually ask MariaDB's service process to reread the authorization table:

mysql> FLUSH PRIVILEGES;