My Oracle Support Banner

Error ORA-28081: Insufficient Privileges, The Command References A Redacted Object With Extend Statistics (Doc ID 2627835.1)

Last updated on FEBRUARY 18, 2020

Applies to:

Oracle Database - Enterprise Edition - Version 18.1.0.0.0 and later
Information in this document applies to any platform.

Symptoms

select command fails with Error 'ORA-28081: Insufficient privileges' when the command references a redacted object with extended statistics.

 

Test case:

+++++++

--SQL*Plus Variables to control testing
SYSTEM> DEFINE schema_name=schema1
SYSTEM> DEFINE test_user=user1

--Create Schema
SYSTEM> CREATE USER &schema_name IDENTIFIED BY &schema_name ACCOUNT LOCK
QUOTA UNLIMITED ON USERS;

--Create a Table in the Schema
SYSTEM> CREATE TABLE &schema_name..emp(empid NUMBER, lname VARCHAR2(50),
fname VARCHAR2(50));

--Insert a Row in the Table
SYSTEM> INSERT INTO &schema_name..emp(empid,fname,lname)
VALUES(1,'name1','name2');

commit;

--Make extended stats on fname and lname column group in emp table
SYSTEM> EXECUTE
dbms_stats.gather_table_stats('&schema_name','emp',method_opt=>'for all
columns size skewonly for columns size skewonly(fname,lname)');

--Add a redaction policy on lname column in the table
SYSTEM> EXECUTE dbms_redact.add_policy(policy_name=>'EMP_REDACT_POLICY', -
object_schema=>'&schema_name', -
object_name=>'emp', -
column_name=>'lname', -
function_type => DBMS_REDACT.full,expression
=>'1=1');

--Create test user
SYSTEM> CREATE USER &test_user IDENTIFIED BY &test_user ACCOUNT UNLOCK;
SYSTEM> GRANT CREATE SESSION to &test_user;

--Grant test user select privilege on table
SYSTEM> GRANT SELECT ON schema1.emp to &test_user;

--As test user query the table
TEST_USER> SELECT * FROM schema1.emp;
ERROR at line 1:
ORA-28081: Insufficient privileges - the command references a redacted object.

 

 

Cause

To view full details, sign in with your My Oracle Support account.

Don't have a My Oracle Support account? Click to get started!


In this Document
Symptoms
Cause
Solution
References


My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.