| Data Logon trigger [message #293093] |
Thu, 10 January 2008 21:26  |
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 #293104 is a reply to message #293096] |
Thu, 10 January 2008 22:21  |
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 #293116 is a reply to message #293110] |
Thu, 10 January 2008 23:24 |
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 #293133 is a reply to message #293093] |
Fri, 11 January 2008 00:17 |
 |
Michel Cadot
Messages: 68770
Registered: March 2007
Location: Saint-Maur, France, https...
|
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
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
