To create or upgrade a geodatabase, you must grant execute permissions on these packages to the 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;
Execute permission on dbms_lob, dbms_utility, dbms_sql, and utl_raw are granted to public by default in Oracle. Therefore, you only need to grant these permissions if you explicitly revoked them from public. Alternatively, you could grant execute privileges to the ArcSDE administrator and every user who accesses the geodatabase. Note that you must grant the execute to individual users. it cannot be granted through roles other than public.
Remove Current (9.3 or 9.3.1) installation
Control Panel > Programes and Features
Install ArcSDE Binaries (No Post Installation Steps)
Install ArcSDE 10 but don't run Post Installation wizard. Just complete installation and finish setup.
Now go to ArcCatalog >ArcToolbox > Data Management Tools > Upgrade Geodatabase
Select Geodatabase
Check Pre-Requisites
First select Pre-Requisites check and leave Upgrade Geodatabase
Output
Executing: UpgradeGDB "Database Connections\sde.sde" PREREQUISITE_CHECK NO_UPGRADE
Start Time: Mon Dec 10 14:06:20 2012
There are no other active connections.
User does not have required privileges to upgrade. [To upgrade your ArcSDE for Oracle service, the
following
additional permissions need to be granted to the SDE user
CREATE ANY VIEW
CREATE ANY TRIGGER
CREATE ANY INDEX
CREATE TYPE
CREATE TABLE
CREATE INDEXTYPE
CREATE LIBRARY
CREATE PUBLIC SYNONYM
CREATE OPERATOR
SELECT ANY TABLE
DROP ANY VIEW
DROP ANY INDEX
DROP PUBLIC SYNONYM
ALTER ANY INDEX
ADMINISTER DATABASE TRIGGER
]
Connected RDBMS instance is not setup for ST_GEOMETRY configuration. [Unable to determine current version
of ST_SHAPELIB
The latest ST_GEOMETRY and dependent libraries need
to be copied to the correct software location.
Please consult ArcSDE for Oracle Installation Guide
for further details.
]
Running Pre-Requisite check (C:\Users\farhat\AppData\Roaming\ESRI\Desktop10.0\ArcCatalog\sde.sde).
Instance supports XML type.
Checking datasets.
Checking dataset (FARHAT.BUILDINGS).
Check succeeded (FARHAT.BUILDINGS).
Checked datasets.
Checking representations.
Checked representations.
Checking domains.
Checked domains.
Checking replicas.
Checked replicas.
Checking historical markers.
Checking historical marker (DEFAULT).
Check succeeded (DEFAULT).
Checked historical markers.
Finished checking prerequisites.
Failed to execute (UpgradeGDB).
Failed at Mon Dec 10 14:06:20 2012 (Elapsed Time: 0.00 seconds)
User SDE Needs some extra privileges (highlighted in red) to upgrade Geodatabase. Grant these privileges to SDE user.
===============================================================
Grant Required Privilages to SDE user
open a sqlplus prompt through as sysdba and issue following commands
GRANT CREATE ANY VIEW TO SDE;
GRANT CREATE ANY TRIGGER TO SDE;
GRANT CREATE ANY INDEX TO SDE;
GRANT CREATE TYPE TO SDE;
GRANT CREATE TABLE TO SDE;
GRANT CREATE INDEXTYPE TO SDE;
GRANT CREATE LIBRARY TO SDE;
GRANT CREATE PUBLIC SYNONYM TO SDE;
GRANT CREATE OPERATOR TO SDE;
GRANT SELECT ANY TABLE TO SDE;
GRANT DROP ANY VIEW TO SDE;
GRANT DROP ANY INDEX TO SDE;
GRANT DROP PUBLIC SYNONYM TO SDE;
GRANT ALTER ANY INDEX TO SDE;
GRANT ADMINISTER DATABASE TRIGGER TO SDE;
================================================================
Check Pre-Requisites again:-
Executing: UpgradeGDB "Database Connections\sde.sde" PREREQUISITE_CHECK NO_UPGRADE
Start Time: Mon Dec 10 14:10:36 2012
There are no other active connections.
User has privileges required to upgrade.
Connected RDBMS instance is not setup for ST_GEOMETRY configuration. [Unable to determine current version
of ST_SHAPELIB
The latest ST_GEOMETRY and dependent libraries need
to be copied to the correct software location.
Please consult ArcSDE for Oracle Installation Guide
for further details.
]
Running Pre-Requisite check (C:\Users\farhat\AppData\Roaming\ESRI\Desktop10.0\ArcCatalog\sde.sde).
Instance supports XML type.
Checking datasets.
Checking dataset (FARHAT.BUILDINGS).
Check succeeded (FARHAT.BUILDINGS).
Checked datasets.
Checking representations.
Checked representations.
Checking domains.
Checked domains.
Checking replicas.
Checked replicas.
Checking historical markers.
Checking historical marker (DEFAULT).
Check succeeded (DEFAULT).
Checked historical markers.
Finished checking prerequisites.
Succeeded at Mon Dec 10 14:10:37 2012 (Elapsed Time: 1.00 seconds)
==============================================================
Run Upgrade
Now select Upgrade Geodatabase and Click Ok.
========================================================================
Output
Executing: UpgradeGDB "Database Connections\sde.sde" NO_PREREQUISITE_CHECK UPGRADE
Start Time: Mon Dec 10 14:12:35 2012
Paused the database for upgrade.
There are no other active connections.
User has privileges required to upgrade.
Connected RDBMS instance is not setup for ST_GEOMETRY configuration. [Unable to determine current version
of ST_SHAPELIB
The latest ST_GEOMETRY and dependent libraries need
to be copied to the correct software location.
Please consult ArcSDE for Oracle Installation Guide
for further details.
]
Running Pre-Requisite check (C:\Users\farhat\AppData\Roaming\ESRI\Desktop10.0\ArcCatalog\sde.sde).
Instance supports XML type.
Updating ArcSDE server tables and stored procedures.
Unpaused the database.
Paused the database for upgrade.
Updated ArcSDE server tables and stored procedures.
Upgrading Geodatabase (C:\Users\farhat\AppData\Roaming\ESRI\Desktop10.0\ArcCatalog\sde.sde).
Creating catalog tables.
Created catalog tables.
Populating default items.
Found max object class ID.
Populated default items.
Found the root folder.
Adding feature datasets.
Finished adding feature datasets.
Adding domains.
Finished adding domains.
Adding object classes.
Adding feature class (FARHAT.BUILDINGS).
Added feature class (FARHAT.BUILDINGS).
Adding domains to feature class (FARHAT.BUILDINGS).
Finished adding domains to feature class (FARHAT.BUILDINGS).
Finished adding object classes.
Adding relationship classes at the root level.
Finished adding relationship classes at root level.
Finished adding relationship classes.
Adding geometric networks.
Added the geometric networks.
Adding topologies.
Added the topologies.
Adding network datasets.
Added all network datasets.
Adding terrains.
Added terrain datasets.
Adding parcel fabrics.
Added all parcel fabrics.
Adding representations.
Added all representations.
Adding workspace extensions.
Adding address locators.
Added address locators.
Adding tooboxes.
Added toolboxes.
Added workspace extensions.
Adding replicas.
Added replicas.
Adding historical markers.
Added historical markers.
Deleting old schema tables.
Finished deleting old schema tables.
Finished upgrade.
Unpaused the database.
Succeeded at Mon Dec 10 14:13:13 2012 (Elapsed Time: 38.00 seconds)
You can confirm it from database properties
Revoking execute privileges from public
After you have created or upgraded the geodatabase, you can restrict who has execute permission on these packages:
- dbms_lob
- dbms_lock
- dbms_pipe
- dbms_utility
- dbms_sql
- utl_raw
To restrict access, revoke execute from public then explicitly grant execute to every user who logs into the geodatabase, including the sde user. Permissions must be granted to individual users.
Important:
You cannot grant execute to a role then grant the role to all the users because privileges granted through roles are not applicable when executing Oracle packages.
Recompile SDE Schema
After granting execute to individual users, recompile the sde schema:
EXEC dbms_utility.compile_schema( 'SDE' );
No comments:
Post a Comment