Home » SQL & PL/SQL » SQL & PL/SQL » Data Logon trigger (oracle 9i)
Data Logon trigger [message #293093] Thu, 10 January 2008 21:26 Go to next message
ShridharV
Messages: 29
Registered: January 2008
Junior Member
Hi,

I have created a data logon trigger something like this.

create or replace trigger data_logon_trigger
after logon
ON database
begin
if sys_context('USERENV','CURRENT_USER')='USER1' then

execute immediate
'alter session set current_schema =USER2';

end if;

end;


The problem I facing s no matter with what USER ID I login, the schema is getting assigned to USER2. i.e., even if I login with USER3, the schema is getting assigned to USER2. What could be the reason? Please help.

[Updated on: Thu, 10 January 2008 21:26]

Report message to a moderator

Re: Data Logon trigger [message #293096 is a reply to message #293093] Thu, 10 January 2008 22:10 Go to previous messageGo to next message
BlackSwan
Messages: 25032
Registered: January 2009
Location: SoCal
Senior Member
>>The problem I facing s no matter with what USER ID I login, the schema is getting assigned to USER2.

Please use SQL*Plus & CUT & PASTE to demonstrate how you arrive at the conclusion stated above.
Re: Data Logon trigger [message #293104 is a reply to message #293096] Thu, 10 January 2008 22:21 Go to previous messageGo to next message
ShridharV
Messages: 29
Registered: January 2008
Junior Member
Hi,

Thanks for the reply.

As soon as I logged in with USER3, I checked the schema using the following command.

select sys_context('USERENV','CURRENT_SCHEMA') from dual;

The result is USER2.

I cannot paste the snap shot of sql* as the schema name is supposedly confidential.
Re: Data Logon trigger [message #293106 is a reply to message #293093] Thu, 10 January 2008 22:31 Go to previous messageGo to next message
BlackSwan
Messages: 25032
Registered: January 2009
Location: SoCal
Senior Member
Are you logged onto the OS as USER2 at while executing this "test"?
Re: Data Logon trigger [message #293107 is a reply to message #293106] Thu, 10 January 2008 22:32 Go to previous messageGo to next message
ShridharV
Messages: 29
Registered: January 2008
Junior Member
Hi,

No man. Certainly not. I logged in as USER3 and checkd the schema.
Re: Data Logon trigger [message #293108 is a reply to message #293093] Thu, 10 January 2008 22:41 Go to previous messageGo to next message
BlackSwan
Messages: 25032
Registered: January 2009
Location: SoCal
Senior Member
> I logged in as USER3 and checked the schema.
There is no "schema" at the OS level to be checked.

something is amiss & you are not accurately reporting reality.

You refuse to provide details so others (like me) to reproduce the problem.

You're On Your Own (YOYO)!
Re: Data Logon trigger [message #293110 is a reply to message #293093] Thu, 10 January 2008 22:54 Go to previous messageGo to next message
Michel Cadot
Messages: 64098
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Who owns the logon trigger?

Regards
Michel
Re: Data Logon trigger [message #293116 is a reply to message #293110] Thu, 10 January 2008 23:24 Go to previous messageGo to next message
ShridharV
Messages: 29
Registered: January 2008
Junior Member
@anacedent

By saying I logged in as USER3 I meant, I logged into sql* with the User name as USER3 and checked the schema. The database is MAINDB. But there are 3 different schemas namely USER1,USER2 and USER3.

@Michel Cadot

The trigger is owned by sysadmin. It was him who created the trigger.
Re: Data Logon trigger [message #293127 is a reply to message #293116] Fri, 11 January 2008 00:08 Go to previous messageGo to next message
Michel Cadot
Messages: 64098
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:

The trigger is owned by sysadmin.

You have an Oracle user named SYSADMIN, don't you?

Regards
Michel
Re: Data Logon trigger [message #293133 is a reply to message #293093] Fri, 11 January 2008 00:17 Go to previous message
Michel Cadot
Messages: 64098
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I bet your trigger is owned by USER1:
SQL> show user
USER is "MICHEL"

SQL> drop table t purge;

Table dropped.

SQL> create table t (us varchar2(30), cus varchar2(30));

Table created.

SQL> set role dba;

Role set.

SQL> grant all on t to system;

Grant succeeded.

SQL> create or replace trigger system.mytrg after logon on database
  2  begin
  3    insert into michel.t values (user, sys_context('USERENV','CURRENT_USER'));
  4  end;
  5  /

Trigger created.

SQL> connect michel/michel
Connected.
SQL> select * from t;
US                             CUS
------------------------------ ------------------------------
MICHEL                         SYSTEM

1 row selected.

SYS_CONTEXT gives you the owner of the trigger, you have to use USER function to know the connected user.

Regards
Michel
Previous Topic: Resetting running total
Next Topic: About Oracle data dictionary view
Goto Forum:
  


Current Time: Fri Dec 02 12:38:46 CST 2016

Total time taken to generate the page: 0.17178 seconds