Oracle common sqls

common oracle sql

--List all users who have been assigned a particular role

select * from dba_role_privs where granted_role = 'DBA'


--List all roles given to a user

select * from dba_role_privs where grantee = 'PHIL';


--List all privileges given to a user

select
  lpad(' ', 2*level) || granted_role "User, his roles and privileges"
from
  (
  /* THE USERS */
    select 
      null     grantee, 
      username granted_role
    from 
      dba_users
    where
      username like upper('%&enter_username%')
  /* THE ROLES TO ROLES RELATIONS */ 
  union
    select 
      grantee,
      granted_role
    from
      dba_role_privs
  /* THE ROLES TO PRIVILEGE RELATIONS */ 
  union
    select
      grantee,
      privilege
    from
      dba_sys_privs
  )
start with grantee is null
connect by grantee = prior granted_role;



--List which tables a certain role gives SELECT access to?

select * from role_tab_privs where role='DBA' and privilege = 'SELECT';



--List all tables a user can SELECT from?

select * from dba_tab_privs where GRANTEE ='PHIL' and privilege = 'SELECT';


--List all tables in db:

SELECT DISTINCT OWNER, OBJECT_NAME 
  FROM DBA_OBJECTS
 WHERE OBJECT_TYPE = 'TABLE'
   AND OWNER = 'MSP' AND object_name like 'T_CUST_DOC%';