openGauss每日一练第 5 天

1.创建用户
–以下两种设置密码方法等效

root@modb:~#  
root@modb:~#  
root@modb:~# su - omm  
omm@modb:~$ gsql -r  
failed to connect Unknown:5432.  
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.  
  
  
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@456';  
NOTICE: The encrypted password contains MD5 ciphertext, which is not secure.  
CREATE ROLE  

–用户dim具有创建数据库权限

omm=# CREATE USER dim CREATEDB PASSWORD 'abcd@789';  
NOTICE: The encrypted password contains MD5 ciphertext, which is not secure.  
CREATE ROLE  

–查看用户

\du  
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, Operatoradmin,  
Policyadmin, UseFT | {}  

2.修改用户属性
–修改密码

  
omm=# ALTER USER jim IDENTIFIED BY 'Abcd@123' REPLACE 'abcd@123';  
NOTICE: The encrypted password contains MD5 ciphertext, which is not secure.  
ALTER ROLE  

–为用户jim增加CREATEROLE权限

omm=# ALTER USER jim CREATEROLE;  
ALTER ROLE  

–查看用户

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

3.授权
–将用户jim的权限授权给用户kim

omm=# GRANT jim to kim;  
GRANT ROLE  
  

–将sysadmin权限授权给用户dim

omm=# GRANT ALL PRIVILEGES TO dim;  
ALTER ROLE  

–重命名用户dim

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 |  | {jim}  
omm     | Sysadmin, Create role, Create DB, Replication, Administer audit, Monitoradmin, Operatoradmin, Policyadmin,UseFT | {}  
    tim | Create DB, Sysadmin  | {}  

4.回收权限
–撤消kim的权限

omm=# REVOKE jim FROM kim;  
REVOKE ROLE  

–撤消用户dim的sysadmin权限

omm=# revoke all privilege 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, Operatoradmin,  
Policyadmin, UseFT | {}  
tim | Create DB  
| {}  
  

5.删除用户

omm=# drop user tim;  
DROP ROLE  
omm=# drop user jim;  
DROP ROLE  
omm=# drop user kim;  
DROP ROLE  

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

omm=# create user user1 createrole password 'asdf@1234';  
NOTICE: The encrypted password contains MD5 ciphertext, which is not secure.  
CREATE ROLE  
omm=# create user user2 createdb identified by 'admin@110.com';  
NOTICE: The encrypted password contains MD5 ciphertext, which is not secure.  
CREATE ROLE  
omm=# create user user3 password 'ppaaws@123';  
NOTICE: The encrypted password contains MD5 ciphertext, which is not secure.  
CREATE ROLE  

2.修改用户user1的密码

omm=# alter user user1 identified by 'yes&no1234' replace 'asdf@1234';  
NOTICE: The encrypted password contains MD5 ciphertext, which is not secure.  
ALTER ROLE  

3.重命名用户user2

omm=# alter user user2 rename to user22;  
NOTICE: MD5 password cleared because of role rename  
omm=# ALTER ROLE  

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

omm=# grant user1 to user3;  
GRANT ROLE  
omm=# revoke all privileges from user3  
omm-# ;  
ALTER ROLEomm=# \du  
List of roles  
Role name | Attributes  
| Member of  
\-----------+-----------------------------------------------------------------------------------------------  
\-------------------+-----------  
gaussdb | Sysadmin  
| {}  
omm | Sysadmin, Create role, Create DB, Replication, Administer audit, Monitoradmin, Operatoradmin,  
Policyadmin, UseFT | {}  
user1 | Create role  
| {}  
user22 | Create DB  
| {}  
user3 |  
| {user1}  
  
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 | {} |  
user1 | Create role  
| {} |  
user22 | Create DB  
| {} |  
user3 |  
| {user1} |  

5.删除所有创建用户

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