mysql 系统权限_MySQL权限系统

MySQL权限级别

全局性的管理权限，作用整个MySQL实例级别

数据库级别的权限，作用于某个指定的数据库上或所有数据库上

数据库对象级别权限，作用于指定的数据库对象上(表，视图等)或所有的数据库对象上

权限存储在mysql库的user,db,tables_priv,columns_priv,and procs_priv这几个系统表

查看所有用户

mysql> selectuser,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 ***************************Grantsfor 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 ***************************Grantsfor 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 ***************************Grantsfor 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.02sec)

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.00sec)

mysql> show grants foryang@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 foryang@localhost;+------------------------------------------+

| Grants for yang@localhost |

+------------------------------------------+

| GRANT USAGE ON *.* TO `yang`@`localhost` |

+------------------------------------------+

1 row in set (0.00sec)

mysql> grant select on *.*to yang@localhost;

Query OK,0 rows affected (0.01sec)

mysql> show grants foryang@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 存放存储过程和函数级别权限