In order for an application user (APP_USER) to use another schema (SCHEMA_OWNER) without the need for prefixing queries with the schema name, I have implemented a logon trigger that changes the session parameter CURRENT_USER.
This works fine except for sequences. When I do either of
select my_seq.nextval from dual;
insert into foo(bar) values(my_seq.nextval);
… I get the error
SQL Error [1031] [42000]: ORA-01031: insufficient privileges
Any ideas on why this may be and how to solve it?
Two possibilties comes to mind, maybe I need to
- grant select privileges to SYS.DUAL of SCHEMA_OWNER? or
- use the SESSION_USER instead of the deprecated CURRENT_USER parameter/variable?
Unfortunately I do not have admin rights on the database, else I would test it myself. I must work through a DBA, which means I have some difficulty in trying out different solutions and ask the question here in the hope to speed up the process.
Please note that workarounds like creating Oracle synonyms or views is not an option and likewise I cannot prefix all queries with the schema name since the application is a packaged product. Running as the schema owner is not an option either because of enterprise policy.
Best Answer
Sounds like you don't have SELECT access on the sequence. Ask the DBA to run:
GRANT SELECT ON SCHEMA_OWNER.MY_SEQ TO APP_USER;
Can you edit your post with the full error message you're receiving?
Regarding your comment. I suspect you've got
SELECT ANY TABLE
etc privs. These don't apply to sequences, as demonstrated below: