For partners that build or integrate commercially available applications and service solutions with the Oracle Cloud Platform
For partners that provide implementation or managed services around Oracle Cloud Applications
On Manager data access for user page
what sql or tables do i use to get USERS WITHOUT Data Access
I found table for users with data access. FUN_USER_ROLE_DATA_ASGNMNTS but need to find how to get the ones with users without data access
Comment
Angela, Do you need SQL query for it? I mean what is the ask here?
Be the first to rate this
|
Sign in to rate this
Use below query and build a report, it will give the list of users With/Without Data Access,
SELECT
T1.USERNAME,
T1.ROLE_NAME,
T1.ROLE_COMMON_NAME,
T2.SECURITYCONTEXT,
T2.SECURITYCONTEXTVALUE,
T2.CREATION_DATE
FROM
(SELECT U.USERNAME, RV.ROLE_NAME, RV.ROLE_COMMON_NAME
FROM PER_USERS U, PER_USER_ROLES UR, PER_ROLES_DN_VL RV
WHERE U.USER_ID(+) = UR.USER_ID AND UR.ROLE_ID (+) = RV.ROLE_ID AND UR.ACTIVE_FLAG = 'Y'
) T1,
(SELECT 'DATA ACCESS SET' SECURITYCONTEXT, S.NAME SECURITYCONTEXTVALUE, R.ROLE_NAME, U.USERNAME, R.CREATION_DATE, R.CREATED_BY, R.LAST_UPDATE_DATE, R.LAST_UPDATED_BY
FROM FUSION.FUN_USER_ROLE_DATA_ASGNMNTS R, FUSION.GL_ACCESS_SETS S, FUSION.PER_USERS U
WHERE S.ACCESS_SET_ID = R.ACCESS_SET_ID AND U.USER_GUID = R.USER_GUID
) T2
WHERE T1.USERNAME (+) = T2.USERNAME AND T1.ROLE_COMMON_NAME (+) = T2.ROLE_NAME
Be the first to rate this
|
Sign in to rate this
Hi,
I found that sql did not return all data access set assign to user. This is one of the example: User Peter has 20 data access set have been assigned to his account but now the sql just return for two rows. Please advice how to include all. Thanks.
Be the first to rate this
|
Sign in to rate this
Hi, Try attached sql query,
Average Rating:



1 rating
|
Sign in to rate this
Hi Senthilrajan,
It is perfect. How to include some roles have been assigned to users but no need to assign data access set?
Thanks.
Be the first to rate this
|
Sign in to rate this
Hi
i have a question, how we can restrict the data access in the reports?
e.g user A has one BU access and another has different BU , how can we dynamically find out what access user has , is there any API available which can be used to initialize the session and then include the security or access which user has
regards
sunny
Be the first to rate this
|
Sign in to rate this