学习目标
掌握openGauss的用户和角色管理。
课程作业
1、创建test10_tbs的表空间，在这个表空间中创建数据库testdb10
omm=# CREATE TABLESPACE test10_tbs RELATIVE LOCATION 'tablespace/test10_tbs1';
CREATE TABLESPACE
omm=# CREATE DATABASE testdb10 WITH TABLESPACE = test10_tbs;
CREATE DATABASE
omm=#
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=>
创建测试表t1和t2（初次创建失败，需要提权，提权后创建成功）
testdb10=> create table t1(id int);
ERROR: permission denied for schema public
DETAIL: N/A
testdb10=>
登录omm给user10提权。
omm=# ALTER USER user10 SYSADMIN;
ALTER ROLE
提权后测试创建成功。
testdb10=> create table t1(id int);
CREATE TABLE
testdb10=> create table t2(id int);
CREATE TABLE
testdb10=>
3、使用create role创建角色role10，登录数据库testdb10
create role 创建role10。
omm@modb:~$ gsql -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.
omm=# CREATE ROLE role10 IDENTIFIED BY 'kunpeng@1234';
NOTICE: The encrypted password contains MD5 ciphertext, which is not secure.
CREATE ROLE
omm=# GRANT ALL ON DATABASE testdb TO role10;
GRANT
omm=# \du
List of roles
Role name | Attributes
| Member of
-----------+-------------------------------------------------------------------------
-----------------------------------------+-----------
gaussdb | Sysadmin
| {}
omm | Sysadmin, Create role, Create DB, Replication, Administer audit, Monitor
admin, Operatoradmin, Policyadmin, UseFT | {}
role10 | Cannot login
| {}
test | Sysadmin
| {}
user10 | Sysadmin
| {}
user2 |
| {}
user1 |
| {}
omm=# \q
使用role10登录testdb10(失败)
原因：由于数据库用户role10 是使用create role语句创建的，目前还不被允许登录到openGauss数据库管理系统。
omm@modb:~$ gsql -d testdb10 -U role10 -W kunpeng@1234 -r
gsql: FATAL: role "role10" is not permitted to login
omm@modb:~$
对用户role10授予登录权限，可以登录数据库
omm@modb:~$ gsql -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.
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, Monitor
admin, Operatoradmin, Policyadmin, UseFT | {}
role10 |
| {}
test | Sysadmin
| {}
user1 |
| {}
user10 | Sysadmin
| {}
user2 |
| {}
omm=# \q
omm@modb:~$ gsql -d testdb10 -U role10 -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=>
4、将表t1直接删除，将前面创建的表空间和数据库、表t2转给role10，删除用户user10
删除表t1：
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=> drop table t1;
testdb10=> DROP TABLE
testdb10=> \q
将test10_tbs表空间、testdb10数据库、测试表t2 转给role10
omm@modb:~$ gsql -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.
omm=# alter database testdb10 owner to role10;
ALTER DATABASE
omm=# alter tablespace test10_tbs owner to role10;
ALTER TABLESPACE
omm=# REASSIGN OWNED BY user10 to role10;
REASSIGN OWNED
回收用户user2对testdb数据库的权限：
omm=# REVOKE ALL ON DATABASE testdb10 FROM user10;
REVOKE
使用user10用户进入testdb10，将测试表t2 转移至role10
testdb10=> alter table t2 owner to role10;
ALTER TABLE
omm=#
删除user10并验证。
omm=# drop user user10;
DROP ROLE
omm=# \du
List of roles
Role name | Attributes
| Member of
-----------+-------------------------------------------------------------------------
-----------------------------------------+-----------
gaussdb | Sysadmin
| {}
omm | Sysadmin, Create role, Create DB, Replication, Administer audit, Monitor
admin, Operatoradmin, Policyadmin, UseFT | {}
role10 |
| {}
test | Sysadmin
| {}
user1 |
| {}
omm=#
5、最后删除role10
直接删除role10，会提示有数据库、表空间等。无法删除
omm=# drop user role10;
ERROR: role "role10" cannot be dropped because some objects depend on it
DETAIL: owner of schema user10
owner of tablespace test10_tbs
owner of database testdb10
privileges for database testdb
1 object in database testdb10
omm=#
首先删除数据库、表空间等。然后删除role10
omm=# drop database testdb10;
DROP DATABASE
omm=# drop tablespace test10_tbs;
DROP TABLESPACE
omm=# drop user role10;
DROP ROLE
omm=#
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
