学习openGauss的第四天
主要内容是角色创建，赋权，更改属性，删除
角色是用来管理权限的，从数据库安全的角度考虑，可以把所有的管理和操作权限划分到不同的角色上
登录数据库
root@modb:~# su - omm
omm@modb:~$ gsql -r
gsql ((openGauss 2.0.0 build 78689da9) compiled at 2021-03-31 21:03:52 commit 0 last mr )
Non-SSL connection (SSL connection is recommended when requiring high-security)
Type "help" for help.
1.创建角色role1为系统管理员, role2指定生效日期, role3具有LOGIN属性
omm=# create role role1 sysadmin identifie dby 'role_123';
NOTICE: The encrypted password contains MD5 ciphertext, which is not secure.
CREATE ROLE
omm=# create role role2 identified by 'role_456' valid begin '2021-12-18' valid until '2021-12-28';
NOTICE: The encrypted password contains MD5 ciphertext, which is not secure.
CREATE ROLE
omm=# create role role3 login identified by 'role+789';
NOTICE: The encrypted password contains MD5 ciphertext, which is not secure.
CREATE ROLE
omm=# \du+
List of roles
Role name | Attributes | Member of | Desc
ription
role3 | | {} |
-----------+------------------------------------------------------------------------------------------------------------------+-----------+-----
--------
gaussdb | Sysadmin | {} |
omm | Sysadmin, Create role, Create DB, Replication, Administer audit, Monitoradmin, Operatoradmin, Policyadmin, UseFT | {} |
role1 | Cannot login, Sysadmin | {} |
role2 | Cannot login +| {} |
| Role valid begin 2021-12-18 00:00:00+08 +| |
| Role valid until 2021-12-28 00:00:00+08 |
2.重命名role1为role11
omm=# alter role role1 rename to role11 ;
NOTICE: MD5 password cleared because of role rename
ALTER ROLE
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 | {}
role11 | Cannot login, Sysadmin | {}
role2 | Cannot login +| {}
| Role valid begin 2021-12-18 00:00:00+08 +|
| Role valid until 2021-12-28 00:00:00+08 |
role3 |
| {}
3.修改role2密码
omm=# alter role role2 identified by 'test';
ERROR: Password must contain at least 8 characters.
omm=# alter role role2 identified by 'testtest';
ERROR: Password must contain at least three kinds of characters.
omm=#
omm=#
omm=# alter role role2 identified by 'test_4567';
NOTICE: The encrypted password contains MD5 ciphertext, which is not secure.
ALTER ROLE
4.将omm权限授权给role3,再回收role3的权限
omm=# grant omm to role3 ;
GRANT ROLE
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 | {}
role11 | Cannot login, Sysadmin | {}
role2 | Cannot login +| {}
| Role valid begin 2021-12-18 00:00:00+08 +|
omm=# | Role valid until 2021-12-28 00:00:00+08 |
role3 | | {omm}
omm=# revoke all privilege from role3 ;
ALTER ROLE
omm=# \du
List of roles
Role name | Attributes | Member of
role3 | | {omm}
-----------+------------------------------------------------------------------------------------------------------------------+-----------
gaussdb | Sysadmin | {}
omm | Sysadmin, Create role, Create DB, Replication, Administer audit, Monitoradmin, Operatoradmin, Policyadmin, UseFT | {}
role11 | Cannot login, Sysadmin | {}
role2 | Cannot login +| {}
| Role valid begin 2021-12-18 00:00:00+08 +|
| Role valid until 2021-12-28 00:00:00+08 |
omm=#
5.删除所有创建角色
omm=# drop role role11 ;
omm=# DROP ROLE
omm=#
omm=# drop role role2 ;
DROP ROLE
omm=# drop role role3 ;
DROP ROLE
omm=#
omm=# \du
omm | Sysadmin, Create role, Create DB, Replication, Administer audit, Monitoradmin, Operatoradmin, Policyadmin, UseFT | {}
omm=# List of roles
Role name | Attributes | Member of
-----------+------------------------------------------------------------------------------------------------------------------+-----------
gaussdb | Sysadmin | {}
omm=#
omm=#
