Sys Can Execute DDLs Against Realm Protected Tables But Cannot Run DMLs

(Doc ID 1135944.1)

Last updated on OCTOBER 24, 2017

Applies to:

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


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 - 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 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining,
Oracle Database Vault and Real Application Testing options

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


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