ANBOB™

在openGauss 数据库中如果存在多个用户如A和B， 希望B用户可以在用户A的同名schema下创建table对象，需要授权给用户B，在oracle中如create any table的系统权限或者是user Proxy 高级用法，在我之前的笔记Oracle 23c 几个开发相关新特性中,在oracle 23c 才引入grant xx  ON SCHEMA xx to xx的语法，就是这样的功能在openGauss中有不同的用法。今天在一个项目遇到了这个问题，下面演示一下这个情况。

openGauss V6

创建2个用户

openGauss=# create user anbob identified by "www.anbob.com";

openGauss=# grant Sysadmin to anbob;

openGauss=# create user weejar  identified by "weejar";
ERROR:  Password must contain at least 8 characters.

openGauss=# create user weejar  identified by "6.anbob.com";
NOTICE:  The encrypted password contains MD5 ciphertext, which is not secure.
CREATE ROLE

授权 usage on schema

openGauss=# grant usage on schema anbob to weejar;
GRANT

weejar尝试创建anbob下的表

openGauss=# \c - weejar
Password for user weejar:
Non-SSL connection (SSL connection is recommended when requiring high-security)
You are now connected to database "postgres" as user "weejar".

openGauss=> create table anbob.test1(id int);
ERROR:  permission denied for schema anbob

openGauss=# \dn+ anbob
                         List of schemas
 Name  | Owner | Access privileges | Description | WithBlockChain
-------+-------+-------------------+-------------+----------------
 anbob | anbob | anbob=UC/anbob   +|             | f
       |       | weejar=U/anbob    |             |

openGauss=# \du anbob
           List of roles
 Role name | Attributes | Member of
-----------+------------+-----------
 anbob     | Sysadmin   | {}

openGauss=# \du weejar
           List of roles
 Role name | Attributes | Member of
-----------+------------+-----------
 weejar    |            | {}

授权 all on schema

openGauss=# GRANT all on schema anbob to weejar;
GRANT

weejar尝试创建anbob下的表

openGauss=>  create table anbob.test1(id int);
ERROR:  current user does not have privilege to role anbob

解决方法

授权 usera to userb

openGauss=# grant anbob to weejar;
GRANT ROLE

weejar尝试创建anbob下的表

openGauss=> create table anbob.test1(id int);
CREATE TABLE

POSTGRESQL17

在postgresql中与openGauss有些不同，如同名schema和权限.

D:\postgresql\pgsql\bin>psql -d postgres
psql (17rc1)
输入 "help" 来获取帮助信息.

postgres=# create user anbob password 'www.anbob.com';
CREATE ROLE


postgres=# create user weejar1 password 'www.anbob.com';
CREATE ROLE

postgres=# grant usage  on schema anbob to weejar1;
错误:  模式 "anbob" 不存在

postgres=# create schema anbob;
CREATE SCHEMA

postgres=# grant usage  on schema anbob to weejar1;
GRANT

postgres=# \c - weejar1
您现在已经连接到数据库 "postgres",用户 "weejar1".

postgres=> create table anbob.test1(id int);
错误:  对模式 anbob 权限不够
第1行create table anbob.test1(id int);
                  ^

解决方法

D:\postgresql\pgsql\bin>psql -d postgres
psql (17rc1)
输入 "help" 来获取帮助信息.

postgres=# grant all  on schema anbob to weejar1;
GRANT
postgres=# \c - weejar1
您现在已经连接到数据库 "postgres",用户 "weejar1".

postgres=> create table anbob.test1(id int);
CREATE TABLE
postgres=> select version();
                                 version
--------------------------------------------------------------------------
 PostgreSQL 17rc1 on x86_64-windows, compiled by msvc-19.41.34120, 64-bit
(1 行记录)

总结:
yyy用户在schema XXX下创建表，在postgresql中使用grant all on schema xxx to yyy，可以解决该问题，但是在openGauss中并不适用，需要grant xxx to yyy。

— enjoy —

打赏

微信扫一扫，打赏作者吧～