Special setup to create views in Oracle in default schema

oracleoracle-11gpermissionsview

When creating views in Oracle, do you need special setup for a given user if the view and table in which the view is reading from are in two different schemas? The user creating the view has dba privileges.

The following command throws the error below.

create or replace VIEW view_patient as select * from DATA.PATIENT_DIMENSION where PATIENT_NUM in (select PATIENT_NUM from patient_list);

Error:

Error report -
SQL Error: ORA-01031: insufficient privileges
01031. 00000 -  "insufficient privileges"
*Cause:    An attempt was made to change the current username or password
           without the appropriate privilege. This error also occurs if
           attempting to install a database without the necessary operating
           system privileges.
           When Trusted Oracle is configure in DBMS MAC, this error may occur
           if the user was granted the necessary privilege at a higher label
           than the current login.
*Action:   Ask the database administrator to perform the operation or grant
           the required privileges.
           For Trusted Oracle users getting this error although granted the
           the appropriate privilege at a higher label, ask the database
           administrator to regrant the privilege at the appropriate label.

Best Answer

DBA role alone is not enough.

CREATE VIEW

The owner of the schema containing the view must have the privileges necessary to either select (READ or SELECT privilege), insert, update, or delete rows from all the tables or views on which the view is based. The owner must be granted these privileges directly, rather than through a role.

Related Question