Coskan’s Approach to Oracle

November 30, 2009

Do you check your script library?

Filed under: Diary, Security — coskan @ 5:52 pm

I’m wondering how do you secure your own script library ?

If you love to do things by calling scripts  isn’t there a big one way trust for security of your own machine ?

Have you ever think of  your own machine hacked by a  hacker who knows how to be invisible and how to write dangerous  sqls as well ?

what happens if your basic v$session query scripts  suddenly tries to drop, deletes or updates something ?

I start to double check every script I run but still worried and want to hear about your solutions ?


  1. Yes, I do worry about my “scripts” !

    It is a scary thought that someone, with malicious intent, could have modified a “standard” script. This would be much easier to do than to modify application code (in whichever language it is written !).

    Occassionally, I check a script before executing it. Or at least look at timestamps.
    But I don’t think that it is validation enough.

    Possibly, this is a reason to go to OEM and have OEM manage all alerts and report ?


    Comment by Hemant K Chitale — December 1, 2009 @ 3:23 pm

  2. Thank you for your comments Hemant,

    I was thinking about going fully DBA v2 but it is not that easy to leave command lines simplicity. Scary. I wonder if Oracle Security experts check this issue when they do auditing at sites and if they are I wonder what is their advice.

    Maybe Pete Finnegan can leave a comment on this issue

    Comment by coskan — December 1, 2009 @ 3:49 pm

    • What do we have is the scripts in a share folder where only two persons can write.
      If someone wants to add a file or a modification he/she has to contact those persons.
      What “I” am looking for is a “code” organizer (like The Guide) with password protection.
      I still have not found exactly what I’m looking for 😦

      Comment by Fidelinho — December 2, 2009 @ 9:23 am

      • I wish there is a wallet like solution for sql scripts as well. When I login I need to open the wallet. When I go and edit files in directory I need to open the wallet first etc. etc.

        Comment by coskan — December 4, 2009 @ 3:47 pm

  3. Hello Coskan,
    Here is my idea how to solve security problem with sql repository:

    1)First we need to encrypt file with openssl:
    = = = = = = = = = = = = = = = = = = = = = = = =
    cd ~/sql_rep && openssl enc -aes-128-cbc -in check_db_status.sql -out check_db_status.enc
    once prompted, please enter password for yuor file it can be the same for all sqls.

    enter aes-128-cbc encryption password:
    or you can specify password via additional option

    cd ~/sql_rep && openssl enc -aes-128-cbc -in check_db_status.sql -out check_db_status.sql.enc -pass pass:tezzt && rm -f check_db_status.sql

    spool /tmp/teztingz.lst
    select NAME,OPEN_MODE,FORCE_LOGGING,FLASHBACK_ON from gv$database;
    spool off

    NOw you have only encrypted version of your sql in your repository.

    2)Decrypt your sql and run:
    = = = = = = = = = = = = = = = = = = = = = = = =
    Just run this command to decrypt file and run sql, upon completion it will delete an *sql and will leave *enc only:

    cd ~/sql_rep && \
    cd ~/sql_rep && openssl enc -d -aes-128-cbc -in check_db_status.sql.enc -out check_db_status.sql && \
    sqlplus “/as sysdba” @check_db_status.sql && rm -f check_db_status.sql

    this one will prompt password.

    cd ~/sql_rep && \
    openssl enc -d -aes-128-cbc -in check_db_status.sql.enc -out check_db_status.sql -pass pass:tezzt && \
    sqlplus “/as sysdba” @check_db_status.sql && rm -f check_db_status.sql

    And this one already have password as an option.

    3)To simplify execution you can create an alias:
    = = = = = = = = = = = = = = = = = = = = = = = =
    function dbstf {
    cd ~/sql_rep && \
    openssl enc -d -aes-128-cbc -in check_db_status.sql.enc -out check_db_status.sql -pass pass:tezzt && \
    sqlplus “/as sysdba” @check_db_status.sql && rm -f check_db_status.sql
    alias dbst=dbstf

    So just run dbst from command prompt and you will run decrypted sql and than it will be deleted:

    [oracle@xxxx sql_rep]$ XXX_db_1st >>dbst

    SQL*Plus: Release – Production on Mon Nov 23 08:35:33 2009

    Copyright (c) 1982, 2007, Oracle. All rights reserved.

    Disconnected from Oracle Database 11g Enterprise Edition Release – Production

    I think use of openssl is very simple and it installed on every unix machine.

    I hope this solution will be applicable for you.

    Thanks and best regards Eugene

    Comment by Eugene — December 2, 2009 @ 6:04 pm

    • Thank you for your comments and your time Eugene but my main problem is that my workstation is windows and I am calling scripts inside the sqlplus.

      your solution is very good for the maintenance scripts we run on the server. I will try your way as soon as I can.

      Comment by coskan — December 4, 2009 @ 3:45 pm

  4. If you have grid control you could put your scripts on there. SQL*Plus can execute scripts available via http. See the link below for an example.

    Assuming your grid control server is secure then I guess you can sleep easy knowing your scripts are safe 🙂

    Comment by Neil Johnson — December 5, 2009 @ 8:35 pm

RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

Blog at

%d bloggers like this: