|
"The
soul should always stand ajar. Ready to welcome the ecstatic
experience."
-Emily
Dickinson (1830-1886)
|
How do I keep track of the SSO (Single Sign On) users' transactions in Oracle Portal?
I use the ORASSO.WWSSO_AUDIT_LOG_TABLE_T table to keep track of my SSO transactions in the Oracle Portal. I made some queries and
use them most of the time to report any suspicious activities if the login failed
to trigger or any other activities on my application server..
The WWSSO_AUDIT_LOG_TABLE_T table is very useful and
is owned by the ORASSO user. It contains very important information about the Single Sign On transactions.
The following is a list of columns used by the ORASSO.WWSSO_AUDIT_LOG_TABLE_T table.
SQL> desc orasso.wwsso_audit_log_table_t
Name
Null
Type
--------------------------- --------
----------
SUBSCRIBER_ID
NOT NULL
NUMBER
LOG_ID
NOT NULL
NUMBER
USER_NAME
NOT NULL
VARCHAR2(256)
AUDIT_TYPE
NOT NULL
VARCHAR2(32)
ACTION_CODE
NOT NULL
NUMBER
ACTION
NOT NULL
VARCHAR2(80)
IP_ADDRESS
NOT NULL
VARCHAR2(32)
APP_SITE
NOT NULL
VARCHAR2(80)
MESSAGE
NOT NULL
VARCHAR2(256)
LOG_DATE
NOT NULL
DATE
PROCESS_DATE
DATE
EMAIL
VARCHAR2(80)
MAINTAINER_ID
VARCHAR2(80)
You may not use much of the SUBSCRIBER_ID, LOG_ID, and MAINTAINER_ID columns. The SUBSCRIBER_ID and LOG_ID are generated by ORASSO. The SUBSCRIBER_ID is a unique number and most of the time is only 1. The LOG_ID is a unique number which will be assigned
anytime a USER_NAME logs-in to the portal or application server.
The following are some of the most frequent queries that I use to retrieve data from the
orasso.wwsso_audit_log_table_t table.
Assuming that you are login to sql*plus:
SQL> connect orasso/password
Notice that you should have password to the ORASSO user or SYS.
Then, I set the following SQL*PLUS environment options.
SQL> set linesize 100
SQL> col user_name format a25
SQL> col ip_address format a15
SQL> col message format a25
SQL> col email format a20
SQL> set pagesize 1000
SQL> set arraysize 15
You need to set the pagesize high so that you won't get too many headings.
Also, set arraysize so that you can use the horizontal scroll bar.
The following query shows the first 100 records that list the users that have used different IP_ADDRESS or computer to access
the application server. This gives me a clue as to who is using different computers to access the application server using SSO.
Query:
BREAK ON user_name SKIP 1
SELECT USER_NAME, IP_ADDRESS, MESSAGE, EMAIL
FROM orasso.wwsso_audit_log_table_t
where rownum < 101
ORDER BY 1, 2
/
USER_NAME IP_ADDRESS MESSAGE
EMAIL
---------------- --------------- ------------------------- ------
PORTAL 186.33.206.77 pwd_exp_err
John@iselfschooling
PORTAL
Login Successful
John@iselfschooling
PORTAL
Login Successful
PORTAL 196.33.200.88 Login Successful
PORTAL
Login failed
PORTAL
Login Successful
PORTAL
Login Successful
Most of the time, my boss wants to know who accessed the system with the most
failed logins on a specific date sorted in descending order. We sort
these in descending order so that we can see the user that has the most failures in the login first.
Query:
SELECT user_name, COUNT(*) "Login Failed Since July"
FROM orasso.wwsso_audit_log_table_t
WHERE message LIKE '%failed%' AND
log_date >= TO_DATE('01-JUL-06')
GROUP BY user_name
ORDER BY 2 DESC
/
USER_NAME
Login Failed Since July
------------------------- -----------------------
USER1
120
USER2
45
USER3
17
To know who is working hard we can just reverse the query to a successful login
with a specific date.
Query:
SELECT user_name, COUNT(*) "Login Successfully Since July"
FROM orasso.wwsso_audit_log_table_t
WHERE message LIKE '%Successful%' AND
log_date >= TO_DATE('01-JUL-06')
GROUP BY user_name
ORDER BY 2 DESC
/
USER_NAME
Login Successfully Since July
------------------------- -----------------------------
PORTAL
689
ORCLADMIN
17
DIANE
12
ANNETTE
11
DIANA
7
.. rows selected.
Sometimes, we need to know when was the last time a user logged-in to
the application server. We don't care whether it was successful or not.
Query:
SELECT user_name, MAX(log_date) "Last login date"
FROM orasso.wwsso_audit_log_table_t
WHERE log_date > TO_DATE('01-JUL-06')
GROUP BY user_name
/
USER_NAME
Last logi
------------------------- ---------
USER1
08-AUG-06
USER2
15-SEP-06
USER3
11-AUG-06
To get statistics about daily access to our application server we use the following query.
Query:
SELECT TO_CHAR(log_date,'YYYY-MM-DD') "transaction date", COUNT(*) "Number of Login"
FROM orasso.wwsso_audit_log_table_t
GROUP BY TO_CHAR(log_date,'YYYY-MM-DD')
ORDER BY 1
/
transactio
Number of Login
---------- ---------------
2006-01-31
1034
2006-02-03
3453
2006-06-14
27650
2006-06-20
1234215
2006-06-29
564398
To get more detail on daily activities and how many of the logins of
the users failed or were successful, we use the following query.
Query:
BREAK ON "transaction date" skip 1
SELECT TO_CHAR(log_date,'YYYY-MM-DD') "transaction date", message, COUNT(*) "Number of Login"
FROM orasso.wwsso_audit_log_table_t
GROUP BY TO_CHAR(log_date,'YYYY-MM-DD'), message
ORDER BY 1, 2
/
transactio MESSAGE
Number of Login
---------- ------------------------- ---------------
2006-01-31 Login Successful
1
2006-02-03 Login Successful
2
Login failed
1
2006-06-14 Login Successful
1
pwd_exp_err
1
2006-06-26 Login Successful
10
Login failed
2
The following is a list of the users that logged-in with different IP addresses.
Query:
BREAK ON user_name skip 1
BREAK ON ip_address skip 1
SELECT user_name, ip_address, COUNT(*) "Number of login"
FROM orasso.wwsso_audit_log_table_t
GROUP BY user_name, ip_address
ORDER BY 1,3 DESC
/
USER_NAME IP_ADDRESS
Number of login
------------------------- --------------- ---------------
USER1
176.66.106.66
9
176.66.114.80
8
176.66.114.171
2
USER2
158.77.219.65
1
158.77.219.186
1
The following is a list that provides ip_addresses and user names that have so many
failed log-ins within IP_ADDRESS since a specific date.
Query:
BREAK ON user_name skip 1
BREAK ON ip_address skip 1
SELECT ip_address, user_name, COUNT(*) "Login Failed Since July"
FROM orasso.wwsso_audit_log_table_t
WHERE message LIKE '%failed%' AND
log_date > TO_DATE('01-JUL-06')
GROUP BY ip_address, user_name
ORDER BY 1,3 DESC
/
IP_ADDRESS USER_NAME
Login Failed Since July
--------------- ------------------------- -----------------------
145.45.106.66 PORTAL
10
DIANA
9
ANNETTE
2
167.24.106.73 PORTALXX
3
PORTAL
2
The following is a list that provides you
with a list of all logs plus the one was locked separated with
ip_address for the PORTAL account on May 16, 2007.
Query:
SQL> set linesize 100
SQL> col user_name format a25
SQL> col ip_address format a15
SQL> col message format a45
SQL> col email format a20
SQL> set pagesize 1000
SQL> set arraysize 15
SQL>
BREAK ON ip_address skip 1
SQL> SELECT user_name, ip_address, message,
to_char(log_date,'YYYY-MM-DD HH24:MI:SS')
FROM orasso.wwsso_audit_log_table_t
WHERE (log_date >= TO_DATE('16-MAY-07') AND
log_date < TO_DATE('17-MAY-07')) AND
user_name LIKE 'PORTAL%' AND
(message LIKE '%lock%' OR
message LIKE '%fail%' OR
message LIKE '%Login%')
ORDER BY 2,4
/
|