1. 基本操作指令

1、查看当前系统数据库

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

2、切换数据库

mysql> use erp;Reading table information for completion of table and column namesYou can turn off this feature to get a quicker startup with -ADatabase changed

2. 权限回收准备工作

1、统计当前环境所有的用户（%表示所有机器可访问；127.0.0.1、localhost表示本机可访问）

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)

2、统计业务用户具有哪些权限以用户名erp举例如下

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)

3. 操作回收高危权限

1、回收所有权限或回收某一权限，比如drop权限

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

备注：
（1）列举部分特殊的服务器权限及其功能说明：

super：拥有此权限允许用户终止任何查询；修改全局变量的SET语句；使用CHANGE MASTER，PURGE MASTER LOGSshutdown：关闭数据库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…操作

（2）普通用户权限及其功能说明：

all：允许任何操作(usage权限不能被回收)usage：只允许登录alter：修改数据库的表alter routine：修改/删除存储过程create：创建表create routine：创建存储过程create temporary tables：创建临时表create：创建新的数据库或表create view：创建视图delete：删除表数据drop：删除数据库/表event：创建/更改/删除/查看事件execute：执行权限grant option：将自身所拥有的权限授予其他用户index：创建/删除索引insert：添加表数据lock tables：锁表references：将其它表的一个字段作为某一个表的外键约束select：查询表数据show view：查看视图trigger：创建触发器update：更新表数据

2、重新授权必要权限

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

3、确认权限

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

4. 注意事项

1）、file, process, super为危险权限，切勿权限授予管理员以外的账号；

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

2）、查看某个或所有用户的服务器权限，确认普通账号没有授权上述三种危险权限

mysql> select * from mysql.user where user=\'erp\'\\G;*************************** 1. row ***************************Host: %User: erpPassword: *33F471D4D8A84CD6C0Select_priv: NInsert_priv: NUpdate_priv: NDelete_priv: NCreate_priv: NDrop_priv: NReload_priv: NShutdown_priv: NProcess_priv: NFile_priv: NGrant_priv: NReferences_priv: NIndex_priv: NAlter_priv: NShow_db_priv: NSuper_priv: NCreate_tmp_table_priv: NLock_tables_priv: NExecute_priv: NRepl_slave_priv: NRepl_client_priv: NCreate_view_priv: NShow_view_priv: NCreate_routine_priv: NAlter_routine_priv: NCreate_user_priv: NEvent_priv: NTrigger_priv: NCreate_tablespace_priv: Nssl_type:ssl_cipher:x509_issuer:x509_subject:max_questions: 0max_updates: 0max_connections: 0max_user_connections: 0plugin: mysql_native_passwordauthentication_string:password_expired: N1 row in set (0.00 sec)mysql> select * from mysql.user \\G;

3）、授予某张表权限，权限信息保存在mysql.tables_priv表中

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 |             |+-----------+-----+-------+------------+----------------+---------------------+-------+

4）、授予某个字段权限，权限信息保存在mysql.columns_priv表中

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|+-----------+-----+-------+------------+-------------+---------------------+----------+

5）、当使用如下命令回收权限时，它回收的只是全局的权限，username用户其他的权限，比如对dbname数据库的权限，对tablename表的权限，对某个Column_name字段的权限仍然持有。

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

所以为了回收用户的所有权限，要使用如下命令

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

5. 结束