SAPFree generatorPDF, Excel, Word exportSAP on SAP HANA

SAP SQL Query Generator: Who Has This Authorization Object?

'Who can run SE16?' and 'who has this authorization value?' are the most common ad hoc questions in an SAP access review. This generator writes the SQL against the role tables so you can answer them from the database or a replicated copy.

Fill in

ABAP schema name

The database schema that holds the ABAP tables, often SAPHANADB or SAPABAP1

Client (MANDT)

Authorization object

Authorization field

Value to look for

Generated code

Download:

1. Users with the value through a directly assigned role

SELECT u.UNAME, u.AGR_NAME, a.OBJECT, a.FIELD, a.LOW, a.HIGH
FROM "SAPHANADB"."AGR_1251" a
JOIN "SAPHANADB"."AGR_USERS" u
  ON u.MANDT = a.MANDT AND u.AGR_NAME = a.AGR_NAME
WHERE a.MANDT = '100'
  AND a.OBJECT = 'S_TCODE'
  AND a.FIELD = 'TCD'
  AND a.DELETED = ' '
  AND ( a.LOW = 'SE16' OR a.LOW = '*' )
  AND u.TO_DAT >= TO_VARCHAR(CURRENT_DATE, 'YYYYMMDD')
ORDER BY u.UNAME, u.AGR_NAME;

2. Users with the value through a composite role

SELECT u.UNAME, u.AGR_NAME AS COMPOSITE_ROLE, c.CHILD_AGR AS SINGLE_ROLE, a.LOW, a.HIGH
FROM "SAPHANADB"."AGR_1251" a
JOIN "SAPHANADB"."AGR_AGRS" c
  ON c.MANDT = a.MANDT AND c.CHILD_AGR = a.AGR_NAME
JOIN "SAPHANADB"."AGR_USERS" u
  ON u.MANDT = c.MANDT AND u.AGR_NAME = c.AGR_NAME
WHERE a.MANDT = '100'
  AND a.OBJECT = 'S_TCODE'
  AND a.FIELD = 'TCD'
  AND a.DELETED = ' '
  AND ( a.LOW = 'SE16' OR a.LOW = '*' )
  AND u.TO_DAT >= TO_VARCHAR(CURRENT_DATE, 'YYYYMMDD')
ORDER BY u.UNAME, u.AGR_NAME;

3. Add lock status from USR02

SELECT x.UNAME, x.AGR_NAME, s.USTYP, s.UFLAG, s.TRDAT
FROM (
  SELECT u.MANDT, u.UNAME, u.AGR_NAME
  FROM "SAPHANADB"."AGR_1251" a
  JOIN "SAPHANADB"."AGR_USERS" u
    ON u.MANDT = a.MANDT AND u.AGR_NAME = a.AGR_NAME
  WHERE a.MANDT = '100'
    AND a.OBJECT = 'S_TCODE'
    AND a.FIELD = 'TCD'
    AND a.DELETED = ' '
    AND ( a.LOW = 'SE16' OR a.LOW = '*' )
) x
JOIN "SAPHANADB"."USR02" s
  ON s.MANDT = x.MANDT AND s.BNAME = x.UNAME
ORDER BY x.UNAME;

This is a first pass analysis of role content only. It does not expand ranges in the HIGH column, wildcard patterns such as 'SE*', additional field combinations on the same object, profiles assigned directly (table UST04), reference users, or the effect of organizational level fields. Treat a hit as a lead for a full SUIM or GRC analysis. Run read only queries on a copy or with approval, since these tables are large in big landscapes.

Role analysis on your own data

Optional: ask how we could run access analysis privately across your SAP systems.

Optional. The tool and every export work without this. No spam.

How to use the queries

Enter your ABAP schema, client, authorization object, field and the value you are looking for. Run the first query for users who have the role directly, the second for composite roles, and the third to add the user type and lock status from USR02. Export the result and attach it to your review. In SAP itself, SUIM report 'Users by Complex Selection Criteria' gives a similar answer; SQL is useful when you want to join with other data or analyse offline.

Use the schema name that holds ABAP tables in your database
Run per client
Join the result with your HR or identity data after export

The tables behind the query

AGR_1251 stores the authorization values of a role, with the object, field, low and high value and a deleted flag. AGR_USERS stores the assignment of roles to users with validity dates. AGR_AGRS links composite roles to their single roles. USR02 holds the user master data including user type and lock flag. Joining these four answers most role based access questions; direct profile assignments live in UST04 and are not covered here.

What the query cannot see

Authorization checks are evaluated with several fields at once, so a value in one field of an object does not by itself grant the capability. A role might hold S_TCODE with SE16 but lack the S_TABU_DIS authorization needed to display data. Wildcards and ranges also matter. Use these results to shortlist, then confirm with a trace or a GRC rule set before concluding that a user can perform an action.

Check wildcard values such as SE* separately
Check ranges in the HIGH column
Check profile assignments in UST04 and reference users

Frequently Asked Questions

Which table links users to roles in SAP?

AGR_USERS holds the role to user assignment with validity dates. Composite roles are linked to single roles through AGR_AGRS.

Does the query work on non HANA databases?

The table names are the same on any database, but the date function and schema quoting differ. Adjust the CURRENT_DATE expression and the schema prefix for your database.

Why does a user appear who cannot actually run the transaction?

The query only checks one field value. The real check combines several objects and fields, so an authorization trace or a full GRC analysis is needed to confirm actual capability.

How do I include users with directly assigned profiles?

Directly assigned profiles are stored in UST04. You would need to resolve profile contents with the profile tables, which is more involved. Most organizations require roles, so first check whether any user has direct profiles.

If you need access analysis across many SAP systems without sending role data to a cloud service, we can build it on your own infrastructure.

Talk to an Expert Explore More Free Tools

Related Tools

SAP

More SAP tools

See all

Back to All Tools