MySQL 数据库，如何分角色权限建表？

角色一直存在各个数据库中，比如 SQL Server、Oracle 等，MySQL 自从版本 8.0 release，引入了角色这个概念。

角色的概念

角色就是一组针对各种数据库权限的集合。比如，把一个角色分配给一个用户，那这个用户就拥有了这个角色包含的所有权限。一个角色可以分配给多个用户，另外一个用户也可以拥有多个角色，两者是多对多的关系。不过 MySQL 角色目前还没有提供类似于其他数据库的系统预分配的角色。比如某些数据库的 db_owner、 db_datareader 、 db_datawriter 等等。那接下来我分几个方面，来示例说明角色的使用以及相关注意事项。

示例 1：一个完整角色的授予步骤

用管理员创建三个角色：db_owner, db_datareader, db_datawriter

mysqlcreate role db_owner,db_datareader,db_datawriter

Query OK, 0 rows affected (0.02 sec)

mysqlgrant all on ytt_new.* to db_owner

Query OK, 0 rows affected (0.01 sec)

mysqlgrant select on ytt_new.* to db_datareader

Query OK, 0 rows affected (0.01 sec)

mysqlgrant insert,delete,update on ytt_new.* to db_datawriter

Query OK, 0 rows affected (0.01 sec)

分别授予这三个用户对应的角色。

-- 授权角色

mysqlgrant db_owner to ytt1

Query OK, 0 rows affected (0.02 sec)

-- 激活角色

mysqlset default role db_owner to ytt1

Query OK, 0 rows affected (0.00 sec)

mysqlgrant db_datareader to ytt2

Query OK, 0 rows affected (0.01 sec)

mysqlset default role db_datareader to ytt2

Query OK, 0 rows affected (0.01 sec)

mysqlgrant db_datawriter to ytt3

Query OK, 0 rows affected (0.01 sec)

mysqlset default role db_datawriter to ytt3

Query OK, 0 rows affected (0.01 sec)

示例 2：一个用户可以拥有多个角色

-- 用管理员登录并且创建用户

mysqlcreate user ytt4 identified by 'ytt'

Query OK, 0 rows affected (0.00 sec)

-- 把之前的三个角色都分配给用户ytt4.

mysqlgrant db_owner,db_datareader,db_datawriter to ytt4

Query OK, 0 rows affected (0.01 sec)

-- 激活用户ytt4的所有角色.

mysqlset default role all to ytt4

Query OK, 0 rows affected (0.02 sec)

-- ytt4 用户登录

root@ytt-pc:/var/lib/mysql# mysql -uytt4 -pytt -P3304 -hytt-pc

...

-- 查看当前角色列表

mysqlselect current_role()

+--------------------------------------------------------+

| current_role()                                         |

+--------------------------------------------------------+

| `db_datareader`@`%`,`db_datawriter`@`%`,`db_owner`@`%` |

+--------------------------------------------------------+

1 row in set (0.00 sec)

-- 简单创建一张表并且插入记录， 检索记录，完了删掉这张表

mysqluse ytt_new

Database changed

mysqlcreate table t11(id int)

Query OK, 0 rows affected (0.05 sec)

mysqlinsert into t11 values (1)

Query OK, 1 row affected (0.02 sec)

mysqlselect * from t11

+------+

| id   |

+------+

|    1 |

+------+

1 row in set (0.00 sec)

mysqldrop table t11

Query OK, 0 rows affected (0.04 sec)

其实意思是说，用户连接到 MySQL 服务器后，可以切换当前的角色列表，比如由 db_owner 切换到 db_datareader。

-- 还是之前的用户ytt4, 切换到db_datareader

mysqlset role db_datareader

Query OK, 0 rows affected (0.00 sec)

mysqlselect current_role()

+---------------------+

| current_role()      |

+---------------------+

| `db_datareader`@`%` |

+---------------------+

1 row in set (0.00 sec)

-- 切换后，没有权限创建表

mysqlcreate table t11(id int)

ERROR 1142 (42000): CREATE command denied to user 'ytt4'@'ytt-pc' for table 't11'

-- 切换到 db_owner，恢复所有权限。

mysqlset role db_owner

Query OK, 0 rows affected (0.00 sec)

mysqlcreate table t11(id int)

Query OK, 0 rows affected (0.04 sec)

示例 4：关于角色的两个参数

-- 用管理员连接MySQL,

-- 设置默认激活角色

mysqlset global activate_all_roles_on_login=on

Query OK, 0 rows affected (0.00 sec)

-- 设置强制给所有用户赋予角色db_datareader

mysqlset global mandatory_roles='db_datareader'

Query OK, 0 rows affected (0.00 sec)

-- 创建用户ytt7.

mysqlcreate user ytt7

Query OK, 0 rows affected (0.01 sec)

-- 用 ytt7登录数据库

root@ytt-pc:/var/lib/mysql# mysql -uytt7 -P3304 -hytt-pc

...

mysqlshow grants

+-------------------------------------------+

| Grants for ytt7@%                         |

+-------------------------------------------+

| GRANT USAGE ON *.* TO `ytt7`@`%`          |

| GRANT SELECT ON `ytt_new`.* TO `ytt7`@`%` |

| GRANT `db_datareader`@`%` TO `ytt7`@`%`   |

+-------------------------------------------+

3 rows in set (0.00 sec)

示例 5 ：create role 和 create user 都有创建角色权限，两者有啥区别？

-- 管理员登录，创建用户ytt8,ytt9.

mysqlcreate user ytt8,ytt9

Query OK, 0 rows affected (0.01 sec)

mysqlgrant create role on *.* to ytt8

Query OK, 0 rows affected (0.02 sec)

mysqlgrant create user on *.* to ytt9

Query OK, 0 rows affected (0.01 sec)

-- 用ytt8 登录，

root@ytt-pc:/var/lib/mysql# mysql -uytt8 -P3304 -hytt-pc

...

mysqlcreate role db_test

Query OK, 0 rows affected (0.02 sec)

-- 可以创建角色，但是不能创建用户

mysqlcreate user ytt10

ERROR 1227 (42000): Access deniedyou need (at least one of) the CREATE USER privilege(s) for this operation

mysql\q

Bye

-- 用ytt9 登录

root@ytt-pc:/var/lib/mysql# mysql -uytt9 -P3304 -hytt-pc

...

-- 角色和用户都能创建

mysqlcreate role db_test2

Query OK, 0 rows affected (0.02 sec)

mysqlcreate user ytt10

Query OK, 0 rows affected (0.01 sec)

mysql\q

Bye

示例 6：MySQL 用户也可以当角色来用

-- 用管理员登录，创建用户ytt11,ytt12.

mysqlcreate user ytt11,ytt12

Query OK, 0 rows affected (0.01 sec)

mysqlgrant select on ytt_new.* to ytt11

Query OK, 0 rows affected (0.01 sec)

-- 把ytt11普通用户的权限授予给ytt12

mysqlgrant ytt11 to ytt12

Query OK, 0 rows affected (0.01 sec)

-- 来查看 ytt12的权限，可以看到拥有了ytt11的权限

mysqlshow grants for ytt12

+-----------------------------------+

| Grants for ytt12@%                |

+-----------------------------------+

| GRANT USAGE ON *.* TO `ytt12`@`%` |

| GRANT `ytt11`@`%` TO `ytt12`@`%`  |

+-----------------------------------+

2 rows in set (0.00 sec)

-- 在细化点，看看ytt12拥有哪些具体的权限

mysqlshow grants for ytt12 using ytt11

+--------------------------------------------+

| Grants for ytt12@%                         |

+--------------------------------------------+

| GRANT USAGE ON *.* TO `ytt12`@`%`          |

| GRANT SELECT ON `ytt_new`.* TO `ytt12`@`%` |

| GRANT `ytt11`@`%` TO `ytt12`@`%`           |

+--------------------------------------------+

3 rows in set (0.00 sec)

示例 7：角色的撤销

-- 用管理员登录，移除ytt2的角色

mysqlrevoke db_datareader from ytt2

Query OK, 0 rows affected (0.01 sec)

-- 删除所有角色

mysqldrop role db_owner,db_datareader,db_datawriter

Query OK, 0 rows affected (0.01 sec)

-- 对应的角色也从ytt1上移除掉了

mysqlshow grants for ytt1

+----------------------------------+

| Grants for ytt1@%                |

+----------------------------------+

| GRANT USAGE ON *.* TO `ytt1`@`%` |

+----------------------------------+

1 row in set (0.00 sec)

至此，我分了 7 个目录说明了角色在各个方面的使用以及注意事项，希望对大家有帮助。

首先创建一个登陆号：

sp_addlogin

loginame,

passwd

loginame

登录名

passwd

密码

全部语法（一般不用到）为：

sp_addlogin

loginame,

passwd

[,

defdb]

[,

deflanguage

]

[,

fullname

]

[,

passwdexp

]

[,

minpwdlen

]

[,

maxfailedlogins

]

然后创建用户：

sp_adduser

loginame

这样创建当前数据库的用户，且用户名和登陆名是一样的。

全部语法为：

[dbname..]sp_adduser

loginame

[,

username[,

grpname]]

最后分配权限：

grant

all

|

select,insert,delete,update

on

table_name

|

view_name

|

stored_procedure_name

to

username

或

grant

all

|

create

database,create

default,create

procedure,create

rule,create

table,create

view,set

proxy,set

session

authorization

to

username

例，假如当前DB为：DB001：

sp_addlogin

'test001','password001'

go

sp_adduser

'test001',

'user001'

go

grant

create

table

to

user001

go

创建了一个test001的登录名，之后为之创建一个当前数据库的用户，使这个登录名可以 *** 作当前数据库。之后分配了创建表的权限给user001。

这个时候你就可以使用用户名'test001'和密码'password001'登陆了，并且可以在DB001中创建表了。同时由于sp_adduser

的时候没有指定grpname参数，那么默认是public用户组，这个组里的用户默认即开通了select/delete/update/insert的权限。

如果你希望去掉一些权限，请使用revoke命令，其语法了grant完全相同，你只需要将grant换成revoke即可，例：

revoke

select

on

address

to

user001

禁止user001查看address表。

开通其中2张表的查询权限，方法如下：

grant select on bas_checkcycle to jdc这个是整个语句。

语句分析：

grant select on bas_checkcycle to jdc；

这个颜色为执行语句。

这个颜色是数据表。

这个颜色是用户；就是需要权限的用户。

下面这个是查看，更新，删除，增加的权限都开启。

grant select,update,delete,insert on UserTagPool to [app_webservice]

grant select,update,delete,insert on UserTagPoolMap to [app_webservice]

扩展资料

Oracle权限管理

权限分类：

系统权限：系统规定用户使用数据库的权限。（系统权限是对用户而言)。

实体权限：某种权限用户对其它用户的表或视图的存取权限。（是针对表或视图而言的）。

系统权限分类：

DBA: 拥有全部特权，是系统最高权限，只有DBA才可以创建数据库结构。

RESOURCE：拥有Resource权限的用户只可以创建实体，不可以创建数据库结构。

CONNECT：拥有Connect权限的用户只可以登录Oracle，不可以创建实体，不可以创建数据库结构。

欢迎分享，转载请注明来源：内存溢出

原文地址:https://outofmemory.cn/sjk/10648893.html