Oracle Database

References

The biggest issue faced as a beginner is where to find the right resources. Here's a summary:

Oracle Database Documentation: Serves as both references and tutorials
- Select the Oracle Database version (in my case, Oracle Database 19c), and click on any of the topics to bring you to the documentation subpage.
- Virtual Private Database (VPD): Security > Database Security Guide > Controlling Data Access > 14 Using Oracle VPD (note that VPD is deprecated since Oracle 12c, ought to move on to RAS instead)
- Oracle Label Security (OLS): Security > Label Security Administrator's Guide
OracleTutorial.com: Provides quick recipes

Google isn't a particularly good way to reach the correct documentation, because of all the legacy documentation available online (which is great, but confusing). Sections tend to be the same across different releases, so good for initial keyword finding via search engine first. See some screenshots here:

Some documentation directly relevant to OLS:

Also a nice article:

https://blogs.oracle.com/sql/post/how-to-create-users-grant-them-privileges-and-remove-them-in-oracle-database

Getting started

This is assuming the Oracle database is already installed. The populated directories seem to be:

# Oracle database installation directory
> cd $ORACLE_HOME
> pwd
/opt/oracle/product/19c/dbhome_1

# Patching database
# Log for latest patch found in:
# /opt/oracle/cfgtoollogs/sqlpatch/34133642/24865470
#   /34133642_apply_CS5322_2022Aug13_14_09_22.log
> unzip p34130714_190000_Linux-x86-64.zip
> cd 34133642/
> /opt/oracle/product/19c/dbhome_1/OPatch/opatch apply

# Not sure what this command does
> cd $ORACLE_HOME/OPatch
> ./datapatch -verbose
> cd $ORACLE_HOME/rdbms/admin

# Configuration for listener
> vim /opt/oracle/product/19c/dbhome_1/network/admin/listener.ora

Starting the SQL database,

> sqlplus

SQL*Plus: Release 19.0.0.0.0 - Production on Wed Sep 21 09:23:28 2022
Version 19.16.0.0.0

Copyright (c) 1982, 2022, Oracle.  All rights reserved.

Enter user-name: / as sysdba

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.16.0.0.0

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area 2801794952 bytes
Fixed Size                  8900488 bytes
Variable Size             721420288 bytes
Database Buffers         2063597568 bytes
Redo Buffers                7876608 bytes
Database mounted.
Database opened.

Running the listener (stop using lsnrctl stop):

> lsnrctl start
Starting /opt/oracle/product/19c/dbhome_1/bin/tnslsnr: please wait...

TNSLSNR for Linux: Version 19.0.0.0.0 - Production
System parameter file is /opt/oracle/product/19c/dbhome_1/network/admin/listener.ora
Log messages written to /opt/oracle/diag/tnslsnr/cs5322-10-i/listener/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=cs5322-10-i.comp.nus.edu.sg)(PORT=1521)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=PNPKEY)))

Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=cs5322-10-i)(PORT=1521))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 19.0.0.0.0 - Production
Start Date                21-SEP-2022 09:26:51
Uptime                    0 days 0 hr. 0 min. 0 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /opt/oracle/product/19c/dbhome_1/network/admin/listener.ora
Listener Log File         /opt/oracle/diag/tnslsnr/cs5322-10-i/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=cs5322-10-i.comp.nus.edu.sg)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=PNPKEY)))
Services Summary...
Service "cs5322.comp.nus.edu.sg" has 1 instance(s).
  Instance "cs5322", status UNKNOWN, has 3 handler(s) for this service...
The command completed successfully

Connections to Oracle database can be performed through the sqldeveloper client:

Debug

Status : Failure -Test failed: IO Error: The Network Adapter could not establish the connection (CONNECTION_ID=c37zUnR9TT2zm2E74xAv3Q==)

Likely from a killed telnet process without properly shutting down the connection, with the following processes still running after listener and DB has been shutdown. Update: Nope, didn't fix the problem.

> lsof -nPi
COMMAND   PID   USER   FD   TYPE DEVICE SIZE/OFF NODE NAME
oracle  31274 oracle    5u  IPv6 276989      0t0  TCP *:16252 (LISTEN)
oracle  31274 oracle    7u  IPv4 277000      0t0  TCP 172.25.77.158:34950->172.25.77.158:1521 (CLOSE_WAIT)
oracle  31276 oracle    5u  IPv6 276992      0t0  TCP *:9057 (LISTEN)
oracle  31276 oracle    7u  IPv4 277003      0t0  TCP 172.25.77.158:34952->172.25.77.158:1521 (CLOSE_WAIT)

Somehow connecting to the database works again after a day has passed, and outside of internal NUS network.

Database initialization

Step 1: Create tables.

GENERATED BY DEFAULT ON NULL AS IDENTITY acts as auto-incrementor if no value provided, see tutorial.
CONSTRAINT [name] [TYPE] (column)... for constraints

CREATE TABLE Planner (
    planner_id NUMBER GENERATED BY DEFAULT ON NULL AS IDENTITY,
    username varchar2(50) NOT NULL,
    password varchar2(50) NOT NULL,
    CONSTRAINT pk_Planner PRIMARY KEY (planner_id),
    CONSTRAINT uc_Planner_username UNIQUE (username)
);

Step 2: Create policies

No idea why SCHEMA_P and TABLE_P specified. The IN keyword means the variable is an outside reference that cannot be modified (needs INOUT instead if modification required).
RETURN statement declares return datatype, followed by more local variable declarations. No difference between IS and AS when creating a procedure, see SO.
Note occasional references to the DUAL table, essentially some syntactic markup.

CREATE OR REPLACE FUNCTION GET_PLAN(
    SCHEMA_P IN VARCHAR2,
    TABLE_P IN VARCHAR2
) RETURN VARCHAR2 AS
    PRED VARCHAR2 (400);
BEGIN
    IF LOWER(SYS_CONTEXT('USERENV', 'CLIENT_IDENTIFIER')) = 'customer'
        THEN PRED := 'plan_id in (select plan_id from system.purchase)';
    ELSE NULL;
    END IF;
    RETURN PRED;
END;
/
 
BEGIN
    DBMS_RLS.ADD_POLICY (
        OBJECT_SCHEMA => 'system',
        OBJECT_NAME => 'plan',
        POLICY_NAME => 'plan_policy',
        POLICY_FUNCTION => 'get_plan',
        STATEMENT_TYPES => 'select',
        POLICY_TYPE => DBMS_RLS.CONTEXT_SENSITIVE
    );
END;
/

Main objective to use CLIENT_IDENTIFIER in USERENV to determine session user, then apply different policies.

If Customer, select only Plans that Customer can see (from Purchase)
If Agent, select/update only Customers that Agent can see (same for Customer). Planners and Financer can only see their own rows.
As Customer, hide all usernames and passwords.
Agent and Customer can only see payment IDs + premiums + purchases that they can see in Customer.
Agent and Customer and Financer can only see Claims that are assigned to them (for Agents, claims of the Customers assigned to them)

Note that Planner and Financer can see almost everything by security policy => need to restrict on access rights level to specific tables.

Also note that CLIENT_IDENTIFIER is set automatically by the database client.

Step 3: Create logon triggers to set context values:

The trigger fires the SET_AGENTID procedure within the AGENT_CTX_PKG package...
... which sets the environment variable 'agent_id' with the agent's ID pulled from the database.
All triggers will fire, and only those that have the required 'agent_id', etc. columns will have the context set, otherwise the 'NO_DATA_FOUND' exception is caught and the procedure ends.

-- Create application context
CREATE OR REPLACE CONTEXT AGENT_CTX USING AGENT_CTX_PKG;
 
-- Create a PL/SQL package to set the application context
CREATE OR REPLACE PACKAGE AGENT_CTX_PKG IS
    PROCEDURE SET_AGENTID;
END;
/
 
CREATE OR REPLACE PACKAGE BODY AGENT_CTX_PKG IS
    PROCEDURE SET_AGENTID AS
        AGENTID NUMBER;
    BEGIN
        SELECT
            AGENT_ID INTO AGENTID
        FROM
            SYSTEM.AGENT
        WHERE
            USERNAME = SYS_CONTEXT('USERENV',
            'SESSION_USER');
        DBMS_SESSION.SET_CONTEXT('agent_ctx', 'agent_id', AGENTID);
    EXCEPTION
        WHEN NO_DATA_FOUND THEN
            NULL;
    END SET_AGENTID;
END;
/
 
-- Create a logon trigger to run the application context PL/SQL package
CREATE TRIGGER SET_AGENTID_CTX_TRIG AFTER LOGON ON DATABASE
BEGIN
    AGENT_CTX_PKG.SET_AGENTID;
END;
/

Step 4: Alter tables to change default values

Default value for AGENT_ID in Customer changed to current agent ID
Can do on a case-by-case basis to have default values

ALTER TABLE SYSTEM.CUSTOMER MODIFY (AGENT_ID  DEFAULT SYS_CONTEXT('agent_ctx', 'agent_id'));

Tablespaces

https://www.oracletutorial.com/oracle-administration/oracle-create-tablespace/

https://docs.oracle.com/cd/B19306_01/server.102/b14220/physical.htm#i2006

View tablespaces created:

SELECT 
  tablespace_name, 
  file_name, 
  bytes/1024/1024 MB
FROM
  dba_data_files;