外观
用户权限管理
MySQL中的表和其他任何关系表没有区别,都可以通过典型的SQL语句修改其结构和数据。使用GRANT和REVOKE命令可以创建和删除用户,也可以在线授予和撤回用户访问权限。由于语法严谨,消除了由不好的SQL语句(如忘记在UPDATE语句中添加WHERE子句)带来潜在的错误。
## 使用CREATE USER命令添加用户
CREATE USER用于创建新用户。要使用CREATE USER语句必须具有MySQL数据库全局的CREATE USER权限,或拥有INSERT权限。对于每个账户,CREATE USER会在没有权限的mysql.user表中创建一个新纪录。若账户已经存在则会发生错误。使用自选的IDENTIFIED BY子句,可以为账户设置一个密码。user值的密码和GRANT语句一样,其命令的语法如下。
CREATE USER user_name [IDENTIFIED BY [[PASSWORD] 'password']]
[,user_name [IDENTIFIED BY [[PASSWORD] 'password']]] ...如我们创建一个新用户,用户名叫user1,密码为123456。
create user user1 identified by '123456';
常用的几种创建特定要求的用户的语句:
- 若要创建一个新用户admin,只允许该用户从localhost主机并使用密码123456连接到MySQL数据库,则CREATE USER语句如下。
create user admin@localhost identified by '123456';
- 若用户admin还可以从IP为192.168.1.100的主机连接到MySQL服务器,则CREATE USER语句如下。
create user admin@192.168.1.100 identified by '123456';
- 若允许用户从任何主机连接,则使用百分号通配符。
create user admin@'%' identified by '123456';
使用DROP USER命令删除用户
若存在一个或多个闲置用户,应当考虑将其删除,确保其不会被用于可能的违法活动。使用DROP USER就可以删除用户。该命令会从权限表中删除用户的所有信息。DROP USER命令语法如下。
DROP USER user1[,user2];DROP USER不能自动关闭任何已经打开的用户对话框。而且若用户有被打开的对话,此时删除用户,则命令不会立即生效,直至用户对话被关闭后删除用户才生效。一旦用户对话被关闭,用户被删除,此用户再次登录时就会失败,因为此用户已经不存在了。
如我们使用DROP USER命令删除上面创建的user1用户,语法如下。
drop user user1;使用RENAME USER命令重命名用户
语法如下。
RENAME USER old_user TO new_user [,old_user TO new_user]...;使用GRANT和REVOKE命令管理访问权限
GEANT和REVOKE命令用来管理权限,也可以用来创建和删除用户。但是使用CREATE USER和DROP USER更容易实现。
查看用户权限
使用SHOW GRANTS语句可以查看用户的权限,语法如下。
SHOW GRANTS FOR 用户名@主机名;如我们创建一个新用户叫demo,然后查看此用户的权限。
show grants for demo@localhost;
上面结果中的*\*表示用户只能登录到数据库服务器,没有其他权限。
设置用户权限
只有拥有GRANT权限的用户才可以执行GRANT语句,其语法如下。
GRANT priv_type [(column_list)] ON database.table TO user [IDENTIFIED BY [PASSWORD] 'password'] [,user [IDENTIFIED BY [PASSWORD] 'password']] ... [WITH with_option [with_option]...];参数说明如下表。
| 参数 | 含义 |
|---|---|
priv_type | 权限类型 |
column_list | 表示权限作用于哪些列上,省略该参数时,表示作用于整个表 |
database.table | 用于指定权限的级别 |
user | 代表用户账户,由用户名和主机名构成,如user@localhost |
IDENTIFIED BY | 为用户设置密码 |
password | 是用户的新密码 |
WITH | 该参数后面跟着一个或多个with_option参数,该参数有五个选项,详细介绍如下表 |
| 可选项 | 含义 |
|---|---|
GRANT_OPTION | 被授权的用户可以将这些权限授予其他用户 |
MAX_QUERIES_PER_HOUR count | 设置每小时可以执行count次查询 |
MAX_UPDATES_PER_HOUR count | 设置每小时可以执行count次更新 |
MAX_CONNECTIONS_PER_HOUR count | 设置每小时可以建立count个连接 |
MAX_USER_CONNECTIONS count | 设置单个用户可以同时具有count个连接 |
MySQL中可以授予的权限有以下几组。
- 列权限:和表的一个具体列相关。如可以使用UPDATE语句更新students表的name字段的值的权限。
- 表权限:和一个具体表的所有字段数据相关。如可以使用SELECT语句查询students表的所有数据的权限。
- 数据库权限:和一个具体的数据库的所有数据表相关。如可以在已有的数据库mytest中创建新表的权限。
- 用户权限:和MySQL中所有数据相关。如可以删除一个数据库的权限。
对应地,在GRANT语句中可用于指定权限级别的值有以下几类格式。
*:表示当前数据库的所有表。*.*:表示所有数据库的所有表。db.*:表示db数据库中的所有表。db.tb表示db数据库中的tb表。db.routine:表示db数据库中的某个存储过程或存储函数。
TO子句表示将权限授予一个用户,若此用户不存在则会创建此用户,但是必须为此用户设置密码。
权限列表如下。
| 权限 | 意义 |
|---|---|
ALL [PRIVILEGES] | 设置除GRANT OPTION外的所有简单权限 |
ALTER | 允许使用ALTER TABLE |
ALTER ROUTINE | 更改或取消已有的子程序 |
CREATE | 允许使用CREATE TABLE |
CREATE ROUTINE | 创建已存储的子程序 |
CREATE TEMPORARY TABLES | 允许使用CREATE TEMPORARY TABLE |
CREATE USER | 允许使用CREATE USER、DROP USER、RENAME USER 和REVOKE ALL PRIVILEGES |
CREATE VIEW | 允许使用CREATE VIEW |
DELETE | 允许使用DELETE |
DROP | 允许使用DROP TABLE |
EXECUTE | 允许用户运行已存储的子程序 |
FILE | 允许使用LOAD DATA INFILE和SELECT INTO OUTFILE |
INDEX | 允许使用CREATE INDEX和DROP INDEX |
INSERT | 允许使用INSERT |
LOCK TABLES | 允许对拥有SELECT权限的表使用LOCK TABLES |
PROCESS | 允许使用SHOW FULL PROCESSLIST |
REFERENCES | 未被实施 |
RELOAD | 允许使用FLUSH |
REPLICATION CLIENT | 允许用户询问从属服务器或主服务器的地址 |
REPLICATION SLAVE | 用于复制型从属服务器(从主服务器中读取二进制日志事件) |
SELECT | 允许使用SELECT |
SHOW DATABASES | 显示所有数据库 |
SHOW VIEWS | 允许使用SHOW CREATE VIEW |
SHUTDOWN | 允许使用mysqladmin shutdouwn |
SUPER | 允许使用CHANGE MASTER、KILL、PURGE MASTER LOGS和SET GLOBAL语句,myadmin debug命令;允许连接(一次),即使已达到max_connections |
UPDATE | 允许使用UPDATE |
USAGE | “无权限”的同义词 |
GRANT OPTION | 允许授予权限 |
下面会创建一个管理员,来演示如何使用GRANT命令。
- 以root身份登录,使用CREATE USER命令创建一个管理员user_demo,设置密码为1234,语法如下。
create user demo_user identified by '1234';
使用root用户为用户demo赋予在db数据库下执行INSERT、SELECT、UPDATE和DELETE权限,然后查看权限是否赋予成功,代码如下。
grant insert,select,update,delete on db.* to demo_user;
show grants for demo_user;
新建一个cmd窗口,使用demo用户登录MySQL,执行CREATE TABLE命令创建user数据表,代码如下。
create table user(
id int primary key auto_increment,
name varchar(50)
);
在上图中,数据库提示“CREATE command denied to user 'demo_user'@'locallhost' for table 'user'”,这是因为demo_user不具备创建数据表的权限。
- 使用root用户赋予demo_user用户在db数据库下执行CREATE的权限,并查看demo_user的权限,代码如下。
grant create on db.* to demo_user;
show grants for demo_user;
- 新建一个cmd窗口,用demo_user登录,并再次尝试创建表user。
create table user(
id int primary key auto_increment,
name varchar(50)
);
可以看到创建表成功了,因为root管理员已授予demo_user创建表的权利。