MySQL用户和数据权限管理代码示例

本篇文章小编给大家分享一下MySQL用户和数据权限管理代码示例，小编觉得挺不错的，现在分享给大家供大家参考，有需要的小伙伴们可以来看看。

1、管理用户

1.1、添加用户

可以使用CREATE USER语句添加一个或多个用户，并设置相应的密码

语法格式：

CREATE USER 用户名 [IDENTIFIED BY [PASSWORD]'密码']

CREATE USER用于创建新的MySQL账户。CREATE USER会在系统本身的mysql数据库的user表中添加一个新记录。要使用CREATE USER，必须拥有mysql数据库的全局CREATE USER权限或INSERT权限。如果账户已经存在，则出现错误（报错）。

例：添加一个新用户usr1，密码为123456

CREATE USER usr1@localhost IDENTIFIED BY'123456';

在用户名的后面声明了关键字localhost。该关键字指定用户创建所使用的MySQL服务器来自于主机。如果一个用户名和主机名中包含特殊符号_或通配符%，则需要用单引号将其括起，%表示一组主机。

1.2、删除用户

语法格式：

DROP USER 用户名

要使用该语句，必须有mysql数据库的全局CREATE USER权限或DELETE权限。DROP USER语句用于删除一个或多个MySQL账户，并取消其权限。

例：删除用户usr1

DROP USER usr1@localhost;

1.3、修改用户名

语法格式：

RENAME USER 旧用户名 TO 新用户名

要使用该语句，必须有全局CREATE USER权限或mysql数据库的UPDATE权限。如果旧账户不存在或者新账户已存在，则会出现错误（报错）。该语句用于对原有MySQL账户进行重命名，可以一次对多个用户更名。

例：将用户usr1和usr2的名字分别修改为user1和user2

RENAME USER
usr1@localhost TO user1@localhost,
usr2@localhost TO user2@localhost;

1.4、修改密码

语法格式：

SET PASSWORD [ FOR 用户名 ]=PASSWORD（'新密码'）

如果不加FOR用户名，表示修改当前用户的密码。加了FOR用户名则是修改当前主机上的特定用户的密码。用户名的值必须以user_name@host_name的格式给定。

例：将用户user1的密码修改为king

SET PASSWORD FOR user1@localhost=PASSWORD('king');

2、授予权限和回收权限

2.1、授予权限

新的SQL用户不允许访问属于其他SQL用户的表，也不能立即创建自己的表，它必须被授权，可以授予的权限有以下几组：

1、列权限：和表中的一个具体列相关。例如，使用UPDATE语句更新表Book书号列值的权限。

2、表权限：和一个具体表中的所有数据相关。例如，使用SELECT语句查询表Book所有数据的权限。

3、数据库权限：和一个具体的数据库中所有表相关。例如，在已有的Bookstore数据库中创建新表的权限。

4、用户权限：和MySQL所有的数据库相关。例如，删除已有的数据库或者创建一个新数据库的权限。

给某用户授予权限可以使用GRANT语句。使用SHOW GRANTS语句可以查看当前账户拥有的权限。

语法格式：

GRANT 权限1[(列名列表1)][,权限2[(列名列表2)]...

ON [目标]{表名 | * | *.* | 库名.*}

TO 用户1 [IDENTIFIED BY [PASSWORD]'密码1']

[,用户2 [IDENTIFIED BY [PASSWORD]'密码2']]...

[WITH 权限限制1[权限限制2]...]

2.1.1、授予表权限

授予表权限时，权限可以是以下值：

1、SELECT：授予用户使用SELECT语句访问特定表（或视图）的权力。对于视图，用户必须对视图中指定的每个表（或视图）都有SELECT权限。

2、INSERT：授予用户使用INSERT语句向一个特定表中添加行的权力

3、DELETE：授予用户使用DELETE语句向一个特定表中删除行的权力

4、UPDATE：授予用户使用UPDATE语句修改特定表中值的权力

5、REFERENCES：授予用户创建一个外键来参照特定的表的权力

6、CREATE：授予用户使用特定的名字创建一个表的权力

7、ALTER：授予用户使用ALTER TABLE语句修改表的权力

8、INDEX：授予用户在表上定义索引的权力

9、DROP：授予用户删除表的权力

10、ALL或ALL PRIVILEGES：表示所有权限

在授予表权限时，ON关键字后面跟表名，指定授予权限的为表名或视图名

例：授予用户user1在Book表上的SELECT权限

USE Bookstore;
GRANT SELECT
ON Book
TO user1@localhost;

这里假设是在root用户输入了这些语句，这样用户user1就可以使用SELECT语句来查询Book表，而不管是谁创建了该表。

若在TO子句中给存在的用户指定密码，则新密码将原密码覆盖。如果权限授予了一个不存在的用户，MySQL会自动执行一条CREATE USER语句来创建这个用户，但必须为该用户指定密码。

例：用户liu和zhang不存在，授予他们在Book表上的SELECT和UPDATE权限

GRANT SELECT,UPDATE
ON Book
TO liu@localhost IDENTIFIED BY'123456',
zhang@localhost IDENTIFIED BY'123';

2.1.2、授予列权限

对于列权限，权限的值只能取SELECT、INSERT和UPDATE。权限的后面需要加上列名列表。

例：授予user1在Book表上的图书编号列和书名列的UPDATE权限。

GRANT UPDATE（图书编号,书名）
ON Book
TO user1@localhost;

2.1.3、授予数据库权限

表权限适用于一个特定的表，MySQL还支持针对整个数据库的权限。授予数据库权限时，权限可以是以下值：

1、SELECT：授予用户使用SELECT语句访问特定数据库中所有表和视图的权力

2、INSERT：授予用户使用INSERT语句向特定数据库所有表中添加行的权力

3、DELETE：授予用户使用DELETE语句在特定数据库所有表中删除行的权力

4、UPDATE：授予用户使用UPDATE语句更新特定数据库所有表中值的权力

5、REFERENCES：授予用户创建指向特定数据库中的表外键的权力

6、CREATE：授予用户使用CREATE TABLE语句在特定数据库中创建新表的权力

7、ALTER：授予用户使用ALTER TABLE语句修改特定数据库中所有表的结构的权力

8、INDEX：授予用户在特定数据库中的所有表上定义和删除索引的权力

9、DROP：授予用户删除特定数据库中所有表和视图的权力

10、CREATE TEMPORARY TABLES：授予用户在特定数据库中创建临时表的权力

11、CREATE VIEW：授予用户在特定数据库中创建新视图的权力

12、SHOW VIEW：授予用户查看特定数据库中已有视图的视图定义的权力

13、CREATE ROUTINE：授予用户为特定数据库创建存储过程和存储函数的权力

14、ALTER ROUTINE：授予用户更新和删除数据库中已有存储过程和存储函数的权力

15、EXECUTE ROUTINE：授予用户调用特定数据库的存储过程和存储函数的权力

16、LOOK TABLES：授予用户锁定特定数据库中已有表的权力

17、ALL或ALL PRIVILEGES：表示以上所有权限

在GRANT语法格式中，授予数据库权限时ON关键字后面跟*和“库名.*”。*表示当前数据库中的所有表。“库名.*”表示某个数据库中的所有表。

例：授予user1在Bookstore数据库中所有表的SELECT权限

GRANT SELECT
ON Bookstore.*
TO user1@localhost;

2.1.4、授予用户权限

最有效率的权限就是用户权限，可以将授予数据库的权限直接授予用户，使用户获得对服务器上所有数据库的该权限。

MySQL授予用户权限时权限还可以是以下值：

1、CREATE USER：授予用户创建和删除新用户的权限

2、SHOW DATABASES：授予用户使用SHOW DATABASES语句查看所有已有数据库的定义的权力

在GRANT语法格式中，授予用户权限时ON子句中使用“*.*”，表示所有数据库的所有表

例：授予user2对所有数据库中所有表的CREATE、ALTERT和DROP权限

GRANT CREATE,ALTER,DROP
ON *.*
TO user2@localhost IDENTIFIED BY'123456';

例：授予user2创建新用户的权限

GRANT CREATE USER
ON *.*
TO user2@localhost;

2.2、权限的转移和限制

GRANT语句的最后可以使用WITH子句。如果指定权限限制为GRANT OPTION，则表示TO子句中指定的所有用户都有把自己所拥有的权限授予其他用户的权力，而不管其他用户是否拥有该权限。

例：授予user3在Book表上的SELECT权限，并允许其将该权限授予其他用户

GRANT SELECT
ON Bookstore.Book
TO user3@localhost IDENTIFIED BY'123456'
WITH GRANT OPTION;

使用了WITH GRANT OPTION子句后，如果user3在该表上还拥有其他权限，他可以将其他权限也授予其他用户而不仅限于SELECT。

WITH子句后的权限限制也可以对一个用户授予使用限制，其中，MAX_QUERIES_PER_HOUR次数表示每小时可以查询数据库的次数。

MAX_CONNECTIONS_PER_HOUR次数表示每小时可以连接数据库的次数。

MAX_UPDATES_PES_HOUR次数表示每小时可以修改数据库的次数。

MAX_USER_CONNECTIONS次数表示同时连接MySQL的最大用户数。

次数是一个数值，对于前3个权限限制指定，次数如果为0则表示不起限制作用。

例：授予D每小时只能处理一条SELECT语句的权限

GRANT SELECT
ON Book
TO D@localhost
WITH MAX_QUERIES_PER_HOUR 1;

2.3、回收权限

要从一个用户回收权限，但不从mysql数据库的user表中删除该用户，可以使用REVOKE语句，该语句和GRANT语句格式相似，但具有相反的效果。要使用REVOKE语句，用户必须拥有mysql数据库的全局CREATE USER权限或UPDATE权限。

语法格式：

REVOKE 权限[（列名列表）]...

ON {表名 | * | *.* | 库名.* }

FROM 用户...

或

REVOKE ALL PRIVILEGES,GRANT OPTION FROM 用户

第一种格式用来回收用户的某些特定权限，第二种格式回收用户的所有权限

例：回收用户user在Book表上的SELECT权限

REVOKE SELECT
ON Book
FROM user@localhost;