MySQL权限系统
MySQL权限级别
- 全局性的管理权限，作用整个MySQL实例级别
- 数据库级别的权限，作用于某个指定的数据库上或所有数据库上
- 数据库对象级别权限，作用于指定的数据库对象上（表，视图等）或所有的数据库对象上
- 权限存储在mysql库的user,db,tables_priv,columns_priv,and procs_priv这几个系统表
查看所有用户
mysql> select user,host from mysql.user; +------------------+-----------+ | user | host | +------------------+-----------+ | mysql.infoschema | localhost | | mysql.session | localhost | | mysql.sys | localhost | | root | localhost | +------------------+-----------+ 4 rows in set (0.01 sec)
查看mysql实例默认root用户权限
ysql> show grants for root@'localhost'\G
*************************** 1. row *************************** Grants for root@localhost: GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, SHUTDOWN, PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, SUPER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER, CREATE TABLESPACE, CREATE ROLE, DROP ROLE ON *.* TO `root`@`localhost` WITH GRANT OPTION *************************** 2. row *************************** Grants for root@localhost: GRANT APPLICATION_PASSWORD_ADMIN,AUDIT_ADMIN,BACKUP_ADMIN,BINLOG_ADMIN,BINLOG_ENCRYPTION_ADMIN,CLONE_ADMIN,CONNECTION_ADMIN,ENCRYPTION_KEY_ADMIN,GROUP_REPLICATION_ADMIN,INNODB_REDO_LOG_ARCHIVE,PERSIST_RO_VARIABLES_ADMIN,REPLICATION_SLAVE_ADMIN,RESOURCE_GROUP_ADMIN,RESOURCE_GROUP_USER,ROLE_ADMIN,SERVICE_CONNECTION_ADMIN,SESSION_VARIABLES_ADMIN,SET_USER_ID,SYSTEM_USER,SYSTEM_VARIABLES_ADMIN,TABLE_ENCRYPTION_ADMIN,XA_RECOVER_ADMIN ON *.* TO `root`@`localhost` WITH GRANT OPTION *************************** 3. row *************************** Grants for root@localhost: GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION
创建用户并查看权限
mysql> create user yang@'localhost' identified by 'mysql'; Query OK, 0 rows affected (0.02 sec)
mysql> show grants for 'yang'@localhost ; +------------------------------------------+ | Grants for yang@localhost | +------------------------------------------+ | GRANT USAGE ON *.* TO `yang`@`localhost` | +------------------------------------------+ 1 row in set (0.00 sec)
授权用户所有权限(全局权限)
mysql> grant all privileges on *.* to yang@localhost; Query OK, 0 rows affected (0.00 sec) mysql> show grants for yang@localhost; +------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Grants for yang@localhost | +------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, SHUTDOWN, PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, SUPER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER, CREATE TABLESPACE, CREATE ROLE, DROP ROLE ON *.* TO `yang`@`localhost` | | GRANT APPLICATION_PASSWORD_ADMIN,AUDIT_ADMIN,BACKUP_ADMIN,BINLOG_ADMIN,BINLOG_ENCRYPTION_ADMIN,CLONE_ADMIN,CONNECTION_ADMIN,ENCRYPTION_KEY_ADMIN,GROUP_REPLICATION_ADMIN,INNODB_REDO_LOG_ARCHIVE,PERSIST_RO_VARIABLES_ADMIN,REPLICATION_SLAVE_ADMIN,RESOURCE_GROUP_ADMIN,RESOURCE_GROUP_USER,ROLE_ADMIN,SERVICE_CONNECTION_ADMIN,SESSION_VARIABLES_ADMIN,SET_USER_ID,SYSTEM_USER,SYSTEM_VARIABLES_ADMIN,TABLE_ENCRYPTION_ADMIN,XA_RECOVER_ADMIN ON *.* TO `yang`@`localhost` | +------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 2 rows in set (0.00 sec)
回收权限
mysql> revoke all privileges on *.* from 'yang'@'localhost'; Query OK, 0 rows affected (0.00 sec)
mysql> show grants for yang@localhost;
+------------------------------------------+
| Grants for yang@localhost |
+------------------------------------------+
| GRANT USAGE ON *.* TO `yang`@`localhost` |
+------------------------------------------+
1 row in set (0.00 sec)
只授予select权限
mysql> show grants for yang@localhost; +------------------------------------------+ | Grants for yang@localhost | +------------------------------------------+ | GRANT USAGE ON *.* TO `yang`@`localhost` | +------------------------------------------+ 1 row in set (0.00 sec) mysql> grant select on *.* to yang@localhost; Query OK, 0 rows affected (0.01 sec) mysql> show grants for yang@localhost; +-------------------------------------------+ | Grants for yang@localhost | +-------------------------------------------+ | GRANT SELECT ON *.* TO `yang`@`localhost` | +-------------------------------------------+ 1 row in set (0.00 sec)
只给yang用户授予查询test库下students表中id字段
mysql> grant select(id) on test.students to yang@localhost;
授予yang用户 查询，更改，删除，插入权限，在test库下所有表
mysql> grant select,update,delete,insert on test.* to yang@localhost;
Mysql权限详解
- All/All privileges 全局或者全数据库对象级别权限
- alter 允许修改表结构的权限，但必须要求有create和insert权限，如果rename表名，要求有alter和drop原表，create和insert新表的权限
- alter routine 允许修改或者删除存储过程，函数权限
- create 允许创建新的数据库和表权限
- create routine 允许创建新存储过程，函数
- create tablespace 允许创建，修改，删除表空间和日志组权限
- create temporary table 允许创建临时表
- create user 允许创建，修改，删除，重命名user权限
- create view 允许创建视图
- delete允许删除行数据权限
- drop允许删除数据库，表，视图
- event 允许查询，创建，修改，修改，删除mysql事件
- execute允许执行存储过程和函数权限
- file允许在mysql可以访问的目录进行读写磁盘文件操作，可使用的命令包括load data infile,select...into outfile ,load file()函数
- grant option 是否允许此用户授权或者收回给其他用户你给予的权限
- index 是否允许创建和删除索引
- insert 是否允许在表里插入数据，同时在执行analyze table,optimize table,repair table语句的时候也需要insert权限
- lock 允许对拥有select权限的表进行锁定，以防止其他链接对此表的读或写
- select 允许查看表中查看数据，某些表不查询表数据的select执行则不需要此权限，如select 1+1
- update 允许修改表中数据
- shutdown 允许关闭数据库实例
- super允许执行一系列数据库关命令
- trigger 允许创建删除执行显示 触发器
- usage创建一个用户之后默认权限，自身表示无权限
- process允许查看mysql中的进程信息
- reference 允许创建外键
- reload 允许执行flush命令
- replication client 允许执行show master status show slave status show binary logs命令
- replication slave 允许slave主机通过此用户连接master
- show database 查看数据库名
- show view 查看视图
系统表权限
- 权限存储在mysql库的user，db，table_priv,columns_priv, procs_priv
- user 存放用户信息以及全局级别权限（所有数据库）,决定了来自哪些主机的哪些用户可以访问数据库
- db表 存放数据库级别的权限
- tables_priv表，存放表级别的权限
- columns_priv 存放列级别的权限
- procs_priv 存放存储过程和函数级别权限
