How to grant the user privileges for a particular scheme?

Question

The situation is that I have two schemes: A and B. I have a limited user, to whom I must grant privilege, make SELECT queries in the B-scheme and just it. How can I provide this user?

Answer 1

You can not.

The best you can do is give the user the privilege of "selecting" each table in schema b.

this request will generate the commands you need:

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

The problem with this is that you will want to add a new table to A., then you will have to grant this privilege separately. he will not do it automatically.

Answer 2

, dba_tables, , (a) , , b

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