Secure Logger Schema Setup

Create script or doc page to cover how to create a "secure" logger schema. This way if any concerns about the create any context is raised it can be locked down

Example:


-- This script will not preserve existing logs. If so it can be updated to do so

-- Assumptions:
-- Schame Names
--  - Current schema is called MYSCHEMA and new "logger" schema will be MYSCHEMA_LOGGER
--  - Change accordingly.
-- Directory
--  - Current directory is the logger install directory

-- *** As SYSTEM ***
-- TODO change tablespace, quota, etc accordingly 
-- TODO use the create user schema script
create user MYSCHEMA_LOGGER identified by MYSCHEMA default tablespace users quota unlimited on users;  
grant connect,create view, create job, create table, create sequence, create trigger, create procedure, create any context to MYSCHEMA_LOGGER; 

-- *** As MYSCHEMA_LOGGER ***
@logger_install.sql

grant execute on logger to MYSCHEMA;
grant select, delete on logger_logs to MYSCHEMA;
grant select on logger_logs_apex_items to MYSCHEMA;
grant select, update on logger_prefs to MYSCHEMA;
grant select on logger_prefs_by_client_id to MYSCHEMA;
grant select on logger_logs_5_min to MYSCHEMA;
grant select on logger_logs_60_min to MYSCHEMA;
grant select on logger_logs_terse to MYSCHEMA;

-- *** As MYSCHEMA ***
-- Uninstall Logger (this will create some invalid packages for those that currently reference it)
@drop_logger.sql

-- Create synonyms
create or replace synonym logger for MYSCHEMA_LOGGER.logger;
create or replace synonym logger_logs for MYSCHEMA_LOGGER.logger_logs;
create or replace synonym logger_logs_apex_items for MYSCHEMA_LOGGER.logger_logs_apex_items;
create or replace synonym logger_prefs for MYSCHEMA_LOGGER.logger_prefs;
create or replace synonym logger_prefs_by_client_id for MYSCHEMA_LOGGER.logger_prefs_by_client_id;
create or replace synonym logger_logs_5_min for MYSCHEMA_LOGGER.logger_logs_5_min;
create or replace synonym logger_logs_60_min for MYSCHEMA_LOGGER.logger_logs_60_min;
create or replace synonym logger_logs_terse for MYSCHEMA_LOGGER.logger_logs_terse;

-- TODO may need to recompile invalid objects
-- If so can use: exec dbms_utility.compile_schema(schema => user,  compile_all => false);

-- *** As SYSTEM ***
alter user MYSCHEMA_LOGGER account lock;
-- Note: account will need to be unlocked when doing upgrades to logger

tmuth / Logger---A-PL-SQL-Logging-Utility

Secure Logger Schema Setup #56