One object privilege quesiton.

From: Violin <violin.hsiao_at_mail.pouchen.com.tw>
Date: 9 Apr 1999 03:55:31 GMT
Message-ID: <37177abc.2976590@news.twsc.pouchen.com.tw>

Hello,
I have a object privilege question and maybe you could help!!

I have 2 users : AP_DBA and AP_USER
I want to have a trigger :
If AP_DBA create a new_table,grant SELECT on new_table to AP_USER;

First,I create a procedure of SYS:
CREATE OR REPLACE PROCEDURE obj_pri

      (obj_nm IN CHAR)
AS

    cursor_name INTEGER;
    rows_processed INTEGER;
BEGIN
    cursor_name := dbms_sql.open_cursor;     dbms_sql.parse(cursor_name, 'GRANT SELECT ON ' || obj_nm || ' TO AP_USER',

                   dbms_sql.native);

    rows_processed := dbms_sql.execute(cursor_name);     dbms_sql.close_cursor(cursor_name); EXCEPTION
WHEN OTHERS THEN
    dbms_sql.close_cursor(cursor_name); END; Then,I find 2 tables of SYS : sys.user$ and sys.obj$ From sys.user$,I get user# = 22 where NAME = 'AP_DBA'. From sys.obj$,I get type = 1 where NAME = 'TABLE_NAME'.

I want to create a trigger of sys.obj$: CREATE OR REPLACE TRIGGER ins_obj

       AFTER INSERT ON OBJ$ FOR EACH ROW 
       WHEN new.owner# = 22 and new.type = 1
BEGIN
     EXEC obj_pri(:new.name)

END ins_obj;

But it does not work,cause SYS could not grant AP_DBA's object privilege to AP_USER.
How could SYS become AP_DBA during executing the trigger?

CREATE OR REPLACE TRIGGER ins_obj

       AFTER INSERT ON OBJ$ FOR EACH ROW 
       WHEN new.owner# = 22 and new.type = 1
BEGIN
     /* BECOME AP_DBA FROM SYS*/     

     EXEC obj_pri(:new.name)

END ins_obj;

Help me with any idea or suggestion.
I'll appreciate it very much.
Best Regards.

Violin.
violin.hsiao_at_mail.pouchen.com.tw Received on Thu Apr 08 1999 - 22:55:31 CDT