Expanded user details, including system privileges and tablespace quotas
[= allowed ] User : DEVCONN
Exact=N DEVCONN
User Details : Search for all like 'DEVCONN'
User Name Account Status Def Table Space Temp T Space Created Last Login Profile
--------- -------------- --------------- ------------ ------- ---------- -------
DEVCONN OPEN OQ06_DS001 TEMP 13/11/2012 15:18 05/10/2015 09:41 IMDD_ORACLE_SYS_PROFILE
Profile: IMDD_ORACLE_SYS_PROFILE
--------------------------------
COMPOSITE_LIMIT KERNEL UNLIMITED
CONNECT_TIME KERNEL UNLIMITED
CPU_PER_CALL KERNEL UNLIMITED
CPU_PER_SESSION KERNEL UNLIMITED
FAILED_LOGIN_ATTEMPT PASSWORD UNLIMITED
IDLE_TIME KERNEL UNLIMITED
LOGICAL_READS_PER_CA KERNEL UNLIMITED
LOGICAL_READS_PER_SE KERNEL UNLIMITED
PASSWORD_GRACE_TIME PASSWORD 0
PASSWORD_LIFE_TIME PASSWORD UNLIMITED
PASSWORD_LOCK_TIME PASSWORD UNLIMITED
PASSWORD_REUSE_MAX PASSWORD UNLIMITED
PASSWORD_REUSE_TIME PASSWORD UNLIMITED
PASSWORD_VERIFY_FUNC PASSWORD NULL
PRIVATE_SGA KERNEL UNLIMITED
SESSIONS_PER_USER KERNEL UNLIMITED
System privileges
DEVCONN ALTER SYSTEM (no admin)
ALTER TABLESPACE (no admin)
ALTER USER (no admin)
ANALYZE ANY (no admin)
CREATE ANY SEQUENCE (no admin)
CREATE ANY TABLE (no admin)
CREATE ANY TRIGGER (no admin)
CREATE DATABASE LINK (no admin)
CREATE PUBLIC SYNONYM (no admin)
CREATE USER (no admin)
DELETE ANY TABLE (no admin)
DROP ANY TABLE (no admin)
DROP PUBLIC SYNONYM (no admin)
DROP USER (no admin)
GRANT ANY OBJECT PRIVILEGE (no admin)
GRANT ANY PRIVILEGE (no admin)
INSERT ANY TABLE (no admin)
SELECT ANY DICTIONARY (no admin)
SELECT ANY SEQUENCE (no admin)
SELECT ANY TABLE (no admin)
UNLIMITED TABLESPACE (no admin)
UPDATE ANY TABLE (no admin)
Roles
Grantee Role Admi Defa
------- ---- ---- ----
DEVCONN DBA NO YES
DEVCONN QUOTA_ROLE YES YES
DEVCONN TEST_ACCESS1_RL YES YES
DEVCONN TEST_ACCESS1_RL_ONE YES YES
DEVCONN TEST_ACCESS2_RL YES YES
DEVCONN TEST_ACCESS2_RL_ONE YES YES
DEVCONN TEST_RELATION_R5 YES YES
Tablespace quotas
Tablespace User Bytes Alloc Max Bytes Blocks Max Blocks
------- ---- ----------- ------------ ------ ----------
Tablespace User MB Bytes Max MB Blocks Max Blocks
------- ---- ----------- ------------ ------ ----------
PL/SQL procedure successfully completed.
prompt
spool udets.rpt
accept xs_username prompt '[= allowed ] User : ';
declare
ls_exact varchar2(1) := 'N';
ls_username varchar2( 30 ) := upper( '&xs_username' );
cursor csr_user_dets is
select *
from dba_users
where ( ( ls_exact <> 'Y'
and username like upper( '%&xs_username%' ) )
or
( ls_exact = 'Y'
and username = ls_username )
)
and username not in ( 'SYS', 'SYSTEM' )
order by username;
cursor c_profiles is
select unique profile
from dba_users
where ( ( ls_exact <> 'Y'
and username like upper( '%&xs_username%' ) )
or
( ls_exact = 'Y'
and username = ls_username )
)
and username not in ( 'SYS', 'SYSTEM' )
order by profile;
cursor csr_sys_privs( xs_username in varchar2 ) is
select *
from dba_sys_privs
where grantee = xs_username
order by privilege;
cursor c_dba_role_privs is
select *
from dba_role_privs
where ( ( ls_exact <> 'Y'
and grantee like upper( '%&xs_username%' ) )
or
( ls_exact = 'Y'
and grantee = ls_username )
)
order by grantee;
cursor c_ts_quotas is
select *
from dba_ts_quotas
where ( ( ls_exact <> 'Y'
and username like upper( '%&xs_username%' ) )
or
( ls_exact = 'Y'
and username = ls_username )
)
order by username, tablespace_name;
i_loop_counter integer;
s_sql_text varchar2( 200 );
cursor c_db_logon_info ( i_username in varchar2 ) is
select *
from db_logon_info
where username = i_username
and logon_time = ( select max( logon_time )
from db_logon_info
where username = i_username
);
lt_db_logon_info db_logon_info%rowtype;
cursor c_dba_profiles ( i_profile in varchar2 ) is select * from dba_profiles where profile = i_profile order by resource_name;
begin
if substr( '&xs_username', 1, 1) = '=' then
ls_exact := 'Y';
ls_username := substr( upper( '&xs_username' ), 2 );
else
ls_exact := 'N';
ls_username := upper( '&xs_username' );
end if;
dbms_output.put_line( 'Exact=' || ls_exact || ' ' || ls_username );
dbms_output.put_line( '' );
if ls_exact = 'Y' then
dbms_output.put_line( 'User Details : Search for exact match on ' || '''' || ls_username || '''' || chr(10) );
else
dbms_output.put_line( 'User Details : Search for all like ' || '''' || ls_username || '''' || chr(10) );
end if;
dbms_output.put_line( rpad( 'User Name', 20 ) || ' ' ||
rpad( 'Account Status', 20 ) || ' ' ||
rpad( 'Def Table Space', 15 ) || ' ' ||
rpad( 'Temp T Space', 15 ) || ' ' ||
'Created ' || ' ' ||
'Last Login ' || ' ' ||
rpad( 'Profile', 25 ) );
dbms_output.put_line( rpad( '---------', 20 ) || ' ' ||
rpad( '--------------', 20 ) || ' ' ||
rpad( '---------------', 15 ) || ' ' ||
rpad( '------------', 15 ) || ' ' ||
'------- ' || ' ' ||
'---------- ' || ' ' ||
rpad( '-------', 25 ) );
for i_csr_user_dets in csr_user_dets loop
open c_db_logon_info( i_csr_user_dets.username ); fetch c_db_logon_info into lt_db_logon_info; close c_db_logon_info;
dbms_output.put_line( rpad( i_csr_user_dets.username, 20 ) || ' ' ||
rpad( i_csr_user_dets.account_status, 20 ) || ' ' ||
rpad( i_csr_user_dets.default_tablespace, 15 ) || ' ' ||
rpad( i_csr_user_dets.temporary_tablespace, 15 ) || ' ' ||
to_char( i_csr_user_dets.created, 'dd/mm/yyyy hh24:mi' ) || ' ' ||
nvl( to_char( lt_db_logon_info.logon_time, 'dd/mm/yyyy hh24:mi' ), 'No logon record ' ) || ' ' ||
rpad( i_csr_user_dets.profile, 25 ) );
end loop;
for i in c_profiles loop
dbms_output.put_line( chr(10) || 'Profile: ' || i.profile );
dbms_output.put_line( rpad( '-', length( 'Profile: ' || i.profile ), '-' ) );
for j in c_dba_profiles( i.profile ) loop
dbms_output.put_line( rpad( j.resource_name, 20 ) || ' ' || rpad( j.resource_type, 15 ) || ' ' || j.limit );
end loop;
end loop;
dbms_output.put_line( chr(10) );
dbms_output.put_line( 'System privileges' );
for i_csr_user_dets in csr_user_dets loop
i_loop_counter := 0;
for i_csr_sys_privs in csr_sys_privs( i_csr_user_dets.username ) loop
if i_loop_counter = 0 then
s_sql_text := rpad( i_csr_user_dets.username, 14 ) || chr(9);
else
s_sql_text := chr(9) || chr(9);
end if;
if i_csr_sys_privs.admin_option = 'YES' then
s_sql_text := s_sql_text || i_csr_sys_privs.privilege || ' with admin';
else
s_sql_text := s_sql_text || i_csr_sys_privs.privilege || ' (no admin)';
end if;
dbms_output.put_line( s_sql_text );
i_loop_counter := i_loop_counter + 1 ;
end loop;
if i_loop_counter > 0 then
dbms_output.put_line( chr(9) );
end if;
end loop;
dbms_output.put_line( chr(9) );
dbms_output.put_line( 'Roles' );
dbms_output.put_line( rpad( 'Grantee', 30 ) || ' ' ||
rpad( 'Role', 30 ) || ' ' ||
rpad( 'Admin', 4 ) || ' ' ||
rpad( 'Default', 4 ) );
dbms_output.put_line( rpad( '-------', 30 ) || ' ' ||
rpad( '----', 30 ) || ' ' ||
rpad( '-----', 4 ) || ' ' ||
rpad( '-------', 4 ) );
for i_c_dba_role_privs in c_dba_role_privs loop
dbms_output.put_line( rpad( i_c_dba_role_privs.grantee, 30 ) || ' ' ||
rpad( i_c_dba_role_privs.granted_role, 30 ) || ' ' ||
rpad( i_c_dba_role_privs.admin_option, 4 ) || ' ' ||
rpad( i_c_dba_role_privs.default_role, 4 ) );
end loop;
dbms_output.put_line( chr(10) );
dbms_output.put_line( 'Tablespace quotas' || chr(10) );
dbms_output.put_line( rpad( 'Tablespace', 30 ) || ' ' ||
rpad( 'User', 30 ) || ' ' ||
lpad( 'Bytes Alloc', 14 ) || ' ' ||
lpad( 'Max Bytes', 16 ) || ' ' ||
lpad( 'Blocks', 12 ) || ' ' ||
lpad('Max Blocks', 12 ) );
dbms_output.put_line( rpad( '-------', 30 ) || ' ' ||
rpad( '----', 30 ) || ' ' ||
lpad( '-----------', 14 ) || ' ' ||
lpad( '------------', 16 ) || ' ' ||
lpad( '------', 12 ) || ' ' ||
lpad( '----------', 12 ) );
for i_c_ts_quotas in c_ts_quotas loop
dbms_output.put_line( rpad( i_c_ts_quotas.tablespace_name, 30 ) || ' ' ||
rpad( i_c_ts_quotas.username, 30 ) || ' ' ||
to_char( ( i_c_ts_quotas.bytes ), '99999,999,999' ) || ' ' ||
to_char( ( i_c_ts_quotas.max_bytes ), '9999999,999,999' ) || ' ' ||
to_char( ( i_c_ts_quotas.blocks ), '999,999,999' ) || ' ' ||
to_char( ( i_c_ts_quotas.max_blocks ), '999,999,999' ) );
end loop;
dbms_output.put_line( chr(10) );
dbms_output.put_line( rpad( 'Tablespace', 30 ) || ' ' ||
rpad( 'User', 30 ) || ' ' ||
lpad( 'MB Bytes', 14 ) || ' ' ||
lpad( 'Max MB', 16 ) || ' ' ||
lpad( 'Blocks', 12 ) || ' ' ||
lpad('Max Blocks', 12 ) );
dbms_output.put_line( rpad( '-------', 30 ) || ' ' ||
rpad( '----', 30 ) || ' ' ||
lpad( '-----------', 14 ) || ' ' ||
lpad( '------------', 16 ) || ' ' ||
lpad( '------', 12 ) || ' ' ||
lpad( '----------', 12 ) );
for i_c_ts_quotas in c_ts_quotas loop
dbms_output.put_line( rpad( i_c_ts_quotas.tablespace_name, 30 ) || ' ' ||
rpad( i_c_ts_quotas.username, 30 ) || ' ' ||
to_char( ( i_c_ts_quotas.bytes/(1024*1024) ), '99999,999,999' ) || ' ' ||
to_char( ( i_c_ts_quotas.max_bytes/(1024*1024) ), '9999999,999,999' ) || ' ' ||
to_char( ( i_c_ts_quotas.blocks ), '999,999,999' ) || ' ' ||
to_char( ( i_c_ts_quotas.max_blocks ), '999,999,999' ) );
end loop;
end;
/
spool off