openGauss每日一练第19天

学习用户和角色管理

学习用户和角色管理，理解用户和角色的关系，完成课后作业。

创建test10_tbs的表空间

在omm用户下使用gsql -r连接数据库。

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

在test10_tbs中创建数据库testdb10

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

创建用户user10，登录数据库testdb10

创建user10并赋予权限

omm=# CREATE USER user10 IDENTIFIED BY 'ttt@1234';
NOTICE:  The encrypted password contains MD5 ciphertext, which is not secure.
CREATE ROLE
omm=# ALTER USER user10 SYSADMIN;
ALTER ROLE
omm=# \c testdb10 user10
Password for user user10: 
Non-SSL connection (SSL connection is recommended when requiring high-security)
You are now connected to database "testdb10" as user "user10".
testdb10=> 

创建测试表t1和t2

testdb10=> create table t1(name char(10));
CREATE TABLE
testdb10=> create table t2(name char(10));
CREATE TABLE

创建角色role10，登录数据库testdb10

创建role10后需要给予Login权限，才能登录。

testdb10=> CREATE ROLE role10 IDENTIFIED BY 'ttt@1234';
NOTICE:  The encrypted password contains MD5 ciphertext, which is not secure.
CREATE ROLE
testdb10=> alter user role10 LOGIN;
ALTER ROLE
testdb10=> \c testdb10 role10
Password for user role10: 
Non-SSL connection (SSL connection is recommended when requiring high-security)
You are now connected to database "testdb10" as user "role10".
testdb10=> 

将表t1删除，将test10_tbs、testdb10和t2传给role10，删除用户user10

使用omm用户连接testdb10数据库

testdb10=> \c testdb10 omm
Non-SSL connection (SSL connection is recommended when requiring high-security)
You are now connected to database "testdb10" as user "omm".

删除t1表

testdb10=# drop table t1;
DROP TABLE

将test10_tbs、testdb10和t2传给role10

testdb10=# alter database testdb10 owner to role10;
ALTER DATABASE
testdb10=# alter tablespace test10_tbs owner to role10;
ALTER TABLESPACE
testdb10=# alter table t2 owner to role10;
ALTER TABLE

回收user10权限并删除user10

testdb10=# REVOKE ALL ON DATABASE testdb10 FROM user10;
REVOKE
testdb10=# drop user user10;
DROP ROLE

删除role10

我们需要删除与role10相关的对象才能删除role10，首先删除testdb10数据库和test10_tbs表空间，最后就可以删除role10了

omm=# drop database testdb10;
DROP DATABASE
omm=# drop tablespace test10_tbs;
DROP TABLESPACE
omm=# drop user role10;
DROP ROLE