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

原创洋 2022-12-12

421

0.学习内容与环境进入

掌握openGauss的用户和角色管理。
使用create user创建的用户与使用create role创建的用户的区别在于，前者可以直接连接登录数据库，而使用create role创建的用户不能直接登录到数据库。必须添加LOGIN权限后，才能登录到数据库管理系统。
删除用户，首先需要将用户拥有的数据库对象转移或者删除。

转移权限
reassign OWNED BY <userA> to <userB>
消去权限
REVOKE <auth> FROM <user>;
进入环境

su - omm
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.

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

创建test10_tbs的表空间

DROP TABLESPACE if exists test10_tbs;
CREATE TABLESPACE test10_tbs RELATIVE LOCATION 'tablespace/test10';

NOTICE: Tablespace "test10_tbs" does not exist, skipping.
DROP TABLESPACE
CREATE TABLESPACE

创建数据库testdb10

CREATE DATABASE testdb10 WITH TABLESPACE = test10_tbs;

CREATE DATABASE

查看全部数据库

\l

List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+-------+----------+---------+-------+-------------------
omm | omm | UTF8 | C | C |
postgres | omm | UTF8 | C | C |
template0 | omm | UTF8 | C | C | =c/omm +
| | | | | omm=CTc/omm
template1 | omm | UTF8 | C | C | =c/omm +
| | | | | omm=CTc/omm
testdb10 | omm | UTF8 | C | C |
(5 rows)

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

创建用户user10并赋权限

CREATE USER user10 IDENTIFIED BY 'usr@1234';
ALTER USER user10 SYSADMIN;

CREATE ROLE
ALTER ROLE

登录数据库testdb10，创建测试表t1和t2

\c testdb10 user10
CREATE TABLE t1(id integer,  col2 char(30));
INSERT into t1 values(1, 't1 testdata');
CREATE TABLE t2(id integer, col2 char(30));
INSERT into t2 values(1, 't2 testdata');

Non-SSL connection (SSL connection is recommended when requiring high-security)
You are now connected to database "testdb10" as user "user10".
CREATE TABLE
INSERT 0 1
CREATE TABLE
INSERT 0 1

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

CREATE role role10 IDENTIFIED BY 'usr@4321';
\c testdb10 role10

FATAL: role "role10" is not permitted to login
Previous connection kept

alter role role10 LOGIN;
\c testdb10 role10

Non-SSL connection (SSL connection is recommended when requiring high-security)
You are now connected to database "testdb10" as user "role10".

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

删除表t1
依次转移 table , database, tablespace给role10
删除user10

drop table t1;
alter table t2 owner to role10;
alter database testdb10 owner to role10;
alter tablespace test10_tbs owner to role10;
REASSIGN OWNED BY USER10 to ROLE10;
\c testdb10 role10
drop user user10;

DROP TABLE
ALTER TABLE
ALTER DATABASE
ALTER TABLESPACE
ERROR: current user cannot be dropped

Non-SSL connection (SSL connection is recommended when requiring high-security)
You are now connected to database "testdb10" as user "role10".
DROP ROLE

5、最后删除role10

先转移数据，然后才能删除

\c omm omm
alter table t2 owner to omm;
alter database testdb10 owner to omm;
alter tablespace test10_tbs owner to omm;
drop role role10;

DROP ROLE

「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」

关注作者

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