ArcGIS Help 10.2 - Master geodatabase configuration in Oracle (Maritime Charting)

A master geodatabase is created under the sde schema, which keeps track of the separate user-schema geodatabases and contains the ST_Geometry type, its subtypes and functions, and the ST_SPATIAL_REFERENCES system table.

You only need one master sde geodatabase for all the user-schema geodatabases in the ArcGIS for Maritime: Charting.

Tablespace configuration

It is recommended that you store the system tables for the sde master geodatabase in separate tablespaces than the ones used for the user-schema geodatabases.

The tablespaces, which can be created manually, will be used when the Create Enterprise Geodatabase geoprocessing tool in ArcGIS for Desktop is run. If the tablespace is not set up manually, it will be created automatically by the system during the geodatabase creation process with default sizing and parameters. Creating the tablespace manually allows you to better control the size and parameters.

The following example can be used to create tablespaces in Oracle as the sys user in SQL*Plus. Replace <text> with values appropriate for your organization.

Create SDE tablespace – master geodatabase repository

Example:

create tablespace sde
       datafile '<data location>/<SID>/sde.dbf' size 400M autoextend off
       logging
       extent management local uniform size 1M
       segment space management auto;

Database users and roles

Specific privileges must be granted to users based on functions they need to perform on the Oracle database. Roles will be created to manage these groups of privileges and users will be created and assigned to the appropriate roles based on their function.

Learn more about user privileges for geodatabases in Oracle

Package privileges

Before new roles and users are created, execute privileges are required on certain packages. These privileges must be granted to the public role during geodatabase creation and upgrade. However, they may be granted to all individual users after geodatabase creation or upgrade if you want to revoke them from the public role.

Note:

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.

If you grant the execute privilege to individual users, recompile the sde schema by executing the following statement as the sys user in SQL*Plus:

EXEC dbms_utility.compile_schema( 'SDE' );

As sys user in SQL*Plus, execute the following commands to grant execute privileges to PUBLIC role:

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;

Roles

There are some predefined roles that will be created in Oracle which will be assigned to different users for carrying out different tasks. They can be created using the examples provided in this section by executing the scripts as sys user in SQL*Plus.

Create SDE_ADMIN role – geodatabase administrator (not during geodatabase creation or upgrade)

Example:

create role SDE_ADMIN;

grant CREATE SESSION              to SDE_ADMIN;
grant CREATE SEQUENCE             to SDE_ADMIN;
grant CREATE TRIGGER              to SDE_ADMIN;
grant CREATE TABLE                to SDE_ADMIN;
grant CREATE PROCEDURE            to SDE_ADMIN;

Create SDE_UPGRADE role – assigned to geodatabase administrator during geodatabase creation or upgrade

Example:

create role SDE_UPGRADE;

grant CREATE SESSION              to SDE_UPGRADE;
grant CREATE TABLE                to SDE_UPGRADE;
grant CREATE TRIGGER              to SDE_UPGRADE;
grant CREATE SEQUENCE             to SDE_UPGRADE;
grant CREATE PROCEDURE            to SDE_UPGRADE;
grant CREATE INDEXTYPE            to SDE_UPGRADE;
grant CREATE LIBRARY              to SDE_UPGRADE;
grant CREATE OPERATOR             to SDE_UPGRADE;
grant CREATE PUBLIC SYNONYM       to SDE_UPGRADE;
grant CREATE TYPE                 to SDE_UPGRADE;
grant CREATE VIEW                 to SDE_UPGRADE;
grant DROP PUBLIC SYNONYM         to SDE_UPGRADE;
grant ADMINISTER DATABASE TRIGGER to SDE_UPGRADE;
grant ALTER ANY INDEX             to SDE_UPGRADE;
grant CREATE ANY INDEX            to SDE_UPGRADE;
grant CREATE ANY TRIGGER          to SDE_UPGRADE;
grant CREATE ANY VIEW             to SDE_UPGRADE;
grant DROP ANY INDEX              to SDE_UPGRADE;
grant DROP ANY VIEW               to SDE_UPGRADE;
grant SELECT ANY TABLE            to SDE_UPGRADE;

Users

Users will be created in Oracle, which will be assigned the appropriate roles from the previous section based on their function. They can be created using the examples provided in this section by executing the scripts as sys user in SQL*Plus.

Create SDE user – sde master geodatabase owner

Example:

create user sde identified by <password> default tablespace sde;
alter user sde quota unlimited on sde;

/* The SDE_UPGRADE role needs to be granted only during  */
/*   geodatabase creation and upgrade.  Afterward, this  */
/*   role may be revoked and the SDE_ADMIN role granted  */
grant CONNECT, SDE_UPGRADE to sde;

/* This execute privilege need only exist for the master */
/*   sde geodatabase owner during geodatabase creation   */
/*   and upgrade.  Afterward, it may be revoked          */
grant EXECUTE on DBMS_CRYPTO to sde;

2/17/2015