Enhance security in Oracle database environments
Separate users and permissions for the application and the application's updates to enhance security.
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
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
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;
/