set echo OFF verify OFF feedback OFF;
set heading OFF
col vsid noprint
column vsid new_value vsid;
SELECT host_name
||'_'
||Upper(instance_name) vSID
FROM v$instance;
-------------------------------------------------
var vin VARCHAR2 (36);
undefine username
define vin="upper('&&USERNAME')"
-------------------------------------------------
clear COLUMNS;
ttitle OFF;
set feedback OFF;
set verify OFF;
set heading OFF;
set linesize 150;
set pagesize 600;
-- SET STANDARD COLUMN SIZES HERE --
col username format a16 heading "Username";
col granted_role heading "Granted Role";
clear breaks;
clear computes;
clear COLUMNS;
ttitle OFF;
set feedback OFF;
set verify OFF;
set heading OFF;
set linesize 300;
set pagesize 600;
-- SET STANDARD COLUMN SIZES HERE --
-- ------------------------------------------------- --
col owner format a20 heading "Owner";
col tablespace_name format a16 heading "Tablespace";
col file_id format 999 heading "File|ID";
col file_name format a70 heading "Datafile";
col member format a70 heading "Datafile";
col name format a25 heading "Name";
col object_name format a31 heading "Object";
col table_name format a30 heading "Table";
col index_name format a15 heading 'Index';
col file# format 999999 heading "File#";
col status heading "Status";
col segment_type format a10 heading "Type";
col segment_name format a20 heading "Segment";
col largest format 99999.9 justify c heading "Largest|Frag(Mb)";
col fragment justify c heading "Free|Fragment";
col blocks heading "Blocks";
col mbytes format 9999999 justify c heading "Size|(Mb)";
col kbytes format 9999999 justify c heading "Size|(Kb)";
col extents format 999999 heading "Extents";
col initial_extent format 99999999999 justify c heading "Init|Ext";
col initial_extent_size format 999999 justify c heading 'Init Ext|(Kb) '
col next_extent format 99999999999 justify c heading "Next|Ext";
col next_extent_size format 999999 justify c heading 'Next Ext|(Kb) '
col min_extents format 999 justify c heading 'Min|Extent'
col min_extlen justify c heading " Min|Extlen";
col max_extents format 999999 justify c heading "Max|Exts";
col pct_increase heading "% Inc";
col pct_free format 9999 justify c heading "Pct|Free";
col pct_used format 9999 justify c heading "Pct|Used";
-- ------------------------------------------------- --
spool $home/local/log/&&vsid._privschk_&&username..log
--- chkDB.sql
-- select 'Script: #chkAll.sql ' from dual;
SELECT To_char(SYSDATE, 'mm-dd-yyyy HH:MI:SS')
||' --- &&vSID started ---'
FROM v$instance v,
dual d;
prompt "checking (&&vIN)"
set heading ON;
--- Procedure ---
set heading OFF
SELECT 'chk_&&vSID: --- User Info --- '
FROM sys.dual;
set heading ON;
col account_name FOR a15
col account FOR a15
col role_assignment FOR a20
col user_status FOR a15
col role_owner FOR a15
SELECT account_name,
CASE
WHEN account_type = 'USER' THEN 'User'
WHEN account_type = 'ROLE' THEN 'Role'
ELSE 'Unknown'
END AS account_type,
CASE
WHEN account_type = 'USER' THEN (SELECT account_status
FROM dba_users
WHERE username = account_name)
ELSE 'N/A'
END AS user_status,
CASE
WHEN account_type = 'USER' THEN (SELECT oracle_maintained
FROM dba_users
WHERE username = account_name)
ELSE 'N/A'
END AS oracle_maintained_user,
CASE
WHEN account_type = 'ROLE' THEN (SELECT oracle_maintained
FROM dba_roles
WHERE ROLE = account_name)
ELSE 'N/A'
END AS oracle_maintained_role,
CASE
WHEN account_type = 'ROLE' THEN (SELECT Count(*)
FROM dba_role_privs
WHERE granted_role = account_name)
|| ' user(s) assigned'
ELSE 'N/A'
END AS role_assignment,
CASE
WHEN account_type = 'ROLE' THEN (SELECT grantee
FROM dba_role_privs
WHERE granted_role = account_name
AND ROWNUM = 1)
ELSE 'N/A'
END AS role_owner
FROM (SELECT username AS account_name,
'USER' AS account_type
FROM dba_users
UNION ALL
SELECT ROLE AS account_name,
'ROLE' AS account_type
FROM dba_roles)
WHERE account_name IN ( &&vin );
col PROFILE format a10;
col username format a13;
col account_status format a16;
col default_tablespace format a10;
col last_changed format a20;
col next_change format a12;
col last_pw justify c heading 'Last Password Change' FOR a20;
col last_logged justify c heading 'Last|Logged|In';
col pw_chng justify c heading 'Reminder|Password|Change';
col password format a18;
col consumer_group FOR a22;
SELECT u.username,
p.PROFILE,
u.account_status,
To_char(su.ptime, 'yyyy-mm-dd') last_pw,
initial_rsrc_consumer_group CONSUMER_GROUP
FROM dba_users u,
(SELECT DISTINCT PROFILE
FROM dba_profiles) p,
sys.user$ su
WHERE p.PROFILE = u.PROFILE(+)
AND su.name = u.username
AND su.name IN ( &&vin )
ORDER BY 1,
2;
SELECT u.username,
To_char(u.created, 'mm-dd-yyyy HH:MI') Created,
To_char(u.expiry_date, 'mm-dd-yyyy HH:MI') expired,
To_char(u.lock_date, 'mm-dd-yyyy HH:MI') LOCKED,
u.password
FROM dba_users u,
sys.user$ su
WHERE su.name = u.username
AND su.name IN ( &&vin );
col sessionid format 999999999;
col STATEMENT format 99999 justify c heading "STMT";
col ntimestamp# format a30;
col userhost format a30;
col userid format a20;
col clientid format a30;
col terminal format a20;
col ses$actions format a10;
col sqltext format a30;
col comment$text format a40;
col vdate justify c heading "Last Logged in" format a20;
set heading OFF
SELECT 'chk_&&vSID: --- check last time logged in --- '
FROM sys.dual;
set heading ON;
set heading OFF;
SELECT 1 seqn,
'from date: '
||Min(To_char(ntimestamp#, 'mm-dd-yyyy HH24:MI'))
FROM m11515.sys_aud$_copy
UNION
SELECT 2 seqn,
' to date: '
||Max(To_char(ntimestamp#, 'mm-dd-yyyy HH24:MI'))
FROM sys.aud$
ORDER BY 1;
set heading ON;
col username FOR a10;
col account_status FOR a16;
col userid FOR a10;
col userhost FOR a10;
SELECT ur.username,
Nvl(Max(vdate), 'n/a') AS vdate,
ur.account_status,
userid,
userhost,
-- Adding last_login from dba_users
To_char(u.last_login, 'mm-dd-yyyy HH:MI') last_login19c
FROM (SELECT u.username,
Max(To_char(ntimestamp#, 'mm-dd-yyyy HH24:MI')) AS vdate,
account_status,
userid,
userhost
FROM sys.aud$,
dba_users u
WHERE u.username = userid(+)
GROUP BY u.username,
u.account_status,
userid,
userhost
UNION
SELECT u.username,
Max(To_char(ntimestamp#, 'mm-dd-yyyy HH24:MI')) AS vdate,
account_status,
userid,
userhost
FROM m11515.sys_aud$_copy,
dba_users u
WHERE u.username = userid(+)
GROUP BY u.username,
u.account_status,
userid,
userhost) ur
left join dba_users u
ON ur.username = u.username
-- Ensuring left join to fetch last_login safely
WHERE ur.username IN ( &&vin )
GROUP BY ur.username,
ur.account_status,
userid,
userhost,
u.last_login
ORDER BY ur.username;
set lines 200
set heading OFF
SELECT 'chk_&&vSID: --- object size --- '
FROM sys.dual;
col vline format a100;
SELECT t.vowner
||' : '
|| t.gsize
||' gig' vline
FROM (SELECT u.username vowner,
Nvl(Round(SUM (s.bytes / ( 1024 * 1024 * 1024 )), 2), 0) gsize
FROM dba_segments s,
dba_users u
WHERE u.username IN ( &&vin )
AND u.username = s.owner(+)
GROUP BY u.username) t
GROUP BY t.vowner,
t.gsize
ORDER BY 1;
set heading ON;
--compute sum label 'Total Gig' of 'gsize' by owner on report;
compute SUM label 'Total Meg' OF 'msize' BY owner ON report;
SELECT seg.owner,
seg.msize,
seg.tablespace_name,
df.file_name,
df.mbytes,
df.maxm,
df.ext,
df.enabled
FROM (SELECT owner,
Round(SUM (bytes / ( 1024 * 1024 )), 2) msize,
tablespace_name
FROM dba_segments
WHERE owner IN ( &&vin )
GROUP BY owner,
tablespace_name) seg,
(SELECT d.tablespace_name,
d.file_id,
'"'
|| d.file_name
||'"' file_name,
d.bytes / 1024 / 1024 mbytes,
maxbytes / 1048576 maxM,
autoextensible EXT,
enabled
FROM dba_data_files d,
v$datafile v
WHERE d.file_id = v.file#
UNION
SELECT d.tablespace_name,
d.file_id,
'"'
|| d.file_name
||'"' file_name,
d.bytes / 1024 / 1024 mbytes,
maxbytes / 1048576 maxM,
autoextensible EXT,
enabled
FROM dba_temp_files d,
v$datafile v
WHERE d.file_id = v.file#
ORDER BY tablespace_name,
file_id ASC) df
WHERE seg.tablespace_name = df.tablespace_name
ORDER BY 1;
set heading OFF
SELECT 'chk_&&vSID: --- Tablespace Quota --- '
FROM sys.dual;
set heading ON;
SELECT q.username,
q.tablespace_name "Tablespace",
q.bytes / 1048576 mbytes,
q.max_bytes,
t.CONTENTS,
t.status
FROM dba_ts_quotas q,
dba_tablespaces t
WHERE q.username IN ( &&vin )
AND q.tablespace_name = t.tablespace_name
AND q.dropped = 'NO'
ORDER BY 1,
2;
set heading OFF
SELECT 'chk_&&vSID: --- Objects --- '
FROM sys.dual;
set heading ON;
SELECT u.username owner,
Count(o.object_name) "Count",
o.object_type "Type"
FROM dba_objects o,
dba_users u
WHERE u.username = o.owner(+)
AND u.username IN ( &&vin )
GROUP BY u.username,
o.object_type
ORDER BY u.username,
o.object_type;
set heading OFF
SELECT 'chk_&&vSID: --- System privileges --- '
FROM sys.dual;
set heading ON;
col username FOR a15
col granted_role FOR a20
col privilege FOR a30
break ON username nodup ON granted_role nodup ON report skip 2;
col grantee FOR a25;
SELECT *
FROM dba_sys_privs
WHERE grantee IN ( &&vin );
SELECT 'chk_&&vSID: --- User privileges --- '
FROM sys.dual;
set heading ON;
col username FOR a15
col granted_role FOR a20
col privilege FOR a30
break ON username nodup ON granted_role nodup ON report skip 2;
SELECT 1 seqn,
du.username,
'n/a' GRANTED_ROLE,
dsp.privilege "Privilege",
dsp.admin_option
FROM dba_users du,
dba_sys_privs dsp
WHERE du.username = dsp.grantee(+)
AND username IN ( &&vin )
UNION
SELECT 2 seqn,
du.username,
drp.granted_role,
dsp.privilege,
dsp.admin_option
FROM dba_users du,
dba_role_privs drp,
dba_sys_privs dsp
WHERE du.username = drp.grantee(+)
AND drp.granted_role = dsp.grantee(+)
AND username IN ( &&vin )
ORDER BY username,
2,
seqn ASC,
3;
set heading OFF;
SELECT 'chk_&&vSID: --- Object privileges --- '
FROM sys.dual;
set heading ON;
col grantee format a20;
col privilege format a15;
col object FOR a30
col vobjprivs format a30 heading "Object Privileges";
SELECT Nvl(grantable, ' ') ADMIN,
'grantee' TYPE,
grantee,
privilege,
owner
||'.'
||table_name object
FROM dba_tab_privs
WHERE grantee IN ( &&vin )
AND table_name NOT LIKE 'BIN$%'
GROUP BY grantee,
owner,
table_name,
privilege,
grantable
ORDER BY grantee,
owner,
table_name,
privilege,
grantable;
col vobjprivs format a80 heading "Granted Privileges";
SELECT Nvl(grantable, ' ') ADMIN,
'owner ' TYPE,
grantee,
privilege,
owner
||'.'
||table_name object
FROM dba_tab_privs
WHERE owner IN ( &&vin )
AND table_name NOT LIKE 'BIN$%'
GROUP BY grantee,
owner,
table_name,
privilege,
grantable
ORDER BY grantee,
owner,
table_name,
privilege,
grantable;
-- end ---
set heading OFF;
SELECT To_char(SYSDATE, 'mm-dd-yyyy HH:MI:SS')
||' --- &&vSID ended ---'
FROM v$instance v,
dual d;
set verify OFF;
set feedback OFF;
spool OFF
prompt
prompt "File output: $HOME/local/log/&&vSID._privschk_&&USERNAME..log"
prompt
No comments:
Post a Comment