Does the Segregation of Duties Report not give you what you’re looking for?

Screenshot of the location for it in Apps10 below:

HTH,

Nick

​@NickPorter than you but no it does not. I need to know what permission set that has the conflicts.

Can a report be created that shows the permission sets?

Thanks,

Is there a table that stores which database objects are granted to a Role/permission set?

I don’t believe there is one for Apps10 with regards to DB objects.  My understanding is that permissions are granted directly on the DB objects (views, methods) in real time when saved/applied, rather than having a list somewhere of who can see what. 

FND_ROLE_CONTENT which is a View does contain Permission Sets and their Presentation Objects that are granted (i.e. forms), but nothing more granular to the DB level.

You would likely have to scan for permissions sets that have the conflicting methods granted to them, then scan for users that have those permission sets granted from the FND_USER_TAB

Is there a table that stores which database objects are granted to a Role/permission set?

 

 

In Apps 10

You can see Database Packages being Granted in security_sys_privs_tab

You can see Specific Package Methods that are REVOKED from that grant in security_sys_tab

(The way it works is that when you grant a package, it grants the full package, but then it specifically revokes particular methods within it, it does not store which methods are particularly GRANTED, it considers all methods within a package to be granted EXCEPT the ones that are unticked, as being revoked)

Additionally, the following CURSOR is what’s used in security_sys.is_method_available to determine whether a particular method is available to a user, which if it returns nothing means it’s not available.

Requires parsing the package_ , method_ and fnd_user_, but it’ll basically look through any granted package for a given role, but remove (MINUS) it if the particular method is revoked.

      SELECT grantee
      FROM   security_sys_privs_tab
      WHERE  table_name = package_
      AND    grantee IN (SELECT role
                         FROM   fnd_user_role_runtime_tab
                         WHERE  identity = fnd_user_)

      MINUS
      SELECT role
      FROM   security_sys_tab
      WHERE  package_name = package_
      AND    method_name  = method_
      AND    role IN (SELECT role
                      FROM   fnd_user_role_runtime_tab
                      WHERE  identity = fnd_user_);

This basically allows you to check at a glance if any user has access to any particular method within a package  (and which role(s), including functional roles, are granting said access to them)