Sys Can Execute DDLs Against Realm Protected Tables But Cannot Run DMLs (Doc ID 1135944.1)

Last updated on JUNE 28, 2010

Applies to:

Oracle Server - Enterprise Edition - Version: 10.2.0.4 and later   [Release: 10.2 and later ]
Information in this document applies to any platform.

Symptoms

User SYS is able to perform DDLs on the tables protected by a realm but is not able to run queries/DMLs on those tables:


[oracle@oratest3-ro ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.1.0.7.0 - Production on Mon Jun 28 10:30:32 2010

Copyright (c) 1982, 2008, Oracle. All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining,
Oracle Database Vault and Real Application Testing options

SQL>
SQL>
SQL> create table testrole.testtable(col1 number);

Table created.

SQL> alter table testrole.testtable add col2 varchar2(100);

Table altered.

SQL> select * from testrole.testtable;
select * from testrole.testtable
*
ERROR at line 1:
ORA-01031: insufficient privileges


SQL> insert into testrole.testtable values(1,'A');
insert into testrole.testtable values(1,'A')
*
ERROR at line 1:
ORA-01031: insufficient privileges


Cause

Sign In with your My Oracle Support account

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

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms