User privileges 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, but 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 privileges must be granted to the public role for geodatabase creation and upgrading. 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 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:

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;

TipTip:

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

CautionCaution:

You cannot grant the execute privilege to a role then 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:

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 file tables (the default), additional privileges may be needed. See ArcSDE 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 geodatabase uses shared ArcSDE log file tables (the default), additional privileges may be needed. See ArcSDE log file table configuration options for Oracle for more information.

If the user will be editing versioned data through a versioned view, the user must also be granted SELECT, INSERT, UPDATE, and DELETE privileges on the versioned view. When you use the Privileges dialog box in ArcGIS to grant the SELECT, INSERT, UPDATE, and DELETE privileges on a versioned feature class, those privileges are automatically granted on the associated versioned view.

Data creator
  • CREATE SESSION
  • CREATE SEQUENCE
  • CREATE TRIGGER
  • CREATE VIEW
  • CREATE TABLE

Geodatabase administrator
  • CREATE SESSION
  • CREATE SEQUENCE
  • CREATE TABLE
  • CREATE TRIGGER
  • CREATE PROCEDURE
Minimum privileges in Oracle
NoteNote:

Beginning with Oracle 10g Release 2, access to ORACLE_HOME is more restrictive for increased security. To allow the geodatabase administrator to access the files in ORACLE_HOME without granting elevated privileges to the geodatabase 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 tables list the privileges that must be granted to the geodatabase 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 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 in the SDE user's schema. This is referred to as the SDE master geodatabase.

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

The third table lists the privileges required by a user other than SDE to create a geodatabase in his or her schema. These geodatabases are referred to as user-schema geodatabases.

The fourth table lists the privileges required by a user other than SDE to upgrade a user-schema geodatabase.

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

Privilege

Purpose

  • 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 and ST_Raster user-defined data types 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 is dropped, altered, or renamed using SQL. This privilege can be revoked after geodatabase creation.
Oracle SDE user privileges for creating an SDE master geodatabase

Privilege

Purpose

  • 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 and ST_Raster user-defined data types 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.
Oracle SDE user privileges for upgrading an SDE master geodatabase

Privilege

Purpose

  • CREATE SESSION

Connect to Oracle.

  • CREATE TABLE
  • CREATE TRIGGER
  • CREATE VIEW

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.

  • CREATE TYPE

Create types used for query optimization.

Oracle privileges for creating a user-schema geodatabase

Privilege

Purpose

  • CREATE SESSION

Connect to Oracle.

  • CREATE TABLE

Upgrade the geodatabase repository.

  • CREATE SEQUENCE

Upgrade sequences to generate IDs. This privilege 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
  • CREATE PROCEDURE
  • CREATE TYPE

Upgrade geodatabase contents.

Oracle privileges for upgrading a user-schema geodatabase

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.

Privilege

Purpose

  • ALTER SESSION
  • PLUSTRACE

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.
  • ADVISOR (Oracle 10g only)
  • ALTER ANY INDEX
  • ANALYZE ANY
  • SELECT ANY DICTIONARY
  • CREATE JOB (Oracle 10g only)

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 DBA.
  • CREATE DATABASE LINK
  • CREATE MATERIALIZED VIEW
  • CREATE VIEW

This is useful for integrating the geodatabase with other nonspatial databases in the enterprise.
  • RESTRICTED SESSION

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

Granting this privilege to the geodatabase administrator for installation and upgrade ensures that there is sufficient storage space in the geodatabase administrator's tablespace in the database to complete the installation or upgrade; this privilege can be revoked after installing or upgrading the geodatabase if you have set quotas for space management. See Memory tuning in Oracle for information on using storage quotas.
  • ALTER SYSTEM
  • SELECT_CATALOG_ROLE

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 DBA role to allow it to disconnect users from the database.
Oracle optional privileges

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

Related Topics

What are user privileges?
User groups or roles
The geodatabase administrator in Oracle
Granting and revoking privileges on datasets
Copyright © 1995-2015 Esri. All rights reserved.
6/12/2015