GRANT SELECT does not work on modified session
I have the following situation. I need to write a procedure to give one object access to another object. The point is that this procedure is performed by an administrative account across the span.
I have tried many options, but I come across the following:
Error starting at line : 3 in command - (my begin...end procedure)
Error report -
ORA-00942: table or view does not exist
ORA-06512: at line 3
00942. 00000 - "table or view does not exist"
My code:
ALTER SESSION SET CURRENT_SCHEMA = AppUser;
BEGIN
FOR R IN (SELECT owner, table_name FROM dba_tables WHERE owner='AppUser') LOOP
EXECUTE IMMEDIATE 'GRANT SELECT ON '||R.owner||'.'||R.table_name||' TO QAUser';
END LOOP;
END;
Neither does it work without changing the schema.
+3
source to share
1 answer
Owner AppUser
in a mixed case. Thus, you will need to specify it when using it in statements, otherwise Oracle will convert it to uppercase.
So, you can try this:
ALTER SESSION SET CURRENT_SCHEMA = AppUser;
BEGIN
FOR R IN (SELECT owner, table_name FROM dba_tables WHERE owner='AppUser') LOOP
EXECUTE IMMEDIATE 'GRANT SELECT ON "'||R.owner||'"."'||R.table_name||'" TO "QAUser"';
END LOOP;
END;
+1
source to share