访问表而不指定模式名称

I have a schema called GBO_ARC_SCHEMA, in which I have one table called TEST_EMP, and I have two users say USER_A and USER_B.

我有一个名为GBO_ARC_SCHEMA的模式,其中我有一个名为TEST_EMP的表,我有两个用户说USER_A和USER_B。

First I connected to USER_A and fired below query

首先,我连接到USER_A并触发下面的查询

select count(*)from TEST_EMP;

count
-----
20

After that I connected as USER_b and fired below query but it is giving an error, saying that table or view does not exit

之后,我作为USER_b连接并解决了以下查询,但是它给出了一个错误,说表或视图没有退出

select count(*)from TEST_EMP;

But if I use scma.object name it is allowing me to query like below

但是,如果我使用scma.object名称,它允许我查询如下

select count(*)from GBO_ARC_SCHEMA.TEST_EMP;

but as per my requirement I don't want to specify schema name.

但根据我的要求,我不想指定架构名称。

can somebody help me out?

有人可以帮帮我吗?

3 个解决方案

#1

If you want all users to be able to select from the table without qualifying with the schema name, you want to create a public synonym:

如果您希望所有用户都能够从表中进行选择而不使用模式名称进行限定,则需要创建公共同义词:

create public synonym TEST_EMP for GBO_ARC_SCHEMA.TEST_EMP;

If you only want user_b to omit the schema name, you want to create a private synonym WITHIN user_b's schema (that is logged on as user_b)

如果您只希望user_b省略模式名称,则需要创建一个私有同义词WITHIN user_b的模式(以user_b身份登录)

create synonym TEST_EMP for GBO_ARC_SCHEMA.TEST_EMP;

If you insist on not using synonyms, then, after logging in, do a

如果您坚持不使用同义词,那么在登录后,请执行

alter session set current_schema = GBO_ARC_SCHEMA;

#2

On the server roles for the login you are using, simply uncheck sysadmin and serveradmin roles. That should solve it

在您正在使用的登录的服务器角色上,只需取消选中sysadmin和serveradmin角色即可。那应该解决它

#3

-2

For Postgres users with the same problem

对于具有相同问题的Postgres用户

ALTER ROLE <YOUR_USERNAME> SET search_path TO GBO_ARC_SCHEMA;

#1