Month: December 2005

lock system, restrict dbsnmp

An unlocked user is a security problem. Currently, all my unlocked users have only CREATE SESSION as system privilege, evtl ALTER SESSION. Except SYS, SYSTEM and DBSNMP To minimize this security problem, I implemented the following strategy on my test system. 1) delete password file, set remote_login_passwordfile=NONE, O7_DICTIONARY_ACCESSIBILITY=FALSE 2) alter user SYSTEM account lock; 3a) […]

the forums I use

on otn : forums.oracle.com 1) SQL and PL/SQL 2) Database General 3) iSQL*Plus 4) Documentation Feedback when otn is down or too slow, I read the metalink forums. Especially SQL*Plus and Oracle PL/SQL. If necessary, I post questions on the DBA Administration forum. to post questions/comments specific to security, I go to Pete Finnigan’s Oracle […]

sys_connect_by_path in 8i or the danger to use undocumented parameters…

I have been posting on metalink technical forum about a query that I run against all my test databases but did not work in production. as it simpliest form select sys_connect_by_path(dummy,’:’) from dual connect by 1=2; well, there is nothing wrong with this query. I tried it on 8i, 9iR2, 10gR1 10gR2 and it worked […]

oracle10gR2 on suse10

I prefer and recommend using Oracle on a supported version, like Suse Entreprise 9, because the installation is fair. The Installer does complain on SLES9 what is missing. Ok, I just received a brand new notebook, I decided to go to Suse 10, and, Ô Miracle, the installation was pretty easy! I surely installed C++ […]

Tom Day 3 : read consistency, transparent data encryption, dbms_advanced_rewrite

Day 3 concludes the Tom workshop in Switzerland. We started with read consistency and write consistency, with an interesting example were a single row update could make a big job run thrice slower, because of the write consistency. Once again, Tom insist on saying that you must understand Oracle to write applications. Tom demonstrates a […]

Tom Kyte Day 1

Day one was quite interesting! We learnt a lot of staff about tuning approach, I have got confirmation that most of the “WE KNOW THAT, IT HAS ALWAYS BEEN SO” were maybe one day true, but are no longer, for example “separate index and tables”, or, delightfull, “you must periodically reorganise your tables”. I also […]

encrypted listener password

There a few major changes in the database administration and the database security between 9i and 10g. In 9i, I used to grep in the listener.ora to find out the password. LISTENER_LSC61 = (DESCRIPTION=(ADDRESS_LIST= (ADDRESS=(PROTOCOL=TCP)(HOST=dbsrv85a.ex.zkb.ch)(PORT=10061)(QUEUESIZE=200)) )) PASSWORDS_LISTENER_LSC61 = 1234567890ABCDEF this 64bit encrypted string can be used in 9i to stop the listener $ lsnrctl LSNRCTL […]

add_years and years_between

I just wrote those two functions add_years and years_between they work similary to add_months and months_between, with the exception of leap years. there is exactly 1 year between 28-feb-2003 and 28-feb-2004 there is 1.00273224 year (1+1/366) between 28-feb-2003 and 29-feb-2004 there is 0.99726776 year (1-1/366) between 29-feb-2004 and 28-feb-2005 there is exactly 1 year between […]