Mariadb user and rights management

Permission category

Library level
 Table level
 Field level
 Management category
 Program class

Management category:

CREATE TEMPORARY TABLES Create temporary table
CREATE USER Create user
FILE
SUPER
SHOW DATABASES
RELOAD
SHUTDOWN
REPLACTION SLAVE
REPLACTION CLIENT
LOCK TABLES
PRECESS

Program class:

FUNCTION
PROCEDURE
TRIGGER

CREATE,ALTER,DROP,EXCUTE

Library and table levels:

ALTER
CREATE
CREATE VIEW
UPDATE
INDEX
DROP
SHOW VIEW
GRANT OPTION: Can I transfer my permissions to another user

Data manipulation

SELECT
DELETE
UPDATE
INSERT

Field level

SELECT(COLL1,COLL2...)
UPDATE(COLL1,COLL2...)
...

All privileges
mysql metabase of mysql

mysql Authorization table in the library
db,host,user
//In the user table, you can view what permissions the user has and the user's password (encrypted or clear text)
DESC user;
-->
+------------------------+-----------------------------------+------+-----+---------+-------+
| Field                  | Type                              | Null | Key | Default | Extra |
+------------------------+-----------------------------------+------+-----+---------+-------+
| Host                   | char(60)                          | NO   | PRI |         |       |
| User                   | char(16)                          | NO   | PRI |         |       |
| Password               | char(41)                          | NO   |     |         |       |
| Select_priv            | enum('N','Y')                     | NO   |     | N       |       |
| Insert_priv            | enum('N','Y')                     | NO   |     | N       |       |
| Update_priv            | enum('N','Y')                     | NO   |     | N       |       |
| Delete_priv            | enum('N','Y')                     | NO   |     | N       |       |
| Create_priv            | enum('N','Y')                     | NO   |     | N       |       |
| Drop_priv              | enum('N','Y')                     | NO   |     | N       |       |
| Reload_priv            | enum('N','Y')                     | NO   |     | N       |       |
| Shutdown_priv          | enum('N','Y')                     | NO   |     | N       |       |
| Process_priv           | enum('N','Y')                     | NO   |     | N       |       |
| File_priv              | enum('N','Y')                     | NO   |     | N       |       |
| Grant_priv             | enum('N','Y')                     | NO   |     | N       |       |
| References_priv        | enum('N','Y')                     | NO   |     | N       |       |
| Index_priv             | enum('N','Y')                     | NO   |     | N       |       |
| Alter_priv             | enum('N','Y')                     | NO   |     | N       |       |
| Show_db_priv           | enum('N','Y')                     | NO   |     | N       |       |
| Super_priv             | enum('N','Y')                     | NO   |     | N       |       |
| Create_tmp_table_priv  | enum('N','Y')                     | NO   |     | N       |       |
| Lock_tables_priv       | enum('N','Y')                     | NO   |     | N       |       |
| Execute_priv           | enum('N','Y')                     | NO   |     | N       |       |
| Repl_slave_priv        | enum('N','Y')                     | NO   |     | N       |       |
| Repl_client_priv       | enum('N','Y')                     | NO   |     | N       |       |
| Create_view_priv       | enum('N','Y')                     | NO   |     | N       |       |
| Show_view_priv         | enum('N','Y')                     | NO   |     | N       |       |
| Create_routine_priv    | enum('N','Y')                     | NO   |     | N       |       |
| Alter_routine_priv     | enum('N','Y')                     | NO   |     | N       |       |
| Create_user_priv       | enum('N','Y')                     | NO   |     | N       |       |
| Event_priv             | enum('N','Y')                     | NO   |     | N       |       |
| Trigger_priv           | enum('N','Y')                     | NO   |     | N       |       |
| Create_tablespace_priv | enum('N','Y')                     | NO   |     | N       |       |
| ssl_type               | enum('','ANY','X509','SPECIFIED') | NO   |     |         |       |
| ssl_cipher             | blob                              | NO   |     | NULL    |       |
| x509_issuer            | blob                              | NO   |     | NULL    |       |
| x509_subject           | blob                              | NO   |     | NULL    |       |
| max_questions          | int(11) unsigned                  | NO   |     | 0       |       |
| max_updates            | int(11) unsigned                  | NO   |     | 0       |       |
| max_connections        | int(11) unsigned                  | NO   |     | 0       |       |
| max_user_connections   | int(11)                           | NO   |     | 0       |       |
| plugin                 | char(64)                          | NO   |     |         |       |
| authentication_string  | text                              | NO   |     | NULL    |       |
+------------------------+-----------------------------------+------+-----+---------+-------+

User account

'USERNAME'@'HOST'
	@'HOST' can use the following format
	@'192.168.1.1'
	@'%.%.%.%'
	@'192.168.%.%'
	@'localhost'

Create user

CREATE USER 'USERNAME'@'HOST' [IDENTIFIED BY 'PASS'];
View all user permissions
SHOW GRANT FOR 'user'@'host';

Rename User

RENAME USER old_name TO new_name;

delete user

DROP USER 'username'@'host';

Change Password

SET PASSWORD FOR username = PASSWORD('password');
	SET PASSWORD FOR slackware = PASSWORD('openstack');
//Modify mysql.user table
	UPDATE mysql.user SET Password=PASSWORD('passwoard') WHERE User='xxx';
mysqladmin password command

Forget administrator password solution

Add mysqld section in / etc/my.cnf
skip_grant_tables
skip_networking
systemctl stop mariadb
systemctl start mariadb
 You can ignore the authorization table login and use the root user to log in to the mysql database
 You can then change the password for root
 After modifying exit, delete two parameters in my.cnf

To grant authorization

GRANT prvi_type[,...] ON [table|function|procedure] db.{table|routine} TO 
'username'@'HOST' [IDENTIFIED BY 'password'] [REQUIRE SSL] [WITH with_options]
with_options:
	MAX_QUERIES_PER_HOUR count(numerical value)
	MAX_UPDATES_PER_HOUR count
	MAX_CONNECTIONS_PER_HOUR count
	MAX_USER_CONNECTIONS count

Cancellation of authorization

REVOKE prvi_type [[(column_list)] [,(column_list)]] ... ON prvi_level FROM user