Autonomous Transaction Processing databases come with a predefined database role named
DWROLE. This role provides the privileges necessary for most database users. Here are the privileges it grants a user:
CREATE ANALYTIC VIEW CREATE ATTRIBUTE DIMENSION ALTER SESSION CREATE HIERARCHY CREATE JOB CREATE MINING MODEL CREATE PROCEDURE CREATE SEQUENCE CREATE SESSION CREATE SYNONYM CREATE TABLE CREATE TRIGGER CREATE TYPE CREATE VIEW READ,WRITE ON directory DATA_PUMP_DIR
To grant the
DWROLE role to a database user, connect to the database as the ADMIN user using any SQL client tool and then enter this SQL statement:
GRANT DWROLE TO user;
Instead of or in addition to granting
DWROLE privileges, you can grant individual privileges to users with the
GRANT command. See GRANT in Oracle Database SQL Language Reference.
DWROLE role does not allocate any tablespace quota to the user. If the user is going to be adding data or other objects, you need to grant the user tablespace quota in one of these ways:
Grant the user
GRANT UNLIMITED TABLESPACE TO user;
Grant the user quota to tablespaces individually; for example:
ALTER USER user QUOTA 500M ON data;
UNLIMITED TABLESPACEprivilege allows a user to use all the allocated storage space. You cannot selectively revoke tablespace access from a user with the
UNLIMITED TABLESPACEprivilege. You can grant selective or restricted access only after revoking the privilege.