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 ?

March 11, 2009

Alter user identified by values on 11G without using SYS.USER$

Filed under: Security, Tips — coskan @ 11:13 am

You probably know that on Oracle 11G, hash values of passwords, are no longer shown in dba_users table, so to change the password temporarily, it is not possible to use the way I explained here before.

On Oracle 11G, there is one method posted by Laurent Schneider which is, to use SYS.USER$ table to extract hash value of the password.

Another method, I tried and got success, is using DBMS_METADATA.GET_DDL function without going to any other dictionary tables.

DBA Session — check the output of DBMS_METADATA.GET_DDL

SQL> select * from v$version;

Oracle Database 11g Enterprise Edition Release - Production
PL/SQL Release - Production
CORE      Production
TNS for 32-bit Windows: Version - Production
NLSRTL Version - Production

SQL> drop user coskan cascade;

User dropped.

SQL> create user coskan identified by oracle;

User created.

SQL> grant create session to coskan;

Grant succeeded.

SQL>  select dbms_metadata.get_ddl('USER','COSKAN') from dual;


VALUES 'S:1F0648E7E665F0A0EE44B1E9BD4B626A77CA25B376A49177F9E97DF98BFA;26EB15F771A78542'

As you see gathering DDL of the user gives us the hash value

On the User session —try connection

SQL> connect coskan/oracle

DBA Session —change user password

SQL> alter user coskan identified by msssql;

User altered.

User Session —connect with new password

SQL> connect coskan/msssql

DBA Session — Update the user password with the value you gathered by DBMS_METADATA.GET_DDL

SQL> alter user coskan identified by
  2  VALUES 'S:1F0648E7E665F0A0EE44B1E9BD4B626A77CA25B376A49177F9E97DF98BFA;26EB15F771A78542';

User altered.

User Session — Try the old password

SQL> connect coskan/oracle

I think this option is a bit easier than extracting from table approach.

Update-01/12/11 After I read the question on this oracle-l question I think I need to mention that you need to have “SET LONG 999999” setting for your sqlplus env

April 23, 2007

Act as if, temporarily in Oracle

Filed under: Security — coskan @ 10:37 am

While discovering the internets new trend StumbleUpon after reading Eddie Awads last entry I found a nice site Red Database Security about Oracle Security. The whitepaper about passwords has useful paragraphs for Oracle DBAs.

Here is a sample paragraph about changing a users password temporarily without knowing the original passwords by using the undocumented feature called “by values” of alter user command. Suppose you want to login as user HR but you don’t know its password and you can’t change it all you have to do is backing up the hash key of password from dba_users table. Lets look how ;

From session 1; –backup the hash key and change the password

idle> connect / as sysdbaConnected.sys@XE> select username,password from dba_users where username=’HR’;

————- ——————————


sys@XE> alter user hr identified by passwd;

User altered.

From Session 2; try to logon with old password

idle> connect hr/hr
ERROR:ORA-01017: invalid username/password;logon denied

Warning: You are no longer connected to ORACLE.

From session 1; –login with temporary passsword do your job and change back it

idle> connect hr/passwd;>…..
–do you job
hr@XE>connect / as sysdba

sys@XE> alter user hr identified by values ‘4C6D73C3E8B0F0DA’;

User altered.

From Session 2; –vadaaaaaaa old password is still working

idle> connect hr/hr;


Because it is undocumented try this carefully !!!

January 29, 2007

What if DBA views are not reliable ??

Filed under: Security — coskan @ 12:13 pm

Suppose that you are the DBA of big Corporate with many DB users.

One day you query the v$session table and you see a username HACKER.

who is this ????

firts you look to toad and see nothing about user HACKER

than you query dba_users

and you see nothing about user HACKER

Where this user come from ??? Why you cant see him ??

Answer is below.
13:04:40 SQL> create user hacker identified by hacker;

User created.

13:05:50 SQL> grant create session to hacker;

Grant succeeded.

13:05:58 SQL> grant dba to hacker;

Grant succeeded.

13:12:33 SQL> select username from dba_users where username=’HACKER’;

no rows selected


13:14:23 SQL> select name from user$ where name = ‘HACKER’;


1 row selected.

The answer can be read between the lines

select, u.user#, u.password, m.status,
decode(u.astatus, 4, u.ltime, 5, u.ltime, 6, u.ltime,
8, u.ltime,9, u.ltime, 10, u.ltime, to_date(NULL)),
decode(u.astatus, 1, u.exptime, 2, u.exptime, 5, u.exptime,
6, u.exptime, 9, u.exptime, 10, u.exptime,
decode(u.ptime, ”, to_date(NULL),
decode(pr.limit#, 2147483647, to_date(NULL),
decode(pr.limit#, 0,
decode(dp.limit#, 2147483647, to_date(NULL), u.ptime +
u.ptime + pr.limit#/86400)))),,, u.ctime,,
nvl(cgm.consumer_group, ‘DEFAULT_CONSUMER_GROUP’),
from sys.user$ u left outer join sys.resource_group_mapping$ cgm
on (cgm.attribute = ‘ORACLE_USER’ and cgm.status = ‘ACTIVE’ and
cgm.value =,
sys.ts$ dts, sys.ts$ tts, sys.profname$ p,
sys.user_astatus_map m, sys.profile$ pr, sys.profile$ dp
where u.datats# = dts.ts#
and u.resource$ = p.profile#
and u.tempts# = tts.ts#
and u.astatus = m.status#
and u.type# = 1
and u.resource$ = pr.profile#
and dp.profile# = 0
and dp.type#=1
and dp.resource#=1
and pr.type# = 1
and pr.resource# = 1
and <> ‘HACKER’

Thanks Steve Callan, for informing us about the situation above.

What i have learned today;

If you want to be a real dba look behind the VIEWS.

Security Comes First

Security First

Blog at