MySQL关于grant与revoke的详细教程
grant命令主要是用来授权
语法:
1 grant 权限 on 数据库对象 to 用户; //仅给某用户授予某数据库对象某权限
grant 权限 on 数据库对象 to 用户@'ip或者localhost'; //注意:最好使用该格式,因为mysql是根据User及Host来匹配用户的。
2 grant 权限 on 数据库对象 to 用户@'ip地址' identified by '用户密码'; //给某个ip地址的某个用户对某个数据库对象授予某权限,并指定该用户访问密码。
3 grant 权限 on 数据库对象 to 用户@'ip地址' identified by '用户密码' with grant option; //除了具备第二项的功能外,还额外赋予该ip的用户授予其他用户授权的权限。对应mysql.user表该用户的Grant_priv字段为Y,即该用户也可以使用grant命令了给其他用户授予他自身权力下的操作权限。(注意,不带with grant option该字段为N)
英文说明文档:
After creating a new user account, the user doesn’t have any privileges. To grant privileges to a user account, you use the GRANT
statement.
The following illustrates the syntax of the GRANT
statement:
Let’s examine the GRANT
statement in greater detail.
- First, specify one or more privileges after the
GRANT
keyword. If you grant the user multiple privileges, each privilege is separated by a comma. (see a list of privilege in the table below). - Next, specify the
privilege_level
that determines the level at which the privileges apply. MySQL supports global (*.*
), database (database.*
), table (database.table
) and column levels. If you use column privilege level, you must specify one or a list of comma-separated column after each privilege. - Then, place the
user
that you want to grant privileges. If user already exists, theGRANT
statement modifies its privilege. Otherwise, theGRANT
statement creates a new user. The optional clauseIDENTIFIED BY
allows you set a newpassword
for the user. - After that, you specify whether the user has to connect to the database server over a secure connection such as SSL, X059, etc.
- Finally, the optional
WITH GRANT OPTION
clause allows you to grant other users or remove from other users the privileges that you possess. In addition, you can use theWITH
clause to allocate MySQL database server’s resource e.g., to set how many connections or statements that the user can use per hour. This is very helpful in the shared environments such as MySQL shared hosting.
Notice that in order to use the GRANT
statement, you must have the GRANT OPTION
privilege and the privileges that you are granting. If the read_only
system variable is enabled, you need to have the SUPER
privilege to execute the GRANT
statement.
Let’s practice with some examples of using MySQL GRANT
statement to have a better understanding.
MySQL GRANT examples
For example, the followingCREATE USER
statement creates a new super user account.
CREATE USERsuper@localhostIDENTIFIED BY'dolphin';
To display the privileged assigned to super@localhost user, you use SHOW GRANTS
statement.
SHOWGRANTSFORsuper@localhost;
如果未曾赋予用户权限,则会提示:
1
2
3
4
5
6
|
+-------------------------------------------+
|
Grants
for
super@localhost
|
+-------------------------------------------+
|
GRANT
USAGE
ON
*.*
TO
`
super`@`localhost`
|
+-------------------------------------------+
1
row
in
set
(0.00
sec)
|
To grant all privileges to the super@localhost user account, you use the following statement.Note that USAGE
privilege means no privileges in MySQL.
GRANTALLON*.*TO'super'@'localhost'WITH GRANT OPTION;、//赋予本地super用户超级权限(含grant)
The
权限包含有:
SELECT /INSERT /UPDATE / DELETE / DROP / CREATE / CREATE USER / ALTER / ALTER ROUTINE (使用alter procedure和drop procedure) / CREATE ROUTINE (使用create procedure) / CREATE
TEMPORARY TABLES (使用create temporary table)/ CREATE VIEW / EXECUTE (使用call和存储过程) / EVENT / FILE (使用select into outfile 和 load data infile) / GRANT OPTION (可以使用grant和revoke) / ALL / ALL PRIVILEGES / INDEX (可以使用create index和drop index) / LOCK TABLES (锁表) / PROCESS (使用show full processlist) / RELOAD (使用flush) / REPLICATION CLIENT (服务器位置访问) / REPLICATION SLAVE (由复制从属使用) / SHOW DATABASES / SHOW VIEW / SHUT DOWN (使用mysqladmin shutdown 来关闭mysql)/ SUPER / USAGE (无访问权限)
ALL PRIVILEGES; //等同于All
数据对象:
*.* 所有库和所有表。
databaseName.* 某个库中的所有表
databaseName.tableName 某个库中某个表
使用案例:
grant 普通数据用户,查询、插入、更新、删除 数据库中所有表数据的权利。
grant select, insert, update, delete on testdb.* to common_user@’%’
grant 数据库开发人员,创建表、索引、视图、存储过程、函数。。。等权限。
grant 创建、修改、删除 MySQL 数据表结构权限。
grant create on testdb.* to developer@’192.168.0.%’;
grant alter on testdb.* to developer@’192.168.0.%’;
grant drop on testdb.* to developer@’192.168.0.%’;
grant 操作 MySQL 外键权限。
grant references on testdb.* to developer@’192.168.0.%’;
grant 操作 MySQL 临时表权限。
grant create temporary tables on testdb.* to developer@’192.168.0.%’;
grant 操作 MySQL 索引权限。
grant index on testdb.* to developer@’192.168.0.%’;
grant 操作 MySQL 视图、查看视图源代码 权限。
grant create view on testdb.* to developer@’192.168.0.%’;
grant show view on testdb.* to developer@’192.168.0.%’;
grant 操作 MySQL 存储过程、函数 权限。
grant create routine on testdb.* to developer@’192.168.0.%’; -- now, can show procedure status
grant alter routine on testdb.* to developer@’192.168.0.%’; -- now, you can drop a procedure
grant execute on testdb.* to developer@’192.168.0.%’;
grant 作用在整个 MySQL 服务器上:
grant select on *.* to dba@localhost; -- dba 可以查询 MySQL 中所有数据库中的表。
grant all on *.* to dba@localhost; -- dba 可以管理 MySQL 中的所有数据库
grant 作用在单个数据库上:
grant select on testdb.* to dba@localhost; -- dba 可以查询 testdb 中的表。
grant 作用在单个数据表上:
grant select, insert, update, delete on testdb.orders to dba@localhost;
grant 作用在表中的列上:
grant select(id, se, rank) on testdb.apache_log to dba@localhost;
grant 作用在存储过程、函数上:
grant execute on procedure testdb.pr_add to ’dba’@’localhost’
grant execute on function testdb.fn_add to ’dba’@’localhost’
注意:修改完权限以后 一定要刷新服务,或者重启服务,刷新服务用:FLUSH PRIVILEGES。
同理:revoke英文文档如下:
Introduction to the MySQL REVOKE Statement
In order to revoke privileges from a user account, you use the MySQL REVOKE
statement. MySQL allows you to revoke one or more privileges or all privileges from a user.
The following illustrates the syntax of revoking specific privileges from a user:
Let’s examine the MySQL REVOKE statement in more detail.
- First, specify a list of privileges that you want to revoke from a user right after the
REVOKE
keyword. You need to separate privileges by commas. - Second, specify the privilege level at which privileges is revoked in the
ON
clause . - Third, specify the user account that you want to revoke the privileges in the
FROM
clause.
Note that to revoke privileges from a user account, you must have GRANT OPTION
privilege and the privileges that you are revoking.
To revoke all privileges from a user, you use the following form of the REVOKE statement:
To execute the REVOKE ALL
statement , you must have the global CREATE USER
privilege or the UPDATE
privilege for the mysql database.
To revoke proxy user, you use the REVOKE PROXY
command as follows:
A proxy user is a valid user in MySQL who can impersonate another user, therefore, the proxy user has all privileges of the user that it impersonates.
Before revoking privileges of a user, it is good practice to check if the user has the privileges by using theSHOW GRANTS
statement as follows:
//查看rfc用户的权限
SHOWGRANTSFORrfc;
//MySQL返回如下结果:
GRANTSELECT,UPDATE,DELETEON'classicmodels'.*TO'rfc'@'%'
//为rfc用户指定密码
CREATE USERIF EXISTSrfcIDENTIFIED BY'dolphin';
//授权
GRANTSELECT,UPDATE,DELETEON classicmodels.*TOrfc;
//解除 rfc用户的更新、删除权限
REVOKE
UPDATE,
DELETE
ON
classicmodels.*
FROM
rfc;
|
//查看rfc的权限
SHOWGRANTSFOR'rfc'@'localhost';
//返回结果:
GRANTSELECTON'classicmodels'.*TO'rfc'@'%'
//解除rfc所有权限及grant
REVOKE ALL PRIVILEGES,GRANT OPTIONFROMrfc;
//查看rfc权限
SHOWGRANTSFOR'rfc'@'localhost';
//返回结果
Note that USAGE
privilege means no privileges in MySQL.