CODEWITHSUNDEEP
sqldatabaseoracle-databaseplsqloracle12c
en Lopes
en Lopes

Reputation: 2153

Oracle Users Tables

I've created some TABLES in 1 DB with the user name DEVICE.

Querying the tables I see that they belong to the user DEVICE

SELECT owner, table_name FROM dba_tables

Then I created a user named CAR, I logged in the DB with that user and create some tables, I've tried to query the other tables with 

SELECT * FROM DEVICE.table_name;

with the result

00942. 00000 -  "table or view does not exist"

and logged with the user CAR and don't see the user DEVICE in others users from SQL Developer

Upvotes: 0

Views: 119

Answers (3)

XING
XING

Reputation: 9886

Here the script to generate grant select on all the tables.

select 'grant select on Admin.' || object_name || ' to User;' 
from user_objects 
where object_type in('TABLE');

Then you have to create a script to run these grant statements at once or you can use PL/SQL as well. Type the following in the SQL prompt.

SQL> spool grant_statements.sql
SQL> set pagesize 0
SQL> set feedback off
SQL> set linesize 300
SQL> set trimout on
SQL> set trimspool on
SQL> select 'grant select on Admin.' || object_name || ' to User;' from user_objects where object_type in('TABLE','SYNONYM')
/
SQL> spool off

And you have got the script file you can run it. OR You can run the following PL/SQL block (Run as admin user).

BEGIN
  FOR s IN (SELECT *
              FROM user_objects where object_type in('TABLE','SYNONYM'))
  LOOP
    EXECUTE IMMEDIATE  'grant select on admin.' || s.object_name || ' to user';
  END LOOP;
END;

Upvotes: 1

Cyrille MODIANO
Cyrille MODIANO

Reputation: 2376

This error comes up because CAR doesn't have privileges to query the DEVICE's table. You need to grant access to this table to CAR.

1- login as DEVICE

2- Issue the following grant:

GRANT SELECT ON TABLE_NAME TO CAR;

If you want to do it for all the tables of the DEVICE user, create a script like that:

select 'GRANT SELECT ON ' || table_name || ' TO CAR;' FROM USER_TABLES;

My advice would be to create a ROLE if you do that:

CREATE ROLE DEVICE_READ_ROLE;

select 'GRANT SELECT ON ' || table_name || ' TO DEVICE_READ_ROLE;' FROM USER_TABLES;
GRANT DEVICE_READ_ROLE TO CAR;

Note that if you create new tables in the DEVICE schema you will need to grant select on the new table to the ROLE for CAR to have access to it.

Upvotes: 1

Pawan Rawat
Pawan Rawat

Reputation: 525

you need to grant access to user CAR for the tables created by DEVICE . login using DEVICE user and grant access to the tables CAR should be able to access using sql like below 

GRANT SELECT , UPDATE , INSERT ON TABLE_NAME TO CAR;

Upvotes: 0

Related Questions