I think i wasn't able to explain my question
Arof, March 20, 2019 - 5:49 am UTC
The problem is that table was created in schema B by the owner of Schema A. Now schema B does not have connect privileges and Schema A cannot grant to itself, hence the question.
.
David D., March 20, 2019 - 9:59 am UTC
"Schema A cannot grant to itself" : really?
Did you try?
My thoughts
J. Laurindo Chiappa, March 20, 2019 - 5:28 pm UTC
What I undestood is : for some (unknow) reason, A was granted with the permission to create a table in other schemas, BUT he/seh does not have the privilege to SELECT tables in others schemas... So , A created a table in B schema but A is unable to select that table, this way :
SYSTEM@O11GR2>create user USER_A identified by USER_A ;
Usuário criado.
SYSTEM@O11GR2>create user USER_B identified by USER_B ;
Usuário criado.
SYSTEM@O11GR2>grant create session to USER_A;
Concessão bem-sucedida.
SYSTEM@O11GR2>grant create any table to USER_A;
Concessão bem-sucedida.
SYSTEM@O11GR2>exit
Desconectado de Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining
and Real Application Testing options
c:\app\oracle\product\11gR2\dbhome_r2>sqlplus USER_A/USER_A
SQL*Plus: Release 11.2.0.4.0 Production on Qua Mar 20 14:21:46 2019
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Conectado a:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining
and Real Application Testing options
USER_A@O11GR2>create table USER_B.TAB_TEST(c1 number, c2 varchar2(80));
Tabela criada.
USER_A@O11GR2>select * from USER_B.TAB_TEST;
select * from USER_B.TAB_TEST
*
ERRO na linha 1:
ORA-01031: privilégios insuficientes
USER_A@O11GR2>
==> The missing concept here is : when someone create a table in OTHER schema, the OTHER user will be the OWNER, and not the CREATOR, see :
SYSTEM@O11GR2>select owner, table_name from dba_tables where table_name='TAB_TEST';
OWNER TABLE_NAME
---------------- ------------------------------
USER_B TAB_TEST
1 linha selecionada.
SYSTEM@O11GR2>
Regards,
Chiappa
Obvious answer...
J. Laurindo Chiappa, March 20, 2019 - 5:35 pm UTC
If the scene is like I wrote, the Obvios answer is : give to the CREATOR the privilege to SELECT ANY TABLE, ** or ** someone Administrator/more powerful user can give the GRANT to access B´s tables , such as :
SYSTEM@O11GR2>grant SELECT on USER_B.TAB_TEST to user_A;
Concessão bem-sucedida.
SYSTEM@O11GR2>exit
Desconectado de Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining
and Real Application Testing options
c:\app\oracle\product\11gR2\dbhome_r2>sqlplus USER_A/USER_A
SQL*Plus: Release 11.2.0.4.0 Production on Qua Mar 20 14:32:15 2019
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Conectado a:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining
and Real Application Testing options
USER_A@O11GR2>desc USER_B.TAB_TEST;
Nome Nulo? Tipo
----------------------------------------- -------- ----------------------------
C1 NUMBER
C2 VARCHAR2(80)
USER_A@O11GR2>