SiteMap

Create Database «Prev Next»

Lesson 10	Product user profile
Objective	Describe the tables and views that are created as part of the product user profile.

Current Oracle 23c Technology to Replace Product User Profile

In Oracle 23c, application-level command restriction and session control are accomplished using modern, secure, and supported features. 🔐 Modern Security Mechanisms:

Login Triggers: Automatically execute PL/SQL code when a user session begins. Useful for enforcing session-level policies or recording session context.
Oracle Database Vault: Enables powerful rule-based enforcement to restrict sensitive operations (e.g., `DROP`, `ALTER`, `SELECT`) based on user, time, program, or IP.
Role-Based Access Control (RBAC): Use `GRANT` and `REVOKE` to control access to commands and objects. Roles can be dynamically enabled or disabled during a session.
Unified Auditing: Monitors and logs access and usage across the database, replacing fragmented auditing mechanisms. It supports fine-grained auditing policies to detect unauthorized operations.
Fine-Grained Access Control (FGAC) / Virtual Private Database (VPD): Dynamically appends `WHERE` clauses to queries based on user identity, allowing row-level access policies.

✅ Recommended Additional Information

Here is the refactored list of security elements, presented as an HTML ordered list with child elements in unordered lists.

🔐 Enhanced Login Trigger Examples

Expand the section on login triggers by:
- Showing how to block specific users from using certain tools (e.g., TOAD, SQL Developer):
```
CREATE OR REPLACE TRIGGER restrict_tools
AFTER LOGON ON DATABASE
BEGIN
  IF SYS_CONTEXT('USERENV', 'MODULE') IN ('TOAD.exe', 'SQL Developer') THEN
    RAISE_APPLICATION_ERROR(-20000, 'Access from this tool is not allowed.');
  END IF;
END;
/
        
```
- Including context variables like:
  - SYS_CONTEXT('USERENV', 'OS_USER')
  - SYS_CONTEXT('USERENV', 'IP_ADDRESS')
🛡️ Oracle Database Vault Integration

Add a section explaining how to restrict SQL command categories using Database Vault:
- Use Command Rules to:
  - Prevent DROP TABLE by non-DBAs
  - Disable CREATE USER outside approved hours
  - Enforce policies based on client IP or program
- ✅ Example use case:
  
  Block DROP TABLE commands unless the user is in a protected realm or has a specific factor like “Approved Workstation.”

📋 Unified Auditing Strategy

Show how to set up fine-grained audit policies for specific actions like ALTER SYSTEM, GRANT, DROP, or even access to sensitive tables:

CREATE AUDIT POLICY admin_ops
  ACTIONS ALL ON SYS.SENSITIVE_TABLE;

AUDIT POLICY admin_ops;

Also include:

How to use UNIFIED_AUDIT_TRAIL view

Sample query to check violations:

SELECT EVENT_TIMESTAMP, DBUSERNAME, ACTION_NAME
FROM UNIFIED_AUDIT_TRAIL
WHERE ACTION_NAME = 'DROP TABLE';

🎯 Fine-Grained Access Control (FGAC)

Add a short demo for row-level security using policy functions:

BEGIN
  DBMS_RLS.ADD_POLICY(
    object_schema   => 'HR',
    object_name     => 'EMPLOYEES',
    policy_name     => 'emp_region_policy',
    function_schema => 'SECURITY_ADMIN',
    policy_function => 'get_user_region',
    statement_types => 'SELECT'
  );
END;
/

✅ Tip: You can reference this as Oracle's Virtual Private Database (VPD) feature.

🧠 Use of Roles and Secure Application Roles

Demonstrate:

How to create secure roles that are only enabled under certain conditions:

CREATE ROLE hr_data_access;
CREATE FUNCTION verify_hr_user RETURN BOOLEAN IS
BEGIN
  RETURN SYS_CONTEXT('USERENV', 'SESSION_USER') = 'HR_USER';
END;
/
CREATE ROLE hr_secure_role IDENTIFIED USING verify_hr_user;

Show how to dynamically enable/disable roles:

SET ROLE hr_data_access;

📦 Optional: Include Architecture Diagram

To complement your narrative:

Add a visual of session evaluation flow (Login → Trigger → Role Assignment → Command Control → Auditing)
Use boxes/arrows showing flow from "User Logon" → "Login Trigger" → "Command Rules" → "Auditing"

Optional Section for Use Cases
Add a "Use Cases" section that presents real-world scenarios:

Scenario	Modern Feature to Use
Restrict `DROP TABLE` to maintenance window	Oracle Database Vault Command Rule
Allow HR users to see only their region	VPD / FGAC
Log all login attempts from SQL*Plus	Unified Auditing + Login Trigger
Block users from TOAD and SQL Developer	Login Trigger using `MODULE` context
Prevent accidental DDL in Production	Secure Application Roles + Auditing

Secure Application Roles in Oracle 23c

Secure Application Roles (SARs) are roles that can only be enabled when a session meets specific criteria, such as using an approved client or passing a validation function. This adds a dynamic layer of control to session-based privileges.

Benefits

Restrict role activation based on application, IP, or session context
Prevent unauthorized tools from enabling powerful roles
Enhance separation of duties in multi-user environments

Example

 -- Step 1: Create a validation function
CREATE OR REPLACE FUNCTION hr_role_auth RETURN BOOLEAN IS
BEGIN
  RETURN SYS_CONTEXT('USERENV', 'MODULE') = 'MyApp.exe';
END;
/

-- Step 2: Create the secure role
CREATE ROLE hr_secure_role IDENTIFIED USING hr_role_auth;

-- Step 3: Grant privileges
GRANT SELECT ON hr.employees TO hr_secure_role;

-- Step 4: Enable role at runtime
SET ROLE hr_secure_role;

Real Application Security (RAS) in Oracle 23c

Real Application Security (RAS) allows application users—not database users—to be managed directly by the database with fine-grained access controls. RAS is ideal for modern, multi-tier applications where user context must drive security policies.

Key Concepts

Application Users: Session-level users managed via the app
Security Classes: Policies grouped by object or action type
Access Policies: Define what actions users can perform

Example

-- Enable RAS
ALTER SYSTEM SET REAL_APPLICATION_SECURITY = ENABLE;

-- Create an application user
BEGIN
  DBMS_RAS.CREATE_USER('web_user');
END;
/

-- Tag session with application identity
EXECUTE DBMS_SESSION.SET_IDENTIFIER('web_user');

-- Attach access control policy
BEGIN
  DBMS_RAS.ATTACH_ACCESS_POLICY(
    schema_name        => 'HR',
    object_name        => 'EMPLOYEES',
    policy_name        => 'HR_ACCESS_POLICY',
    access_policy_type => DBMS_RAS.ACCESS_POLICY_ROW
  );
END;
/

Multitenant Considerations for Security in Oracle 23c

Oracle 23c supports a multitenant architecture, using container databases (CDBs) and pluggable databases (PDBs). Security features like Secure Application Roles and RAS are PDB-specific unless configured globally.

Secure Application Roles

Defined and used within each PDB
Cannot be shared across PDBs
Validation functions must also reside in the same PDB

-- Inside PDB1
ALTER SESSION SET CONTAINER = PDB1;
CREATE ROLE finance_role IDENTIFIED USING validate_finance_user;

Real Application Security (RAS)

Enabled independently in each PDB
Application users and policies are scoped to the local PDB
RAS is ideal for SaaS-style multi-tenant deployments

-- Enable RAS in a specific PDB
ALTER SYSTEM SET REAL_APPLICATION_SECURITY = ENABLE SCOPE=SPFILE;

Auditing and Command Control

Unified Auditing records actions separately for each PDB
Database Vault command rules must be defined per PDB unless configured globally