一. 课堂练习

1.连接环境：

root@modb:~#  
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. 

2.创建用户

omm=# create user jim password 'abcd@123';  
NOTICE: The encrypted password contains MD5 ciphertext, which is not secure.  
CREATE ROLE  
omm=# create user kim identified by 'abcd@123';  
NOTICE: The encrypted password contains MD5 ciphertext, which is not secure.  
CREATE ROLE  
omm=# create user dim createdb password 'abcd@123';  
NOTICE: The encrypted password contains MD5 ciphertext, which is not secure.  
omm=# CREATE ROLE

3.查看用户

omm=# \du  
List of roles  
Role name | Attributes  
| Member of  
\-----------+-------------------------------------------------------------------------------------  
\-----------------------------+-----------  
dim | Create DB  
| {}  
gaussdb | Sysadmin  
| {}  
jim |  
| {}  
kim |  
| {}  
omm | Sysadmin, Create role, Create DB, Replication, Administer audit, Monitoradmin, Opera  
toradmin, Policyadmin, UseFT | {}

4.修改密码

omm=# alter user jim identified by 'abcd@1234';  
NOTICE: The encrypted password contains MD5 ciphertext, which is not secure.  
omm=# ALTER ROLE

5.修改CREATEROLE权限

omm=# alter user jim createrole;  
ALTER ROLE  
omm=# \du  
List of roles  
Role name | Attributes  
| Member of  
\-----------+-------------------------------------------------------------------------------------  
\-----------------------------+-----------  
dim | Create DB  
| {}  
gaussdb | Sysadmin  
| {}  
omm | Sysadmin, Create role, Create DB, Replication, Administer audit, Monitoradmin, Opera  
toradmin, Policyadmin, UseFT | {}  
  
jim | Create role  
| {}  
kim |  
| {}

6.授权和回收

omm=# grant jim to kim  
omm=# grant all privileges to dim;  
ALTER ROLE  
omm=# alter user dim rename to tim;  
NOTICE: MD5 password cleared because of role rename  
ALTER ROLE  
omm=# \du  
List of roles  
Role name | Attributes  
| Member of  
\-----------+-------------------------------------------------------------------------------------  
\-----------------------------+-----------  
gaussdb | Sysadmin  
| {}  
jim | Create role  
| {}  
kim |  
| {}  
tim | Create DB, Sysadmin  
| {}  
  
omm | Sysadmin, Create role, Create DB, Replication, Administer audit, Monitoradmin, Opera  
toradmin, Policyadmin, UseFT | {}  
omm=# revoke jim from kim;  
WARNING: role "kim" is not a member of role "jim"  
REVOKE ROLE  
omm=# revoke all privileges from tim;  
ALTER ROLE  
omm=# \du  
List of roles  
Role name | Attributes  
| Member of  
\-----------+-------------------------------------------------------------------------------------  
\-----------------------------+-----------  
gaussdb | Sysadmin  
| {}  
jim | Create role  
| {}  
kim |  
| {}  
omm | Sysadmin, Create role, Create DB, Replication, Administer audit, Monitoradmin, Opera  
toradmin, Policyadmin, UseFT | {}  
tim | Create DB  
| {}

7.删除用户

omm=# drop user tim;  
DROP ROLE  
omm=# drop user jim;  
omm=# DROP ROLE  
omm=#  
  
omm=# drop user kim;  
DROP ROLE  
omm=# \du  
List of roles  
Role name | Attributes  
| Member of  
\-----------+-------------------------------------------------------------------------------------  
\-----------------------------+-----------  
gaussdb | Sysadmin  
| {}  
omm | Sysadmin, Create role, Create DB, Replication, Administer audit, Monitoradmin, Opera  
toradmin, Policyadmin, UseFT | {}

二. 课后作业

1.创建用户user1、user2和user3，user1具有CREATEROLE权限，user2具有CREATEDB权限，要求使用两种不同的方法设置密码

omm=# create user user1 createrole password 'abcd@123';  
NOTICE: The encrypted password contains MD5 ciphertext, which is not secure.  
CREATE ROLE  
omm=# create user user2 createdb identified by 'abcd@123';  
NOTICE: The encrypted password contains MD5 ciphertext, which is not secure.  
CREATE ROLE  
omm=# create user user3 identified by 'abcd@123';  
NOTICE: The encrypted password contains MD5 ciphertext, which is not secure.  
CREATE ROLE  
omm=# \du  
List of roles  
Role name | Attributes  
| Member of  
\-----------+-------------------------------------------------------------------------------------  
\-----------------------------+-----------  
gaussdb | Sysadmin  
| {}  
omm | Sysadmin, Create role, Create DB, Replication, Administer audit, Monitoradmin, Opera  
user2 | Create DB  
| {}  
toradmin, Policyadmin, UseFT | {}  
user1 | Create role  
| {}  
user3 |  
| {}  

2.修改用户user1的密码

omm=# alter user user1 identified by 'abcd@1234';  
NOTICE: The encrypted password contains MD5 ciphertext, which is not secure.  
ALTER ROLE  
omm=# \du+  
List of roles  
Role name | Attributes  
| Member of | Description  
\-----------+-------------------------------------------------------------------------------------  
\-----------------------------+-----------+-------------  
gaussdb | Sysadmin  
| {} |  
omm | Sysadmin, Create role, Create DB, Replication, Administer audit, Monitoradmin, Opera  
toradmin, Policyadmin, UseFT | {} |  
user1 | Create role  
| {} |  
user2 | Create DB  
| {} |  
user3 |  
| {} |  

3.重命名用户user2

omm=# alter user user2 rename to user22;  
NOTICE: MD5 password cleared because of role rename  
omm=# ALTER ROLE  
  
omm=# \du  
List of roles  
Role name | Attributes  
| Member of  
\-----------+-------------------------------------------------------------------------------------  
\-----------------------------+-----------  
gaussdb | Sysadmin  
| {}  
omm | Sysadmin, Create role, Create DB, Replication, Administer audit, Monitoradmin, Opera  
toradmin, Policyadmin, UseFT | {}  
user1 | Create role  
| {}  
| {}  
  
user22 | Create DB  
| {}  
user3 |

4.将用户user1的权限授权给用户user3，再回收用户user3的权限

omm=# grant user1 to user3;  
GRANT ROLE  
omm=# revoke user1 from user3;  
REVOKE ROLE  
omm=# \du  
List of roles  
Role name | Attributes  
| Member of  
\-----------+-------------------------------------------------------------------------------------  
\-----------------------------+-----------  
gaussdb | Sysadmin  
| {}  
omm | Sysadmin, Create role, Create DB, Replication, Administer audit, Monitoradmin, Opera  
toradmin, Policyadmin, UseFT | {}  
user1 | Create role  
| {}  
user22 | Create DB  
| {}  
user3 |  
| {}  

5.删除所有创建用户

omm=# drop user user1;  
DROP ROLE  
omm=# drop user user22;  
DROP ROLE  
omm=# drop user user3;  
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, Opera  
toradmin, Policyadmin, UseFT | {} |