User permissions for geodatabases in Oracle

Specific privileges must be granted to users based on what they need to do in the Oracle database. Some privileges can be granted to roles, others have to be granted directly to the user.

The first section in this topic lists package privileges that are required for all users. These permissions must be granted to the public role for geodatabase creation and upgrade. However, they can be granted to all individual users after geodatabase creation or upgrade if you want to revoke them from the public role.

The second section lists the minimum required database privileges for common types of users: data viewers, data editors, data creators, and the ArcSDE administrator. These privileges are needed in addition to the ones listed in the first section.

The third section lists the privileges needed by the ArcSDE administrator to create or upgrade a geodatabase. Again, these privileges are in addition to the ones listed in the first section.

The last section lists optional privileges that are commonly assigned to ArcSDE for Oracle users.

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

Package privileges

Execute privileges are required on the following packages:

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

You must grant the execute privilege on these packages to the public role to create or upgrade the geodatabase.

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 privileges on dbms_lob, dbms_utility, dbms_sql, and utl_raw are granted to the public role by default in Oracle. Therefore, you only need to grant execute on these packages if you explicitly revoked them from public.

After you have created or upgraded the geodatabase, you can restrict privileges on these packages by revoking them from the public role and granting them to each individual user who logs into the geodatabase, including the ArcSDE administrator.

Caution:

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

Minimum privileges

In addition to the privileges stated in the previous section, the following are required for each type of user listed.

Type of user	Database privileges	Dataset privileges	Notes
Data viewer	CREATE SESSION	SELECT on database objects	If your database is configured to use shared ArcSDE log files (the default), additional privileges may be needed. See Log file table configuration options for Oracle for more information.
Data editor	CREATE SESSION	SELECT, INSERT, UPDATE, and DELETE on other users' datasets	If your database is configured to use shared ArcSDE log files (the default), additional privileges may be needed. See Log file table configuration options for Oracle for more information.
Data creator	CREATE SESSION CREATE SEQUENCE CREATE TRIGGER CREATE VIEW CREATE TABLE		CREATE VIEW privilege is only needed if the user creates database views, spatial views, or multiversioned views.
ArcSDE administrator	CREATE SESSION CREATE SEQUENCE CREATE TABLE CREATE TRIGGER CREATE PROCEDURE SELECT ANY TABLE		The SELECT ANY TABLE privilege is required if using autoregistration with Oracle Spatial. If not using autoregistration, the SELECT ANY TABLE privilege is not needed.

Minimum privileges in Oracle

Note:

Beginning with Oracle 10g Release 2, access to ORACLE_HOME is more restrictive for increased security. To allow the ArcSDE administrator to access the files in ORACLE_HOME without granting elevated privileges to the ArcSDE administrator, install a compatible Oracle client under the operating system SDE account. On UNIX/Linux, set the client ORACLE_HOME in the SDE user's shell. See knowledge base article 34824 for details.

Privileges required for geodatabase creation or upgrade

The following table lists the privileges that must be granted to the ArcSDE administrator to create or upgrade an ArcSDE geodatabase. The reason the privilege or group of privileges is needed is also listed. Some of these privileges can be revoked after creation or upgrade is completed, as noted in the Purpose field and as indicated in the minimum ArcSDE administrator permissions shown in the previous table.

Note:

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

Privileges are grouped by the purpose they serve during geodatabase creation and upgrade.

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

Tip:

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.

Common optional privileges

Many organizations choose to take advantage of additional Oracle features to further enhance the capabilities of their geodatabases. Several common optional privileges for the ArcSDE administrator and the purposes of the privileges are listed in the following table. Privileges are grouped by the purpose they serve.

Privilege	Purpose
ALTER SESSION PLUSTRACE	Enables SQL tracing, SQL*Plus AUTOTRACE feature, and modifying session-specific initialization parameters for performance tuning and troubleshooting; create PLUSTRACE role by running ORACLE_HOME/sqlplus/admin/plustrce.sql.
ADVISOR (Oracle10g only) ALTER ANY INDEX ANALYZE ANY SELECT ANY DICTIONARY CREATE JOB (Oracle10g only)	Grant to the ArcSDE administrative user to allow this user to monitor Oracle and perform basic maintenance tasks. Useful for organizations where the ArcSDE administrator is not the Oracle DBA
CREATE DATABASE LINK CREATE MATERIALIZED VIEW CREATE VIEW	Useful for integrating the geodatabase with other nonspatial databases in the enterprise
RESTRICTED SESSION	Enables the ArcSDE administrator user to perform maintenance while the database is online, but not accessible by end users
UNLIMITED TABLESPACE	Granting this privilege to the ArcSDE administrator for installation and upgrade ensures that there is sufficient storage space in the ArcSDE administrator's tablespace in the database to complete the installation or upgrade; this privilege can be revoked after installing or upgrading ArcSDE if you have set quotas for space management. Note: You must assign quotas before granting the UNLIMITED TABLESPACE system privilege; otherwise, you will not be able to alter the quota through the Oracle Enterprise Manager Console after the fact. See Memory tuning in Oracle for information on using storage quotas.
ALTER SYSTEM SELECT_CATALOG_ROLE	The sde user must have these permissions to kill direct connections to the geodatabase using the sdemon command. Alternatively, the sde user could be added to the DBA role to kill direct connections.

Oracle optional privileges

Dataset privileges should be granted or revoked by the dataset owner using the Change Privileges geoprocessing tool that is available in ArcGIS Desktop. See Granting and revoking privileges on datasets and Change Privileges for instructions.