CODEWITHSUNDEEP
oracle12csqlplus
w_m02
w_m02

Reputation: 1

ORACLE 12C Grant update on a specific column not working

I'm working with Oracle 12c sqlplus. I want to grant update on just **name ** column for a role like below but I still change value in other column.

This is my code:

SQL> grant select,update(name) on attendance to updatename;

Grant succeeded.

Upvotes: 0

Views: 416

Answers (1)

Alex Poole
Alex Poole

Reputation: 191335

Doing:

grant update(name) on attendance to updatename;

would not revoke a previous plain grant update on.... Privileges are cumulative, so if you have both update and update(name) then that would still allow any column to be modified.

You can see whether there is also a full update privilege by querying the dba_tab_privs or all_tab_privs view:

select *
from all_tab_privs
where grantee = 'UPDATENAME' -- uppercase unless you have a quoted identifier
and table_name = 'ATTENDANCE' -- uppercase unless you have a quoted identifier

You can see individual column privileges in dba_col_privs or all_col_privs.

If there has previously been a full grant then you would need to revoke that first:

revoke update on attendance from updatename;
grant update(name) on attendance to updatename;

The user could also have full update privileges on the table via a role, not a direct grant, in which case you would need to either modify the role (affecting all users with it) or revoke the role from the user.

Upvotes: 0

Related Questions