学习目标
学习openGauss创建角色、修改角色属性、更改角色权限和删除角色
课程学习
角色是用来管理权限的，从数据库安全的角度考虑，可以把所有的管理和操作权限划分到不同的角色上
连接openGauss
root@modb:~# su - omm
omm@modb:~$ gsql -r
1.创建角色
-–列出所有数据库角色
omm=# \du
List of roles
Role name | Attributes | Member of
-----------+------------------------------------------------------------------------------------------------------------------+-----------
gaussdb | Sysadmin | {}
omm | Sysadmin, Create role, Create DB, Replication, Administer audit, Monitoradmin, Operatoradmin, Policyadmin, UseFT | {}
--创建角色 manager1，密码 test_123
omm=# CREATE ROLE manager1 IDENTIFIED BY 'test_123';
NOTICE: The encrypted password contains MD5 ciphertext, which is not secure.
CREATE ROLE
omm=# --原生 PG 语法 with password 不支持 IDENTIFIED BY
--创建角色 manager2 密码 test_456 具有 LOGIN 属性且为系统管理员
omm=# CREATE ROLE manager2 LOGIN SYSADMIN IDENTIFIED BY 'test_456';
NOTICE: The encrypted password contains MD5 ciphertext, which is not secure.
CREATE ROLE
omm=# --原生 PG 有 SUPERUSER 属性，无 SYSADMIN
-–创建角色 manager3 密码 test_789 从2021年12月10日生效，2021年12月30日失效
omm=# CREATE ROLE manager3 WITH LOGIN PASSWORD 'test_789' VALID BEGIN '2021-12-10' VALID UNTIL '2021-12-30';
NOTICE: The encrypted password contains MD5 ciphertext, which is not secure.
CREATE ROLE
omm=# --原生 PG 不支持 VALID BEGIN
-–再次查看所有数据库角色
omm=# \du+
List of roles
Role name | Attributes | Member of | Description
-----------+------------------------------------------------------------------------------------------------------------------+-----------+-------------
gaussdb | Sysadmin | {} |
manager1 | Cannot login | {} |
manager2 | Sysadmin | {} |
manager3 | Role valid begin 2021-12-10 00:00:00+08 +| {} |
| Role valid until 2021-12-30 00:00:00+08 | |
omm | Sysadmin, Create role, Create DB, Replication, Administer audit, Monitoradmin, Operatoradmin, Policyadmin, UseFT | {} |
omm=#
2.修改角色属性
--–修改角色 manage1 具有 LOGIN 属性且为系统管理员
omm=# ALTER ROLE manager1 SYSADMIN LOGIN;
ALTER ROLE
--查看manager1
omm=# \du+ manager1
List of roles
Role name | Attributes | Member of | Description
-----------+------------+-----------+-------------
manager1 | Sysadmin | {} |
omm=#
--修改角色 manager2 密码
omm=# ALTER ROLE manager2 IDENTIFIED BY 'abcd@123' replace 'test_456';
NOTICE: The encrypted password contains MD5 ciphertext, which is not secure.
ALTER ROLE
omm=# --此处可以不带 replace old_pass
-–重命名 manager2
omm=# ALTER ROLE manager2 RENAME TO manager20;
NOTICE: MD5 password cleared because of role rename
ALTER ROLE
omm=#
3.授权
-–将 omm 的权限授权给 manager1
omm=# GRANT omm to manager1 with admin option;
GRANT ROLE
omm=#
omm=# \du+
List of roles
Role name | Attributes | Member of | Description
-----------+------------------------------------------------------------------------------------------------------------------+-----------+-------------
gaussdb | Sysadmin | {} |
manager1 | Sysadmin | {omm} |
manager20 | Sysadmin | {} |
manager3 | Role valid begin 2021-12-10 00:00:00+08 +| {} |
| Role valid until 2021-12-30 00:00:00+08 | |
omm | Sysadmin, Create role, Create DB, Replication, Administer audit, Monitoradmin, Operatoradmin, Policyadmin, UseFT | {} |
omm=#
4.回收权限
omm=# revoke all privilege from manager1;
ALTER ROLE
omm=#
omm=# \du+
List of roles
Role name | Attributes | Member of | Description
-----------+------------------------------------------------------------------------------------------------------------------+-----------+-------------
gaussdb | Sysadmin | {} |
manager1 | | {omm} |
manager20 | Sysadmin | {} |
manager3 | Role valid begin 2021-12-10 00:00:00+08 +| {} |
| Role valid until 2021-12-30 00:00:00+08 | |
omm | Sysadmin, Create role, Create DB, Replication, Administer audit, Monitoradmin, Operatoradmin, Policyadmin, UseFT | {} |
omm=#
5.删除角色
omm=# drop role manager1;
DROP ROLE
omm=# drop role manager20;
DROP ROLE
omm=# drop role manager3;
DROP ROLE
omm=#
omm=# \du+
List of roles
Role name | Attributes | Member of | Description
-----------+------------------------------------------------------------------------------------------------------------------+-----------+-------------
gaussdb | Sysadmin | {} |
omm | Sysadmin, Create role, Create DB, Replication, Administer audit, Monitoradmin, Operatoradmin, Policyadmin, UseFT | {} |
omm=#
课后作业
1.创建角色 role1 为系统管理员, role2 指定生效日期, role3 具有 LOGIN 属性
omm=# CREATE ROLE role1 SYSADMIN ;
ERROR: The password could not be NULL.
--CREATE ROLE 必须有 PASSWORD , 原生 PG 可以不带 password 创建角色
omm=# CREATE ROLE role1 SYSADMIN IDENTIFIED BY 'test_789';
NOTICE: The encrypted password contains MD5 ciphertext, which is not secure.
CREATE ROLE
omm=# CREATE ROLE role2 PASSWORD 'test_789' VALID BEGIN '2021-12-10' VALID UNTIL '2021-12-30';
NOTICE: The encrypted password contains MD5 ciphertext, which is not secure.
CREATE ROLE
omm=# CREATE ROLE role3 with LOGIN PASSWORD 'test_789' ;
NOTICE: The encrypted password contains MD5 ciphertext, which is not secure.
CREATE ROLE
omm=# \du+
List of roles
Role name | Attributes | Member of | Description
-----------+------------------------------------------------------------------------------------------------------------------+-----------+-------------
gaussdb | Sysadmin | {} |
omm | Sysadmin, Create role, Create DB, Replication, Administer audit, Monitoradmin, Operatoradmin, Policyadmin, UseFT | {} |
role1 | Cannot login, Sysadmin | {} |
role3 | | {} |
role2 | Cannot login +| {} |
| Role valid begin 2021-12-10 00:00:00+08 +| |
| Role valid until 2021-12-30 00:00:00+08 | |
omm=#
2.重命名 role1
omm=# alter role role1 rename to role10;
NOTICE: MD5 password cleared because of role rename
ALTER ROLE
omm=#
3.修改 role2 密码
omm=# ALTER ROLE role2 IDENTIFIED BY 'role2@123';
NOTICE: The encrypted password contains MD5 ciphertext, which is not secure.
ALTER ROLE
omm=#
4.将 omm 权限授权给 role3 ,再回收 role3 的权限
omm=# grant omm to role3 with admin option;
GRANT ROLE
omm=# \du+
List of roles
Role name | Attributes | Member of | Description
-----------+------------------------------------------------------------------------------------------------------------------+-----------+-------------
gaussdb | Sysadmin | {} |
omm | Sysadmin, Create role, Create DB, Replication, Administer audit, Monitoradmin, Operatoradmin, Policyadmin, UseFT | {} |
role10 | Cannot login, Sysadmin | {} |
role2 | Cannot login +| {} |
| Role valid begin 2021-12-10 00:00:00+08 +| |
| Role valid until 2021-12-30 00:00:00+08 | |
role3 | | {omm} |
omm=#
omm=# revoke all privilege from role3;
ALTER ROLE
omm=#
omm=# \du+
List of roles
Role name | Attributes | Member of | Description
-----------+------------------------------------------------------------------------------------------------------------------+-----------+-------------
gaussdb | Sysadmin | {} |
omm | Sysadmin, Create role, Create DB, Replication, Administer audit, Monitoradmin, Operatoradmin, Policyadmin, UseFT | {} |
role10 | Cannot login, Sysadmin | {} |
role2 | Cannot login +| {} |
| Role valid begin 2021-12-10 00:00:00+08 +| |
| Role valid until 2021-12-30 00:00:00+08 | |
role3 | | {omm} |
omm=#
5.删除所有创建角色
omm=# drop role role10;
DROP ROLE
omm=# drop role role2;
DROP ROLE
omm=# drop role role3;
DROP ROLE
omm=# \du+
List of roles
Role name | Attributes | Member of | Description
-----------+------------------------------------------------------------------------------------------------------------------+-----------+-------------
gaussdb | Sysadmin | {} |
omm | Sysadmin, Create role, Create DB, Replication, Administer audit, Monitoradmin, Operatoradmin, Policyadmin, UseFT | {} |
omm=#
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
