Enhance security in Oracle database environments

Context

Change of the deprecated synonym.sql environment to the one described here.

Different DML and DDL users are used for the Oracle database to achieve enhanced security.

What is DDL?

DDL - Data Definition Language

DDL statements are used to define the database structure or schema.

Some examples:
CREATE - to create objects in the database
ALTER - alters the structure of the database
DROP - delete objects from the database
TRUNCATE - remove all records from a table, including all spaces allocated for the records are removed
COMMENT - add comments to the data dictionary
RENAME - rename an object

What is DML?

DML - Data Manipulation Language

DML statements are used for managing data within schema objects.

Some examples:
SELECT - retrieve data from the a database
INSERT - insert data into a table
UPDATE - updates existing data within a table
DELETE - deletes all records from a table, the space for the records remain
MERGE - UPSERT operation (insert or update)
CALL - call a PL/SQL or Java subprogram
EXPLAIN PLAN - explain access path to data
LOCK TABLE - control concurrency

Steps

Set up two Oracle users with different permissions.

Create users

EXAMPLE User 1: CORPUS - as schema owner and for DDL updates

Replace CORPUS with your schema user.

CREATE TABLESPACE "CORPUS"
    LOGGING
    DATAFILE '$HOME/orahome/oradata/corpus/CORPUS.dbf'
    SIZE 100M
    AUTOEXTEND ON NEXT 64M
    EXTENT MANAGEMENT LOCAL
    SEGMENT SPACE MANAGEMENT AUTO;

CREATE USER "CORPUS" PROFILE "DEFAULT"
    IDENTIFIED BY "corpus"
    DEFAULT TABLESPACE "CORPUS"
    TEMPORARY TABLESPACE "TEMP"
    ACCOUNT UNLOCK;

GRANT CONNECT, RESOURCE, SELECT_CATALOG_ROLE, CREATE VIEW TO "CORPUS";
GRANT READ, WRITE ON DIRECTORY DATA_PUMP_DIR TO "CORPUS";
ALTER USER "CORPUS" QUOTA UNLIMITED ON "CORPUS";

CONNECT CORPUS/corpus;

Use censhare-Server/database/create-db.sql to create all tables for your censhare-Server.

EXAMPLE User 2: CORPUS_TRX - as application user for DML changes

Replace CORPUS_TRX with your application user.

CREATE USER "CORPUS_TRX" PROFILE "DEFAULT"
    IDENTIFIED BY "corpus"
    DEFAULT TABLESPACE "USERS"
    TEMPORARY TABLESPACE "TEMP"
    ACCOUNT UNLOCK;

Create logon trigger

When DML users log in, they automatically switch their schema.

CREATE OR REPLACE TRIGGER SYS.CORPUS_TRX_AFTERLOGON 
AFTER LOGON 
ON CORPUS_TRX.SCHEMA 
BEGIN 
EXECUTE IMMEDIATE 'alter session set current_schema=CORPUS'; 
END; 
/ 

Replace user view

There is a special user view that is used by censhare. We need to replace it.

CREATE OR REPLACE VIEW CORPUS.user_sequences 
AS 
SELECT * 
FROM SYS.ALL_SEQUENCES 
WHERE sequence_owner = 'CORPUS';
GRANT SELECT ON CORPUS.user_sequences TO CORPUS_TRX;

After every DDL update

Repeat this steps after every SQL update that involves DDL-based changes.

BEGIN 
FOR R IN (SELECT owner, table_name FROM all_tables WHERE owner='CORPUS') LOOP 
EXECUTE IMMEDIATE 'grant select, insert, update, delete on '||R.owner||'.'||R.table_name||' to CORPUS_TRX'; 
END LOOP; 
END; 
/