GL Data Access Sets – Oracle EBS SQL Report
Oracle E-Business Suite SQL report from the Enginatics Library powered by Blitz Report™. Oracle EBS module: General Ledger · all General Ledger reports
Overview
Master data report showing ledger security. Listing of all GL data access sets and the ledgers or ledger sets that each access set can access.
Show Responsibilities adds one row per responsibility using the access set through the GL: Data Access Set profile set at responsibility or application level. Responsibilities inheriting the site level value are not listed.
Summary Accounts Allowed shows Yes when the responsibility’s menu grants the Summary Accounts form and the access set gives full read and write access to the whole ledger. Otherwise the form’s Ledger list does not offer the ledger (FRM-41830 List of Values contains no entries).
Run this report in Excel in seconds: download Blitz Report™ free (your first 30 reports are free) or try it in the online demo.
Report Parameters
Access Set, Ledger, Access Set Type, Show Responsibilities, Summary Accounts Allowed
Oracle EBS Tables Used
gl_access_sets_v, gl_access_set_norm_assign, gl_ledger_set_norm_assign_v, gl_ledgers
Report Categories
Related Reports
FND Responsibility Access, FND Responsibilities, GL Setup Upload, GL Ledger Sets, Blitz Report Security, IBY Payment Process Request Details, GL Ledgers and Legal Entities, AP Supplier Upload
Running This SQL Without Blitz Report
The SQL source can be run in any SQL client. Some Oracle EBS SQL reports in this library use functions from the utility package xxen_util – install it first. Blitz Report™ adds parameters, Excel output with pivots, scheduling and security on top.
Download & Import Options
| Resource | Link |
|---|---|
| Excel Example Output | GL Data Access Sets 12-Apr-2019 022216.xlsx |
| Blitz Report™ XML Import | GL_Data_Access_Sets.xml |
| SQL Source | GL Data Access Sets.sql |
| Full Report on Enginatics | www.enginatics.com/reports/gl-data-access-sets/ |
GL Data Access Sets - Case Study & Technical Analysis
Executive Summary
The GL Data Access Sets report analyzes the security configuration of the General Ledger, specifically focusing on Data Access Sets. It details which ledgers and ledger sets are accessible to specific users or responsibilities, ensuring proper segregation of duties and data security. This report is a key component of system administration and internal audit workflows, providing visibility into “who can see what” within the financial system.
Business Use Cases
- Security Audit: Verifies that users only have access to the ledgers relevant to their role (e.g., ensuring US users access only US ledgers and cannot view or post to European ledgers).
- System Administration: Assists in troubleshooting “access denied” issues or empty reports by confirming the active Data Access Set configuration for a specific responsibility.
- Compliance Reporting: Documents data access controls for SOX (Sarbanes-Oxley) or internal audit requirements, proving that financial data is protected from unauthorized access.
- Implementation Verification: Validates that the complex hierarchy of Ledgers and Ledger Sets has been correctly mapped to Data Access Sets during system setup.
Technical Analysis
Core Tables
GL_ACCESS_SETS: Defines the header information for a data access set (Name, Security Code, Chart of Accounts ID).GL_ACCESS_SET_ASSIGNMENTS: Maps the Data Access Set to specific Ledgers or Ledger Sets.GL_LEDGERS: Stores details about the ledgers themselves.GL_LEDGER_SET_NORM_ASSIGN_V: A view that helps resolve the flattened list of ledgers contained within a Ledger Set.
Key Joins & Logic
- Access Mapping: The query joins
GL_ACCESS_SETStoGL_ACCESS_SET_ASSIGNMENTSto find the scope of access. - Ledger Resolution: It links to
GL_LEDGERSto retrieve the names of the accessible entities. - Hierarchy Flattening: A Data Access Set can grant access to a “Ledger Set” (a group of ledgers). The report logic often needs to expand this Ledger Set to list the individual underlying ledgers to provide a complete picture of access.
- Privilege Check: The report distinguishes between
READ_ONLYandREAD_WRITEprivileges, which is stored in the assignment table.
Key Parameters
- Access Set Name: The specific data access set to analyze.
- Ledger Name: Filter to see which access sets provide access to a specific ledger.
Useful Links
- Blitz Report™ – World’s Fastest Oracle EBS Reporting Tool
- Oracle Discoverer Replacement – Import Worksheets into Blitz Report™
- Oracle EBS Reporting Toolkits by Blitz Report™
- Blitz Report™ FAQ & Community Q&A
- Supply Chain Hub by Blitz Report™
- Blitz Report™ Customer Case Studies
- Oracle EBS Reporting Blog
- Oracle EBS Reporting Resource Centre
© Enginatics GmbH