Data Access Governance — Requirements & Solution Design
This document defines the requirements, user stories, functional and non-functional specifications, and acceptance criteria for the central Data Access Governance repository. The solution consolidates user access information across core systems, stores screen-level data classifications, and provides searchable visibility for governance, audit, and compliance users.
1. Executive Summary
The project will create a central Data Access Governance repository to consolidate user access information across core systems, store screen-level data classifications, and provide searchable visibility for governance, audit, and compliance users. The solution will be built using Oracle Database and Oracle Forms (ODS).
- Initial access data will be loaded once from each source system using agreed Excel / extract templates.
- Daily ETL jobs will refresh users, accounts, roles, privileges, and mapping data from source systems.
- Data Governance will provide one-time classification spreadsheets per system containing screen name and classification.
- After the initial load, classification records will be added or updated directly in the Oracle Forms maintenance screen.
- The search screen will allow users to find who has access to classified screens by employee, AD user, system, role, or privilege.
2. Objectives
3. Scope
- Oracle Database repository tables, lookups, audit columns, and import tracking
- Oracle Forms screens for access search and classification maintenance
- One-time initial import of users / privileges and classification spreadsheets
- Daily ETL sync for source-system access data
- Search, export, and audit support for classified access
- Automated access provisioning or de-provisioning back into source systems
- Approval workflow for access requests
- Real-time API-based synchronization in phase 1
- Classification scanning directly inside source systems
- Advanced analytics dashboards in phase 1
4. Operating Model
| PHASE | DESCRIPTION |
|---|---|
| Initial load | Source teams submit one-time access extracts; Data Governance submits one-time classification spreadsheets. |
| Daily operation | ETL refreshes access data daily; classification updates are made through Oracle Forms when governance decisions change. |
| Ownership | Source system teams own access data quality; Data Governance owns classification quality; IT owns repository / ETL / support. |
| Audit trail | All classification changes and load batches are stored with timestamps and user/process details. |
5. Functional Requirements
| ID | AREA | REQUIREMENT |
|---|---|---|
| FR-01 | System master | The application shall maintain a master list of in-scope systems and their status. |
| FR-02 | Initial access import | The solution shall load users, accounts, roles, privileges, and mappings from each source system through a controlled initial import. |
| FR-03 | Daily ETL sync | The solution shall support scheduled daily ETL jobs to refresh source-system access data. |
| FR-04 | Classification import | The solution shall support one-time import of classification spreadsheets containing screen/object and classification level. |
| FR-05 | Access search | Users shall search by employee number, AD user, employee name, system, role/profile, or status. |
| FR-06 | Access detail | Search results shall show system account, role/privilege, screen/object, and current classification where available. |
| FR-07 | Classification maintenance | Authorized governance users shall add, update, activate, and deactivate screen classification records from Oracle Forms. |
| FR-08 | History and audit | The repository shall keep timestamps, user/process IDs, and batch references for loaded and maintained records. |
| FR-09 | Validation | The system shall validate mandatory fields, lookup values, and duplicate natural keys during file load and maintenance. |
| FR-10 | Export | Search results shall be exportable for audit or evidence use. |
| FR-11 | ETL monitoring | Authorized admins shall review ETL/import run status, counts, and reject summaries. |
| FR-12 | Security | Only authorized users shall access search and maintenance screens; classification maintenance shall be restricted further. |
6. Non-Functional Requirements
| ID | CATEGORY | REQUIREMENT |
|---|---|---|
| NFR-01 | Availability | Application available during business hours with planned ETL outside peak usage where possible. |
| NFR-02 | Performance | Standard search results should return within acceptable operational time for common filters. |
| NFR-03 | Security | Repository access controlled via authorized Oracle Forms / database roles and least privilege. |
| NFR-04 | Auditability | All loads and classification changes must be traceable to batch, user, and timestamp. |
| NFR-05 | Data quality | Mandatory validations, duplicate checks, and reject logging must be enforced. |
| NFR-06 | Scalability | Design should support onboarding additional systems without redesigning the data model. |
| NFR-07 | Maintainability | Lookups and screen classifications should be maintainable without code changes. |
7. User Stories
Story: As a Governance analyst, I want to search by employee or AD user so that I can identify all access held by a person across systems.
Outcome: Search returns all active accounts and related roles/privileges for the selected person.
Story: As a Auditor, I want to see which users can access Secret or Top Secret screens so that I can produce audit evidence.
Outcome: Results include classification and are exportable.
Story: As a Governance analyst, I want to maintain screen classifications so that the repository stays current after business review decisions.
Outcome: Only authorized users can create or update classification records.
Story: As a System admin, I want ETL to refresh access daily so that the repository reflects current source-system entitlements.
Outcome: Daily jobs update the repository and log counts / failures.
Story: As a ETL operator, I want rejected records logged so that I can correct source or mapping issues.
Outcome: Rejected rows are traceable by batch / job.
Story: As a Source-system team member, I want a standard export template so that I know exactly which fields to provide.
Outcome: Template includes mandatory tabs, examples, and field guidance.
Story: As a Governance analyst, I want privilege-to-screen mapping loaded so that classified access can be resolved accurately.
Outcome: Search results show classification only when mapping exists.
Story: As a Compliance user, I want historical traceability of classification updates so that I can understand when a screen classification changed.
Outcome: Classification rows include effective dates and audit metadata.
Story: As a Application admin, I want access to an import / ETL status view so that I can monitor daily synchronization.
Outcome: Batch status, start/end times, and row counts are available.
Story: As a Project lead, I want the design to support onboarding more systems later so that the repository can expand without major redesign.
Outcome: System master and reusable tables support new systems.
8. Data Model Overview
The repository is organized around system, employee, account, role, privilege, screen, and classification entities. A user's classified access is derived by resolving account-to-role, role-to-privilege, and privilege-to-screen relationships, then applying the current screen classification.
| AREA | KEY TABLES |
|---|---|
| Master data | DAG_SYSTEM, DAG_EMPLOYEE, DAG_CLASSIFICATION_LEVEL |
| Access catalog | DAG_SYSTEM_ACCOUNT, DAG_ROLE, DAG_PRIVILEGE, DAG_SCREEN |
| Relationship tables | DAG_ACCOUNT_ROLE, DAG_ROLE_PRIVILEGE, DAG_ACCOUNT_PRIVILEGE, DAG_PRIVILEGE_SCREEN |
| Governance tables | DAG_SCREEN_CLASSIFICATION |
| Operational control | DAG_IMPORT_BATCH, DAG_ETL_RUN_LOG |
Important Design Note
If a source system cannot provide any privilege-to-screen (or equivalent object) mapping, the repository can still show users, roles, and privileges, but it cannot reliably conclude which classified screens are accessible for that system.
9. Acceptance Criteria
Initial load succeeds for agreed in-scope systems using the standard template.
Daily ETL refresh updates changed access records and logs job outcomes.
Classification spreadsheets are loaded and visible on search results where mappings exist.
Authorized users can add or update classifications through Oracle Forms.
Search supports employee, AD user, system, role/profile, and status filters.
Audit exports can be produced for classified access investigations.