Relationship between ORA-01031 and DBMS_COMPRESSION package

Last updated on APRIL 16, 2018

Applies to:

Symptoms

In case an user executes get_compression_type procedure in dbms_compresssion package
it shows ORA-01031 depending on an user; test_user1, test_user2

The following is the test case to be reproducible.

--- scenario ---
drop user test_user1 cascade;
drop user test_user2 cascade;
create user test_user1 identified by test_user1;
grant connect, resource to test_user1;

create table test_user1.test_tab (id number);
insert into test_user1.test_tab values(100);
commit;

create user test_user2 identified by test_user2;
grant connect, resource to test_user2;
grant select on test_user1.test_tab to test_user2;

grant select on seg$ to test_user1;
grant select on DBA_OBJECTS to test_user1;

grant select on seg$ to test_user2;
grant select on DBA_OBJECTS to test_user2;
---------------

Then the below SQL can be executed successfully or not depending on an user.

SELECT rowid, id,
CASE DBMS_COMPRESSION.get_compression_type ('TEST_USER1', 'TEST_TAB', rowid)
WHEN 1 THEN 'NOCOMPRESS'
WHEN 2 THEN 'ADVANCED'
WHEN 4 THEN 'QUERY_HIGH'
WHEN 8 THEN 'QUERY_LOW'
WHEN 16 THEN 'ARCHIVE_HIGH'
WHEN 32 THEN 'ARCHIVE_LOW'
END AS compression_type
FROM test_user1.test_tab
WHERE rownum <= 5;

That is,

- case I : in case of executing the above SQL as an user, test_user1, it works successfully

- case II : in case of executing the above SQL as an user, test_user2, it does not work.
Instead, we can get the following errors.
ORA-01031: insufficient privileges
ORA-06512: at "SYS.DBMS_ROWID", line 142
ORA-06512: at "SYS.DBMS_COMPRESSION", line 246

Changes

1. If test_user2 has dba privilege, it works.

2. With 12.1 and 12.2 as well, it works fine from both version.

Reproduced ?
-----------------
11.2.0.4.0 yes
12.1.0.2.0 no
12.2.0.1.0 no

Cause