Can the execute privilege be removed from public on Oracle packages?

Answer

Yes, the execute privilege can be revoked from the Oracle PUBLIC role on the following packages after creating or upgrading a geodatabase in Oracle:

• sys.dbms_lock
• sys.dbms_pipe
• sys.dbms_lob
• sys.dbms_sql
• sys.dbms_utility
• sys.utl_raw

If the privilege is revoked, the Oracle administrator must then explicitly grant execute privileges on all these packages to the SDE user and CTXSYS user (Oracle Text component owner), as well as each user who connects to the Oracle instance with ArcGIS.

To revoke the privileges on the packages from public, execute the following SQL statements as the Oracle SYS user in SQL*Plus:

SQL> REVOKE EXECUTE ON dbms_lock FROM public;

Revoke succeeded.

SQL> REVOKE EXECUTE ON dbms_pipe FROM public;

Revoke succeeded.

SQL> REVOKE EXECUTE ON dbms_lob FROM public;

Revoke succeeded.

SQL> REVOKE EXECUTE ON dbms_sql FROM public;

Revoke succeeded.

SQL> REVOKE EXECUTE ON dbms_utility FROM public;

Revoke succeeded.

SQL> REVOKE EXECUTE ON utl_raw FROM public;

Revoke succeeded.

Once privileges have been revoked from the public role, the SYS user must grant execute privileges on the packages to the SDE user and CTXSYS user.

SQL> GRANT EXECUTE ON dbms_lock TO sde;

Grant succeeded.

SQL> GRANT EXECUTE ON dbms_pipe TO sde;

Grant succeeded.

SQL> GRANT EXECUTE ON dbms_lob TO sde;

Grant succeeded.

SQL> GRANT EXECUTE ON dbms_sql TO sde;

Grant succeeded.

SQL> GRANT EXECUTE ON dbms_utility TO sde;

Grant succeeded.

SQL> GRANT EXECUTE ON utl_raw TO sde;

Grant succeeded.

SQL> GRANT EXECUTE ON dbms_lock TO ctxsys; 

Grant succeeded. 

SQL> GRANT EXECUTE ON dbms_pipe TO ctxsys; 

Grant succeeded. 

SQL> GRANT EXECUTE ON dbms_lob TO ctxsys; 

Grant succeeded. 

SQL> GRANT EXECUTE ON dbms_sql TO ctxsys; 

Grant succeeded. 

SQL> GRANT EXECUTE ON dbms_utility TO ctxsys; 

Grant succeeded. 

SQL> GRANT EXECUTE ON utl_raw TO ctxsys; 

Grant succeeded.

If the SDE user does not have explicit privileges to execute the sys.dbms_pipe package, attempting to start the ArcSDE service fails, and the following error message is written to the ArcSDE error log:

Error:
SQL Stmt: <CREATE OR REPLACE PROCEDURE SDE1168_TEST AS /* Test EXECUTE Access to DBMS_PIPE.maxwait */ pvalue INTEGER; BEGIN /* ArcSDE plsql */ pvalue := sys.DBMS_PIPE.maxwait; END;>

Execute privilege for required Oracle builtin package DBMS_PIPE must be directly granted for the SDE DBA user, granting them a ROLE with execute access is insufficient.

For individual users to connect to the geodatabase using either the ArcSDE application server or a direct connection, the SYS user must explicitly grant the users privileges on the packages. Again, using SQL*Plus, connect as the SYS user and grant execute privileges on each package to the desired user.

SQL> GRANT EXECUTE ON dbms_lock TO tom;

Grant succeeded.

SQL> GRANT execute ON dbms_pipe TO tom;

Grant succeeded.

SQL> GRANT EXECUTE ON dbms_lob TO tom;

Grant succeeded.
SQL> GRANT EXECUTE ON dbms_sql TO tom;

Grant succeeded.
SQL> GRANT EXECUTE ON dbms_utility TO tom;

Grant succeeded.
SQL> GRANT EXECUTE ON utl_raw TO tom;

Grant succeeded.

Repeat these commands for each user connecting to the geodatabase.

Note:
Granting execute privileges on these packages to a database role and then granting the role to a user is not supported. Because SDE has its own packages dependent on these Oracle packages, privileges granted through roles are not applicable when executing an Oracle stored procedure or package. See the Oracle documentation for additional details.

After granting execute privileges on the packages to each user, recompile the SDE and CTXSYS objects. Connect as the SYS user in SQL*Plus and compile the SDE and CTXSYS schemas.

SQL> EXEC dbms_utility.compile_schema( 'SDE' );

PL/SQL procedure successfully completed.

SQL> EXEC dbms_utility.compile_schema( 'CTXSYS' );

PL/SQL procedure successfully completed.