By default, the database user that runs the View Composer database has Oracle system administrator permissions. To restrict the security permissions for the user that runs the View Composer database, you must configure an Oracle database user with specific permissions.

Prerequisites

Verify that a View Composer database was created in an Oracle 11g or 10g instance.

Procedure

  1. Log in to a SQL*Plus session with the system account.
  2. Run the following SQL command to create a View Composer database user with the correct permissions.
    CREATE USER "VCMPADMIN" PROFILE "DEFAULT" IDENTIFIED BY "oracle" DEFAULT TABLESPACE
    
    "VCMP" ACCOUNT UNLOCK;
       grant connect to VCMPADMIN;
       grant resource to VCMPADMIN;
       grant create view to VCMPADMIN;
       grant create sequence to VCMPADMIN;
       grant create table to VCMPADMIN;
       grant create materialized view to VCMPADMIN;
       grant execute on dbms_lock to VCMPADMIN;
       grant execute on dbms_job to VCMPADMIN;
       grant unlimited tablespace to VCMPADMIN;

    In this example, the user name is VCMPADMIN and the View Composer database name is VCMP.

    By default the resource role has the create procedure, create table, and create sequence privileges assigned. If the resource role does not have these privileges, explicitly grant them to the View Composer database user.