how to grant user privilege on specific schema?

Question

The situation is I have two schemas: A and B. I have a restricted user that I need to give a privilege do SELECT queries in B schema and just it. How can I grant this user?

Answer 1

You can't.

The best you can do is grant user a 'select' privilege each table in schema b.

this query will generate the commands you'll need:

select 'grant select on A.'||table_name||' to B;' 
from dba_Tables 
where owner = 'A';

The problem with this, is in the case you will want to add new table to A. then you'll have to grant the privilege on it separately. it will not do it automatically..

Answer 2

You may find you do not have access to dba_tables, The following block of code run in the owning schema (a) will grant permissions to all tables, to the user b

BEGIN
    FOR t IN (SELECT * FROM user_tables) 
    LOOP   
        EXECUTE IMMEDIATE 'GRANT SELECT ON ' || t.table_name || ' TO b';    
    END LOOP;
END;