CreateApplicationUser.sql

/*
Purpose:
- Create an Oracle application schema with an explicit, minimal privilege set.

Safety:
- Changes state by creating a user and granting privileges.
- Avoids broad legacy roles and UNLIMITED TABLESPACE.

Requirements:
- Oracle 12c or later.
- Administrative privileges to create users and grant system privileges.

Customization:
- Replace every angle-bracket placeholder.
- Size the quota deliberately and supply the password from a secret manager.
*/

CREATE USER <APP_SCHEMA>
    IDENTIFIED BY "<STRONG_PASSWORD_FROM_SECRET_MANAGER>"
    DEFAULT TABLESPACE <APP_DATA_TABLESPACE>
    TEMPORARY TABLESPACE <TEMP_TABLESPACE>
    QUOTA <QUOTA_SIZE> ON <APP_DATA_TABLESPACE>;

GRANT CREATE SESSION TO <APP_SCHEMA>;
GRANT CREATE TABLE TO <APP_SCHEMA>;
GRANT CREATE VIEW TO <APP_SCHEMA>;
GRANT CREATE SEQUENCE TO <APP_SCHEMA>;
GRANT CREATE PROCEDURE TO <APP_SCHEMA>;
GRANT CREATE TRIGGER TO <APP_SCHEMA>;

-- Rollback, if required. CASCADE also removes objects owned by the schema:
-- DROP USER <APP_SCHEMA> CASCADE;