openGauss每日一练第 5 天 |创建用户、修改用户属性、更改用户权限和删除用户

学习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.创建用户user1、user2和user3，user1具有CREATEROLE权限，user2具有CREATEDB权限，要求使用两种不同的方法设置密码

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 | {}


omm=# create user user1 createrole identified by 'user_123';
NOTICE: The encrypted password contains MD5 ciphertext, which is not secure.
CREATE ROLE

omm=# create user user2 createdb password 'user_456' ;
NOTICE: The encrypted password contains MD5 ciphertext, which is not secure.
CREATE ROLE
omm=# create user user3 identified by 'user_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 | {}
|
user2 | Create DB | {}
|
user3 | | {}
|
user1 | Create role | {}
|



2.修改用户user1的密码

omm=# alter user user1 password 'user_1234';
NOTICE: The encrypted password contains MD5 ciphertext, which is not secure.
ALTER ROLE
omm=#

3.重命名用户user2

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

omm=# \du
user1 | Create role | {}
user22 | Create DB | {}
user3 | | {}

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


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

omm=# grant user1 to user3 ;
GRANT ROLE
omm=# revoke user1 from user3 ;
REVOKE ROLE
omm=# \du
gaussdb | Sysadmin | {}
omm | Sysadmin, Create role, Create DB, Replication, Administer audit, Monitoradmin, Operatoradmin, Policyadmin, UseFT | {}
user1 | Create role | {}
user22 | Create DB | {}
user3 | | {}

List of roles
Role name | Attributes | Member
of
-----------+------------------------------------------------------------------------------------------------------------------+-------
----
omm=#



5.删除所有创建用户
omm=# drop user user1 ;
DROP ROLE
omm=# drop user user2 ;
ERROR: role "user2" does not exist
omm=# drop user user22 ;
DROP ROLE
omm=# drop user user3 ;
DROP 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 | {}

omm=#