Solved

Schema level trigger

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
automatic email alert 1 52
SQL Developer 6 48
ODBC in excel 2016 in Windows 10 via VBA 16 84
Not able to drop or recreate an Oracle stored procedure 1 31
How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
Via a live example, show how to take different types of Oracle backups using RMAN.
This video shows how to recover a database from a user managed backup

810 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