Forum Stats

  • 3,824,997 Users
  • 2,260,452 Discussions
  • 7,896,379 Comments

Discussions

Audit users who connects only from sql developer

User_T5PW1
User_T5PW1 Member Posts: 1 Red Ribbon
edited Feb 10, 2020 3:05PM in Database Security - General

Hi Team,

Is there a way to define audit policy who connects DB only from tools ( TOAD,sql developer, pl/sql developer).

I need to monitor only the session that connects from sql developer or TOAD?

Thanks in advance,

Bala

Answers

  • Emad Al-Mousa
    Emad Al-Mousa Member Posts: 716 Bronze Trophy
    edited Feb 9, 2020 12:07PM

    Hi,

    you can implement custom trigger to prevent connections from these tools. Oracle TNS firewall (white listing) is implemented from IP-host level....not granular level ---> client tools

    Regards,

    Emad

  • User_KYWTC
    User_KYWTC Member Posts: 17 Green Ribbon
    edited Feb 9, 2020 12:36PM

    Hi Emad,

    Thanks for your response. I dont want to prevent connections coming from sql developer , i want to monitor the session that is coming from sql developer.

    Is there a way to define audit policy with such conditions?

    Thanks,

    Bala

  • Emad Al-Mousa
    Emad Al-Mousa Member Posts: 716 Bronze Trophy
    edited Feb 10, 2020 10:36AM

    Hi,

    try this

    SQL> create audit policy policytest

    actions all

    when 'sys_context(''userenv'', ''current_user'') = ''c##dropme2'' and sys_context(''userenv'', ''program'')= ''sqlplus.exe'''

    evaluate per statement

    ;

    SQL> audit policy policytest;

  • User_KYWTC
    User_KYWTC Member Posts: 17 Green Ribbon
    edited Feb 10, 2020 3:05PM

    Hi Emad,

    I tried... but it is not working nor it is not auditing the sessions coming from sql developer.

    Something is missing

    create audit policy AUDIT_table actions select,insert,update,delete  when 'sys_context (''userenv'',''client_program_name'') in (''SQL DEVELOPER'')' evaluate per session;

    audit policy audit_table by userx;

    Thanks,

    Bala

  • User_BH897
    User_BH897 Member Posts: 9 Green Ribbon

    Hi,

    Give this a go...


    Example (from CDB$ROOT to enable policy across all DB):


    NOTE: Lot's of single quotation marks below - NOT doubles (")


    SQL> create audit policy policytest actions all

     2 when 'sys_context(''userenv'',''client_program_name'')=''SQL Developer'''

     3  evaluate per statement

     4  container=all;


    Audit policy created.


    SQL> audit policy policytest;


    Audit succeeded.


    SQL>


    View:


    SELECT policy_name, enabled_option, entity_name

     FROM audit_unified_enabled_policies

     WHERE policy_name = 'POLICYTEST';

     

    POLICY_NAME  ENABLED_OPTION ENTITY_NAME

    -----------  -----------  ---------

    POLICYTEST BY USER ALL USERS


    Viewing Audit Records:


    select os_username, userhost, dbusername,client_program_name,event_timestamp,action_name,system_privilege_used 

    from unified_audit_trail 

    where unified_audit_policies='POLICYTEST' 

    order by event_timestamp desc;

    Hope this helps.

  • mpatzwahl
    mpatzwahl Member Posts: 280 Bronze Badge

    the problem is, when you change the name of the tool =>

    for example sqldeveloper.exe to sqldeveloper2.exe you won´t audit this connections !

    Marco

  • User_BH897
    User_BH897 Member Posts: 9 Green Ribbon

    That's a shame. Maybe try something else.

  • Christyxo
    Christyxo Member Posts: 151 Silver Badge

    I assume that you have some application or service connecting to your DB?

    Why not use the function provided by @User_BH897 to audit everything except the above - or just audit all?

    create audit policy policytest actions all
    when 'sys_context(''userenv'',''client_program_name'') <> ''<MY APPLICATION>'''
    evaluate per statement
    container=all;
    

    You will force yourself into a corner if you audit only one/two particular applications... What if someone used Squirrel, DBeaver for example? Audit everything and then query the specifics when it comes to reporting.

    andrewmy