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 ?
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 ?
Hemant
Comment by Hemant K Chitale — December 1, 2009 @ 3:23 pm
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
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
check_db_status.sql
~~~~
spool /tmp/teztingz.lst
select NAME,OPEN_MODE,FORCE_LOGGING,FLASHBACK_ON from gv$database;
spool off
exit
~~~~
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
~~~~~~~
dbst
SQL*Plus: Release 11.1.0.6.0 – Production on Mon Nov 23 08:35:33 2009
Copyright (c) 1982, 2007, Oracle. All rights reserved.
XXX11G READ WRITE NO NO
Disconnected from Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 – 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
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.
http://blog.tanelpoder.com/2007/08/31/sqlplus-is-my-second-home-part2/
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