Oracle Security Migration to Azure¶

-- Oracle: Database users with password authentication
CREATE USER app_user IDENTIFIED BY "ComplexP@ss123"
    DEFAULT TABLESPACE users
    QUOTA UNLIMITED ON users
    PROFILE app_profile;

CREATE PROFILE app_profile LIMIT
    PASSWORD_LIFE_TIME 90
    PASSWORD_REUSE_TIME 365
    PASSWORD_REUSE_MAX 12
    FAILED_LOGIN_ATTEMPTS 5
    PASSWORD_LOCK_TIME 1/24;

GRANT CREATE SESSION TO app_user;
GRANT SELECT ON hr.employees TO app_user;

-- Azure SQL MI: Entra ID authentication (recommended)
-- 1. Create Entra ID user (no password managed by database)
CREATE USER [app_user@agency.onmicrosoft.com] FROM EXTERNAL PROVIDER;
ALTER ROLE db_datareader ADD MEMBER [app_user@agency.onmicrosoft.com];

-- 2. Or create Entra ID group for role-based access
CREATE USER [DBA-Team-SecurityGroup] FROM EXTERNAL PROVIDER;
ALTER ROLE db_owner ADD MEMBER [DBA-Team-SecurityGroup];

-- 3. Managed identity for applications (no credentials to manage)
CREATE USER [app-managed-identity] FROM EXTERNAL PROVIDER;
GRANT SELECT ON dbo.employees TO [app-managed-identity];

-- PostgreSQL: Entra ID authentication
-- Configure in server parameters: azure.extensions = pgaadauth
-- Then create users mapped to Entra ID
SELECT * FROM pgaadauth_create_principal('app_user@agency.onmicrosoft.com', false, false);
GRANT SELECT ON ALL TABLES IN SCHEMA app_schema TO "app_user@agency.onmicrosoft.com";

-- Oracle VPD: Restrict employees to see only their department's data
CREATE OR REPLACE FUNCTION dept_security_policy(
    p_schema IN VARCHAR2,
    p_object IN VARCHAR2
) RETURN VARCHAR2 IS
    v_dept_id NUMBER;
BEGIN
    -- Get current user's department from context
    v_dept_id := SYS_CONTEXT('HR_CTX', 'DEPARTMENT_ID');

    IF v_dept_id IS NOT NULL THEN
        RETURN 'department_id = ' || v_dept_id;
    ELSE
        RETURN '1=0';  -- No access if no department context
    END IF;
END;
/

-- Apply policy to table
BEGIN
    DBMS_RLS.ADD_POLICY(
        object_schema   => 'HR',
        object_name     => 'EMPLOYEES',
        policy_name     => 'DEPT_ACCESS_POLICY',
        function_schema => 'HR',
        policy_function => 'DEPT_SECURITY_POLICY',
        statement_types => 'SELECT,INSERT,UPDATE,DELETE'
    );
END;
/

-- Azure SQL MI: Row-Level Security
-- 1. Create schema for security predicates
CREATE SCHEMA Security;
GO

-- 2. Create predicate function
CREATE FUNCTION Security.fn_dept_security_predicate(
    @department_id int
)
RETURNS TABLE
WITH SCHEMABINDING
AS
    RETURN SELECT 1 AS fn_result
    WHERE @department_id = (
        SELECT department_id
        FROM dbo.user_department_mapping
        WHERE user_name = SUSER_SNAME()
    )
    OR IS_MEMBER('db_owner') = 1;  -- DBA bypass
GO

-- 3. Create security policy
CREATE SECURITY POLICY Security.DeptAccessPolicy
ADD FILTER PREDICATE Security.fn_dept_security_predicate(department_id)
    ON dbo.employees,
ADD BLOCK PREDICATE Security.fn_dept_security_predicate(department_id)
    ON dbo.employees AFTER INSERT,
ADD BLOCK PREDICATE Security.fn_dept_security_predicate(department_id)
    ON dbo.employees AFTER UPDATE
WITH (STATE = ON);
GO

-- PostgreSQL: Row-Level Security
-- 1. Enable RLS on the table
ALTER TABLE employees ENABLE ROW LEVEL SECURITY;

-- 2. Create policy
CREATE POLICY dept_access_policy ON employees
    USING (
        department_id = (
            SELECT department_id
            FROM user_department_mapping
            WHERE user_name = current_user
        )
        OR current_user IN (SELECT rolname FROM pg_roles WHERE rolsuper)
    );

-- 3. Force RLS for table owner too (optional)
ALTER TABLE employees FORCE ROW LEVEL SECURITY;

-- Oracle OLS: Row labeled with security classification
-- Labels: UNCLASSIFIED, CUI, SECRET, TOP_SECRET
-- Users have maximum label clearance

-- Azure SQL MI equivalent: Classification column + RLS
-- 1. Add classification column to table
ALTER TABLE sensitive_documents ADD security_level nvarchar(20) NOT NULL DEFAULT 'UNCLASSIFIED';

-- 2. Create user-clearance mapping table
CREATE TABLE dbo.user_clearance (
    user_name nvarchar(128) PRIMARY KEY,
    max_clearance_level int NOT NULL  -- 0=UNCLASS, 1=CUI, 2=SECRET, 3=TS
);

-- 3. Create clearance-level mapping
CREATE TABLE dbo.clearance_levels (
    level_name nvarchar(20) PRIMARY KEY,
    level_order int NOT NULL
);
INSERT INTO dbo.clearance_levels VALUES
    ('UNCLASSIFIED', 0), ('CUI', 1), ('SECRET', 2), ('TOP_SECRET', 3);

-- 4. Create RLS predicate that enforces clearance
CREATE FUNCTION Security.fn_classification_predicate(
    @security_level nvarchar(20)
)
RETURNS TABLE WITH SCHEMABINDING AS
RETURN SELECT 1 AS result
WHERE (
    SELECT cl.level_order FROM dbo.clearance_levels cl WHERE cl.level_name = @security_level
) <= (
    SELECT uc.max_clearance_level FROM dbo.user_clearance uc WHERE uc.user_name = SUSER_SNAME()
);

-- 5. Apply security policy
CREATE SECURITY POLICY Security.ClassificationPolicy
ADD FILTER PREDICATE Security.fn_classification_predicate(security_level)
    ON dbo.sensitive_documents
WITH (STATE = ON);

-- Oracle: TDE configuration
ALTER SYSTEM SET ENCRYPTION KEY IDENTIFIED BY "wallet_password";
ALTER TABLESPACE users ENCRYPTION USING 'AES256' ENCRYPT;

-- Azure SQL MI: TDE is enabled by default
-- No configuration needed for service-managed keys

-- For customer-managed keys (BYOK):
-- 1. Create key in Azure Key Vault
-- az keyvault key create --vault-name kv-feddb --name tde-key --kty RSA --size 2048

-- 2. Configure SQL MI to use customer-managed key
-- az sql mi tde-key set --server-key-type AzureKeyVault \
--     --kid "https://kv-feddb.vault.azure.net/keys/tde-key/..." \
--     --managed-instance mi-instance \
--     --resource-group rg-prod

-- PostgreSQL: Column-level encryption (pgcrypto)
CREATE EXTENSION IF NOT EXISTS pgcrypto;

-- Encrypt sensitive column
UPDATE employees
SET ssn_encrypted = pgp_sym_encrypt(ssn, 'encryption_key')
WHERE ssn IS NOT NULL;

-- Oracle: Unified audit policy
CREATE AUDIT POLICY sensitive_data_access
    ACTIONS SELECT ON hr.employees,
            UPDATE ON hr.employees,
            DELETE ON hr.employees;
AUDIT POLICY sensitive_data_access;

-- Oracle: Fine-grained auditing
BEGIN
    DBMS_FGA.ADD_POLICY(
        object_schema => 'HR',
        object_name   => 'EMPLOYEES',
        policy_name   => 'FGA_SALARY_ACCESS',
        audit_column  => 'SALARY,SSN',
        statement_types => 'SELECT'
    );
END;
/

-- Azure SQL MI: SQL Auditing (automatic, logs to Azure Monitor)
-- Enabled via Azure Portal or CLI:
-- az sql mi audit-policy update \
--     --resource-group rg-prod \
--     --managed-instance mi-instance \
--     --state Enabled \
--     --storage-account staudit \
--     --log-analytics-target-state Enabled \
--     --log-analytics-workspace-resource-id /subscriptions/.../workspaces/law-prod

-- Query audit logs in Log Analytics
-- AzureDiagnostics
-- | where Category == "SQLSecurityAuditEvents"
-- | where statement_s contains "employees"
-- | project TimeGenerated, server_principal_name_s, statement_s, response_rows_d

-- PostgreSQL: pgAudit extension
-- Enable in server parameters: shared_preload_libraries = 'pgaudit'
CREATE EXTENSION IF NOT EXISTS pgaudit;

-- Configure audit logging
ALTER SYSTEM SET pgaudit.log = 'read, write, ddl';
ALTER SYSTEM SET pgaudit.log_catalog = 'off';
ALTER SYSTEM SET pgaudit.log_relation = 'on';

-- Object-level auditing
ALTER ROLE auditor SET pgaudit.role = 'auditor';
GRANT SELECT ON hr.employees TO auditor;
-- Now all SELECT on hr.employees is audited