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 he or she 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 have to be granted directly to the user. User privileges are set at different levels.

The first section in this topic lists package privileges that are required for all users. These privileges must be granted to the public role to create or upgrade a geodatabase. However, they can be granted to all individual users after geodatabase creation or upgrading 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 geodatabase administrator. These privileges are needed in addition to the ones listed in the first section.

The third section lists the privileges needed by the geodatabase 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 users in geodatabases in Oracle.

You can use the Oracle 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_utility, dbms_sql, and utl_raw are granted to the public role by default in Oracle. Therefore, you only need to grant execute privileges 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 in 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 executing 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 should be granted or revoked using ArcGIS clients 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. CREATE VIEW is needed to create system views: GDB_Items_vw and GDB_ItemRelationships_vw. These privileges can be revoked after geodatabase creation.

  • 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 can be revoked after geodatabase creation.

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.

  • 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 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 PLUSTRACE role by running ORACLE_HOME/sqlplus/admin/plustrce.sql.

  • ALTER ANY INDEX
  • ANALYZE ANY
  • SELECT ANY DICTIONARY

Geodatabase administrator

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

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

  • CREATE DATABASE LINK
  • CREATE MATERIALIZED VIEW
  • CREATE VIEW

Geodatabase administrator

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

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 have select privileges on DBA_ROLES to get a list of roles in the database.

INHERIT PRIVILEGES ON <user>

Or

INHERIT ANY PRIVILEGES ON <user>

sde

This optional privilege only applies to Oracle 12c or later releases. You must grant this privilege 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.