Solved

Alter Sesson set Current_schema = <schema_name> not working

Posted on 2004-03-25
3
3,513 Views
Last Modified: 2007-12-19
I have a logon trigger that seems to be executing when I logon to my database.  I'm trying to set the current_schema at that time and when I run:

    select sys_context('userenv', 'current_schema') from dual;

I see that my current_schema is the users schema (e.g. USER2) and not the one I'm setting it to (e.g. USER1).  This is my trigger:

 CREATE OR REPLACE TRIGGER db_logon
 AFTER logon ON DATABASE WHEN (USER IN ('USER2'))
 BEGIN
 
  execute immediate 'ALTER SESSION SET CURRENT_SCHEMA = USER1';

end;

The user has alter session privileges.  Anyone have any helpful hints??
0
Comment
Question by:ameliam
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 
LVL 8

Expert Comment

by:Danielzt
ID: 10683116
Please read this:

Within the procedure you can give:
EXECUTE IMMEDIATE 'alter session set current_schema = user_name';
This changes the current schema of the session to the specified schema. Subsequent unqualified references to schema objects during the session will resolve to objects in the specified schema. The setting persists for the duration of the session or until you issue another ALTER SESSION SET CURRENT_SCHEMA statement.
This setting offers a convenient way to perform operations on objects in a schema other than that of the current user without having to qualify the objects with the schema name. This setting changes the current schema, but it does not change the session user or the current user, nor does it give you any additional system or object privileges for the session.
Hence you can make use of this for your requirement. But changing the current user within a procedure is not possible.

it looks it does not change the current_schema variable.
0
 
LVL 8

Accepted Solution

by:
Danielzt earned 125 total points
ID: 10683281

I tried this, it works for me. I am using Oracle 9i R2.

BEGIN
  execute immediate 'ALTER SESSION SET CURRENT_SCHEMA = TEST';
end;
select sys_context('userenv', 'current_schema') from dual

what oracle version are you using?

Bug 2015342  ALTER SESSION SET CURRENT_SCHEMA may not work in definers rights rocedure
Versions confirmed as being affected 8.1.7.2 9.0.1.2  

This issue is fixed in 8.1.7.3 (Server Patch Set)
9.0.1.3 (Server Patch Set)
9.2.0.1 (Base Release)
 
0
 

Author Comment

by:ameliam
ID: 10687762
Ah - I'm using 8.1.5 so that could be the problem.  Not the answer I wanted, but thanks Danielzt.  Back to the drawing board... I can't upgrade our database b/c one of our apps will no longer function (little thing like the ERP system... )

Thanks.
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…

724 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question