MySQL数据库高危权限回收的实现

# mysql -uroot -p
mysql> show databases;
+--------------------+
| database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| erp              |
+--------------------+
3 rows in set (0.00 sec)

mysql> use erp;
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> select distinct concat('user: ''',user,'''@''',host,''';') as query from mysql.user;
+-----------------------------------------+
| query                                   |
+-----------------------------------------+
| user: 'root'@'%';                       |
| user: 'root'@'127.0.0.1';               |
| user: 'root'@'::1';                     |
| user: ''@'localhost';                   |
| user: 'root'@'localhost';               |
| user: 'erp'@'%';                       |
+-----------------------------------------+
5 rows in set (0.00 sec)

mysql> show grants for 'erp'@'%';
+------------------------------------------------------------------------------------+
| grants for erp@%                                                         |
+------------------------------------------------------------------------------------+
| grant usage on *.* to 'erp'@'%' identified by password '*******'      |
| grant all privileges on `ump`.* to 'ump'@'%'                          |
+------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

mysql> revoke all privileges on erp.* from 'erp'@'%';
mysql> revoke drop on erp.* from 'erp'@'%';
mysql> flush privileges;

super：拥有此权限允许用户终止任何查询；修改全局变量的set语句；使用change master，purge master logs
shutdown：关闭数据库
show databases：查看数据库
replication client：查询master server、slave server状态
replication slave：查看从服务器
reload：拥有此权限才可执行flush [tables | logs | privileges]
process：拥有此权限才可以执行show processlist和kill命令
file：拥有file权限才可以执行 select ..into outfile和load data infile…操作

mysql> grant select,insert,alter,update,delete,create,execute on erp.* to 'erp'@'%' ;
mysql> flush privileges;

mysql> show grants for 'erp'@'%';

mysql> revoke file,process,super on erp.* from 'erp'@'%';

mysql> select * from mysql.user where user='erp'\g;
*************************** 1. row ***************************
                  host: %
                  user: erp
              password: *33f471d4d8a84cd6c0
           select_priv: n
           insert_priv: n
           update_priv: n
           delete_priv: n
           create_priv: n
             drop_priv: n
           reload_priv: n
         shutdown_priv: n
          process_priv: n
             file_priv: n
            grant_priv: n
       references_priv: n
            index_priv: n
            alter_priv: n
          show_db_priv: n
            super_priv: n
 create_tmp_table_priv: n
      lock_tables_priv: n
          execute_priv: n
       repl_slave_priv: n
      repl_client_priv: n
      create_view_priv: n
        show_view_priv: n
   create_routine_priv: n
    alter_routine_priv: n
      create_user_priv: n
            event_priv: n
          trigger_priv: n
create_tablespace_priv: n
              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: 
      password_expired: n
1 row in set (0.00 sec)
mysql> select * from mysql.user \g;

mysql> grant select on dbname.tablename to 'username'@'%' with grant option;
mysql> select * from mysql.tables_priv;
select * from mysql.tables_priv;
+-----------+-----+-------+------------+----------------+---------------------+-------+
| host | db  | user  | table_name  | grantor | timestamp | table_priv | column_priv |
+-----------+-----+-------+------------+----------------+---------------------+-------+
| % | dbname | username | tablename | root@localhost | 0000-00-00 00:00:00 | select,grant |             |
+-----------+-----+-------+------------+----------------+---------------------+-------+

mysql> grant select(column_name) on dbname.tablename to 'username'@'%' with grant option;
mysql> select * from mysql.columns_priv;
select * from mysql.columns_priv;
+-----------+-----+-------+------------+-------------+---------------------+----------+
| host   | db  | user  | table_name | column_name | timestamp   | column_priv |
+-----------+-----+-------+------------+-------------+---------------------+----------+
| % | dbname | username | tablename | column_name | 0000-00-00 00:00:00 | select|
+-----------+-----+-------+------------+-------------+---------------------+----------+

mysql> revoke all privileges on *.* from 'username'@'localhost';

mysql> revoke all privileges,grant option from 'username'@'%';