RE: DBA Role Privs
- From: "Burton, Laura" <BurtonL@xxxxxxxxxxx>
- To: "Baumgartel, Paul" <paul.baumgartel@xxxxxxxxxxxxxxxxx>, <oracle-l@xxxxxxxxxxxxx>
- Date: Fri, 5 May 2006 16:18:06 -0500
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:
