Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Schema level trigger

Posted on 2003-12-03
4
Medium Priority
?
1,747 Views
Last Modified: 2008-03-04
XYZDBA is the owner of all the tables in a schema called XYZ.

I have created public synonyms for all these tables.

I have created USER1 who has alter/insert/update/select/reference priviliges on the public synonyms created above.

I have a schema level trigger like

CREATE OR REPLACE TRIGGER XYZDBA_logintrig
AFTER LOGON ON XYZDBA.SCHEMA
DECLARE
      /* variable declarations-------start*/
      ssn number(8)  ;
      hst varchar(30) ;
      logtime varchar(10);
BEGIN
      select sys_context('USERENV','SESSIONID') into ssn from dual;
      select sys_context('USERENV','HOST') into hst from dual;
      select to_char(SYSDATE,'HH24:MM:SS') into logtime from dual;
      insert into  XYZDBA.Audloginlogs(user_id,session_id,host,logon_day,logon_time)
            values (user,ssn,hst,to_date(sysdate),logtime) ;
END ;

XYZDBA has administer database trigger system privilige.

Whenever USER1 logins in I want the trigger to create a row in audloginlogs table ?

Whenever I login as XYZDBA a row is getting created . But, when I login as USER1 the row is not getting created .

What am I  doing wrong ?

regards
jana
0
Comment
Question by:janar
[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
4 Comments
 
LVL 23

Accepted Solution

by:
seazodiac earned 500 total points
ID: 9872415
This is actually the normal behavior of your trigger.
in oracle, schema is owned by individual user.
Users can log on the oracle database as a whole to use other's schema objects, but they cannot log on other's shema.

for example:

The TEST oracle database has two users A and B, and their corresponding SCHEMAs.
User A and User B can both log on the TEST database, if privileges assigned properly, they can cross-reference each other's schema object, but user A cannot log on the user B's schema, and vice versa.
But User A, by default, log on his own schema by logging on the TEST database, so is USER B.

so if your above trigger is changed like this in the header:
CREATE OR REPLACE TRIGGER XYZDBA_logintrig
AFTER LOGON ON DATABASE
---
---

You will have a record of each user logon.

but this is not what you want, I think.

in conclusion, for events like which users are logging onto the schema level , I guess you cannot do that.
0
 
LVL 12

Assisted Solution

by:catchmeifuwant
catchmeifuwant earned 500 total points
ID: 9872416
The trigger is created to fire,only when XYZDBA logson.

CREATE OR REPLACE TRIGGER XYZDBA_logintrig
AFTER LOGON ON XYZDBA.SCHEMA  --- Only for XYZDBA

From Oracle docs:
-----------------
Creating a trigger on DATABASE implies that the triggering event is outside the scope of a user (for example, database STARTUP and SHUTDOWN), and it applies to all users (for example, a trigger created on LOGON event by the DBA).

Creating a trigger on SCHEMA implies that the trigger is created in the current user's schema and is fired only for that user.

------------------

If you need to fire it for USER1,then use a DB level trigger..


 
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

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

Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

721 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