Solved

Schema level trigger

Posted on 2003-12-03
4
1,730 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
4 Comments
 
LVL 23

Accepted Solution

by:
seazodiac earned 125 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 125 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Convert Oracle data into XML document 2 59
Oracle DATE Column Space 11 67
Extract the first word (before the , ) 2 40
constraint check 2 20
This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
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 video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

863 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

Need Help in Real-Time?

Connect with top rated Experts

24 Experts available now in Live!

Get 1:1 Help Now