Granting privileges to product library components in Oracle using a script (Production Mapping)

Read/Write privileges are already assigned to all the product library tables, except the PCAT_PERMISSION, which doesn't need them. This is done when the product library is defined or upgraded. If a product library XML was imported into a geodatabase, or distributed, you need to re-create the CKB_USERS role and assign permissions in the new product library geodatabase.

The PCAT_PERMISSION table only needs read privileges assigned to it.

NoteNote:

Copying and pasting the example may cause syntax errors.

set echo off;
set verify off;
set heading off;
set feedback off;
set newpage none;
set termout off;
set lines 200;
set trims on;
ttitle off;
btitle off;
clear;
SET SERVEROUTPUT ON;
spool ROLE_CKB_USERS.sql;

select 'DROP ROLE "CKB_USERS";' from dual;
select 'CREATE ROLE "CKB_USERS" NOT IDENTIFIED;' from dual;

select 'grant select,insert,update,delete on ' ||owner|| '.' || table_name || ' to CKB_USERS;'  
from sys.dba_tables where lower(owner) = 'prodlib' order by table_name;

select 'REVOKE INSERT,UPDATE,DELETE ON PRODLIB.PCAT_PERMISSION FROM CKB_USERS;' from dual;

spool off;
set echo off;
set verify off;
set heading off;
set feedback off;
set newpage none;
set termout off;
set lines 200;
set trims on;
ttitle off;
btitle off;
clear;

SET SERVEROUTPUT ON;
/
@ROLE_CKB_USERS.sql;
/
6/8/2015