-
ON LOGON trigger not working
Hi DBAs,
I'm trying to create a ON LOGON trigger that runs a stored procedure which grants privileges to a particular user upon logon. The stored procedure works well when it is ran independently, however when the user logs in the stored procedure is not executed by the trigger. No errors either.
Here's the trigger and the stored procedure.
CREATE OR REPLACE TRIGGER ON_LOGON AFTER LOGON ON DATABASE WHEN ( USER = 'TEST' )
DECLARE
USERID VARCHAR2(25);
BEGIN
SELECT USER INTO USERID from dual;
grants_restore(USERID);
EXCEPTION
WHEN OTHERS THEN NULL;
END;
PROCEDURE:
CREATE OR REPLACE PROCEDURE grants_restore
(USERID IN varchar2)
IS
CURSOR tabpriv_cur IS
SELECT UC_DBA_FIELD_DESC,RECNAME,USER_NAME from sysadm.ps_uc_dba_details where USER_NAME='TEST';
priv_source SYSADM.ps_uc_dba_details.UC_DBA_FIELD_DESC%type;
object_source SYSADM.ps_uc_dba_details.RECNAME%type;
grantee_source SYSADM.ps_uc_dba_details.USER_NAME%type;
BEGIN
OPEN tabpriv_cur;
LOOP
FETCH tabpriv_cur INTO priv_source,object_source,grantee_source;
IF tabpriv_cur%FOUND THEN
EXECUTE IMMEDIATE 'GRANT ' || priv_source || ' ON ' || object_source ||' TO '|| grantee_source;
ELSE
EXIT;
END IF;
END LOOP;
END;
Am I missing anything here?
Thanks in advance.
Regards
Badri
-
does that trigger even compile - im guessing not
-
And an EXCEPTION WHEN OTHERS THEN NULL; for the pièce de résistance : oh , day....
Regards,
Chiappa
-
The trigger and the procedure compiles without any errors. The procedure runs independently without any errors. The trigger is not working for some reason though.
SQL> alter trigger on_logon compile;
Trigger altered.
SQL> alter procedure grants_restore compile;
Procedure altered.
-
Add some error messages with DBMS_OUTPUT and see if its working, step by step.
But - as mentioned above - an error handling EXCEPTIONS section might be quicker
-
Originally Posted by JChiappa
And an EXCEPTION WHEN OTHERS THEN NULL; for the pièce de résistance : oh , day....
Regards,
Chiappa
How often do you see that?
Tom Kyte says it's tantamount to saying "and if this doesn't work then I don't care"
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|