Privileges for geodatabases in Oracle

Privileges determine what someone is authorized to do with the data and the database. Privileges should be assigned based on the type of work the person does within the organization. Is this person involved with administration of the geodatabase? Does this person need to edit or create data? Or would this person only need to query the data?

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, but others must be granted directly to the user. User privileges are set at different levels.

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

Privileges listed in this page apply to Oracle, Autonomous Transaction Processing, and Amazon Relational Database Service (RDS) for Oracle.

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:

The execute privilege on dbms_utility, dbms_sql, and utl_raw are granted to the public role by default in Oracle. Therefore, you only need to grant the execute privilege on these packages if you explicitly revoked them from public.

After you create or upgrade the geodatabase, you can restrict privileges on these packages by revoking them from the public role and granting them to each individual user who connects to the geodatabase, including the geodatabase administrator.

Caution:

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

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

EXEC dbms_utility.compile_schema( 'SDE' );

Minimum privileges

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

Minimum privileges in Oracle

Type of userDatabase privilegesDataset privilegesNotes

Data viewer

  • CREATE SESSION

SELECT on database objects

Data editor

  • CREATE SESSION

SELECT, INSERT, UPDATE, and DELETE on datasets owned by other users.

When you use ArcGIS to grant the SELECT, INSERT, UPDATE, and DELETE privileges on a feature class or table registered for traditional versioning, those privileges are automatically granted on the associated versioned view. These privileges are required for the user to edit using a SQL and a versioned view.

Data creator

  • CREATE SEQUENCE
  • CREATE SESSION
  • CREATE TABLE
  • CREATE TRIGGER
  • CREATE VIEW

Geodatabase administrator

  • CREATE PROCEDURE
  • CREATE SEQUENCE
  • CREATE SESSION
  • CREATE TABLE
  • CREATE TRIGGER

Privileges on datasets in geodatabases must be granted or revoked using ArcGIS, and must be done by the dataset owner.

Privileges required for geodatabase creation or upgrade

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

The first table lists the privileges required for the sde user to create a geodatabase.

The second table lists the privileges required for the sde user to upgrade the geodatabase.

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

Oracle sde user privileges for creating a geodatabase

PrivilegePurpose
  • CREATE SESSION

Connect to Oracle.

  • CREATE TABLE
  • CREATE TRIGGER

Create the geodatabase repository.

  • CREATE SEQUENCE

Create sequences to generate IDs. This privilege can be revoked after geodatabase creation.

  • CREATE PROCEDURE

Create packages for maintaining the contents of geodatabase system tables.

  • EXECUTE ON DBMS_CRYPTO

Allows the creation of a map member function for the ST_Geometry type, which is called whenever a spatial union or intersection is performed.

  • CREATE INDEXTYPE
  • CREATE LIBRARY*
  • CREATE OPERATOR
  • CREATE PUBLIC SYNONYM
  • CREATE TYPE
  • CREATE VIEW
  • DROP PUBLIC SYNONYM

Create the ST_Geometry data type and types used for query optimization. The CREATE VIEW privilege is needed to create system views: GDB_Items_vw and GDB_ItemRelationships_vw. These privileges can be revoked after geodatabase creation.

*The CREATE LIBRARY privilege is not required when creating a geodatabase in Autonomous Transaction Processing (ATP).

  • 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 column is dropped, altered, or renamed using SQL. This privilege is also required to create pl/sql triggers. This privilege can be revoked after geodatabase creation.

  • SELECT_CATALOG_ROLE

This privilege is only required to enable and upgrade a geodatabase in Autonomous Transaction Processing. This privilege is optional for other supported Oracle database offerings.

Oracle sde user privileges for upgrading a geodatabase

PrivilegePurpose
  • CREATE SESSION

Connect to Oracle.

  • CREATE TABLE
  • CREATE TRIGGER
  • CREATE VIEW

Upgrade the geodatabase repository. The CREATE VIEW privilege can be revoked after upgrading.

  • CREATE PROCEDURE

Upgrade packages for maintaining the contents of geodatabase system tables.

  • CREATE SEQUENCE

Upgrade sequences to generate IDs. This privilege can be revoked after upgrading.

  • EXECUTE ON DBMS_CRYPTO

Upgrade a map member function for the ST_Geometry type, which is called whenever a spatial union or intersection is performed.

  • CREATE INDEXTYPE
  • CREATE LIBRARY*
  • CREATE OPERATOR
  • CREATE PUBLIC SYNONYM
  • CREATE TYPE
  • DROP PUBLIC SYNONYM

Upgrade the ST_Geometry data type and types used for query optimization. These privileges can be revoked after upgrading.

*The CREATE LIBRARY privilege is not required when upgrading a geodatabase in ATP.

  • 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 is also required to create pl/sql triggers. This privilege can be revoked after upgrading.

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 geodatabase administrator and the purposes of the privileges are listed in the following table. Privileges are grouped by the purpose they serve.

Oracle optional privileges for geodatabase administrator

PrivilegeGranted toPurpose
  • ALTER SESSION
  • PLUSTRACE

Geodatabase administrator

Enable SQL tracing, the SQL*Plus AUTOTRACE feature, and modifying session-specific initialization parameters for performance tuning and troubleshooting; create the PLUSTRACE role by running ORACLE_HOME/sqlplus/admin/plustrce.sql.

  • ALTER ANY INDEX
  • ANALYZE ANY
  • SELECT ANY DICTIONARY

Geodatabase administrator

Grant these privileges to the geodatabase administrator to allow this user to monitor Oracle and perform basic maintenance tasks.

This is useful for organizations in which the geodatabase administrator is not the Oracle database administrator.

  • CREATE DATABASE LINK
  • CREATE MATERIALIZED VIEW
  • CREATE VIEW

Geodatabase administrator

This set of privileges is useful for integrating the geodatabase with other nonspatial databases in the enterprise.

  • RESTRICTED SESSION

Geodatabase administrator

This privilege allows the geodatabase administrator to perform maintenance while the database is online but not accessible by end users.

  • UNLIMITED TABLESPACE

Geodatabase administrator

Granting this privilege to the geodatabase administrator for creation and upgrade ensures that there is sufficient storage space in the geodatabase administrator's tablespace in the database to complete the geodatabase creation or upgrade; this privilege can be revoked after creating or upgrading the geodatabase if you have set quotas for space management.

  • ALTER SYSTEM
  • SELECT_CATALOG_ROLE*

Geodatabase administrator

The sde user must have these privileges to remove connections from the geodatabase. The Create Enterprise Geodatabase geoprocessing tool grants these privileges to the sde user. You can revoke these privileges from the sde user after running this tool but, if you do, the sde user will not be able to disconnect users from the database.

Alternatively, the sde user could be added to the database administrator role to allow it to disconnect users from the database.

*The SELECT_CATALOG_ROLE privilege is required for the geodatabase administrator in Autonomous Transaction Processing.

SELECT ON DBA_ROLES

Data creators

If you want data creators to grant privileges on their datasets to database roles using the Privileges dialog box, they must be granted the SELECT privilege on DBA_ROLES to get a list of roles in the database.

INHERIT PRIVILEGES ON <user>

Or

INHERIT ANY PRIVILEGES ON <user>

sde

You must grant one of these privileges to the sde user to allow Data Pump imports of the sde user schema to be performed by another user, such as the Oracle sys or system user.

This privilege does not apply to user-schema geodatabase owners.

Other privileges

Other ArcGIS software that connects to a geodatabase in Oracle may require additional privileges, as listed below:

  • ArcGIS Insights may require additional privileges. See Required database privileges in the ArcGIS Insights help for more information.
  • Additional privileges may be required for ArcGIS Monitor to access and monitor the geodatabase. See Register Oracle databases for more information.