openGauss每日一练第19天
今日目标：openGauss的用户和角色管理。
使用create user创建的用户与使用create role创建的用户的区别在于，前者可以直接连接登录数据库，而使用create role创建的用户不能直接登录到数据库。必须添加LOGIN权限后，才能登录到数据库管理系统。
删除用户，首先需要将用户拥有的数据库对象转移或者删除。
查看用户
\du
删除用户
drop user <user_name>
删除用户，同时会删除同名的schema。
omm=# \dn
List of schemas
Name | Owner
-----------------+-------
blockchain | omm
cstore | omm
db4ai | omm
dbe_perf | omm
dbe_pldebugger | omm
dbe_pldeveloper | omm
pkg_service | omm
public | omm
snapshot | omm
sqladvisor | omm
(10 rows)
omm=# create user test identified by 'gauss@1234';
NOTICE: The encrypted password contains MD5 ciphertext, which is not secure.
CREATE ROLE
omm=# \dn
List of schemas
Name | Owner
-----------------+-------
blockchain | omm
cstore | omm
db4ai | omm
dbe_perf | omm
dbe_pldebugger | omm
dbe_pldeveloper | omm
pkg_service | omm
public | omm
snapshot | omm
sqladvisor | omm
test | test
(11 rows)
omm=# drop user test ;
DROP ROLE
omm=# \dn
List of schemas
Name | Owner
-----------------+-------
blockchain | omm
cstore | omm
db4ai | omm
dbe_perf | omm
dbe_pldebugger | omm
dbe_pldeveloper | omm
pkg_service | omm
public | omm
snapshot | omm
sqladvisor | omm
(10 rows)
omm=#
REASSIGN OWNED
修改数据库对象的属主。REASSIGN OWNED要求系统将所有old_roles拥有的数据库对象的属主更改为new_role
REASSIGN OWNED BY old_role [, …] TO new_role;
-
REASSIGN OWNED常用于在删除角色之前的准备工作。
-
执行REASSIGN OWNED需要有原角色和目标角色上的权限。
REVOKE
REVOKE用于撤销一个或多个角色的权限。
2.课后作业
2.1 创建test10_tbs的表空间，在这个表空间中创建数据库testdb10
omm=# create tablespace test10_tbs relative location 'tablesapce/test10_tbs';
CREATE TABLESPACE
omm=# create database testdb10;
CREATE DATABASE
omm=# \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
testdb | user1 | UTF8 | C | C | =Tc/user1 +
| | | | | user1=CTc/user1 +
| | | | | user1=APm/user1
testdb10 | omm | UTF8 | C | C |
(6 rows)
omm=# \db
List of tablespaces
Name | Owner | Location
omm=# ------------+-------+-----------------------
pg_default | omm |
pg_global | omm |
test10_tbs | omm | tablesapce/test10_tbs
test_tbs | user1 | tablespace/test_tbs1
test_tbs2 | user1 | tablespace/test_tbs2
(5 rows)
2.2 使用create user创建用户user10，登录数据库testdb10，创建测试表t1和t2
omm=# create user user10 identified by 'gauss@1234';
NOTICE: The encrypted password contains MD5 ciphertext, which is not secure.
CREATE 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=> create table t1(col int);
ERROR: permission denied for schema public
DETAIL: N/A
testdb10=> \q
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=# grant all on DATABASE testdb10 TO user10 ;
GRANT
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=> create table t1(col int);
ERROR: permission denied for schema public
DETAIL: N/A
testdb10=> \q
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=# grant all privileges to user10;
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=> create table t1(col int);
CREATE TABLE
testdb10=> create table t2(col int);
CREATE TABLE
2.3 使用create role创建角色role10，登录数据库testdb10
使用create role创建的用户不能直接登录到数据库。必须添加LOGIN权限后，才能登录到数据库管理系统。
testdb10=> create role role10 identified by 'gauss@1234';
NOTICE: The encrypted password contains MD5 ciphertext, which is not secure.
CREATE ROLE
testdb10=> \du
gaussdb | Sysadmin
| {}
omm | Sysadmin, Create role, Create DB, Replication, Administer audit, Monitoradmin, Operatoradmin, Policyadmin, UseFT
| {}
role10 | Cannot login
| {}
user10 | Sysadmin
| {}
List of roles
Role name | Attributes
| Member of
-----------+------------------------------------------------------------------------------------------------------------------
+-----------
testdb10=> alter user role10 login ;
ALTER ROLE
testdb10=> \du
List of roles
Role name | Attributes
| Member of
-----------+------------------------------------------------------------------------------------------------------------------
+-----------
gaussdb | Sysadmin
| {}
omm | Sysadmin, Create role, Create DB, Replication, Administer audit, Monitoradmin, Operatoradmin, Policyadmin, UseFT
| {}
role10 |
| {}
user10 | Sysadmin
| {}
testdb10=> \q
omm@modb:~$ gsql -d testdb10 -U role10 -W gauss@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=>
2.4.将表t1直接删除，将前面创建的表空间和数据库、表t2转给role10，删除用户user10
-- 删除用户，首先需要将用户拥有的数据库对象转移或者删除。
omm=# drop user user10 ;
ERROR: role "user10" cannot be dropped because some objects depend on it
DETAIL: privileges for database testdb10
2 objects in database testdb10
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 ;
DROP TABLE
-- 将user10拥有的数据库对象转移
testdb10=> reassign owned by user10 to role10 ;
REASSIGN OWNED
testdb10=> drop user user10 ;
ERROR: current user cannot be dropped
testdb10=> \testdb10 ommtestdb10=>
Invalid command \testdb10. Try \? for help.
testdb10=> \q
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=# drop user user10 ;
ERROR: role "user10" cannot be dropped because some objects depend on it
DETAIL: privileges for database testdb10
omm=# revoke all on DATABASE testdb10 from user10;
REVOKE
omm=# drop user user10 ;
DROP ROLE
omm=#
2.5.最后删除role10
omm=# \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=> \dt
List of relations
Schema | Name | Type | Owner | Storage
--------+------+-------+--------+----------------------------------
public | t2 | table | role10 | {orientation=row,compression=no}
(1 row)
testdb10=> \db
pg_global | |
test10_tbs | |
(3 rows)
testdb10=> List of tablespaces
Name | Owner | Location
------------+-------+----------
pg_default | |
testdb10=> drop table t2 ;
DROP TABLE
testdb10=> revoke all on DATABASE testdb10 from role10;
WARNING: no privileges could be revoked for "testdb10"
REVOKE
testdb10=> drop user role10;
ERROR: Permission denied to drop role.
testdb10=> \q
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=# drop user role10 ;
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=#
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
