title: ORACLE PL/SQL - GRANT/REVOKE Privilege
body: <p>I want to be able to insert data in my table. As soon as the data is insert, people can only UPDATE one column in the whole table. I try to to this below but i get the error - ORA-04092: cannot COMMIT or ROLLBACK in a trigger : </p>
  
  <p>Here's my triggers. My idea was to give all the privilege to let the user insert data. After data is insert, remove the UPDATE privilege on only one column. Let say we have this table : </p>
  
  <pre><code>CREATE TABLE tabelName(
      col1 INTEGER,
      col2 INTEGER,
      col3 INTEGER,
      col4 INTEGER
  );
  
  
  CREATE OR REPLACE TRIGGER TRG_TABLENAME_BI
  BEFORE INSERT
  ON TABLENAME
  
  BEGIN
      EXECUTE IMMEDIATE 'GRANT ALL ON TABLENAME TO PUBLIC';
  END;
  /
  
  CREATE OR REPLACE TRIGGER TRG_TABLENAME_AI
  AFTER INSERT
  ON TABLENAME
  
  BEGIN
      EXECUTE IMMEDIATE 'REVOKE UPDATE (col1,col2,col3) TABLENAME to PUBLIC';
  END;
  /
  </code></pre>
  
  <p>So at the end, we can only UPDATE the col4 of tableName after we insert data in the table. If we do :</p>
  
  <pre><code>INSERT INTO tableName VALUES(1,2,3,4);
  1 row created
  </code></pre>
  
  <p>I can only do</p>
  
  <pre><code>UPDATE tableName
  SET col4= 10
  WHERE col1=1;
  </code></pre>
  
  <p>This UPDATE below wont work : </p>
  
  <pre><code>UPDATE tableName
  SET col2= 10
  WHERE col1=1;
  </code></pre>
  
  <p>But i dont know how to figure it out.
  Thank you.</p>