-
Notifications
You must be signed in to change notification settings - Fork 3
/
odris_linux_6b_asd.sql
104 lines (104 loc) · 2.51 KB
/
odris_linux_6b_asd.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
PROMPT
PROMPT =======================================
PROMPT # 6.0 Access Source of Database (ASD) #
PROMPT =======================================
PROMPT
PROMPT =============================
PROMPT # 6.1 Access by App Servers #
PROMPT =============================
PROMPT
--COLUMN newline NEW_VALUE newline NOPRINT
--SELECT chr(10) newline FROM dual;
--PROMPT [01] Via calling bash shell script *mining_listener_logxml_review_appserver_ip_2.sh*&newline-
-- to review the IP Address of App server.
SET SERVEROUTPUT ON FORMAT WRAPPED
BEGIN
DBMS_OUTPUT.PUT_LINE('[01] Via calling bash shell script *mining_listener_logxml_review_appserver_ip_2.sh*');
DBMS_OUTPUT.PUT_LINE(' to review the IP Address of App server.');
END;
/
HOST sh ~/odris_linux/script/mining_listener_logxml_review_appserver_ip_2.sh
--PROMPT [02] Another approach is creating trigger on SYS&newline-
-- and then checking the client_info column of v$session.
BEGIN
DBMS_OUTPUT.PUT_LINE('[02] Another approach is creating trigger on SYS');
DBMS_OUTPUT.PUT_LINE(' and then checking the client_info column of v$session.');
END;
/
CREATE OR REPLACE TRIGGER on_logon_trigger
AFTER LOGON ON DATABASE
BEGIN
DBMS_APPLICATION_INFO.SET_CLIENT_INFO(SYS_CONTEXT('userenv', 'ip_address'));
END;
/
COLUMN username FORMAT a25
COLUMN machine FORMAT a20
COLUMN client_info FORMAT a15
--COLUMN program FORMAT a25
--COLUMN status FORMAT a8
SELECT username
, machine
, client_info
-- , program
-- , status
, count(*)
FROM v$session
WHERE username IS NOT NULL
--AND status = 'ACTIVE'
--AND program = 'JDBC Thin Client'
GROUP BY username
, machine
, client_info
-- , program
-- , status
ORDER BY count(*) DESC
-- , program
-- , status
/
PROMPT
PROMPT ================================
PROMPT # 6.2 Access by Business Users #
PROMPT ================================
--PROMPT
COLUMN username FORMAT a25
COLUMN account_status FORMAT a14
SELECT username
, account_status
FROM dba_users
WHERE username NOT IN
(
'ANONYMOUS'
, 'APEX_030200'
, 'APEX_PUBLIC_USER'
, 'APPQOSSYS'
, 'CTXSYS'
, 'DBSNMP'
, 'DIP'
, 'EXFSYS'
, 'FLOWS_FILES'
, 'MDDATA'
, 'MDSYS'
, 'MGMT_VIEW'
, 'OLAPSYS'
, 'ORACLE_OCM'
, 'ORDDATA'
, 'ORDPLUGINS'
, 'ORDSYS'
, 'OUTLN'
, 'OWBSYS'
, 'OWBSYS_AUDIT'
, 'SCOTT'
, 'SI_INFORMTN_SCHEMA'
, 'SPATIAL_CSW_ADMIN_USR'
, 'SPATIAL_WFS_ADMIN_USR'
, 'SQLTXADMIN'
, 'SQLTXPLAIN'
, 'SYS'
, 'SYSMAN'
, 'SYSTEM'
, 'WMSYS'
, 'XDB'
, 'XS$NULL'
)
ORDER BY 1
/