Oracle Distributed Systems

An Example

Since data dictionary tables cannot be replicated, the DBA must perform various administrative tasks in multiple sites. One particular task that I have found to be a needless nuisance is user administration, so I have created the package USERADMIN to replicate calls to create and drop users and to grant and revoke roles. The scripts to create the package are:

cr_seq_audit_admin.sql

Creates the sequence SEQ_AUDIT_ADMIN which is used to populate the ID field in the table AUDIT_ADMIN.

cr_audit_admin.sql

Creates the table AUDIT_ADMIN, which logs usage of the USERADMIN package.

pl_useradmin.sql

Calls the scripts cr_seq_audit_admin.sql and cr_audit_admin.sql and creates the package USERADMIN. This script should be run by user SYSTEM.

These scripts are included here .

cr_seq_audit_admin.sql

---------------------------------------------------------------------------
-- Filename:    cr_seq_audit_admin.sql
-- Purpose:     Creates SEQ_AUDIT_ADMIN used by USER_ADMIN procedure
-- Author:      Chas. Dye ([e-mail])
-- Date:        5-Mar-1998
--------------------------------------------------------------------------- 

set echo on
set termout on
spool seq_audit_admin.log

DROP PUBLIC SYNONYM seq_audit_admin
/
DROP SEQUENCE seq_audit_admin
/
CREATE SEQUENCE seq_audit_admin
START WITH 1
/
CREATE PUBLIC SYNONYM seq_audit_admin FOR seq_audit_admin
/

spool off

cr_audit_admin.sql

--------------------------------------------------------------------------- -- Filename: cr_audit_admin.sql -- Purpose: Creates table ...