MySQL 5.7权限的介绍

谁践踏了优雅 2024-04-17 23:52 55阅读 0赞

**MySQL支持的权限如下：**  
  
ALL或ALL PRIVILEGES 代表指定权限等级的所有权限。  
ALTER 允许使用ALTER TABLE来改变表的结构，ALTER TABLE同时也需要CREATE和INSERT权限。重命名一个表需要对旧表具有ALTER和DROP权限，对新版具有CREATE和INSERT权限。  
ALTER ROUTINE 允许改变和删除存储过程和函数  
CREATE 允许创建新的数据库和表  
CREATE ROUTINE 允许创建创建存储过程和包  
CREATE TABLESPACE 允许创建、更改和删除表空间和日志文件组  
CREATE TEMPORARY TABLES 允许创建临时表  
CREATE USER 允许更改、创建、删除、重命名用户和收回所有权限  
CREATE VIEW 允许创建视图  
DELETE 允许从数据库的表中删除行  
DROP 允许删除数据库、表和视图  
EVENT 允许在事件调度里面创建、更改、删除和查看事件  
EXECUETE 允许执行存储过程和包  
FILE 允许在服务器的主机上通过LOAD DATA INFILE、SELECT ... INTO OUTFILE和LOAD\_FILE()函数读写文件  
GRANT OPTION 允许向其他用户授予或移除权限  
INDEX 允许创建和删除索引  
INSERT 允许向数据库的表中插入行  
LOCK TABLE 允许执行LOCK TABLES语句来锁定表  
PROCESS 允许显示在服务器上执行的线程信息，即被会话所执行的语句信息。这个权限允许你执行SHOW PROCESSLIST和mysqladmin processlist命令来查看线程，同时这个权限也允许你执行SHOW ENGINE命令  
PROXY 允许用户冒充成为另外一个用户  
REFERENCES 允许创建外键  
RELOAD 允许使用FLUSH语句  
REPLICATION CLIENT 允许执行SHOW MASTER STATUS,SHOW SLAVE STATUS和SHOW BINARY LOGS命令  
REPLICATION SLAVE 允许SLAVE服务器连接到当前服务器来作为他们的主服务器  
SELECT 允许从数据库中查询表  
SHOW DATABASES 允许账户执行SHOW DATABASE语句来查看数据库。没有这个权限的账户只能看到他们具有权限的数据库。  
SHOW VIEW 允许执行SHOW CREATE VIEW语句  
SHUTDOWN 允许执行SHUTDOWN语句和mysqladmin shutdown已经mysql\_shutdown() C API函数  
SUPER 允许用户执行CHANGE MASTER TO,KILL或mysqladmin kill命令来杀掉其他用户的线程，允许执行PURGE BINARY LOGS命令，通过SET GLOBAL来设置系统参数，执行mysqladmin debug命令，开启和关闭日志，即使read\_only参数开启也可以执行update语句，打开和关闭从服务器上面的复制，允许在连接数达到max\_connections的情况下连接到服务器。  
TRIGGER 允许操作触发器  
UPDATE 允许更新数据库中的表  
USAGE 代表没有任何权限  
  
**授予全局权限：**  
  
\*.\*代表所有数据库的权限  
  
mysql> grant all on \*.\* to 'test'@'%';  
Query OK, 0 rows affected (0.00 sec)  
  
mysql> grant select, insert on \*.\* to 'test'@'%';  
Query OK, 0 rows affected (0.00 sec)  
  
**授予指定数据库的权限：**  
  
mysql> grant all on test.\* to 'test'@'localhost';  
ERROR 1819 (HY000): Your password does not satisfy the current policy requirements  
mysql> grant select, insert on \*.\* to 'test'@'%';  
Query OK, 0 rows affected (0.00 sec)  
  
mysql> grant select, insert on test.\* to 'test'@'%';  
Query OK, 0 rows affected (0.00 sec)  
  
**授予指定表的权限：**  
  
mysql> grant all on test.orders to 'jeffrey'@'localhost';  
Query OK, 0 rows affected (0.13 sec)  
  
mysql> grant select, insert on test.orders to 'jeffrey'@'localhost';  
Query OK, 0 rows affected (0.07 sec)  
  
**授予指定字段的权限：**  
  
mysql> desc test.orders\_1;  
\+---------------+-------------+------+-----+---------+-------+  
| Field         | Type        | Null | Key | Default | Extra |  
\+---------------+-------------+------+-----+---------+-------+  
| order\_date    | date        | YES  |     | NULL    |       |  
| order\_id      | int(11)     | YES  |     | NULL    |       |  
| customer\_name | varchar(15) | YES  |     | NULL    |       |  
| product\_id    | int(11)     | YES  |     | NULL    |       |  
\+---------------+-------------+------+-----+---------+-------+  
4 rows in set (0.00 sec)  
  
mysql> grant select(order\_date), insert(order\_id,customer\_name) on test.orders\_1 to 'jeffrey'@'localhost';  
Query OK, 0 rows affected (0.01 sec)  
  
\[root@T400-kelong ~\]\# mysql -ujeffrey -p  
Enter password:   
Welcome to the MySQL monitor.  Commands end with ; or \\g.  
Your MySQL connection id is 6  
Server version: 5.7.10-log MySQL Community Server (GPL)  
  
Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.  
  
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> use test;  
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 \* from orders\_1;  
ERROR 1142 (42000): SELECT command denied to user 'jeffrey'@'localhost' for table 'orders\_1'  
  
mysql> select order\_date from orders\_1;  
\+------------+  
| order\_date |  
\+------------+  
| 2016-03-26 |  
\+------------+  
1 row in set (0.00 sec)  
  
**授予存储过程的权限：**  
  
mysql> grant create routine on test.\* to 'jeffrey'@'localhost';  
Query OK, 0 rows affected (0.08 sec)  
  
mysql> grant execute on procedure test.myproc to 'jeffrey'@'localhost';  
Query OK, 0 rows affected (0.04 sec)  
  
**授予代理用户权限：**  
  
PROX权限可以使一个用户成为另外一个用户的代理  
  
mysql> grant proxy on 'jeffrey'@'localhost' to 'test'@'%';  
Query OK, 0 rows affected (0.09 sec)

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/26506993/viewspace-2064453/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/26506993/viewspace-2064453/