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 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_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 user	Database privileges	Dataset privileges	Notes
Data viewer	CREATE SESSION	SELECT on database objects
Data editor	CREATE SESSION	SELECT, INSERT, UPDATE, and DELETE on other users' datasets	When you use ArcGIS to grant the SELECT, INSERT, UPDATE, and DELETE privileges on a versioned feature class or table, those privileges are automatically granted on the associated versioned view. These privileges are required for the user to edit through versioned views.
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 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.

Oracle sde user privileges for creating an sde master geodatabase

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

Oracle privileges for creating a user-schema 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 upgrading 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.

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

Privilege	Granted to	Purpose
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 DBA.
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 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	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 DBA 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 in ArcMap, 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. 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.