Security Architecture Overview

Oracle RMS implements security at two distinct levels:

Both layers work together. A user might have functional access to the Purchase Order screen (they can open it), but data-level security restricts them to seeing only POs for Department 1000 (their assigned department).

Functional Security (Roles & Privileges)

Functional security in RMS is based on a Role → Privilege → Screen model:

The Hierarchy

USER (john.doe)
  │
  ├── ROLE: BUYER
  │     ├── PRIV: PO_CREATE        → Can create Purchase Orders
  │     ├── PRIV: PO_APPROVE       → Can approve POs up to $50,000
  │     ├── PRIV: ITEM_VIEW        → Can view item details
  │     └── PRIV: ITEM_EDIT        → Can modify item attributes
  │
  └── ROLE: REPORTING
        ├── PRIV: REPORT_INVENTORY → Can run inventory reports
        └── PRIV: REPORT_SALES    → Can run sales reports

Privilege Types

• Menu Access: Controls which menu items/screens the user can see in the navigation
• Function Access: Controls which actions within a screen are available (Create, Edit, Delete, Approve)
• Value Threshold: Controls limits (e.g., PO approval limit of $50,000 vs $500,000)
• Report Access: Controls which reports the user can execute

Data-Level Security

Data-level security restricts which rows a user can see based on their organizational assignments. This is implemented through security filters applied to every query.

Security Dimensions

How It Works Internally

When a user queries the Item Master screen, RMS does NOT simply run SELECT * FROM ITEM_MASTER. Instead, it adds a security filter:

-- What the user THINKS the query is:
SELECT item, item_desc, dept, class FROM item_master;

-- What RMS ACTUALLY executes:
SELECT im.item, im.item_desc, im.dept, im.class 
FROM   item_master im
WHERE  im.dept IN (
    SELECT sur.dept 
    FROM   sec_user_role sur 
    WHERE  sur.user_id = SYS_CONTEXT('USERENV', 'CLIENT_IDENTIFIER')
      AND  sur.role_id = 'BUYER'
);

This filtering happens transparently. The user never knows they are seeing a restricted dataset — they simply don't see data outside their assigned scope.

Key Security Tables

User & Role Setup Process

Creating a New User

-- 1. Create the user
INSERT INTO sec_user (
    user_id, user_name, lang, status,
    create_datetime, last_update_id
) VALUES (
    'JDOE', 'John Doe', 'EN', 'A',
    SYSDATE, 'ADMIN'
);

-- 2. Assign the BUYER role
INSERT INTO sec_user_role (
    user_id, role_id, 
    start_date, end_date,
    create_datetime, last_update_id
) VALUES (
    'JDOE', 'BUYER',
    TRUNC(SYSDATE), NULL,
    SYSDATE, 'ADMIN'
);

-- 3. Assign department-level data security
INSERT INTO sec_user_dept (
    user_id, dept,
    create_datetime, last_update_id
) VALUES (
    'JDOE', 1000,   -- Menswear department
    SYSDATE, 'ADMIN'
);

-- 4. Assign location-level data security
INSERT INTO sec_user_loc (
    user_id, loc, loc_type,
    create_datetime, last_update_id
) VALUES (
    'JDOE', 'NE',   -- Northeast region
    'R',             -- Region type
    SYSDATE, 'ADMIN'
);

COMMIT;

Role Template Examples

VPD (Virtual Private Database) Policies

In more advanced RMS deployments, data-level security is enforced using Oracle Virtual Private Database (VPD) — a database-level security feature that automatically appends WHERE clauses to every query.

How VPD Works

1. A VPD policy function is attached to a table (e.g., ITEM_MASTER)
2. Every time ANY query runs against that table, Oracle automatically calls the policy function
3. The policy function returns a WHERE clause predicate based on the current user's security profile
4. Oracle appends this predicate to the query before execution

-- VPD Policy Function Example
CREATE OR REPLACE FUNCTION fn_item_security (
    p_schema  IN VARCHAR2,
    p_object  IN VARCHAR2
) RETURN VARCHAR2 IS
    l_predicate VARCHAR2(4000);
BEGIN
    -- Get the current user's department assignments
    l_predicate := 'dept IN (SELECT dept FROM sec_user_dept ' ||
                   'WHERE user_id = SYS_CONTEXT(''USERENV'', ''CLIENT_IDENTIFIER''))';
    RETURN l_predicate;
END;
/

-- Attach the policy to ITEM_MASTER
BEGIN
    DBMS_RLS.ADD_POLICY(
        object_schema   => 'RMS13',
        object_name     => 'ITEM_MASTER',
        policy_name     => 'ITEM_DEPT_SECURITY',
        function_schema => 'RMS13',
        policy_function => 'FN_ITEM_SECURITY',
        statement_types => 'SELECT,UPDATE,DELETE'
    );
END;
/

LDAP / Active Directory Integration

Enterprise RMS deployments integrate with corporate LDAP or Active Directory for authentication:

1. User logs into RMS with their corporate credentials
2. RMS authenticates against the LDAP server (via the WebLogic LDAP authenticator)
3. RMS maps the LDAP user to the SEC_USER table (user_id must match LDAP uid)
4. RMS loads the user's roles from SEC_USER_ROLE
5. RMS applies data security based on SEC_USER_DEPT and SEC_USER_LOC

This eliminates the need for RMS-specific passwords and enables Single Sign-On (SSO) across the enterprise application suite.

Security in the Cloud (v19+)

In the cloud (MFCS), the security model adds additional layers:

• Oracle Identity Cloud Service (IDCS): Replaces LDAP as the identity provider. All user authentication goes through IDCS with support for SSO, MFA (Multi-Factor Authentication), and social login.
• Application Roles: Managed through IDCS role assignments rather than direct SEC_USER_ROLE inserts
• Network Security: All communication is encrypted (TLS 1.2+). VPN or FastConnect required for integration with on-premise systems.
• API Authentication: REST API access requires OAuth2 tokens with scoped permissions

Security Best Practices