openGauss

day5-学习openGauss创建用户、修改用户属性、更改用户权限和删除用户

学习内容

1、创建用户

create user 名字 (属性) password '';
create user 名字 (属性) identified by '';

2、修改用户信息

就是把 create 改成 alter ，名字后面加上修改的属性、密码等
alter user 名字 (属性) ......;
alter user 名字 rename to 新名字;

3、授权

–将用户jim的权限授权给用户kim
GRANT jim to kim;
–撤消kim的权限
REVOKE jim FROM kim;

–将sysadmin权限授权给用户dim
GRANT ALL PRIVILEGES TO dim;
–撤消用户dim的sysadmin权限
revoke all privilege from tim;

4、删除用户

drop user user1;
drop user user2,user3;

作业内容

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

create user user1 createrole password 'nicPasswedro123d';
create user user2 createdb identified by 'nicpdssa122dWr';
create user user3 password 'ncdsrDwerv3789xzad';

效果

omm=# create user user1 createrole password 'nicPasswedro123d';
NOTICE:  The encrypted password contains MD5 ciphertext, which is not secure.
CREATE ROLE
omm=# create user user2 createdb identified by 'nicpdssa122dWr';
NOTICE:  The encrypted password contains MD5 ciphertext, which is not secure.
CREATE ROLE
omm=# create user user3 password 'ncdsrDwerv3789xzad';
NOTICE:  The encrypted password contains MD5 ciphertext, which is not secure.
CREATE ROLE
omm=# 

2.修改用户user1的密码

alter user user1 identified by 'sadfiuDSF342B';

效果

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

3.重命名用户user2

alter user user2 rename to newuser2;

效果

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 | {}
 user1     | Create role                                                                                                      | {}
 user2     | Create DB                                                                                                        | {}
 user3     |                                                                                                                  | {}

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

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

grant user1 to user3;
revoke user1 from user3;

效果 *注意观察member of 列的变化

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


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

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

omm=# 

5.删除所有创建用户
过程中使用\du或\du+查看用户信息

drop user user1;
drop user newuser2,user3;

效果

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

omm=# drop user user1;
DROP ROLE
omm=# \du
                                                              List of roles
 Role name |                                                    Attributes                                                    | Member of 
-----------+------------------------------------------------------------------------------------------------------------------+-----------
 gaussdb   | Sysadmin                                                                                                         | {}
 newuser2  | Create DB                                                                                                        | {}
 omm       | Sysadmin, Create role, Create DB, Replication, Administer audit, Monitoradmin, Operatoradmin, Policyadmin, UseFT | {}
 user3     |                                                                                                                  | {}

omm=# drop user newuser2,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=#