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.
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; /