RE: DBA Role Privs

Ok, I received a couple of ideas and have tried both of them, but this
is my pick.  However I am getting the following error when trying to
create the attached trigger:

 

Errors for TRIGGER CHECK4DBA_TGR:

 

LINE/COL ERROR

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

40/9     PLS-00103: Encountered the symbol "SEND_EMAIL" when expecting
one

         of the following:

         := . ( @ % ;

         The symbol ":=" was substituted for "SEND_EMAIL" to continue.

 

I did have EXECUTE in front of the procedure, but read where it should
be a CALL.  I thought , AHA, that's the problem....but received the same
error.  I can copy the EXECUTE procedure (now a CALL) line into SqlPlus
and it works fine.  It's just not working within the trigger.  I read
where you can execute a procedure from a trigger, but the example did
not have it in a plsql block.  Could this be the problem?  I thought I
read something about executing a procedure within a plsql block, but I
have read so many different things today I'm having an information
overload.

 

Any help, or direction, would be appreciated.

 

Thanks, Laura

________________________________

From: Baumgartel, Paul [mailto:paul.baumgartel@xxxxxxxxxxxxxxxxx] 
Sent: Friday, May 05, 2006 12:54 PM
To: Burton, Laura; oracle-l@xxxxxxxxxxxxx
Subject: RE: DBA Role Privs

 

You need a DDL trigger, not a table trigger.  Consult CREATE TRIGGER in
the SQL manual and search for "ddl_event".

 

Paul Baumgartel 
CREDIT SUISSE 
Information Technology 
DBA & Admin - NY, KIGA 1 
11 Madison Avenue 
New York, NY 10010 
USA 
Phone 212.538.1143 
paul.baumgartel@xxxxxxxxxxxxxxxxx 
www.credit-suisse.com 

        -----Original Message-----
        From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx]On Behalf Of Burton, Laura
        Sent: Friday, May 05, 2006 1:42 PM
        To: oracle-l@xxxxxxxxxxxxx
        Subject: DBA Role Privs

        I want to be notified every time the DBA role is granted or
revoked via email.  I found the procedure which takes care of the email.
Next I created a trigger on Insert into the Dba Role Privs view and
received the error that this can not be performed on a view.  Does
anyone know the underlying Sys table(s) to use, or a better way of
checking this?

         

        Thank you, 

        Laura

========================================================================
======
Please access the attached hyperlink for an important electronic
communications disclaimer: 
 
http://www.credit-suisse.com/legal/en/disclaimer_email_ib.html
========================================================================
======

CREATE OR REPLACE TRIGGER CHECK4DBA_TGR
  AFTER GRANT OR REVOKE ON SCHEMA

DECLARE

   DUMMY          NUMBER(38);
   HLD_ACTION     VARCHAR2(6);
   HLD_NAME       VARCHAR2(15);
   HLD_TIME       DATE;
   HLD_USERID     VARCHAR2(15);
   HLD_GRANTEE    VARCHAR2(15);
   V_VERBAGE      VARCHAR2(4);
   V_MESSAGE      VARCHAR2(70);
   V_ACTION       VARCHAR2(6);

BEGIN

   SELECT COUNT(*) INTO DUMMY
     FROM SYS.AUD$
    WHERE ACTION# IN (114,115)
      AND OBJ$NAME# = 'DBA'
      AND TO_CHAR(TIMESTAMP#, 'YYYYMMDD HH24:MI') > to_char(sysdate - .1/24, 
'yyyymmdd hh24:mi');
    
   IF DUMMY > 0 THEN

   SELECT ACTION#, OBJ$NAME#, TIMESTAMP#, USERID, AUTH$GRANTEE
     INTO HLD_ACTION, HLD_NAME, HLD_TIME, HLD_USERID, HLD_GRANTEE
     FROM SYS.AUD$
    WHERE ACTION# IN (114,115)
      AND OBJ$NAME# = 'DBA'
      AND TO_CHAR(TIMESTAMP#, 'YYYYMMDD HH24:MI') > to_char(sysdate - .1/24, 
'yyyymmdd hh24:mi');

   IF    HLD_ACTION = 114           THEN
             V_ACTION  := 'GRANT';
             V_VERBAGE := 'TO';
   ELSE
             V_ACTION  := 'REVOKE';
             V_VERBAGE := 'FROM';
   END IF;

   V_MESSAGE := HLD_USERID||' '||V_ACTION||' DBA '||V_VERBAGE||' 
'||HLD_GRANTEE||' AT '||HLD_TIME;

   CALL send_email('OraAlert@xxxxxxxxxxx','burtonl@xxxxxxxxxxx','DBA Role Alert 
Message ','TEST TEST TEST');

   END IF;

END;

/

Other related posts: