GRANT SELECT doesn't work in altered session

Question

I have the following situation. I need to write a procedure to give one schema access to object of the other one. The thing is that this procedure is being executed by administrative account via flyway.

I tried numerous options, but face 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 it works w/o altering schema.

I'm surprised this works at all; shouldn't your OWNER by upper-case (i.e. `'APPUSER'`)? If this is correct then can your "administrative" schema actually see the table (have you tried to select from it?)? — Ben, Aug 03 '15 at 12:20
Yep, it's upper-cased, I just changed actual values with placeholders. And yes, administrative schema can select via `SELECT * FROM APPUSER.APPTABLE` — vladfau, Aug 03 '15 at 12:21
Use dbms_output.put_line('GRANT SELECT ON '||R.owner||'.'||R.table_name||' TO QAUser'); before execute immediate to print the actual statement and share the result — Maulik Shah, Aug 03 '15 at 12:34
Were the select grants on the appuser schema granted with the "WITH GRANT OPTION"? If not, then that will most likely be why your administrative schema can't create the grants. Additionally, why are you granting the individual grants directly to the qauser? Wouldn't it be better to create a readonly role and then get the select grants added to that? Then it's just a matter of assigning the role to the qauser, plus any additional users who need readonly access. — Boneist, Aug 03 '15 at 12:39
@Boneist, so you recommend to create role, grant selects to role and then grant this role to required user? — vladfau, Aug 03 '15 at 12:42
Yes; saves time in the future when other users come on board. Typically, I generally create at least two roles - one readonly and one with the necessary write privileges for the application user(s) to run (and these users are *NOT* object owning accounts), plus potentially one with write privileges for the support team. — Boneist, Aug 03 '15 at 12:45
@Boneist your comments are suits to be an answer for futures users — Moudiz, Aug 03 '15 at 13:00

score 1 · Answer 1 · edited May 23 '17 at 11:58

You owner is AppUser in mixed case. As such, you will need to quote it when using it in statements, otherwise Oracle will convert it to uppercase.

So you could 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;

See Oracle: What exactly do quotation marks around the table name do?

GRANT SELECT doesn't work in altered session

1 Answers1