Skip to Main Content
  • Questions

  • Oracle 12.2: Delete Privilege needs an additional Select Privilege

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Peter.

Asked: February 26, 2019 - 7:00 am UTC

Last updated: December 04, 2024 - 6:01 am UTC

Version: 12.2

Viewed 10K+ times! This question is

You Asked

Hi,

Why do we need an extra select privilege additional to the delete privilege? From my point of view, this does'nt make sense:

--execute as user a:
create table b.t (col varchar2 (10));

insert into b.t values ('a');

commit;

grant delete on b.t to ut;

--execute as user ut:
delete from b.t where col = 'a';
--  ==> ORA-01031: insufficient privileges

--execute as user a:
grant select on b.t to ut;

--execute as user ut:
delete from b.t where col = 'a';
-- => 1 row deleted


Thanks,
Peter

PS: The 4th final question is logically incorrect :-)

and Chris said...

Yep, that's as documented:

If the SQL92_SECURITY initialization parameter is set to TRUE and the DELETE operation references table columns, such as the columns in a where_clause, then you must also have the SELECT object privilege on the object from which you want to delete rows.

https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/DELETE.html#GUID-156845A5-B626-412B-9F95-8869B988ABD7

So if you only grant delete, a plain delete with no where clause will work. But you need select to use a where clause (assuming you've set SQL92_SECURITY; true is the default).

Rating

  (5 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Peter, February 26, 2019 - 1:34 pm UTC

Many thanks for the quick answer.

Peter

oh oh ... new default

Laurent Schneider, February 26, 2019 - 1:43 pm UTC

Good to know... it used to be false until 12.1

https://docs.oracle.com/en/database/oracle/oracle-database/12.2/upgrd/initialization-parameter-changes-oracle-database-12c-r2.html#GUID-BBBA10C4-F00A-4A9A-95A8-CCD925E4041C
Starting in Oracle Database 12c release 2 (12.2), the default setting for this parameter changes from FALSE to TRUE.

Chris Saxon
February 26, 2019 - 2:18 pm UTC

Good spot, thanks for sharing Laurent.

Peter, February 27, 2019 - 6:02 am UTC

Yes, that was my problem - migration from 12.1 to 12.2.

Just quessing

lh, February 27, 2019 - 8:16 am UTC

Hi

The reason for the need of select privilege might come from attempt to prevent reading data by doing delete.

If You have in delete statement a where clause and You execute it (and rollback), database engine is revealing that there exists/does not exists rows with those values in where clause. You can find out all the values of rows.

How practical that is, is a different story. But possible.


lh
Chris Saxon
February 27, 2019 - 10:49 am UTC

Yep, with delete you can search for rows with given values. You can find an example at:

http://blog.fifteentwentyone.co.uk/2010/02/sql92security.html

If you have select you can run these queries anyway, so there's no need to go hunting with delete/update.

Setting SQL92_SECURITY to true always required you to have select + delete to do a selective delete. But it's only in 12.2 that this became the default.

how to find those who need select grant

Stacy, December 02, 2024 - 3:49 pm UTC

Hi!
We recently upgraded the database version, the sql92_security parameter was enabled, and some users who had only update or delete rights started experiencing difficulties.
I understand that to fix the situation, I need to write a script that will grant select grants to those who have update and delete grants, but I don't know how to do this for thousands of users and millions of tables.
Connor McDonald
December 04, 2024 - 6:01 am UTC

You can write SQL to ... write SQL, eg

select 'grant select on '||table_name||' to '||user_name||';'
from dba_tables t, dba_users u
where t.owner in (...)
and u.username in (...)



more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

SQL

The Oracle documentation contains a complete SQL reference.