Temporary access to database accounts and password reuse

Eddie Awad recently shared the "Factsheet about Oracle database passwords" from Red Database Security.

One of the items mentioned is the fact sheet is how to temporarily change a user's password, by getting the value from DBA_USERS (or SYS.USER$ in 11g) and using the IDENTIFIED BY VALUES clause.

It doesn't cover the situation where your policy prohibits password reuse. If you try to reset it back, then you'll get the "ORA-28007: the password cannot be reused" exception. You can set password_reuse_max to unlimited, then set the password and then reset the policy. But that's all a bit of a hack and it is something you shouldn't be doing anyway.

If a DBA has an urgent need to log into someone else, then the DBA should give himself proxy rights


ALTER USER target_usr GRANT CONNECT THROUGH dba_user;

then connect as


CONNECT dba_user[target_user]/dba_password@conn


 which is much tidier. Though the DBA should revoke that grant afterwards.
ALTER USER target_usr REVOKE CONNECT THROUGH dba_user;


And one more thing missing from the fact sheet is that, if you have enabled the functionality to prevent password reuse, obviously Oracle has to store the old password hashes to validate that they are not reused. Those old hashes can be seen in SYS.USER_HISTORY$. While they are not current passwords, if someone does get hold of them, they may provide a clue as to what passwords someone is using.

Fun with GROUP BY

What's wrong with this ?

CREATE TABLE dog 
(id number, name varchar2(10), breed varchar2(10), birth_date date);

select name, breed, count(*)
from dog
group by name;
Yup, you get an ORA-0979 "Not a group by expression" triggered by the "breed" identifier in line 1.

If you execute it through DBMS_SQL...well, you can't. It actually fails at the parse phase.

declare
  v_num integer;
  v_ret number;
begin
  v_num := dbms_sql.open_cursor;
  dbms_sql.parse(v_num,
     'select name, breed, count(*) from dog group by name',dbms_sql.native);
  v_ret := dbms_sql.execute(v_num);
  dbms_sql.close_cursor (v_num);
end;
/
But you can embed the SQL in a procedure and it compiles successfully.

create or replace procedure get_dog is
begin
  for c_rec in (select name, breed, count(*) from dog group by name) loop
    null;
  end loop;
end;
/

Although it fails on execution, the compilation of the procedure has succeeded. It has worked out that the table and columns exist with appropriate privileges. With PLSQL_WARNINGS it will even do some datatype checking. But this demonstrates that the compiling a procedure does NOT do a full parse of the SQL statement. One reason, perhaps, for this is that parsing the SQL statement would mean it ending up in shared memory (eg visible through v$sql) and the compilation of a large package might have a big impact on shared resources.

The 'lite parse' performed by PL/SQL can be done using less shared resources and a reduced impact on other sessions. The drawback is that, you may occasionally get an SQL statement in your PL/SQL code that can never be successfully executed. However you should discover that quickly enough in your testing phase....

PS. Can anyone come up with other SQL 'parse' errors that are not caught when the statement is statically compiled into PL/SQL ? My 'test' would be that the statement fails during a DBMS_SQL.PARSE, but can be included (without recourse to dynamic SQL) in a procedure that can be compiled without error.

Advice from the Ghost-who-walks

On my journey in to work, I read a newspaper. A little old-fashioned perhaps, but both my parents worked as journalists and newspapers have been a 'given' as far back as I can recall.

I also read the comic bits, including Dilbert and the venerable Phantom, the ghost-who-walks. He is currently investigating whether an innocent person might have been locked in a prison. His search was cut short but today's piece, on his way out, he thinks to himself "I can't write that in the chronicles, that I checked every cell but one."

This is where quality, and expectations of quality, play an important role in what we produce. If we are working on a project or system that is known to be "trouble", there's the temptation to skip checking that last prison cell, to skimp on design or testing, to re-use/misuse an existing column or variable rather than create a new one, to not tidy away deprecated columns.

But when the standards are high, even or especially when self-imposed, you'll go to the extra effort to complete the job. When you've written your test cases BEFORE writing the code, you will test every one and you'll make sure they pass. If you do your test cases after writing the code, there'll be the temptation to test only what you know will pass. There's the temptation not to test a couple of edge cases you're not convinced will work, justifying it by the time saved that the consoling thought that "they probably won't crop up anyway".

So aspire to be like the Phantom, and hold yourself to higher standards.

Fun with ROWNUM

Conventional wisdom has it that the only predicate that is effective with ROWNUM is in the form of ROWNUM < (positive integer). Okay, a <= works too. Or a "BETWEEN 1 AND ...", but you get the idea.

In practical terms, that is true. But in totally impractical terms, you can get some interesting effects with a predicate like "rownum in (3,4)" if you use it in conjunction with an OR. Here is a simple demo. Firstly, load up a table with some data.


create table test
  (id number not null, val varchar2(30) not null);
  
create index text_id_ix on test (id, val);
create index text_val_ix on test (val, id);


insert into test (id, val) values (1,'Zebra');
insert into test (id, val) values (2,'Toucan');
insert into test (id, val) values (3,'Rhino');
insert into test (id, val) values (4,'Monkey');
insert into test (id, val) values (5,'Llama');
insert into test (id, val) values (6,'Frog');
insert into test (id, val) values (7,'Emu');
insert into test (id, val) values (8,'Dog');
insert into test (id, val) values (9,'Cat');


Next query it through a simple table scan.


select /*+ NO_INDEX (t) */               * from test t 
where rownum in (3,4) or val between 'Cat' and 'Emu';



        ID VAL
---------- -----
         7 Emu
         8 Dog
         9 Cat

Exactly what you'd expect without the ROWNUM predicate. But now tell it to use the index on the value column...

select /*+ INDEX_FFS (t text_val_ix)*/   * from test t 
where rownum in (3,4) or val between 'Cat' and 'Emu';


        ID VAL
---------- --------
         9 Cat
         8 Dog
         7 Emu
         6 Frog

So, CAT, DOG and EMU match the BETWEEN predicate. However, the scan continues onto the remaining rows in the index and, because CAT, DOG and EMU have been allocated ROWNUMs one, two and three, then the next candidate (FROG) can be assigned ROWNUM four which is passed by the ROWNUM predicate. Since it is an OR, then the row is passed into the eventual result set even though it doesn't satisfy the other criteria.

So why didn't we get four rows on the FULL SCAN ? Well, that is because the EMU, DOG and CAT rows were the last rows inserted into the table. Once the full scan has picked up those three, there aren't any more rows left in the table to be assigned ROWNUM four.

With ROWNUM, the physical ordering of rows in a table and the choice of query path can affect which rows are returned. However by using a really DAFT predicate, you can actually affect the NUMBER of rows returned too.

It's not the only way to have the same query on the same data set return different numbers of rows though.A predicate using DBMS_RANDOM can do it or the use of the SAMPLE clause. Both are intentionally random though. Function based indexes on non-deterministic functions can have similar effects, as I blogged previously.

Ex-this, Ex-that and Ex-the other

No, I didn't go to OpenWorld. It's just too far away from me, and registration/flights/accommodation and all that just make it all too expensive.

While I would have enjoyed meeting people, I'm not sure I missed much else. All this Exadata and Exalogic is very nice if you've got a few million lying around, but my set of Tarot cards isn't seeing any of that in my near future.

But in the 'Ex-other' category, Bradley D. Brown revealed that there is some life in the Express Edition, with the 11g version in the works. Looks like they'll leave ApEx as a separate installable. Wonder if they will reconsider including the JVM in the database. By not removing it, they'll keep the core software closer to the mainstream code-line.

Incidentally, my Oracle Express install is the only item that consistently shows up in my Secunia PSI as being insecure. There's plenty of stuff (mostly .Net framework and Flash) that pops in and out as it becomes insecure and is then patched. When I first installed it, it pointed out a whole bunch of stuff that was dead, dying or lying in wait, prompting a fairly major cleanout and upgrade. This is the 'family' machine, so it has all manner of stuff installed and Secunia makes it a lot simpler to keep the bits up-to-date.

My little netbook got hit by a drive-by-download a couple of weeks back. One web-site I visited had briefly got itself exploited. Not vandalised (which implies something visual), or hacked (which implies information leakage) but probably some XSS attack which threw some nasty trojan through my Chrome browser but which (fortunately for me) got caught by Microsoft Security Essentials.

I did just have a week up at Port Stephens though. Away from Oracle, computers, twitter and all that. I did drop out of the plsqlchallenge rankings so haven't made the playoff this quarter. Maybe next time. With eight players from Australia in the playoff against just five from the UK, can I just say, "Aussie, Aussie, Aussie...Oi, Oi, Oi". Maybe we'll have to ceremoniously burn an Oracle manual, and use the ashes as a trophy.

Sandboarding on Stockton Dunes

I do have a few more days off, and I've got a couple more posts planned for this week.