最后面有总结(命令和格式的使用)
一:权限管理
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 *************************