Granting privileges to the Reviewer workspace components in Oracle

Available with Data Reviewer license.

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

Note:

Copying and pasting the examples may cause syntax errors.

Granting permissions

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

SET SERVEROUTPUT ON;

spool Roles_rev.sql;
DROP ROLE "RLREVEDITOR";

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

Tip:

Grant a user only select, update, insert, and delete privileges to the RevAdminCustomFields and RevAdminDescriptions tables if the user has privileges to modify the database schema and to add customized error descriptions.

Creating an editor user

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

Create the editor user.

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

Creating a viewer user

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

Create the viewer user.

CREATE USER REV_VIEWER PROFILE "DEFAULT" 
IDENTIFIED BY viewer
DEFAULT TABLESPACE "USERS" 
TEMPORARY TABLESPACE "TEMP" ACCOUNT UNLOCK;
GRANT "CONNECT" TO "REV_VIEWER";
GRANT CREATE TABLE TO "REV_VIEWER";
GRANT "RLREVVIEWER" TO "REV_VIEWER";  
/*-- RLREVVIEWER role has SELECT  permission on the REV data (featureclasses, tables, etc.)
ALTER USER REV_VIEWER QUOTA UNLIMITED ON "GDBLOGFILE";
ALTER USER REV_VIEWER QUOTA UNLIMITED ON "GDBLOGFILEIDX";