Fog Creek Software
Discussion Board

Someone said Oracle frustrations?

Someone in an earlier thread wanted to name bugs in Oracle. I ran into the following problem:

I have an Oracle user  to which I granted the database role IMP_FULL_DATABASE, which allows this user to import data into other schemas than his own.

IMP_FULL_DATABASE in turn has another role granted to it, which is called SELECT_CATALOG_ROLE. This select role grants select rights to parts of the data dictionary, for example the DBA_DIRECTORIES view. Hence, if I run SQL*Plus or another interactive SQL query:

SELECT directory_path  FROM dba_directories
WHERE directory_name='TEST_DIR';

this works nicely, and yields the correct answer. However, if the same user creates a stored procedure like this:

create or replace procedure
  test_dir_write authid current_user is

thepath varchar2(255);
f utl_file.file_type;

  SELECT directory_path into thepath
  FROM  dba_directories
  WHERE directory_name='TEST_DIR';,'dummy.txt');
  utl_file.put_line(f,'This is a dummy text file');

This stored procedure fails to compile with the error:
PLS-00201: identifier 'SYS.DBA_DIRECTORIES' must be declared.

This error is a GRANT problem, and it tells that table cannot be selected from. So, there is obviously 2 pieces of code that decides if the user has the rights to select something, one when running interactive SQLs and one when running SQLs from SPs.

The SP grant checking code needs priveliges granted explicitly to the SP owner, and couldnt care less about implicitly granted stuff through roles.


Im done whining :-)

Monday, September 15, 2003

Is the following relevent?

Named PL/SQL Blocks and Roles

All roles are disabled in any named PL/SQL block (stored procedure, function, or trigger) that (a) is created in a user schema that does not own the object being referenced in the PL/SQL block, and (b) can be executed as a user other than the owner of the PL/SQL block.

Anonymous PL/SQL blocks, however, are executed based on privileges granted through enabled roles.

The SESSION_ROLES view shows all roles that are currently enabled. If a named PL/SQL block queries SESSION_ROLES, the query does not return any rows.

I Googled for it, of course...

Grumpy Old-Timer
Monday, September 15, 2003


Thanks for pointing this out.  Much appreciated.

Tuesday, September 16, 2003

Some of the problems I encountered in the past year:
- rollbacks crashing the entire application when using certain Oracle clients (updating the client was not an option)
- UTF8 to unicode on the fly driver conversions converting the internal data buffer only partially leaving the rest a mumbo-jumbo (MS driver for Oracle worked as expected)
- same SQL working differently on different Oracle versions and requiring adjustments
- connections open from .Net and never closed, unless deterministically disposed. Again the MS driver for Oracle worked as expected closing when out of scope.
- oracle server wasting so many resources on the previous connections that we had to reboot the servers.
- banal joins on system views taking up to one hour to complete
- the above mentioned joins being sped up to seconds by reordering the FROM and WHERE arguments

For more on Oracle problems see their forums (free subscription might be required):

19th floor
Tuesday, September 16, 2003

I asked the original question about Oracle problems.

It seems to me that these problems are with client software, applications, and outside the DBMS. I've found the Oracle DBMS to be competent and stable, but I'm not a fan of their other stuff.

Oracle ... DB2 ... Sybase ... MS ... just use ODBC and you'll never care about the rest :)

Tuesday, September 16, 2003

All problems I cited are with various Oracle implemented clients and Oracle implemented OLEDB drivers. They are in my definition Oracle problems since they occur in their code. A stable DB engine is pretty useless if the rollback bombs randomly the application because some Oracle developer did illegal system operations, isn’t it?

19th floor
Wednesday, September 17, 2003

*  Recent Topics

*  Fog Creek Home