ORACLE PL / SQL - GRANT / REVOKE privilege
I want to be able to insert data into my table. Once the data is inserted, people can only UPDATE one column in the entire table. I am trying to do it below, but I am getting error - ORA-04092: Cannot COMMIT or ROLLBACK in trigger:
Here are my triggers. My idea was to grant all privileges for the user to insert data. After entering the data, remove the UPDATE privilege on only one column. Let's 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 in the end we can only UPDATE the col4 tableName after we insert the data into the table. If we do this:
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 doesn't work:
UPDATE tableName
SET col2= 10
WHERE col1=1;
But I don't know how to figure it out. Thank.
source to share
You cannot use privileges grant
or revoke
in a trigger. It sounds like you just want to provide users with the option 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 modify the data in the table. I also assume that you are not really granting privileges public
, but to a specific user or role that you have defined in your system associated with the business role that needs to modify the data.
source to share