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
在用户名的后面声明了关键字localhost。该关键字指定用户创建所使用的MySQL服务器来自于主机。如果一个用户名和主机名中包括特殊符号_或通配符%,则需要用单引号将其括起,%表示一组主机。
1.2、删除用户
语法格式:
DROP USER 用户名
要使用该语句,一定要有mysql数据库的全局CREATE USER权限或DELETE权限。DROP USER语句用于删除一个或多个MySQL账户,并取消其权限。
例:删除用户usr1
1.3、修改用户名
语法格式:
RENAME USER 旧用户名 TO 新用户名
要使用该语句,一定要有全局CREATE USER权限或mysql数据库的UPDATE权限。如果旧账户不存在或新账户已存在,则会出现毛病(报错)。该语句用于对原有MySQL账户进行重命名,可以一次对多个用户更名。
例:将用户usr1和usr2的名字分别修改成user1和user2
usr1@localhost TO user1@localhost,
usr2@localhost TO user2@localhost;
1.4、修改密码
语法格式:
SET PASSWORD [ FOR 用户名 ]=PASSWORD('新密码')
如果不加FOR用户名,表示修改当前用户的密码。加了FOR用户名则是修改当前主机上的特定用户的密码。用户名的值一定要以user_name@host_name的格式给定。
例:将用户user1的密码修改成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权限
GRANT SELECT
ON Book
TO user1@localhost;
这里假定是在root用户输入了这些语句,这样用户user1就能够使用SELECT语句来查询Book表,而不论是谁创建了该表。
若在TO子句中给存在的用户指定密码,则新密码将原密码覆盖。如果权限授与了一个不存在的用户,MySQL会自动履行一条CREATE USER语句来创建这个用户,但一定要为该用户指定密码。
例:用户liu和zhang不存在,授与他们在Book表上的SELECT和UPDATE权限
ON Book
TO liu@localhost IDENTIFIED BY’123456′,
zhang@localhost IDENTIFIED BY’123′;
2.1.2、授与列权限
对列权限,权限的值只能取SELECT、INSERT和UPDATE。权限的后面需要加上列名列表。
例:授与user1在Book表上的图书编号列和书名列的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权限
ON Bookstore.*
TO user1@localhost;
2.1.4、授与用户权限
最有效力的权限就是用户权限,可以将授与数据库的权限直接授与用户,使用户取得对服务器上所有数据库的该权限。
MySQL授与用户权限时权限还可以是以下值:
1、CREATE USER:授与用户创建和删除新用户的权限
2、SHOW DATABASES:授与用户使用SHOW DATABASES语句查看所有已有数据库的定义的权利
在GRANT语法格式中,授与用户权限时ON子句中使用“*.*”,表示所有数据库的所有表
例:授与user2对所有数据库中所有表的CREATE、ALTERT和DROP权限
ON *.*
TO user2@localhost IDENTIFIED BY’123456′;
例:授与user2创建新用户的权限
ON *.*
TO user2@localhost;
2.2、权限的转移和限制
GRANT语句的最后可使用WITH子句。如果指定权限限制为GRANT OPTION,则表示TO子句中指定的所有用户都有把自己所具有的权限授与其他用户的权利,而不管其他用户会不会具有该权限。
例:授与user3在Book表上的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语句的权限
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权限
ON Book
FROM user@localhost;
到此这篇关于MySQL用户和数据权限管理详解的文章就介绍到这了,更多相关MySQL权限管理内容请搜索之前的文章或继续浏览下面的相关文章希望大家以后多多支持!
文章来源:丸子建站
文章标题:MySQL用户和数据权限管理详解
https://www.wanzijz.com/view/63288.html