Useful SQL for SAP Authority Audit.
-------------------------------------
--Query USER,USERNAME,TCODE by specific tcodes and role name.
select a~bname
       a~name_text  
       y~tcode
from v_username as a
  inner join agr_users  as b on a~bname = b~uname
  inner join agr_define as x on b~agr_name = x~agr_name
  inner join agr_tcodes as y on x~agr_name = y~agr_name 
  inner join usr02 as e on a~bname = e~bname 
where a~mandt = '800'   
  and b~agr_name like '%4010%'
  and y~tcode in ('XK01', 'XK02', 'XK03', 'FK01', 'FK02', 'FK03')
  and e~uflag = 0
order by a~bname.

--Query USER,USERNAME,ROLE,TCODE by specific role name
select a~bname
       a~name_text  
       b~agr_name 
       y~tcode
from v_username as a
  inner join agr_users  as b on a~bname = b~uname
  inner join agr_define as x on b~agr_name = x~agr_name
  inner join agr_tcodes as y on x~agr_name = y~agr_name 
  inner join usr02 as e on a~bname = e~bname 
where a~mandt = '800'   
  and e~uflag = 0
  and b~agr_name like '%1000%'
order by a~bname.

--Complex query to fetch all users, roles, authority objects, and tcodes.
select a~bname a~name_text  
       b~agr_name 
       f~text y~tcode
       d~tcode d~ttext 
       tt~objct tt~field tt~value
from v_username as a
  inner join agr_users  as b  on a~bname = b~uname
  inner join agr_define as x  on b~agr_name = x~agr_name
  inner join agr_tcodes as y on x~agr_name  = y~agr_name 
  inner join tstct as d  on y~tcode = d~tcode
  inner join usr02 as e on a~bname = e~bname 
  inner join agr_texts as f on x~agr_name = f~agr_name 
  inner join tstca as tt on d~tcode = tt~tcode
where a~mandt = '800'   
  and e~uflag = 0  
  and f~spras = 'E'  
  and d~sprsl = 'E'   
order by a~bname.

--Related tables.
AGR_USERS 角色与用户对应关系
AGR_TEXTS 角色的中文描述
AGR_TCODES  查询角色所有的T-code，  报表类型 = TR
USER_ADDR 用户信息
AGR_1251 查询角色所有的T-code ，角色对象 = S_TCODE
USR02 查看哪些用户被锁定（UFLAG）