Oracle role info for schema

schema role info
user role info

col pr head "Profile" for a8
col rn head "Resource" for a25
col rt head "Type" for a10
col li head "Value" for a10
break on pr skip

prompt
prompt Profile Details
prompt ===============

select p.profile pr,p.resource_name rn,p.resource_type rt,p.limit li from dba_users u,dba_profiles p
where u.profile=p.profile
and u.username='&&user';

col gr head "Grantor" for a8
col tn head "Object" for a20
col ow head "Owner" for a8
col pr head "Privilege" for a10

prompt
prompt Object Privileges
prompt =================

select t.grantor gr,t.table_name tn,t.owner ow,t.privilege pr from dba_tab_privs t
where t.grantee='&&user';

col cn head "Column" for a20

prompt
prompt Column Privileges
prompt =================
select c.grantor gr,c.column_name cn,c.table_name tn,c.owner ow,c.privilege pr from dba_col_privs c
where c.grantee='&&user';

col ad head "Adm" for a3
col pr head "Privilege" for a30

prompt
prompt System Privileges
prompt =================
select s.privilege pr,s.admin_option ad from dba_sys_privs s
where s.grantee='&&user';

col gr head "Granted Role" for a30
col dr head "Def" for a3
col ad head "Adm" for a3
prompt
prompt Role Privileges
prompt ===============
select r.granted_role gr,r.default_role dr,r.admin_option ad from dba_role_privs r
where r.grantee='&&user';

Profile Details
===============
Enter value for user: SCOTT
old   3: and u.username='&&user'
new   3: and u.username='SCOTT'

Profile  Resource                  Type       Value
-------- ------------------------- ---------- ----------
DEFAULT  COMPOSITE_LIMIT           KERNEL     UNLIMITED
         SESSIONS_PER_USER         KERNEL     UNLIMITED
         CPU_PER_SESSION           KERNEL     UNLIMITED
         CPU_PER_CALL              KERNEL     UNLIMITED
         LOGICAL_READS_PER_SESSION KERNEL     UNLIMITED
         LOGICAL_READS_PER_CALL    KERNEL     UNLIMITED
         IDLE_TIME                 KERNEL     UNLIMITED
         CONNECT_TIME              KERNEL     UNLIMITED
         PRIVATE_SGA               KERNEL     UNLIMITED
         FAILED_LOGIN_ATTEMPTS     PASSWORD   10
         PASSWORD_LIFE_TIME        PASSWORD   180
         PASSWORD_REUSE_TIME       PASSWORD   UNLIMITED
         PASSWORD_REUSE_MAX        PASSWORD   UNLIMITED
         PASSWORD_VERIFY_FUNCTION  PASSWORD   NULL
         PASSWORD_LOCK_TIME        PASSWORD   1
         PASSWORD_GRACE_TIME       PASSWORD   7

16 rows selected.


Object Privileges
=================
old   2: where t.grantee='&&user'
new   2: where t.grantee='SCOTT'

Grantor  Object               Owner    Privilege
-------- -------------------- -------- ----------
SYS      DBMS_REDEFINITION    SYS      EXECUTE


Column Privileges
=================
old   2: where c.grantee='&&user'
new   2: where c.grantee='SCOTT'

no rows selected


System Privileges
=================
old   2: where s.grantee='&&user'
new   2: where s.grantee='SCOTT'

Privilege                      Adm
------------------------------ ---
UNLIMITED TABLESPACE           NO


Role Privileges
===============
old   2: where r.grantee='&&user'
new   2: where r.grantee='SCOTT'

Granted Role                   Def Adm
------------------------------ --- ---
RESOURCE                       YES NO
DBA                            YES NO
CONNECT                        YES NO