SQLite 3.27

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE ROLES (SID VARCHAR(255), SYSID VARCHAR(5), MANDT VARCHAR(3), UNAME VARCHAR(255), AGR_NAME VARCHAR(255)) 
CREATE TABLE USERS (SID VARCHAR(255), SYSID VARCHAR(5), MANDT VARCHAR(3), BNAME VARCHAR(255), GLTGV DATE, GLTGB DATE, USTYP VARCHAR(2), LOCNT VARCHAR(3), UFLAG VARCHAR(3), TRDAT DATE, LTIME VARCHAR(255), CLASS VARCHAR(255), PWDCHGDATE DATE, PROFILE VARCHAR(255))
INSERT INTO ROLES (Sid,SYSID,MANDT,UNAME,AGR_NAME) VALUES (
  '1',
  '1',
  '1',
  'user',
  'ZTEST_ROLE'
);
INSERT INTO ROLES (Sid,SYSID,MANDT,UNAME,AGR_NAME) VALUES (
  '2',
  '1',
  '1',
  'user',
  'ZTEST_ROLE2'
);
INSERT INTO USERS (Sid,SYSID,MANDT,BNAME) VALUES (
  '1',
  '1',
  '1',
  'user'
);
SELECT DISTINCT
    t1.SID, 
    t1.SYSID, 
    t1.MANDT, 
    t1.BNAME, 
    t1.GLTGV, 
    t1.GLTGB, 
    t1.USTYP, 
    t1.LOCNT, 
    t1.UFLAG, 
    t1.TRDAT, 
    t1.LTIME, 
    t1.CLASS, 
    t1.PWDCHGDATE, 
    t1.PROFILE

FROM 
    USERS AS t1, 
    ROLES AS t2 

WHERE 
    t1.SYSID = t2.SYSID AND 
    t1.MANDT = t2.MANDT AND 
    t1.BNAME = t2.UNAME AND
    (t2.AGR_NAME = "ZTEST_ROLE" and  (select AGR_NAME from ROLES  where uname = t1.bname and agr_name = "ZTEST_ROLE2") = "ZTEST_ROLE2")


SID
SYSID
MANDT
BNAME
GLTGV
GLTGB
USTYP
LOCNT
UFLAG
TRDAT
LTIME
CLASS
PWDCHGDATE
PROFILE


1
1
1
user
null
null
null
null
null
null
null
null
null
null