Permissions to create or upgrade a geodatabase in Oracle

Specific privileges must be granted to the ArcSDE administrator to create or upgrade an ArcSDE geodatabase in Oracle. These privileges and the purposes they serve are listed in the table in this topic.

You can use Oracle's Enterprise Manager to administer user privileges. You can also use SQL statements to grant and revoke privileges.

There is a script (createsdeoracle.sql) installed with ArcSDE for Oracle in SDEHOME > tools > oracle that can be altered and used to create an sde tablespace and user, and grant the sde user permissions to either create a geodatabase or upgrade it.

ArcSDE packages are dependent on the following Oracle packages:

• dbms_lob
• dbms_lock
• dbms_pipe
• dbms_sql
• dbms_utility
• utl_raw

To create or upgrade a geodatabase, you must grant execute permissions on these packages to the public role.

GRANT EXECUTE ON dbms_pipe TO public;
GRANT EXECUTE ON dbms_lock TO public;
GRANT EXECUTE ON dbms_lob TO public;
GRANT EXECUTE ON dbms_utility TO public;
GRANT EXECUTE ON dbms_sql TO public;
GRANT EXECUTE ON utl_raw TO public;

Tip:

Execute permission on dbms_lob, dbms_utility, dbms_sql, and utl_raw are granted to public by default in Oracle. Therefore, you only need to grant these permissions if you explicitly revoked them from public. Alternatively, you could grant execute privileges to the ArcSDE administrator and every user who accesses the geodatabase. Note that you must grant the execute to individual users; it cannot be granted through roles other than public.

Privileges to create or upgrade a geodatabase

Privilege	Purpose
CREATE SESSION	Connect to Oracle.
CREATE TABLE CREATE TRIGGER	Create and upgrade ArcSDE repository.
CREATE SEQUENCE	Create sequences when the geodatabase is created.
CREATE PROCEDURE	Create and upgrade packages for maintaining the contents of ArcSDE repository tables.
CREATE INDEXTYPE CREATE LIBRARY CREATE OPERATOR CREATE PUBLIC SYNONYM CREATE TYPE CREATE VIEW DROP PUBLIC SYNONYM	Create the ST_Geometry and ST_Raster user-defined data types. CREATE OPERATOR and CREATE INDEXTYPE are also needed to upgrade ArcSDE. These privileges can be revoked after installation or upgrade. CREATE VIEW is needed to create system views, GDB_Items_vw and GDB_ItemRelationships_vw.
ALTER ANY INDEX CREATE ANY INDEX CREATE ANY TRIGGER CREATE ANY VIEW DROP ANY INDEX DROP ANY VIEW SELECT ANY TABLE	Upgrade geodatabase contents.
ADMINISTER DATABASE TRIGGER	Allows creation of database event triggers needed to modify the ST_GEOMETRY_COLUMNS and ST_GEOMETRY_INDEX tables if a table with an ST_Geometry is dropped, altered, or renamed using SQL This privilege can be revoked after installation or upgrade.

Oracle ArcSDE administrator user privileges for creating or upgrading a geodatabase

Note:

If you use geodatabases created in user schemas in Oracle, the same privileges are needed for the schema owner in which the geodatabase resides to create or upgrade his/her geodatabase.

Privileges that can be revoked after geodatabase creation

The following privileges can be revoked from the ArcSDE administrator user after the geodatabase has been created.

• ADMINISTER DATABASE TRIGGER
• CREATE INDEXTYPE
• CREATE LIBRARY
• CREATE OPERATOR
• CREATE PUBLIC SYNONYM
• CREATE SEQUENCE
• CREATE TYPE
• CREATE VIEW
• DROP PUBLIC SYNONYM

Privileges that can be revoked after geodatabase upgrade

The following privileges can be revoked from the ArcSDE administrator user after the geodatabase is upgraded.

• ADMINISTER DATABASE TRIGGER
• ANALYZE ANY
• CREATE ANY INDEX
• CREATE ANY SEQUENCE
• CREATE ANY TRIGGER
• CREATE ANY VIEW
• CREATE INDEXTYPE
• CREATE LIBRARY
• CREATE OPERATOR
• CREATE TYPE
• DROP ANY INDEX
• DROP ANY PROCEDURE
• DROP ANY VIEW
• EXECUTE ANY PROCEDURE
• SELECT ANY SEQUENCE

Revoking execute privileges from public

After you have created or upgraded the geodatabase, you can restrict who has execute permission on these packages:

• dbms_lob
• dbms_lock
• dbms_pipe
• dbms_utility
• dbms_sql
• utl_raw

To restrict access, revoke execute from public then explicitly grant execute to every user who logs into the geodatabase, including the sde user. Permissions must be granted to individual users.

Caution:

You cannot grant execute to a role then grant the role to all the users because privileges granted through roles are not applicable when executing Oracle packages.

After granting execute to individual users, recompile the sde schema:

EXEC dbms_utility.compile_schema( 'SDE' );