Coskan’s Approach to Oracle

April 23, 2007

Oracle Idiosyncrasies

Filed under: Tips — coskan @ 5:33 pm

Again stumbling uponĀ  internet, I found interesting staff about Oracle by Yong Huang who claims we can’t find anywhere else about what he wrote. Before checking out his web page lets prove one bug like thing he wrote about comments in Oracle.

hr@XE> drop table t PURGE;

Table dropped.

sys@XE> drop table t purge;

Table dropped.

sys@XE> create table t (a number);

Table created.

sys@XE> insert into t values (123);

1 row created.

sys@XE> /*update T set a=321 where a=123;*/

1 row created.

sys@XE> /*insert into T values=456 */

1 row created.

sys@XE> /*example comment*/

1 row created.

sys@XE> /* working comment because of the beginning with space character*/
sys@XE> –this is what a comment must begin with
sys@XE> REM and again this is what a comment must begin with
sys@XE> select * from t;

A
———-
123
123
123
123

sys@XE>—you have 3 more rows and guess what will happen when you commit
sys@XE> commit;

Commit complete.

sys@XE> select * from t;

A
———-
123
123
123
123

This case is working for all statements you execute before the comment because the interpreter runs the previous command if you don’t have space after asterix. Woooow think about what this can cause for your commented batch operations

If I were you I would be careful before commenting and would use — instead of /* */

Now its time to read all off the Oracle Idiosyncrasies

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’;
USERNAME PASSWORD

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

HR 4C6D73C3E8B0F0DA

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;Connected.hr@XE>…..
–do you job
hr@XE>connect / as sysdba

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

User altered.

From Session 2; –vadaaaaaaa old password is still working

idle> connect hr/hr;

Connected.

Because it is undocumented try this carefully !!!

The Silver is the New Black Theme Blog at WordPress.com.

Follow

Get every new post delivered to your Inbox.

Join 193 other followers