• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1710
  • Last Modified:

need to create a trigger thet alter session

Hi,

I need to create a trigger that alter the session of initial schema(FIRST_USER_SCHEMA) to logged on  second schema (SECOND_USER_SCHEMA) which executes some stored procedures on single table on other schema(THIRD_USER_SCHEMA). I took care of flow from SECOND_USER_SCHEMA to THIRD_USER_SCHEMA by granting privileges and executing stored procedures  and updating the single customers table etc. First part of creating trigger which ALTERS and SETS session is pending.

 I wrote trigger like this and executed.

CREATE OR REPLACE FIRST_USER_SCHEMA_NAME.TRIGGER_NAME

AFTER LOGON

ON SECOND_USER_SCHEMA_NAME

BEGIN

EXECUTE IMMEDIATE ('ALTER SESSION SET CURRENT_SCHEMA=SECOND_USER_SCHEMA_NAME');

END;
It is giving error saying...ORA-30506..system triggers cannot be based on tables or view..


Once user logs on to SECOND_USER_SCHEMA_NAME he has privileges to access the stored procedures  which update a single table customers in schema named THIRD_SCHEMA_NAME. How to write this trigger.

Any sample code, ideas, resources, links highly appreciated. Thanks in advance.
0
gudii9
Asked:
gudii9
  • 4
  • 3
2 Solutions
 
johnsoneSenior Oracle DBACommented:
This may be what you are looking for.  I am not sure I follow your example though.
CREATE OR REPLACE FIRST_USER_SCHEMA_NAME.TRIGGER_NAME
AFTER LOGON
BEGIN
  IF sys_context('USERENV', 'SESSION_USER') = 'FIRST_USER_SCHEMA_NAME' THEN
    EXECUTE IMMEDIATE ('ALTER SESSION SET CURRENT_SCHEMA=SECOND_USER_SCHEMA_NAME');
  END IF;
END;

Open in new window

0
 
gudii9Author Commented:
When I execute above trigger like
CREATE OR REPLACE FIRST_USER_SCHEMA_NAME.TRIGGER_NAME
AFTER LOGON
BEGIN
  IF sys_context('USERENV', 'SESSION_USER') = 'FIRST_USER_SCHEMA_NAME' THEN
    EXECUTE IMMEDIATE ('ALTER SESSION SET CURRENT_SCHEMA=SECOND_USER_SCHEMA_NAME');
  END IF;
END;
saying  error message like ORS-00969: missing ON key word

when i execute by including 'ON' like

CREATE OR REPLACE FIRST_USER_SCHEMA_NAME.TRIGGER_NAME
AFTER LOGON
ON FIRST_USER_SCHEMA.SCHEMA
BEGIN
  IF sys_context('USERENV', 'SESSION_USER') = 'FIRST_USER_SCHEMA_NAME' THEN
    EXECUTE IMMEDIATE ('ALTER SESSION SET CURRENT_SCHEMA=SECOND_USER_SCHEMA_NAME');
  END IF;
END;
ORA-01031:insufficient privilege error message is coming. Please advise.
0
 
johnsoneSenior Oracle DBACommented:
This should be it.
CREATE OR REPLACE FIRST_USER_SCHEMA_NAME.TRIGGER_NAME
AFTER LOGON
ON DATABASE
BEGIN
  IF sys_context('USERENV', 'SESSION_USER') = 'FIRST_USER_SCHEMA_NAME' THEN
    EXECUTE IMMEDIATE ('ALTER SESSION SET CURRENT_SCHEMA=SECOND_USER_SCHEMA_NAME');
  END IF;
END;

Open in new window

0
Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

 
gudii9Author Commented:
seems that it created the trigger when i gave similar to what you mentioned. how to test it

CREATE OR REPLACE FIRST_USER_SCHEMA_NAME.TRIGGER_NAME
AFTER LOGON
ON SCHEMA
BEGIN
  --IF sys_context('USERENV', 'SESSION_USER') = 'FIRST_USER_SCHEMA_NAME' THEN
    EXECUTE IMMEDIATE ('ALTER SESSION SET CURRENT_SCHEMA=SECOND_USER_SCHEMA_NAME');
--  END IF;
END;
0
 
gudii9Author Commented:
The trigger looks good without any compilation etc errors. The trigger code looks like

CREATE OR REPLACE FIRST_USER_SCHEMA_NAME.TRIGGER_NAME
AFTER LOGON
ON SCHEMA
BEGIN
  --IF sys_context('USERENV', 'SESSION_USER') = 'FIRST_USER_SCHEMA_NAME' THEN
    EXECUTE IMMEDIATE ('ALTER SESSION SET CURRENT_SCHEMA=SECOND_USER_SCHEMA_NAME');
--AddStroredProcedure_Name('par1', 'par2','par3','par4');
--  END IF;
END;

Now my concern is how to test the trigger. It supposed to change the session when logon etc right.
I found somewhere to execute command like
select username, shemaname, status from v$session.
How can i ensure what result i got is correct. I see schemaone as user name and schema2 as schema name and status is inactive. is that is what i supposed to see while testing. what it mean by inactive. I am bit confused of whole this exercise about what trigger trying to do and what way session is related to schema and why we are sessting session to second schema etc. Can anyone clarify me on these concepts , how to test scenario. (Is there is way i can execute  Add stored procedure  etc on schema three and see whether values are being inserted if yes how can achieve that?). How does the logon triggers work in general on schemas?. Thanks in advance.
0
 
johnsoneSenior Oracle DBACommented:
The best way to test is to try to access an object that normally would not be found as the user you logged in as.  Any objected owned by SECOND_USER_SCHEMA_NAME that does not have a public synonym would do.

CURRENT_SCHEMA does not change who you are logged in as.

From the Oracle doc:

The CURRENT_SCHEMA parameter 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.

0
 
gudii9Author Commented:
thank you very much for your help. I appreciate it.
0

Featured Post

Transaction-level recovery for Oracle database

Veeam Explore for Oracle delivers low RTOs and RPOs with agentless transaction log backup and transaction-level recovery of Oracle databases. You can restore the database to a precise point in time, even to a specific transaction.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now