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.
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;
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.
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' );
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|
SELECT on database objects
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.
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
Connect to Oracle.
Create the geodatabase repository.
Create sequences to generate IDs. This privilege can be revoked after geodatabase creation.
Create packages for maintaining the contents of geodatabase system tables.
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 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.
*CREATE LIBRARY is not required when creating a geodatabase in Autonomous Transaction Processing (ATP) database in Oracle Cloud.
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. Also required to create pl/sql triggers. This privilege can be revoked after geodatabase creation.
This privilege is only required to enable and upgrade a geodatabase in Autonomous Transaction Processing database in Oracle Cloud. This privilege is optional for other supported Oracle database offerings.
Oracle sde user privileges for upgrading a geodatabase
Connect to Oracle.
Upgrade the geodatabase repository. The CREATE VIEW privilege can be revoked after upgrading.
Upgrade packages for maintaining the contents of geodatabase system tables.
Upgrade sequences to generate IDs. This privilege can be revoked after upgrading.
Upgrade a map member function for the ST_Geometry type, which is called whenever a spatial union or intersection is performed.
Upgrade the ST_Geometry data type and types used for query optimization. These privileges can be revoked after upgrading.
*CREATE LIBRARY is not required when upgrading a geodatabase in ATP database in Oracle Cloud.
Upgrade geodatabase contents.
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. 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
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.
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.
This is useful for integrating the geodatabase with other nonspatial databases in the enterprise.
This privilege allows the geodatabase administrator to perform maintenance while the database is online but not accessible by end users.
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.
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_CATALOG_ROLE is required, not optional, for the geodatabase administrator in Autonomous Transaction Processing database in Oracle Cloud.
SELECT ON DBA_ROLES
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>
INHERIT ANY PRIVILEGES ON <user>
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.