ArcGIS Server Banner

User permissions for geodatabases in Oracle

User permissions for geodatabases in Oracle

Release 9.3 E-mail This TopicPrintable VersionGive Us feedback
The tables in this topic list database privileges for ArcSDE for Oracle users.

Table 1 lists the minimum required database privileges for common types of users: data viewers, data editors, data creators, and the ArcSDE administrator.

Table 2 lists the permissions needed by the ArcSDE administrator to install the ArcSDE component and upgrade the geodatabase.

Table 3 lists optional privileges that are commonly assigned to ArcSDE for Oracle users.

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

In most cases, owners of individual datasets should use ArcCatalog to set other users' permissions on these datasets. For information on this, see Granting and revoking privileges on datasets.

Note that to perform an upgrade, the ArcSDE administrator must have write permission to either the SDEHOME/geocode directory on the server or the directory to which the TEMP environment variable is set. This is because during an upgrade, ArcSDE attempts to make backups of the locator files in the geocode directory. If that fails, ArcSDE attempts to write the backups to the TEMP directory. If the ArcSDE administrator does not have write permission to either of these directories, a warning is generated and new locators are loaded.

In Oracle databases, the ArcSDE administrator also requires additional permissions in the database to install or upgrade the ArcSDE component. The first table lists the minimum privileges required by the ArcSDE administrator (the permissions the ArcSDE administrator needs just to perform day-to-day functions) and the permissions needed for other types of users. The second table lists the permissions the ArcSDE administrator requires to be able to install or upgrade the ArcSDE component.

NOTE: In addition to the privileges listed below, the database administrator must grant EXECUTE privileges on dbms_pipe and dbms_lock to PUBLIC.

Minimum privileges


Table 1
Type of user Database permissions Dataset permissions 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 configuration options 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 configuration options 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 to compress the geodatabase. It is also required if using autoregistration with Oracle Spatial.

If not using autoregistration, the SELECT ANY TABLE privilege can be revoked, but will have to be regranted before a compress operation is performed.

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 permissions 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.

ArcSDE administrator installation/upgrade required permissions

The following table lists the privileges that must be granted to the ArcSDE administrator to install or upgrade ArcSDE and the reason the privilege or group of privileges is needed. Some of these privileges can be revoked after installation 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, except the ADMINISTER DATABASE TRIGGER; that permission is only needed for installing or upgrading the SDE master geodatabase.

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


Table 2
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 Spatial Type for Oracle (ST_Geometry) user-defined data type. CREATE OPERATOR and CREATE INDEXTYPE are also needed to upgrade ArcSDE. These privileges can be revoked after installation or upgrade.
SELECT ANY TABLE Autoregistration with the geodatabase of Oracle Spatial layers created by third-party applications and for upgrading geodatabase contents. It is also necessary to perform an upgrade.
  • ALTER ANY INDEX
  • ALTER ANY TABLE
  • ANALYZE ANY
  • CREATE ANY INDEX
  • CREATE ANY PROCEDURE
  • CREATE ANY SEQUENCE
  • CREATE ANY TRIGGER
  • CREATE ANY VIEW
  • DROP ANY INDEX
  • DROP ANY PROCEDURE
  • DROP ANY SEQUENCE
  • DROP ANY TABLE
  • DROP ANY VIEW
  • EXECUTE ANY PROCEDURE
  • SELECT ANY SEQUENCE
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.

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.


Table 3
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 administrative 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 Tuning memory in Oracle for information on using storage quotas.

See Also

  • User permissions