Grant or revoke column privilege to user in Oracle

Note: Only INSERT,UPDATE can be granted at COLUMN level.

Syntax:
GRANT insert(column_name) ON user1.tablename to user2;
Example:
GRANT insert(emp_id) ON TESTUSER.EMPLOYEES TO SCOTT;

GRANT UPDATE(emp_id) ON TESTUSER.EMPLOYEES TO SCOTT;

GRANT update (ename),insert (emp_id, ename)  ON testuser.employees TO SCOTT;

Check the Column Privileges

Select grantee, owner, table_name, column_name, grantor, privileges,grantable from DBA_COL_PRIVS;

Example of Grant and Revoke column Privilege’s

---Conn hr@PDB1

--Check the Column Privileges already present
SQL> select column_name from dba_col_privs where column_name is not null;

COLUMN_NAME
----------------------------
FIELD_3
FIELD_2
FIELD_1

3 rows selected.

--Grant the column privilege's to TEST1 user
SQL> grant update(employee_id) on hr.employees to test1;

Grant succeeded.

SQL> select column_name from dba_col_privs where column_name is not null;

COLUMN_NAME
----------------------------
EMPLOYEE_ID
FIELD_3
FIELD_2
FIELD_1

4 rows selected.

SQL> revoke update(employee_id) on hr.employees from test1;
revoke update(employee_id) on hr.employees from test1
             *
ERROR at line 1:
ORA-01750: UPDATE/REFERENCES may only be REVOKEd from the whole table, not by
column

--Revoke the column priviliges

SQL> revoke update on hr.employees from test1;

Revoke succeeded.


SQL> select column_name from dba_col_privs where column_name is not null;

COLUMN_NAME
---------------------------
FIELD_3
FIELD_2
FIELD_1

3 rows selected.