openGauss每日一练第19天-用户和角色管理

1、创建test10_tbs的表空间，在这个表空间中创建数据库testdb10

omm=# CREATE TABLESPACE test_tbs10 RELATIVE LOCATION 'tablespace/test_tbs10';
omm=# CREATE TABLESPACE

omm=# CREATE DATABASE testdb10 WITH TABLESPACE = test_tbs10;
CREATE DATABASE

2、使用create user创建用户user10，登录数据库testdb10，创建测试表t1和t2

omm=# create user user10 identified by 'kunpeng@1234';
NOTICE:  The encrypted password contains MD5 ciphertext, which is not secure.
CREATE ROLE
omm=# grant all on database testdb10 to user10;
GRANT
omm=# \q
omm@modb:~$ gsql -d testdb10 -U user10 -W kunpeng@1234 -r
gsql ((openGauss 3.0.0 build 02c14696) compiled at 2022-04-01 18:12:00 commit 0 last mr  )
Non-SSL connection (SSL connection is recommended when requiring high-security)
Type "help" for help.
testdb10=>
testdb10=> create schema user10;
CREATE SCHEMA
testdb10=> create table user10.t1(c1 int);
testdb10=> CREATE TABLE

testdb10=> create table user10.t2(c1 int);
testdb10=> CREATE TABLE

3、使用create role创建角色role10，登录数据库testdb10

omm=# create role role10 identified by 'kunpeng@1234';
NOTICE:  The encrypted password contains MD5 ciphertext, which is not secure.
omm=# CREATE ROLE
mm=# \du
                                                              List of roles
 Role name |                                                    Attributes                                                  
  | Member of 
-----------+----------------------------------------------------------------------------------------------------------------
--+-----------
 gaussdb   | Sysadmin                                                                                                       
  | {}
 omm       | Sysadmin, Create role, Create DB, Replication, Administer audit, Monitoradmin, Operatoradmin, Policyadmin, UseF
T | {}
 role10    | Cannot login                                                                                                   
  | {}
 user10    |                                                                                                                
  | {}

omm=# alter user role10 login;
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, UseF
T | {}
 role10    |                                                                                                                
  | {}
 user10    |                                                                                                                
  | {}
omm@modb:~$ gsql -d testdb10 -U role10 -W kunpeng@1234
gsql ((openGauss 3.0.0 build 02c14696) compiled at 2022-04-01 18:12:00 commit 0 last mr  )
Non-SSL connection (SSL connection is recommended when requiring high-security)
Type "help" for help.

testdb10=> 

4、将表t1直接删除，将前面创建的表空间和数据库、表t2转给role10，删除用户user10

omm=# alter user role10 sysadmin;
ALTER ROLE
omm=# drop table user10.t1;
ERROR:  table "t1" does not exist
omm=# drop table testdb10.user10.t1;
ERROR:  cross-database references are not implemented: "testdb10.user10.t1"
omm=# \q
omm@modb:~$ gsql -d testdb10 -U role1 -W kunpeng@123 -W kunpeng@123 -W kunpeng@123 -W kunpeng@1234^C
omm@modb:~$ gsql -d testdb10 -U user10 -W kunpeng@1234
gsql ((openGauss 3.0.0 build 02c14696) compiled at 2022-04-01 18:12:00 commit 0 last mr  )
Non-SSL connection (SSL connection is recommended when requiring high-security)
Type "help" for help.

testdb10=> drop table user10.t1;
DROP TABLE

omm=# alter database testdb10 owner to role10;
ALTER DATABASE
omm=# alter tablespace test_tbs10 owner to role10;
ALTER TABLESPACE
omm=# revoke all on database testdb10 from user10;
REVOKE
omm=# reassign owned by user10 to role10;
REASSIGN OWNED

5、最后删除role10

omm=# drop user role10;
DROP ROLE