We help IT Professionals succeed at work.

We've partnered with Certified Experts, Carl Webster and Richard Faulkner, to bring you a podcast all about Citrix Workspace, moving to the cloud, and analytics & intelligence. Episode 2 coming soon!Listen Now

x

need to create a trigger thet alter session

gudii9
gudii9 asked
on
Medium Priority
2,755 Views
Last Modified: 2013-12-18
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.
Comment
Watch Question

johnsoneSenior Oracle DBA
CERTIFIED EXPERT

Commented:
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

Author

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.
Senior Oracle DBA
CERTIFIED EXPERT
Commented:
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

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts

Author

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;

Author

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.
johnsoneSenior Oracle DBA
CERTIFIED EXPERT
Commented:
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.

Author

Commented:
thank you very much for your help. I appreciate it.
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.