Friday, December 26, 2014

Getting all users and roles that can access objects in a schema

To list all users and roles who can access objects in a schema, we can use following query. This will be useful when there is a need of migrating a schema from one database to another.

users_roles as(
        (select grantee, granted_role from dba_role_privs) union
        (select username, username from dba_users) union
        (select role, role from dba_roles)
direct_grantees as (
        select distinct grantee 
        from dba_tab_privs 
        where owner in ('SCHEMA_USER')
all_grantees as (
        select distinct grantee
        from users_roles
        start with 
                granted_role in (select * from direct_grantees)
        connect by nocycle prior grantee = granted_role
select grantee 
from all_grantees 
-- Filter as needed
-- For example, get only users not roles
where grantee in (select username from dba_users);

No comments:

Post a Comment