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!

Drop table privelege

Discussion in 'Server Administration and Options' started by Tariq Bashir Malhi, Nov 30, 2010.

  1. Tariq Bashir Malhi

    Tariq Bashir Malhi Forum Advisor

    Messages:
    44
    Likes Received:
    0
    Trophy Points:
    80
    How i can grant a user rights to drop some tables, rather than granting Drop any table right.
     
  2. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    You cannot directly grant such a privilege on selected tables. You can write a procedure to provide that functionality and grant execute on that procedure to the desired users:

    Code (SQL):
     
    SQL> CREATE TABLE mytab AS SELECT * FROM emp;
    TABLE created.
    SQL>
    SQL> --
    SQL> -- Grant selected privileges to users
    SQL> --
    SQL> -- Note that DROP ANY TABLE is not granted
    SQL> --
    SQL> GRANT SELECT, UPDATE, DELETE ON mytab TO blerb;
    GRANT succeeded.
    SQL> GRANT SELECT, UPDATE(empno, ename, job) ON mytab TO gleebo;
    GRANT succeeded.
    SQL>
    SQL> --
    SQL> -- Create procedure to drop MYTAB
    SQL> --
    SQL> -- Works as expected to allow non-administrative users with the proper
    SQL> -- privleges to drop the table
    SQL> --
    SQL> CREATE OR REPLACE PROCEDURE drop_mytab
      2  AS
      3
      4          is_allowed NUMBER:=0;
      5          tbl_own varchar2(35):='BING';
      6          tbl_nm varchar2(35):='MYTAB';
      7          not_allowed exception;
      8          pragma exception_init(not_allowed, -20099);
      9
     10  BEGIN
     11          SELECT SUM(decode(privilege, 'SELECT', 1, 'UPDATE', 1, 'DELETE', 1, 0))
     12          INTO is_allowed
     13          FROM user_tab_privs
     14          WHERE TABLE_NAME = 'MYTAB'
     15          AND grantee = USER
     16          GROUP BY TABLE_NAME;
     17
     18          IF is_allowed = 3 THEN
     19                  EXECUTE immediate 'drop table '||tbl_own||'.'||tbl_nm;
     20          ELSE
     21                  raise not_allowed;
     22          END IF;
     23  exception
     24          WHEN not_allowed THEN
     25                  raise_application_error(-20099, 'User not permitted to drop MYTAB');
     26  END;
     27  /
    PROCEDURE created.
    SQL>
    SQL> SHOW errors
    No errors.
    SQL>
    SQL> --
    SQL> -- Allow regular users to execute the procedure
    SQL> --
    SQL> GRANT EXECUTE ON drop_mytab TO blerb;
    GRANT succeeded.
    SQL> GRANT EXECUTE ON drop_mytab TO gleebo;
    GRANT succeeded.
    SQL>
    SQL> --
    SQL> -- Connect as regular user
    SQL> --
    SQL> CONNECT blerb/yakka
    Connected.
    SQL>
    SQL> --
    SQL> -- This fails for lack of privilege
    SQL> --
    SQL> DROP TABLE bing.mytab;
    DROP TABLE bing.mytab
                    *
    ERROR at line 1:
    ORA-01031: insufficient privileges

    SQL>
    SQL> SELECT * FROM bing.mytab;
         EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM
    ---------- ---------- --------- ---------- --------- ---------- ----------
        DEPTNO
    ----------
          7369 SMITH      CLERK           7902 17-DEC-80        800
            20
          7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300
            30
          7521 WARD       SALESMAN        7698 22-FEB-81       1250        500
            30

         EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM
    ---------- ---------- --------- ---------- --------- ---------- ----------
        DEPTNO
    ----------
          7566 JONES      MANAGER         7839 02-APR-81       2975
            20
          7654 MARTIN     SALESMAN        7698 28-SEP-81       1250       1400
            30
          7698 BLAKE      MANAGER         7839 01-MAY-81       2850
            30

         EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM
    ---------- ---------- --------- ---------- --------- ---------- ----------
        DEPTNO
    ----------
          7782 CLARK      MANAGER         7839 09-JUN-81       2450
            10
          7788 SCOTT      ANALYST         7566 09-DEC-82       3000
            20
          7839 KING       PRESIDENT            17-NOV-81       5000
            10

         EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM
    ---------- ---------- --------- ---------- --------- ---------- ----------
        DEPTNO
    ----------
          7844 TURNER     SALESMAN        7698 08-SEP-81       1500          0
            30
          7876 ADAMS      CLERK           7788 12-JAN-83       1100
            20
          7900 JAMES      CLERK           7698 03-DEC-81        950
            30

         EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM
    ---------- ---------- --------- ---------- --------- ---------- ----------
        DEPTNO
    ----------
          7902 FORD       ANALYST         7566 03-DEC-81       3000
            20
          7934 MILLER     CLERK           7782 23-JAN-82       1300
            10

    14 ROWS selected.
    SQL>
    SQL> --
    SQL> -- This works
    SQL> --
    SQL> EXEC bing.drop_mytab
    PL/SQL PROCEDURE successfully completed.
    SQL>
    SQL> DESC bing.mytab;
    ERROR:
    ORA-04043: object bing.mytab does NOT exist

    SQL>
    SQL> CONNECT bing/bong
    Connected.
    SQL>
    SQL> --
    SQL> -- Recreate the table
    SQL> --
    SQL> CREATE TABLE mytab AS SELECT * FROM emp;
    TABLE created.
    SQL> GRANT SELECT, UPDATE, DELETE ON mytab TO blerb;
    GRANT succeeded.
    SQL> GRANT SELECT, UPDATE(empno, ename, job) ON mytab TO gleebo;
    GRANT succeeded.
    SQL>
    SQL> --
    SQL> -- Connect as regular user
    SQL> --
    SQL> CONNECT gleebo/uarm
    Connected.
    SQL>
    SQL> --
    SQL> -- Fails for lack of privilege
    SQL> --
    SQL> DROP TABLE bing.mytab;
    DROP TABLE bing.mytab
                    *
    ERROR at line 1:
    ORA-01031: insufficient privileges

    SQL>
    SQL> --
    SQL> -- This fails, too, for lack of privilege
    SQL> --
    SQL> EXEC bing.drop_mytab
    BEGIN bing.drop_mytab; END;
    *
    ERROR at line 1:
    ORA-20099: USER NOT permitted TO DROP MYTAB
    ORA-06512: at "BING.DROP_MYTAB", line 25
    ORA-06512: at line 1

    SQL>
    SQL> DESC bing.mytab
     Name                                      NULL?    TYPE
     ----------------------------------------- -------- ----------------------------
     EMPNO                                     NOT NULL NUMBER(4)
     ENAME                                              VARCHAR2(10)
     JOB                                                VARCHAR2(9)
     MGR                                                NUMBER(4)
     HIREDATE                                           DATE
     SAL                                                NUMBER(7,2)
     COMM                                               NUMBER(7,2)
     DEPTNO                                             NUMBER(2)
    SQL>
    SQL> SELECT * FROM bing.mytab;
         EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM
    ---------- ---------- --------- ---------- --------- ---------- ----------
        DEPTNO
    ----------
          7369 SMITH      CLERK           7902 17-DEC-80        800
            20
          7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300
            30
          7521 WARD       SALESMAN        7698 22-FEB-81       1250        500
            30

         EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM
    ---------- ---------- --------- ---------- --------- ---------- ----------
        DEPTNO
    ----------
          7566 JONES      MANAGER         7839 02-APR-81       2975
            20
          7654 MARTIN     SALESMAN        7698 28-SEP-81       1250       1400
            30
          7698 BLAKE      MANAGER         7839 01-MAY-81       2850
            30

         EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM
    ---------- ---------- --------- ---------- --------- ---------- ----------
        DEPTNO
    ----------
          7782 CLARK      MANAGER         7839 09-JUN-81       2450
            10
          7788 SCOTT      ANALYST         7566 09-DEC-82       3000
            20
          7839 KING       PRESIDENT            17-NOV-81       5000
            10

         EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM
    ---------- ---------- --------- ---------- --------- ---------- ----------
        DEPTNO
    ----------
          7844 TURNER     SALESMAN        7698 08-SEP-81       1500          0
            30
          7876 ADAMS      CLERK           7788 12-JAN-83       1100
            20
          7900 JAMES      CLERK           7698 03-DEC-81        950
            30

         EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM
    ---------- ---------- --------- ---------- --------- ---------- ----------
        DEPTNO
    ----------
          7902 FORD       ANALYST         7566 03-DEC-81       3000
            20
          7934 MILLER     CLERK           7782 23-JAN-82       1300
            10

    14 ROWS selected.
    SQL>

     
    This example is using a conditional drop (if the user does not possess all three desired privileges the drop fails) however you can code this to simply be a 'straight' drop presuming the user has execute privilege on the procedure.