Granting privileges to the Production Mapping workspace components in Oracle (Production Mapping)

The geodatabase administrator must grant specific privileges to the tables for all users who will be accessing the Esri Production Mapping workspace. This can be accomplished by creating database roles and assigning the roles to the individual users.

NoteNote:

Copying and pasting the example may cause syntax errors.

Granting permissions

In Oracle SQL Plus or Oracle SQL Developer, grant permissions to the Production Mapping workspace tables through the schema. The sample script shows how to drop the roles, re-create them, and assign permissions.

SET SERVEROUTPUT ON;

spool Roles_pm.sql;
DROP ROLE "RLPMEDITOR";

CREATE ROLE "RLPMEDITOR" NOT IDENTIFIED;
DROP ROLE "RLPMVIEWER";
CREATE ROLE "RLPMVIEWER" NOT IDENTIFIED;
select 'grant select on ' ||owner|| '.' || table_name || '  to RLPMVIEWER;'
from sys.dba_tables where lower(owner) = 'pm' order by table_name;
select 'grant select,insert,update,delete on ' ||owner|| '.' || table_name || ' to RLPMEDITOR;'
from sys.dba_tables where lower(owner) = 'pm' order by table_name;
spool off;
SET SERVEROUTPUT ON;
/
@Roles_pm.sql;
/

Creating an editor user

Users should have their own login names. The example below shows how to create an editor ArcSDE user and grant the RLPMEDITOR role to ArcSDE editor user.

Create the editor user.

CREATE USER PM_EDITOR PROFILE "DEFAULT" 
	IDENTIFIED BY editor 
	DEFAULT TABLESPACE "USERS" 
	TEMPORARY TABLESPACE "TEMP" ACCOUNT UNLOCK;
GRANT "CONNECT" TO "PM_EDITOR";
GRANT CREATE TABLE TO "PM_EDITOR";
GRANT "RLPMEDITOR" TO "PM_EDITOR";  
/*-- RLPMEDITOR role has SELECT, INSERT, UPDATE and DELETE permission on the PM data (featureclasses, tables, etc.)
ALTER USER PM_EDITOR QUOTA UNLIMITED ON "SDELOGFILE";
ALTER USER PM_EDITOR QUOTA UNLIMITED ON "SDELOGFILEIDX";

Creating a viewer user

Users should have their own login names. The example below shows how to create a viewer ArcSDE user and grant the RLPMVIEWER role to the ArcSDE viewer user.

Create the viewer user.

CREATE USER PM_VIEWER PROFILE "DEFAULT" 
IDENTIFIED BY viewer
DEFAULT TABLESPACE "USERS" 
TEMPORARY TABLESPACE "TEMP" ACCOUNT UNLOCK;
GRANT "CONNECT" TO "PM_VIEWER";
GRANT CREATE TABLE TO "PM_VIEWER";
GRANT "RLPMVIEWER" TO "PM_VIEWER";  
/*-- GISVIEWER role has SELECT permission on the PM data (featureclasses, tables, etc.)
ALTER USER PM_VIEWER QUOTA UNLIMITED ON "SDELOGFILE";
ALTER USER PM_VIEWER QUOTA UNLIMITED ON "SDELOGFILEIDX";
6/8/2015