I want to have access to our users last login date for IFS10. There is a column in ORACLE_ACCOUNT (dba_users), but this doesn’t seem to log Logins to the Application, only PL/SQL, BAES and other stuff.
Earlier we created a trigger to fetch logins from active sessions, but this caused problems.
Anyone knows how to get hold of this Login information? We want to use it for license control.
Page 1 / 1
If ypu use database authentication, you should be able to create history login for FND_CLIENT_LPGON_TAB. It's this the table you used previously and caused problems?
(If you also want to audit failed login attempts):
audit create session whenever not successful
Then you could query the table dba_audit_trail for the information.
Adding some more to Sampath's reply,
If you are using dba_audit_trail from DB level, The initialization parameters of the audit facility of Oracle can be set from "audit_trial" parameter,
SQL> ALTER SYSTEM SET audit_trail=db SCOPE=SPFILE; SQL> SHOW PARAMETER AUDIT
NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ audit_file_dest string C:\ORACLE\PRODUCT\10.2.0\ADMIN \ORCL\ADUMP audit_sys_operations boolean FALSE audit_trail string DB
Command to begin auditing connects (login) attempts is:
For a full list of last logins in Apps9 and Apps10 you can simply use the following… I don’t believe any specific auditing needs to be turned on to enable this:
select username, last_login from SYS.DBA_USERS where account_status='OPEN' order by last_login desc;
However, based on the Author’s later reply the above query will not track Azure AD authenticated account logins.
Nick
We are also using Azure AD with conditional access to make our users authenticate with MFA when not on our known networks.
I use the Insight reports for the Enterprise Apps in AzureAD portal.
A user might touch one of many areas within the system. I attempted to capture all of these possibilities inside of one query, explicitly excluding the application owner, the IFS% users, and members of a particular user group.
Thanks for all input!
We found that the best solution to our problems is to use the Sign ins from the Enterprise Apps in AzureAD portal.
With a P1 or P2 Microsoft License we can fetch all the data with a PowerShell and store it in a local table where we can check last login for a user.
If ypu use database authentication, you should be able to create history login for FND_CLIENT_LPGON_TAB. It's this the table you used previously and caused problems?
Hi Rusiru,
FND_CLIENT_LOGON_TAB only works in the case of IEE. Can you please tell if there is any table that logs Aurena logons as well?
Regards,
Sahil Gupta
How can we get aurena logs. Getting from Azure is not so logical.
Regards
Pankaj
Hello,
Can you suggest if there is any parameter we can map for Audit log for Aurena logins.
Regards
Pankaj
For a full list of last logins in Apps9 and Apps10 you can simply use the following… I don’t believe any specific auditing needs to be turned on to enable this:
select username, last_login from SYS.DBA_USERS where account_status='OPEN' order by last_login desc;
However, based on the Author’s later reply the above query will not track Azure AD authenticated account logins.
Nick
We were doing this - but after update to Oracle 19C it all seems to have fallen over - the table has not been updated since, other than for IFSAPP and other system users.
Very strange
For a full list of last logins in Apps9 and Apps10 you can simply use the following… I don’t believe any specific auditing needs to be turned on to enable this:
select username, last_login from SYS.DBA_USERS where account_status='OPEN' order by last_login desc;
However, based on the Author’s later reply the above query will not track Azure AD authenticated account logins.
Nick
We were doing this - but after update to Oracle 19C it all seems to have fallen over - the table has not been updated since, other than for IFSAPP and other system users.
Starting with 12.2, when using proxy user authentication, only the proxy user will get their timestamp updated. With IFS Applications, that means IFSSYS will stay current, while the end user account will not get updated. That’s useless for us.
Also, as a side note, that column is “not populated when a user connects to the database with administrative privileges, that is, AS { SYSASM | SYSBACKUP | SYSDBA | SYSDG | SYSOPER | SYSRAC | SYSKM }.”
We are trying to setup IFSAPP usage report to see who and when used this account to access IFS in Apps 10?
We had this working in Apps 8 by using IFSAPP.SERVER_LOG to capture logins via IFS client and DBA_AUDIT_TRAIL for non-IFS logins; so logins to database using tools such as PL/SQL.
SERVER_LOG is no more in Apps 10, so trying to replace this functionality with an equivalent in Apps 10.
DBA_AUDIT_TRAIL doesn’t show detail for all logins irrespective if through IFS client or direct to database. If through IFS, it seems to hide between the MW server which makes the connection so not possible to see the actual user who is logging in using IFSAPP.
Note: The user executing the quick report will not have Administrator system privilege.
Please can you provide your guidance for best approach.
Thanks
Shaun
Hi @NickPorter in app8 is there a way we can get the last login date? last login is not available in SYS.DBA_USERS in app8. Thank you in advance.
You can use the Login Events form (iam_login_event_tab) in IFS Cloud for SSO users. It records the directory ID with a LOGON event type.
This records the IAM clients also.
You can use the Login Events form (iam_login_event_tab) in IFS Cloud for SSO users. It records the directory ID with a LOGON event type.
This records the IAM clients also.
Do you know how long the entries will persists in this table? Does this data reflect who is currently logged in?
I am also testing this. Looks to be controlled by the following system parameter. Defaults to 3 days only. I need it to be kept for 90 days. So need to extend the period.