Grant column privilege to user in Oracle

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.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.