Results 1 to 11 of 11
  1. #1
    Join Date
    Dec 2003
    Posts
    1,074

    Unanswered: DDL Trigger not working

    I'm creating the following DDL trigger (the RAISE_APPLICATION_ERRORs are just in there for debug). I get no errors on the compile:

    CREATE OR REPLACE TRIGGER hr.dba_trgac_ddl AFTER CREATE ON SCHEMA
    DECLARE
    grant_select VARCHAR2(30);
    BEGIN
    if ORA_DICT_OBJ_TYPE = 'TABLE'
    THEN
    grant_select := 'GRANT SELECT ON ' || ORA_DICT_OBJ_NAME || ' TO ' || ORA_DICT_OBJ_OWNER || '_SELECT_ROLE';
    EXECUTE IMMEDIATE grant_select;
    RAISE_APPLICATION_ERROR (-20000, grant_select || 'executed');
    ELSE
    RAISE_APPLICATION_ERROR (-20000, grant_select || ' NOT executed');
    END IF;
    END;


    When I add a table in the HR schema, though, these rights are not being granted to HR_SELECT_ROLE. The RAISE_APPLICATION_ERRORs aren't providing messages to the screen either. All I get is:

    SQL> create table hr.chuck (myname varchar2(30));
    Table created.

    Anyone have an idea what I've done incorrectly?
    -Chuck

  2. #2
    Join Date
    Mar 2004
    Location
    California
    Posts
    58
    Perhaps a way to check this is to print out grant_select to the screen and see what value the variable holds.

  3. #3
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    First of all, grant_select varchar2(30) is too short in size to be able to contain all the text you're trying to put in ('GRANT SELECT ON ' || ORA_DICT_OBJ_NAME || ' TO ' || ORA_DICT_OBJ_OWNER || '_SELECT_ROLE'; ).

    I tried to execute it on my DB but run into "ORA-30511: invalid DDL operation in system triggers". Never seen it before ... As if GRANT SELECT can't be executed from such an trigger. Why not? No idea so far.
    Last edited by Littlefoot; 04-06-04 at 04:46.

  4. #4
    Join Date
    Dec 2003
    Posts
    1,074
    Right on the varchar length, sorry for that mistake. I also found out that this trigger is only firing when a CREATE stmt is executed by the schema owner, but not when a user with CREATE ANY TABLE privs tries to create the table in the HR schema.

    I am getting the same DDL error as well, which looks like the GRANT request cannot be made in the trigger. I am going to try and create a procedure which grants the same rights, and if that works, call it from the trigger and see what happens.

    -Chuck

  5. #5
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    No need to bother, Chuck ... it won't work either. I tried it too As the procedure is called from the trigger, it raises just the same exception (30511).

    I found something about it on O'Reilly's website (procedure that should fix the problem is called ... hmmm, "fixer.fix" or something like that. But, couldn't find that fixer thing. I suppose it must be written somewhere in Feuerstein's PL/SQL Programming book (which I don't have, unfortunatelly). So, if you have it (or have access to it), check for the fixer. Maybe it'll help.

  6. #6
    Join Date
    Dec 2003
    Posts
    1,074
    Don't know in which version it came out, but I found a privilege which I wish I'd found earlier:

    the "GRANT ANY OBJECT PRIVILEGE" privilege

    better late than never. Anyone know of a drawback to providing this priv?
    -Chuck

  7. #7
    Join Date
    Apr 2002
    Location
    California, USA
    Posts
    482
    Carefull when using it:

    GRANT ANY OBJECT PRIVILEGE - Grant any object privilege

    Revoke any object privilege that was granted by the object owner or by some other user with the GRANT ANY OBJECT PRIVILEGE privilege


    HTH,

    clio_usa - OCP 8/8i/9i DBA

  8. #8
    Join Date
    Dec 2003
    Posts
    1,074
    If you have the init param O7_DICTIONARY_ACCESSIBILITY=FALSE, will someone with this privilege still be able to alter privileges on objects in the SYS schema?

    -Chuck

  9. #9
    Join Date
    Jul 2003
    Posts
    2,296
    DDL uses an impicit commit

    You might need to add pragma AUTONOMOUS_TRANSACTION;
    to the trigger in order to get the trigger to work.
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  10. #10
    Join Date
    Apr 2004
    Location
    USA
    Posts
    33
    Or create a job (DBMS_JOB.SUBMIT) inside the trigger and do the 'Grant' inside the job. The job can be set to run x seconds after the trigger is executed.

  11. #11
    Join Date
    Jul 2003
    Posts
    2,296
    Quote Originally Posted by arvindram
    Or create a job (DBMS_JOB.SUBMIT) inside the trigger and do the 'Grant' inside the job. The job can be set to run x seconds after the trigger is executed.
    this also works well.
    be sure to commit after you set up the job or you will get errors.
    - The_Duck
    you can lead someone to something but they will never learn anything ...

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •