topic Re: ArcSDE execute privilege be removed from public on Oracle packages in Data Management Questions

ArcSDE execute privilege be removed from public on Oracle packages

EmadAl-Mousa — Wed, 20 Jun 2012 06:44:01 GMT

Hi,

Per this document:

http://support.esri.com/es/knowledgebase/techarticles/detail/35408

i can revoke the packages from public and grant it directly to SDE user in Oracle. However, when i do that and then try to start the ArcSDE service i face the error in the attached image.

Although the DBMS_UTILITY package has been granted execute privilege directly to 'SDE' user.

can somebody help?

Thanks,

Re: ArcSDE execute privilege be removed from public on Oracle packages

VinceAngelo — Wed, 20 Jun 2012 10:08:28 GMT

You should really post the text of the error message, not a graphic. The reason is two-fold:
1) Folks are less likely to look at images
2) The guiding philosophy behind Forums is that public answers are available for others
to research their problems. When you hide your question, you prevent others from finding
the solution. This "'netiquette violation" also reduces the likelihood of a response.

I'd recommend you follow the procedures again, though once you break core functionality
with permission revocation, it's sometimes very difficult to get back functionality. It may take
a while for even Tech Support to help you work this out.

- V

Re: ArcSDE execute privilege be removed from public on Oracle packages

EmadAl-Mousa — Wed, 20 Jun 2012 10:30:51 GMT

Vince,

first of all if you see my posts in this forum, you will see my contribution visible, i post questions and even post answers whenever i solve problems myself.
So, i am not intentionally preventing people from finding my questions !!!

second, i already followed the ESRI documentation step by step and the below message is appearing everytime i start the ArcSDE service.

If ESRI is recommending not to revoke the privleges from "PUBLIC" then this should be stated very clearly, since it gives an alternative choice of granting the following packages to every geodatabase user !!!
dbms_pipe
dbms_lock
dbms_lob
dbms_utility
dbms_sql
utl_raw

ArcSDE 10.0 for Oracle11g Build 2004 Fri Jan 13 11:45:40 2012
-------------------------------------------------------

ST_Geometry Schema Owner: (SDE) Type Release: 1007

Instance initialized for ((sde)) . . .

Connected to instance . . .

SQL Stmt: <CREATE OR REPLACE PROCEDURE SDE14680144_TEST AS /* Test EXECUTE Access to DBMS_UTILITY.current_instance */ pvalue INTEGER; BEGIN /* ArcSDE plsql */ pvalue := DBMS_UTILITY.current_instance; END;>

SQL Stmt: <DROP PROCEDURE SDE14680144_TEST>

Execute privileges on required Oracle builtin package DBMS_UTILITY must be granted directly to the SDE DBA user, granting them a ROLE with execute access is insufficient.

ERROR in checking system st_geometry type (-25).

Error checking the existence of system tables. Run
the Upgrade Geodatabase tool to install/upgrade system tables (-25).
Refer ArcSDE installation guide for instructions.

Could not start ArcSDE -- Check Network, $SDEHOME disk, DBMS settings and dbinit.sde.

Re: ArcSDE execute privilege be removed from public on Oracle packages

VinceAngelo — Wed, 20 Jun 2012 12:03:16 GMT

Please keep in mind that folks reading new questions with the intent to answer are problem-
oriented by nature. They don't look at who is posting, they look at what is posted. Every new
thread gets evaluated on its own merit, which is why I recommend giving clear details about
the entire environment with every new post. You can generate boilerplate to post the details
about your version(s) and service pack(s) so you don't leave anything out, but more
environment and problem details are better than less (though the same is not true about
questions -- multi-question threads tend to get ignored as if they were homework problems).

I've had lots of customers try to revoke all privileges then add them back until the software works,
and not many have been successful. You'll likely need to create a new instance, try following
the instructions again, and if they work the second time, try to determine what went wrong on the
first pass. If they don't work the second time, then the focus will switch to your environment
configuration, since it's not likely that a KB article will have been untested.

I was surprised that there weren't more caveats, like the boilerplate that is provided when a
solution involves a Registry change to a Microsoft host, since revoking privileges incorrectly
can render an instance useless, but the folks who generally insist on such changes aren't likely
to be swayed by the risks of "greater security".

- V

Re: ArcSDE execute privilege be removed from public on Oracle packages

EmadAl-Mousa — Sat, 14 Sep 2013 09:57:28 GMT

The correct method i have found after "extensive research" and experience is that before
revoking packages from public, grant each
geodatabase user the explicit execute packages privileges. Then compile the "sde" schema. Then, revoke the packages from PUBLIC.

the order of the steps makes difference....

Regards,