部门与用户TCode权限查询：如何捕获未关联角色的权限？

Great question! This is a common gap when only relying on AGR_TCODES, AGR_USERS, and USER_ADDR, since SAP lets users get TCode access through direct authorization assignments (not just roles). Let’s break down exactly how to track these cases, using your example of users with VA03 access that isn’t tied to any role.

Key Tables to Add to Your Query #

The three tables you already use cover role-based access, but you’ll need to include these to capture direct permissions:

• USR12: Stores direct authorization assignments for individual users. This is where you’ll find S_TCODE (the authorization object that controls TCode access) assignments that aren’t linked to any role.
• USR02: Contains basic user details (like full name) that you can join with USR12 to enrich your results.

Step-by-Step Solution #

1. Identify Users with Direct TCode Access #

Query USR12 to find users who have the S_TCODE object assigned directly, with your target TCode (e.g., VA03) in the LOW field (this holds the authorized TCode value).

2. Exclude Users Who Get Access via Roles #

To isolate only users with direct-only access, exclude anyone who appears in your existing role-based query (joining AGR_USERS and AGR_TCODES).

3. Enrich with Department Data #

Join the results with USER_ADDR (just like you do for role-based users) to get department information for these direct-access users.

Example Query #

Here’s a sample ABAP Native SQL or CDS query that combines all these elements:

SELECT 
  u12.BNAME AS USER_ID,
  u02.NAME_TEXT AS FULL_NAME,
  ua.DEPARTMENT,
  u12.LOW AS AUTHORIZED_TCODE
FROM USR12 u12
INNER JOIN USR02 u02 
  ON u12.BNAME = u02.BNAME
LEFT JOIN USER_ADDR ua 
  ON u12.BNAME = ua.BNAME
WHERE 
  u12.AUTH = 'S_TCODE' -- Target the TCode authorization object
  AND u12.LOW = 'VA03' -- Replace with your TCode(s)
  -- Exclude users who have this TCode via any role
  AND u12.BNAME NOT IN (
    SELECT DISTINCT au.BNAME
    FROM AGR_USERS au
    INNER JOIN AGR_TCODES at 
      ON au.AGR_NAME = at.AGR_NAME
    WHERE at.TCODE = 'VA03'
  );

Edge Cases to Account For #

Don’t forget these scenarios that might also grant TCode access without a direct role link:

• Wildcard Authorizations: If a user has S_TCODE with a wildcard (e.g., VA*), they can access VA03 even if it’s not explicitly listed. Adjust your query to use pattern matching (like LIKE 'VA%') instead of exact equality.
• Parameter Profiles: Some TCodes are accessible via parameter profiles assigned to the user in USR02-PROFN. To check this, join USR02 with TPARA (parameter values) and TSTC (TCode details) to see if the profile implicitly grants access.
• Derived Roles: While derived roles are linked to parent roles, double-check that their TCode assignments are captured in AGR_TCODES (they should be, but it’s worth verifying).

Final Notes #

By adding USR12 and USR02 to your existing logic, you’ll cover all cases where a user has TCode access not tied to any role. Test your query with a mix of direct and role-based users to confirm it’s capturing the right data.

内容的提问来源于stack exchange，提问作者ekekakos