Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

  1. grant select privileges to SYS.DUAL of SCHEMA_OWNER? or
  2. 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.

share|improve this question
add comment

2 Answers

up vote 3 down vote accepted

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:

PHIL@PHILL11G2 > create user test1 identified by test1;

User created.

PHIL@PHILL11G2 > grant connect,select any table to test1;

Grant succeeded.

PHIL@PHILL11G2 > create sequence testseq;

Sequence created.

PHIL@PHILL11G2 > conn test1/test1;
Connected.
Loading glogin.sql

Session altered.

TEST1@PHILL11G2 > select count(*) from phil.bigtable;

  COUNT(*)
----------
   2300640

TEST1@PHILL11G2 > 

TEST1@PHILL11G2 > select phil.testseq.nextval from dual;
select phil.testseq.nextval from dual
            *
ERROR at line 1:
ORA-01031: insufficient privileges
share|improve this answer
    
Added literal error message. Yes, making sure that there are enough privileges is definitely worth trying but I do believe APP_USER has general select, update, insert and delete privileges to the SCHEMA_OWNER schema. E.g. If I as SCHEMA_OWNER create a new table I do not have any problems using it from APP_USER. –  Christoffer Soop May 3 '12 at 9:23
    
Yes, could very well be it! I will wait with marking the answer as accepted until I have actually tried it (Given the time a roundtrip with the DBA takes it may be a couple of days or so...) –  Christoffer Soop May 3 '12 at 9:48
    
See this Q if you want to check what's granted to you: stackoverflow.com/questions/1298473/… –  FreshPhilOfSO May 3 '12 at 9:53
    
You could setup Oracle XE oracle.com/technetwork/products/express-edition/overview/… to quickly test/verify these things so you know they will work. –  Leigh Riffel May 3 '12 at 12:16
    
Problem solved, it was a simple case of not having the right privileges. Thanks a lot for input! –  Christoffer Soop May 4 '12 at 10:32
show 1 more comment

You could consider allowing APP_USER to proxy as SCHEMA_OWNER:

ALTER USER SCHEMA_OWNER GRANT CONNECT THROUGH APP_USER;

Then you can connect as APP_USER[SCHEMA_OWNER] using the APP_USER password and it will behave as though you are logged in as SCHEMA_OWNER for schema references, permissions, and almost everything except database links.

share|improve this answer
    
Good call. +1 from me –  FreshPhilOfSO May 4 '12 at 8:18
    
Well, if all (most) permissions are inherited that basically defeats the purpose of having an application user in the first place. The whole idea is to give limited access to the SCHEMA_OWNER schema. –  Christoffer Soop May 4 '12 at 10:29
    
Indeed, it does provide full access to the capabilities (permissions) of SCHEMA_OWNER. My impression was that APP_USER had permissions on most things in SCHEMA_OWNER. If this is not the case then proxying would not be a good option. Note that if APP_USER did have full permissions on SCHEMA_OWNER, there is a benefit to proxying over just using the SCHEMA_OWNER user directly. Specifically, being able to identify APP_USER is beneficial in logs, auditing, etc. –  Leigh Riffel May 4 '12 at 12:23
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.