暂无图片
暂无图片
暂无图片
暂无图片
2
暂无图片

openGauss每日一练第19天

原创 周周 2022-12-12
580

学习目标

掌握openGauss的用户和角色管理。

课程学习

使用create user创建的用户与使用create role创建的用户的区别在于,前者可以直接连接登录数据库,而使用create role创建的用户不能直接登录到数据库。必须添加LOGIN权限后,才能登录到数据库管理系统。

删除用户,首先需要将用户拥有的数据库对象转移或者删除。

1.环境准备

创建一个名叫test_tbs的表空间和一个名叫testdb的数据库:

gsql  -r

CREATE TABLESPACE test_tbs RELATIVE LOCATION ‘tablespace/test_tbs1’;

CREATE DATABASE testdb WITH TABLESPACE = test_tbs;

2.使用create user创建用户

使用create user创建的用户,具有登录权限。

–创建一个名叫user1的数据库用户,其密码为kunpeng@1234,并将数据库testdb所有的权限都授予用户user1:

CREATE USER user1 IDENTIFIED BY ‘kunpeng@1234’;

GRANT ALL ON DATABASE testdb TO user1;

–执行下面的gsql元命令,查看系统目前有哪些数据库:

\l

–执行下面的gsql元命令,查看系统目前有哪些用户:

\du

\q

–用刚刚创建的数据库用户user1登录到数据库testdb

gsql -d testdb   -U user1   -W kunpeng@1234 -r

\q

3.使用create role创建用户

使用create role命令创建的用户user2,没有登录权限。

–创建一个新的名叫user2的角色,并将数据库testdb所有的权限都授予用户user2:

用户user2对数据库testdb具有的权限和用户user1一模一样。

gsql -r

CREATE ROLE user2 IDENTIFIED BY ‘kunpeng@1234’;

GRANT ALL ON DATABASE testdb TO user2;

–查看当前openGauss数据库集群由哪些用户:

\du

\q

–使用用户user2尝试登录到数据库testdb:

gsql -d testdb  -U user2  -W kunpeng@1234 -r

gsql: FATAL:  role “user2” is not permitted to login

原因:

由于数据库用户user2是使用create role语句创建的,目前还不被允许登录到openGauss数据库管理系统。

4.授予用户user2登录权限后:

授予用户user2登录权限后,可以登录数据库

gsql    -r

alter user user2 LOGIN;

\du

\q

–现在我们已经授予了user2用户登录数据库的权限,可以正常登录数据库testdb:

gsql -d testdb   -U user2   -W kunpeng@1234 -r

\q

删除用户和角色

删除用户,首先需要将用户拥有的数据库对象转移或者删除,回收权限。

– 授予用户user2数据库超级用户的权限:

gsql -r

ALTER USER user2 SYSADMIN;

–使用用户user2登录到数据库testdb,创建表空间test_tbs1、数据库testdb、表test1和表test2:

\c testdb user2

CREATE TABLESPACE test_tbs1 RELATIVE LOCATION ‘tablespace/test_tbs11’;

CREATE DATABASE testdb WITH TABLESPACE = test_tbs1;

CREATE TABLE test1(col int);

CREATE TABLE test2(col int);

\q

–执行如下的命令,删除用户user2:

gsql -r

drop user user2;

ERROR:  role “user2” cannot be dropped because some objects depend on it

DETAIL:  owner of database testdb

owner of tablespace test_tbs1

privileges for database testdb

2 objects in database testdb

###可以看出不能删除用户user2的原因是:

1)用户user2拥有数据库testdb。

2)用户user2拥有表空间对象test_tbs1。

3)用户user2对数据库testdb具有权限。

4)用户user2在数据库testdb中有两个对象。

–要删除用户user2,必须先表空间对象和数据库对象的属主修改为其他的用户(如user1用户),或者干脆将其删除:

alter database testdb owner to user1;

alter tablespace test_tbs1 owner to user1;

–回收用户user2对testdb数据库的权限:

REVOKE ALL ON DATABASE testdb FROM user2;

–在testdb中属于用户user2的数据库对象的处理方法可以是:假如该对象还有用,可以将该对象转移给其他用户;假如该对象没有用,可以直接删除掉该对象

–假设表test1已经没有用了,我们可以删除表test1:

\c testdb user2

drop table test1;

–假设表test2还有用将表test2转移给用户user1:

REASSIGN OWNED BY user2 to user1;

\dt

\q

–执行删除用户的操作,可以删除user2用户

gsql -r

drop user user2;

\du

课程作业

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


su - omm

gsql -r

CREATE TABLESPACE test10\_tbs RELATIVE LOCATION 'tablespace/test10\_tbs1';

CREATE DATABASE testdb10 WITH TABLESPACE = test10\_tbs;

\\l

\\db

root@modb:~# su - omm

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=# \\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

(4 rows)



omm=# CREATE TABLESPACE test10\_tbs RELATIVE LOCATION 'tablespace/test10\_tbs1';

CREATE TABLESPACE

omm=# CREATE DATABASE testdb10 WITH TABLESPACE = test10\_tbs;

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

 testdb10  | omm   | UTF8     | C       | C     | 

(5 rows)



omm=# \\db

             List of tablespaces

    Name    | Owner |        Location        

\------------+-------+------------------------

 pg\_default | omm   | 

 pg\_global  | omm   | 

 test10\_tbs | omm   | tablespace/test10\_tbs1

(3 rows)



复制

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


CREATE USER user10 IDENTIFIED BY 'mygstest@1234';

GRANT ALL ON DATABASE testdb10 TO user10;

ALTER USER user10 SYSADMIN;

\\q

gsql -d testdb10 -U user10 -W mygstest@1234 -r

create table t1(product\_id INTEGER,product\_name Char(20),category Char(30));

insert into t1 values(1502,'olympus camera','electrncs'),(1601,'lamaze','toys'),(1700,'wait interface','Books'),(1666,'harry potter','toys');

select \* from t1;

create table t2(product\_id INTEGER,product\_name Char(20),category Char(30));

insert into t2 values(1502,'olympus camera','electrncs'),(1601,'lamaze','toys'),(1700,'wait interface','Books'),(1666,'harry potter','toys');

select \* from t2;

omm=# CREATE USER user10 IDENTIFIED BY 'mygstest@1234';

NOTICE:  The encrypted password contains MD5 ciphertext, which is not secure.

omm=# CREATE ROLE

omm=# GRANT ALL ON DATABASE testdb10 TO user10;

GRANT

omm=# ALTER USER user10 SYSADMIN;

ALTER ROLE

omm=# \\q

omm@modb:~$ gsql -d testdb10   -U user10   -W mygstest@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=> create table t1(product\_id INTEGER,product\_name Char(20),category Char(30));

CREATE TABLE

testdb10=> create table t2(product\_id INTEGER,product\_name Char(20),category Char(30));

CREATE TABLE

testdb10=> insert into t1 values(1502,'olympus camera','electrncs'),(1601,'lamaze','toys'),(1700,'wait interface','Books'),(1666,'harry potter','toys');

INSERT 0 4

testdb10=> insert into t2 values(1502,'olympus camera','electrncs'),(1601,'lamaze','toys'),(1700,'wait interface','Books'),(1666,'harry potter','toys');

testdb10=> INSERT 0 4

testdb10=> 

select \* from t1;

 product\_id |     product\_name     |            category            

\------------+----------------------+--------------------------------

       1502 | olympus camera       | electrncs                     

       1601 | lamaze               | toys                          

       1700 | wait interface       | Books                         

       1666 | harry potter         | toys                          

(4 rows)



testdb10=> select \* from t2;

 product\_id |     product\_name     |            category            

\------------+----------------------+--------------------------------

       1502 | olympus camera       | electrncs                     

       1601 | lamaze               | toys                          

       1700 | wait interface       | Books                         

       1666 | harry potter         | toys                          

(4 rows)



testdb10=> 

复制

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


gsql -r

CREATE ROLE role10 IDENTIFIED BY 'mygstest@1234';

GRANT ALL ON DATABASE testdb10 TO role10;

\--使用用户role10尝试登录到数据库testdb10:

gsql -d testdb10  -U role10  -W mygstest@1234 -r

\\q

gsql -r 

alter user role10 LOGIN;

\\du

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 'mygstest@1234';

NOTICE:  The encrypted password contains MD5 ciphertext, which is not secure.

CREATE ROLE

omm=# GRANT ALL ON DATABASE testdb10 TO role10;

GRANT

omm=# \\q

omm@modb:~$ gsql -d testdb10  -U role10  -W mygstest@1234 -r

gsql: FATAL:  role "role10" is not permitted to login

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=# \\q

omm@modb:~$ gsql -d testdb10  -U role10  -W mygstest@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=> \\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=# \\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                                                                                                         

| {}


复制

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


gsql -d testdb10  -U role10  -W mygstest@1234 -r

drop table t1;

REASSIGN OWNED BY user10 to role10;

\--要删除用户role10,必须先表空间对象和数据库对象的属主修改为其他的用户(如user10用户),或者干脆将其删除:

alter database testdb10 owner to role10;

alter tablespace test10\_tbs owner to role10;

\--回收用户user10对testdb10数据库的权限:

REVOKE ALL ON DATABASE testdb10 FROM user10;

drop user user10;

omm@modb:~$ gsql -d testdb10  -U role10  -W mygstest@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=> drop table t1;

DROP TABLE

testdb10=>\\dt

                         List of relations

 Schema | Name | Type  | Owner  |             Storage              

\--------+------+-------+--------+----------------------------------

 public | t2   | table | user10 | {orientation=row,compression=no}

(1 row)



testdb10=> REASSIGN OWNED BY user10 to role10;

REASSIGN OWNED

testdb10=> \\dt

                         List of relations

 Schema | Name | Type  | Owner  |             Storage              

\--------+------+-------+--------+----------------------------------

 public | t2   | table | role10 | {orientation=row,compression=no}

(1 row)

omm=# \\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=# alter database testdb10 owner to role10;

ALTER DATABASE

omm=# alter tablespace test10\_tbs owner to role10;

ALTER TABLESPACE

omm=# REVOKE ALL ON DATABASE testdb10 FROM user10;

REVOKE

omm=# drop user user10;

DROP ROLE

复制

5、最后删除role10。


su - omm

gsql -r

drop database testdb10;

drop tablespace test10\_tbs;

drop role role10;

root@modb:~# su - omm

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.



Invalid command \\su. Try \\? for help.

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 

| {}

 role10    | Sysadmin                                                                                                         

| {}



omm=# drop role role10;

ERROR:  role "role10" cannot be dropped because some objects depend on it

DETAIL:  owner of tablespace test10\_tbs

owner of database testdb10

1 object in database testdb10

omm=# drop database testdb10;

DROP DATABASE

omm=# drop tablespace test10\_tbs;

DROP TABLESPACE

omm=# drop role role10;

DROP ROLE
复制
最后修改时间:2022-12-14 16:08:26
「喜欢这篇文章,您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容,转载时必须标注文章的来源(墨天轮),文章链接,文章作者等基本信息,否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容,欢迎发送邮件至:contact@modb.pro进行举报,并提供相关证据,一经查实,墨天轮将立刻删除相关内容。

评论

墨天轮福利君
暂无图片
2年前
评论
暂无图片 0
您的sql语句要用markdown来编辑哦
2年前
暂无图片 点赞
1
周小周
暂无图片
2年前
回复
暂无图片 0
谢谢啦👍
2年前
暂无图片 点赞
回复