Mysql study notes (7-DCL commands user account and rights management)

User account and rights management:

User account: 'username'@'host'

Host: which hosts are allowed to remotely create connections when this user accesses the current mysql server

Representation: IP, network address, hostname, wildcard characters (% and _)

Do not check hostname: my.cnf

[mysqld]

Skip_name_resolve = ON

Create a user account:

CREATE USER 'username'@'host' [IDENTIFIED BY' password']

Delete user account:

DROP USER 'user'@'host' [, user@host]...

Authorization:

Permission levels: administrative permissions, databases, tables, fields, storage routines

GRANT priv_type,... ON [object_type] db_name.tbl_name TO 'user'@'host' [IDENTIFIED BY' password']

Priv_type: ALL [PRIVILEGES]

Db_name.tbl_name:

*. *: all tables of all libraries

Db_name.*: specifies all tables of the library

Db_name.tbl_name: specify a specific table for the library

Db_name.routine_name: specify a stored procedure or stored function on the library

[object_type]

TABLE

FUNCTION

PROCEDURE

View the authorization granted to the specified user:

SHOW GRANTS FOR 'user'@'host'

SHOW GRANTS FOR CURRENT_USER

Reclaim permissions:

REVOKE priv_type,... ON db_name.tbl_name FROM 'user'@'host'

Note: when the MariaDB service process starts, all authorization tables of the mysql library are read into memory

(1) permission operations performed by GRANT or REVOKE commands are saved in the table. MariaDB usually rereads the authorization table automatically, and the permission modification takes effect immediately.

(2) for permission modifications implemented in other ways, you must run the FLUSH PRIVILEGES command manually to take effect.

Hardening the mysql server and running the mysql_secure_installation command after the installation is complete