Solved

Schema level trigger

Posted on 2003-12-03
4
1,734 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 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

Creating Instructional Tutorials  

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.

Question has a verified solution.

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

Suggested Solutions

Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
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

696 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