1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Allowing only Creation of Global Temporary Table in Schema

Discussion in 'General' started by thiagesh, Dec 21, 2011.

  1. thiagesh

    thiagesh Guest

    Hi All,

    How to allow only "CREATE GLOBAL TEMPORARY TABLE" DDL in a schema.

    I have to retrict all DDLs performing by a particular schema except GT Table.

    Thanks

    Regards
    Thiagesh.
     
  2. kiran.marla

    kiran.marla Forum Genius

    Messages:
    403
    Likes Received:
    52
    Trophy Points:
    505
    Location:
    Khammam
    Oracle provides Trigger concept to do the automated actions.
     
  3. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    It would have been nice to see an example with your response. Since you didn't provide one I will:

    Code (SQL):
     
    SQL>
    SQL> CREATE OR REPLACE TRIGGER allow_gt_only
      2  BEFORE CREATE ON bing.schema
      3  DECLARE
      4    sql_text DBMS_STANDARD.ora_name_list_t;
      5    n PLS_INTEGER;
      6    v_stmt VARCHAR2(2000);
      7  BEGIN
      8        n := ora_sql_txt(sql_text);
      9  
     10        FOR i IN 1..n LOOP
     11          v_stmt := v_stmt || sql_text(i);
     12        END LOOP;
     13  
     14        IF instr(UPPER(v_stmt), 'GLOBAL TEMPORARY TABLE') = 0
     15        THEN
     16         raise_application_error(-20001, 'Not a global temporary table');
     17        END IF;
     18  
     19  END;
     20  /
     
    TRIGGER created.
     
    SQL>
    SQL> CONNECT bing/bong
    Connected.
    SQL> SET echo ON
    SQL>
    SQL> CREATE TABLE mytab(
      2        id NUMBER,
      3        val varchar2(40)
      4  );
    CREATE TABLE mytab(
    *
    ERROR at line 1:
    ORA-00604: error occurred at recursive SQL level 1
    ORA-20001: NOT a global TEMPORARY TABLE
    ORA-06512: at line 14

    SQL>
    SQL> CREATE global TEMPORARY TABLE mytabtmp(
      2        id NUMBER,
      3        val varchar2(40)
      4  );
     
    TABLE created.
     
    SQL>