ArcSDE execute privilege be removed from public on Oracle packages

2822
4
Jump to solution
06-19-2012 11:44 PM
EmadAl-Mousa
Occasional Contributor III
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,
0 Kudos
1 Solution

Accepted Solutions
EmadAl-Mousa
Occasional Contributor III
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,

View solution in original post

0 Kudos
4 Replies
VinceAngelo
Esri Esteemed Contributor
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
0 Kudos
EmadAl-Mousa
Occasional Contributor III
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.
0 Kudos
VinceAngelo
Esri Esteemed Contributor
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
0 Kudos
EmadAl-Mousa
Occasional Contributor III
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,
0 Kudos