ORACLE PL/SQL - GRANT/REVOKE Privilege

Question

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 :

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 :

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;
/

So at the end, we can only UPDATE the col4 of tableName after we insert data in the table. If we do :

INSERT INTO tableName VALUES(1,2,3,4);
1 row created

I can only do

UPDATE tableName
SET col4= 10
WHERE col1=1;

This UPDATE below wont work :

UPDATE tableName
SET col2= 10
WHERE col1=1;

But i dont know how to figure it out. Thank you.

Answer 1

You cannot grant or revoke privileges in a trigger. It seems like you just want to grant users the ability to INSERT and UPDATE(col4).

CREATE TABLE tableName(
    col1 INTEGER,
    col2 INTEGER,
    col3 INTEGER,
    col4 INTEGER
);

GRANT INSERT ON tableName TO public;

GRANT UPDATE(col4) ON tableName TO public;

Of course, this only affects the privileges that other users have on the table. The owner of the table will always have permissions to change the data in the table. I'm also assuming that you're not really granting privileges to public but to some user or role that you've defined in your system related to the business role(s) that need to modify data.