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!

A conditional truncate problem - Need help

Discussion in 'SQL PL/SQL' started by neel134, Nov 2, 2010.

  1. neel134

    neel134 Active Member

    Messages:
    9
    Likes Received:
    0
    Trophy Points:
    55
    Hi,

    I have a specific requirement, which I am not very sure how to tackle even though I have come up with a few solutions. Can someone please present a good solution to the problem given below:

    I have a table whose owner is 'A'. Now other users will be accessing this table and truncating its data and inserting new data. But all users should not be able to truncate its data. Only those users, which have 'select','insert','delete' privilege on this table, should be able to truncate its content. Please note, data has to be truncated and not deleted as its a datawarehouse with huge data.

    So when a user fires the truncate command on this table, i should first check whether this user has 'select','insert','delete' privilege on this table. This can be done by checking the DBA_USER_PRIVS view. Only if this privilege exists, then we should truncate the table, which can be fired from user A.

    So the basic requirement is that when a user tries to truncate this table, a procedure should be called which will do this check.

    Any good way to do this? Either by a proc or a trigger? Any steps,code or pseudocode will be helpful.

    Thanks
    Neel
     
  2. SBH

    SBH Forum Expert

    Messages:
    99
    Likes Received:
    6
    Trophy Points:
    160
    Location:
    Bangalore
    You can trr to create a DDL trigger like below. Be reminded DDL triggers are at schema level and not at objet level.

    Code (SQL):
    CREATE OR REPLACE TRIGGER  truncate_dll  after ddl ON DATABASE
    BEGIN
    IF (ora_sysevent='TRUNCATE') AND (ora_dict_obj_name = 'MYTAB')
    THEN
    --------check the privilege--------
    -------if not allowed--------
    ------raise exception--------
    END IF

    END;
    You can not handle such requirement with Procedure as TRUNCATE is not a procedural construct.
     
  3. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,346
    Likes Received:
    347
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    That is not true as the trigger can call a procedure to complete the task of verification; a function could also be called by this same trigger. However you don't need any such trigger as truncate requires that the user either own the table being truncated or be granted the DROP ANY TABLE privilege:

    Code (SQL):
     
    SQL> CREATE TABLE mytab AS SELECT * FROM emp;
     
    TABLE created.
     
    SQL>
    SQL> GRANT SELECT, UPDATE, DELETE ON mytab TO blerb;
     
    GRANT succeeded.
     
    SQL>
    SQL> CONNECT blerb/yakka
    Connected.
    SQL>
    SQL> TRUNCATE TABLE bing.mytab;
    TRUNCATE TABLE bing.mytab
                        *
    ERROR at line 1:
    ORA-01031: insufficient privileges

    SQL>

     
    If you're granting DROP ANY TABLE to non-administrative users then you have a larger problem than this table truncation issue.
     
    kiran.marla likes this.
  4. kiran.marla

    kiran.marla Forum Genius

    Messages:
    403
    Likes Received:
    52
    Trophy Points:
    505
    Location:
    Khammam
    The non-administrative users can not truncate the table data ever.
    though if you are granting ALL privileges , ALL includes SELECT, INSERT, DELETE, UPDATE only.
    Neel's requirement is that if user has SELECT, INSERT, DELETE previleges, he can truncate the data. otherwise it should be restricted from truncating the table data.

    I agree with SBH's argument.
     
  5. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,346
    Likes Received:
    347
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Certainly they can provided they have been granted DROP ANY TABLE:

    Code (SQL):
     
    SQL> CONNECT / AS sysdba
    Connected.
    SQL>
    SQL> GRANT DROP any TABLE TO blerb;
     
    GRANT succeeded.
     
    SQL>
    SQL> CONNECT blerb/yakka
    Connected.
    SQL>
    SQL> TRUNCATE TABLE bing.mytab;
     
    TABLE truncated.
     
    SQL>

     
    It doesn't matter if the user has all other privileges if DROP ANY TABLE is not granted then the user cannot truncate any table outside of his or her own schema.
     
  6. kiran.marla

    kiran.marla Forum Genius

    Messages:
    403
    Likes Received:
    52
    Trophy Points:
    505
    Location:
    Khammam
    Ultimately you have connected to Sysdba.
     
  7. neel134

    neel134 Active Member

    Messages:
    9
    Likes Received:
    0
    Trophy Points:
    55
    Thanks for the suggestions and thanks SBH. I am trying this option out along with a few other alternatives. I thought of another option given below. Will this work?
    Suppose i have a procedure in the schema 'A', the owner of the table concerned.
    In that procedure, we have a truncate table statement with execute immediate.
    Now we grant execute privilege on this procedure to any other user.
    Then from this DDL trigger mentioned by SBH above, we call that procedure.
    Will the truncate on this table function successfully now?

    Thanks
    Neel
     
  8. SBH

    SBH Forum Expert

    Messages:
    99
    Likes Received:
    6
    Trophy Points:
    160
    Location:
    Bangalore
    Neel,
    Here comes the role of Invoker' rights and Definer rights !!!
    As per your comment, I understand as below

    1. You have created procedure P1 in schema 'A' with Definer rights. You are trying to truncate table T1 using NDS. Table T1 is owned by 'A'.
    Remarks: Table will be truncated successfully, if 'A' executes the procedure

    2. You grant EXECUTE privilege on P1 to schema 'B'. 'B' is not the owner of the table T1.
    Remarks: In the procedure, you can check the owner of the table. If it is current logged in user, then truncate is executed. Otherwise, if current logged in user is not the table owner AND does not possess the DELETE privilege on the table , then raise exception. I would recommend that even if DBA has grant DROP ANY TABLE privilege to user 'B', system should not allow to Truncate any table with just owner reference.

    Then you can invoke this procedure in TRUNCATE DDL trigger.

    hope it helps you out.
     
  9. neel134

    neel134 Active Member

    Messages:
    9
    Likes Received:
    0
    Trophy Points:
    55
    ummm, what SBH said is not exactly clear to me. So I need some clarifications.

    You are correct in saying that 'P1' is defined in schema 'A' and execute privilege on P1 has been given to user 'B'. 'B' is not the owner of T1. Now I am calling P1 from 'B'.
    So in this scenario, the truncate statement in the procedure will fail. Is that what you mean?

    Then you said, "if current logged in user is not the table owner AND does not possess the DELETE privilege on the table, then raise exception " - this will serve my purpose of preventing a user from truncating, who doesn't have delete privilege on the table. But then, when the user is not the table owner and HAS the delete privilege on this table, then how do I make this user truncate the table? All this has to be done at run time through a pl/sql code, as I cannot login as the table owner and grant a 'Drop any table' privilege at that time and then revoke the privilege after the truncation has been done?
    Any ideas? Anyway I can include a grant n revoke statement, before and after truncation, in the pl/sql code which is running from user 'B'?

    Thanks
    Neel
     
  10. SBH

    SBH Forum Expert

    Messages:
    99
    Likes Received:
    6
    Trophy Points:
    160
    Location:
    Bangalore

    Yes, because a DDL statement in a PL/SQL block executes only if the Invoking user has the direct DROP ANY TABLE privilege to execute this DDL, even if 'B' has execute privilege on the procedure. Here I assume, B does not has DROP ANY TABLE privilege and only SELECT, INSERT, UPDATE, DELETE on T1 and EXECUTE on P1

    For this purpose, I would suggest to grant DROP ANY TABLE privilege from DBA to all the users.
     
  11. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,346
    Likes Received:
    347
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    That's a rather severe recommendation given that DROP ANY TABLE means exactly that; any user granted that privilege can drop ANY table, even those owned by SYSTEM and SYS which includes any table in the data dictionary. Would you want your database compromised in that manner? I wouldn't, and I wouldn't suggest such a grant.
     
  12. SBH

    SBH Forum Expert

    Messages:
    99
    Likes Received:
    6
    Trophy Points:
    160
    Location:
    Bangalore
    I understand its a serious recommendation but got puzzled too, is there any other solution !!
    DELETE can be done but that option was already blocked in the first post :)
     
  13. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,346
    Likes Received:
    347
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Definers rights works in this situation:

    Code (SQL):
     
    SQL> CREATE TABLE mytab AS SELECT * FROM emp;
     
    TABLE created.
     
    SQL>
    SQL> GRANT SELECT, UPDATE, DELETE ON mytab TO blerb;
     
    GRANT succeeded.
     
    SQL> GRANT SELECT, UPDATE ON mytab TO gleebo;
     
    GRANT succeeded.
     
    SQL>
    SQL> CREATE OR REPLACE PROCEDURE trunc_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 'truncate 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 truncated MYTAB');
     26  END;
     27  /
     
    PROCEDURE created.
     
    SQL>
    SQL> SHOW errors

    No errors.

    SQL>
    SQL> GRANT EXECUTE ON trunc_mytab TO blerb;
     
    GRANT succeeded.
     
    SQL> GRANT EXECUTE ON trunc_mytab TO gleebo;
     
    GRANT succeeded.
     
    SQL>
    SQL> CONNECT blerb/yakka
    Connected.
    SQL>
    SQL> TRUNCATE TABLE bing.mytab;
    TRUNCATE 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> EXEC bing.trunc_mytab
     
    PL/SQL PROCEDURE successfully completed.
     
    SQL>
    SQL> SELECT * FROM bing.mytab;
     
    no ROWS selected
     
    SQL>
    SQL> CONNECT bing/bong
    Connected.
    SQL>
    SQL> INSERT INTO mytab SELECT * FROM emp;
     
    14 ROWS created.
     
    SQL>
    SQL> commit;
     
    Commit complete.
     
    SQL>
    SQL> CONNECT gleebo/uarm
    Connected.
    SQL>
    SQL> EXEC bing.trunc_mytab
    BEGIN bing.trunc_mytab; END;
    *
    ERROR at line 1:
    ORA-20099: USER NOT permitted TO truncated MYTAB
    ORA-06512: at "BING.TRUNC_MYTAB", line 25
    ORA-06512: at line 1

    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>

     
     
  14. neel134

    neel134 Active Member

    Messages:
    9
    Likes Received:
    0
    Trophy Points:
    55
    Thanks David. That was really helpful. So as per my understanding, such a procedure to truncate can also be invoked from the DDL trigger. And that should also successfully truncate. Am I right?
    Anyway, I have 2 options now, n I'll be trying them out. Thanks to all of you.
     
  15. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,346
    Likes Received:
    347
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    No, that is incorrect. For truncate to work the user MUST be granted DROP ANY TABLE otherwise the trigger won't fire. The procedure is written to avoid such a grant to non-administrative users and though it can be used in a trigger such a trigger will never be executed:

    Code (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 truncate MYTAB
    SQL> --
    SQL> -- Works as expected to allow non-administrative users with the proper
    SQL> -- privleges to truncate the table
    SQL> --
    SQL> CREATE OR REPLACE PROCEDURE trunc_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 'truncate 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 truncate 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 trunc_mytab TO blerb;
     
    GRANT succeeded.
     
    SQL> GRANT EXECUTE ON trunc_mytab TO gleebo;
     
    GRANT succeeded.
     
    SQL>
    SQL> --
    SQL> -- Create truncate trigger
    SQL> --
    SQL> -- Will never fire for regular users who do not have
    SQL> -- DROP ANY TABLE privilege
    SQL> --
    SQL> CREATE OR REPLACE TRIGGER trunc_trg
      2  after ddl ON schema
      3  BEGIN
      4          bing.trunc_mytab;
      5  END;
      6  /
     
    TRIGGER created.
     
    SQL>
    SQL> SHOW errors

    No errors.

    SQL>
    SQL> --
    SQL> -- Connect as regular user
    SQL> --
    SQL> CONNECT blerb/yakka
    Connected.
    SQL>
    SQL> --
    SQL> -- This fails for lack of privilege so trigger never fires
    SQL> --
    SQL> TRUNCATE TABLE bing.mytab;
    TRUNCATE 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.trunc_mytab
     
    PL/SQL PROCEDURE successfully completed.
     
    SQL>
    SQL> SELECT * FROM bing.mytab;
     
    no ROWS selected
     
    SQL>
    SQL> CONNECT bing/bong
    Connected.
    SQL>
    SQL> --
    SQL> -- Re-populate the table
    SQL> --
    SQL> INSERT INTO mytab SELECT * FROM emp;
     
    14 ROWS created.
     
    SQL>
    SQL> commit;
     
    Commit complete.
     
    SQL>
    SQL> --
    SQL> -- Connect as regular user
    SQL> --
    SQL> CONNECT gleebo/uarm
    Connected.
    SQL>
    SQL> --
    SQL> -- Fails for lack of privilege
    SQL> --
    SQL> TRUNCATE TABLE bing.mytab;
    TRUNCATE 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.trunc_mytab
    BEGIN bing.trunc_mytab; END;
    *
    ERROR at line 1:
    ORA-20099: USER NOT permitted TO TRUNCATE MYTAB
    ORA-06512: at "BING.TRUNC_MYTAB", line 25
    ORA-06512: at line 1

    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> UPDATE bing.mytab SET sal=sal*1.2;
    UPDATE bing.mytab SET sal=sal*1.2
                *
    ERROR at line 1:
    ORA-01031: insufficient privileges

    SQL>

     
     
  16. neel134

    neel134 Active Member

    Messages:
    9
    Likes Received:
    0
    Trophy Points:
    55
    Ya Thanks David, I also tried that to be not working.