数据库的权限管理以及数据的备份和恢复

最后面有总结（命令和格式的使用）

一：权限管理

1.权限级别

Global level：系统级，所有库，所有表的权限

Database level：某个数据库中的所有表的权限

Table level：库中的某个表的权限

Column level：表中的某个字段的权限

procs level：某个存储过程的权限

proxies level：代理服务器的权限

2.查看权限记录表

因为超级管理员默认已经设置；所以直接查询权限即可

Global level

 mysql> select * from mysql.user\G
*************************** 1. row ***************************
                  Host: localhost
                  User: root
           Select_priv: Y
           Insert_priv: Y
           Update_priv: Y
           Delete_priv: Y
           Create_priv: Y
             Drop_priv: Y
           Reload_priv: Y
         Shutdown_priv: Y
          Process_priv: Y
             File_priv: Y
            Grant_priv: Y
       References_priv: Y
            Index_priv: Y
            Alter_priv: Y
          Show_db_priv: Y
            Super_priv: Y
 Create_tmp_table_priv: Y
      Lock_tables_priv: Y
          Execute_priv: Y
       Repl_slave_priv: Y
      Repl_client_priv: Y
      Create_view_priv: Y
        Show_view_priv: Y
   Create_routine_priv: Y
    Alter_routine_priv: Y
      Create_user_priv: Y
            Event_priv: Y
          Trigger_priv: Y
Create_tablespace_priv: Y
              ssl_type: 
            ssl_cipher: 
           x509_issuer: 
          x509_subject: 
         max_questions: 0
           max_updates: 0
       max_connections: 0
  max_user_connections: 0
                plugin: mysql_native_password
 authentication_string: *B1DD4ADE47888D9AEC4D705C85230F1B52D2A817
      password_expired: N
 password_last_changed: 2022-09-25 14:44:38
     password_lifetime: NULL
        account_locked: N

字段介绍：

用户字段：root
权限字段：Select_priv
安全字段：*B1DD4ADE47888D9AEC4D705C85230F1B52D2A817
​
Select_priv：查询权限
Insert_priv：插入权限
Update_priv：更新权限
Delete_priv：删除权限
......

Database level

mysql> select * from mysql.db\G;
*************************** 1. row ***************************
                 Host: localhost
                   Db: performance_schema
                 User: mysql.session
          Select_priv: Y
          Insert_priv: N
          Update_priv: N
          Delete_priv: N
          Create_priv: N
            Drop_priv: N
           Grant_priv: N
      References_priv: N
           Index_priv: N
           Alter_priv: N
Create_tmp_table_priv: N
     Lock_tables_priv: N
     Create_view_priv: N
       Show_view_priv: N
  Create_routine_priv: N
   Alter_routine_priv: N
         Execute_priv: N
           Event_priv: N
         Trigger_priv: N

测试库权限：grant all on identified by

mysql> create database t1;
Query OK, 1 row affected (0.00 sec)
​
mysql> grant all on t1.* to 't1'@'localhost' identified by 'QianFeng@123';
Query OK, 0 rows affected, 1 warning (0.00 sec)

查看：

mysql> select * from mysql.db\G
*************************** 3. row ***************************
                 Host: localhost
                   Db: t1
                 User: t1
          Select_priv: Y
          Insert_priv: Y
          Update_priv: Y
          Delete_priv: Y
          Create_priv: Y
            Drop_priv: Y
           Grant_priv: N
      References_priv: Y
           Index_priv: Y
           Alter_priv: Y
Create_tmp_table_priv: Y
     Lock_tables_priv: Y
     Create_view_priv: Y
       Show_view_priv: Y
  Create_routine_priv: Y
   Alter_routine_priv: Y
         Execute_priv: Y
           Event_priv: Y
         Trigger_priv: Y
3 rows in set (0.00 sec)

验证：

[root@xingdian ~]# mysql -u t1 -pQianFeng@123
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.7.39-log MySQL Community Server (GPL)
​
Copyright (c) 2000, 2022, Oracle and/or its affiliates.
​
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
​
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
​
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| t1                 |
+--------------------+
2 rows in set (0.00 sec)
​

Table level

mysql> select * from mysql.tables_priv\G;
*************************** 1. row ***************************
       Host: localhost
         Db: mysql
       User: mysql.session
 Table_name: user
    Grantor: boot@connecting host
  Timestamp: 0000-00-00 00:00:00
 Table_priv: Select
Column_priv: 
*************************** 2. row ***************************
       Host: localhost
         Db: sys
       User: mysql.sys
 Table_name: sys_config
    Grantor: root@localhost
  Timestamp: 2022-09-25 14:40:58
 Table_priv: Select
Column_priv: 
2 rows in set (0.00 sec)

创建库表验证：

mysql> create database t2;
Query OK, 1 row affected (0.00 sec)
​
mysql> use t2;
Database changed
mysql> create table u1(id int);
Query OK, 0 rows affected (0.01 sec)
​
mysql> insert into u1 values (1);
Query OK, 1 row affected (0.01 sec)
​
mysql> grant all on t2.u1 to 't2'@'localhost' identified by 'QianFeng@123';
Query OK, 0 rows affected, 1 warning (0.00 sec)
​
mysql> create table u2(id int);
Query OK, 0 rows affected (0.01 sec)
​
mysql> show tables;
+--------------+
| Tables_in_t2 |
+--------------+
| u1           |
| u2           |
+--------------+
2 rows in set (0.00 sec)

权限查看：

mysql> select * from mysql.tables_priv\G;
*************************** 3. row ***************************
       Host: localhost
         Db: t2
       User: t2
 Table_name: u1
    Grantor: root@localhost
  Timestamp: 0000-00-00 00:00:00
 Table_priv: Select,Insert,Update,Delete,Create,Drop,References,Index,Alter,Create View,Show view,Trigger
Column_priv: 
3 rows in set (0.00 sec)

验证：（登录t2账户，看到u1表，看不到u2代表权限成功）

[root@xingdian ~]# mysql -u t2 -pQianFeng@123
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 6
Server version: 5.7.39-log MySQL Community Server (GPL)
​
Copyright (c) 2000, 2022, Oracle and/or its affiliates.
​
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
​
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
​
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| t2                 |
+--------------------+
2 rows in set (0.00 sec)
​
mysql> use t2;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
​
Database changed
mysql> show tables;
+--------------+
| Tables_in_t2 |
+--------------+
| u1           |
+--------------+
1 row in set (0.00 sec)

Column level

[root@passion ~]# mysql -uroot -pPassion@123
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 7
Server version: 5.7.39-log MySQL Community Server (GPL)
​
Copyright (c) 2000, 2022, Oracle and/or its affiliates.
​
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
​
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
​
mysql> select * from mysql.columns_priv\G; 
Empty set (0.00 sec)
​
mysql> insert into mysql.columns_priv(host,db,user,table_name,column_name,column_priv) values('%','t2','t2','u1','id','select');  
Query OK, 1 row affected (0.00 sec)
​
mysql> select * from mysql.columns_priv\G;
*************************** 1. row *************************