DBMS_METADATA and hashed passwords

In the wonderfully named "ORAganism" blog (don't try oragasm - it is used for something QUITE different) Neil Johnson remarks on the privilege requirements for DBMS_METADATA.

I like DBMS_METADATA. In the old days, you had scripts that tried to re-engineer CREATE scripts from DBA_TABLES and so on. They were complicated and worked in most situations. DBMS_METADATA is much more powerful.

One feature worth noting about DBMS_METADATA is that it can also extract details about users which can be handy in the post 11g world of Oracle.

In 11g the PASSWORD column in the DBA_USERS returns null, rather than hashed version of the user's password as it did in 10g and earlier. That made it a lot harder to extract those password hashes from the database. There is some underlying SYS object that shows the hashes, but SYS objects are really tricky to access.

DBMS_METADATA gives a backdoor to that information. Not to everyone, but it should be usable by 'regular' DBAs without jumping through hoops.


select dbms_metadata.get_ddl('USER','GARY') a from dual;


CREATE USER "GARY" IDENTIFIED BY VALUES 'S:8A9EA7B75F8899D7163336AD9D29F9019C0361518594E6984E1EF1C4EDB8;34BCEDBA9E0AB83F'
   DEFAULT TABLESPACE "APEX_4875120311438442"
   TEMPORARY TABLESPACE "TEMP"

Before you try to crack my password, don't bother. In my little home environment, it is the same as the username.

Wish List IV - Read only and mean it

Did you know that the SELECT privilege actually allows you to do a SELECT ... FOR UPDATE ? Yes, that means a user with just that privilege could lock an entire table.

I want a SELECT_NOLOCK privilege. The user can still do a SELECT, but they cannot have a lock on the table. Okay, while an SQL is executing, I guess that get the shared table lock. But that's it.

Wish List IV - The Whole Truth

This is another little annoyance that, I hope, would be a simple change.

I want a column in USER_TAB_COLUMNS that tells me whether the object is a table or a view. Surely linking off to USER_TABLES to exclude views is an unnecessary burden.

Wish List III - The Sequence Cometh

This is another of those 'chores' for which every DBA and his dog has a script.

Copy a new version of a table from Test to Dev and then you have to recreate or adjust the associated sequence. My proposed syntax would be

ALTER SEQUENCE blah_seq SYNCHRONIZE WITH blah_table (blah_column);

That would lock the table, get the highest value of 'blah_column' and reset the 'last_number' of the sequence to that value. Even better, retain the fact that the sequence has been synchronized with that table/column so there is some metadata about the relationship.

Wish List II - Ultimate Destruction

I'm continuing on my theme of a wish list for 12c (or beyond), and this time I'm taking aim at destroying stuff.

Juggling development and test areas, every so often it is tempting to wipe the slate clean and start with a fresh schema. The simple way to do that is with a DROP USER blah CASCADE

It would be nice to have something one step below the 'nuclear' option. I'm thinking

DROP ALL TABLES UNDER SCHEMA blah

plus similar options for dropping sequences, views, procedures, triggers and so on. The user/schema continues to exist, with all their privileges and defaults. But you get to clear out the objects the schema owns.

Yes, you can script this. The trick is to disable all the referential integrity constraints first, so that you can drop the tables without worrying about the dependencies.

Cross-schema constraints could still be a problem, as could firing DDL triggers. I'm open to alternative mechanisms. Perhaps DBMS_METADATA could generate a script for dropping objects.

12c Wishlist - Part 1

Last week Nuno posted his 'wish list' for 12c. Here's the "Number 1" on my list:

Oracle Personal Edition for Linux.

A long, long time ago...I can still remember
when Windows was the OS that ran on the PC on your desk. It made sense that someone running their 'personal' database would just install it natively on that desktop machine, and that meant a Windows install.

Too often that would just be an install on top of a Standard Operating Environment pre-configured with Office, a virus checker and the other bloat that hangs around. Yuck.

What I'd like to recommend is that you start with an OS that is stable and decidedly unglamorous, such as RedHat or Oracle Linux. That could run on a commodity x86 under your desk. It could run in a VirtualBox virtual machine on your SOE machine, or in a VMWare or Oracle VM environment managed by real server admins. It could migrate happily between any of them.

But when I go to the documentation I see "Personal Edition is available on Windows platforms only"
Oracle are saying go and buy software off Microsoft before you talk to us about running our database ?

I want Oracle Personal Edition on Linux. Why not a bundle price for the database and Oracle Linux ? You could even sell it as a pre-configured VirtualBox appliance - just add disk. You could 'give away' a one-year Personal Edition licence (cost around $100) with every OCP certification. But push Linux as the preferred platform.

I'm not sure of the production use cases for Personal Edition. I heard, many years ago, that it had market share in Life Sciences, but it may have since been eclipsed by more specialised solutions, open source RDBMS or NoSQL data stores. Maybe it won't even be offered in 12c, squashed out between Express Edition and MySQL.

-----------------------------------------------------------------------------------

I've got a few more items on my wish list which I'll post over the coming days. I figured I'd do them as individual posts. In theory that means Plus +1 and retweets might give a guage as to the support for each idea.

What's the difference

I enjoy working with Apex. However one challenge is that it doesn't sit too well with conventional source control systems. Plus the fact that not all organisations use source control systems, especially for the small departmental applications which are a particular sweet spot for Apex.

One item in my little library of scripts is based on the ApexExport utility. This is part of the main Apex install which you'll probably find on the server. If you don't have access to the server (and some DBAs are understandably cautious about that) ApexExport is also available as part of the Apex Listener. That's a much more compact download, and I've based my script on that.

The Apex Listener download is a zip file. When you unzip it you get a WAR file. If you unzip the WAR file, you find the "apex.jar" file, which is what you need. If you like playing Russian dolls, you can even unzip the JAR file into the class files, but I don't see the point.

My Apex diff script simply extracts the same application (based on the id) from two Apex workspaces and does a compare of the results. This is handy to see if the version in a development workspace is the same as that in Testing/Production (eg has another developer done some work on it). An alternative use case is to ensure that no-one has been sneaking unapproved amendments into Prod.

My script is a zip because downloading BAT files is often blocked. It is trivially small, so the code is shown below too.

I've been lazy and hard-coded the username password here. Feel free to replace them with more parameters. Similarly with directory names, and you can replace the MS-DOS "fc" call with one to your favorite diff utility. I've used ExamDiff for a long time and my preferred editor, PsPad has one built in as well.


REM
REM Usage "diff JDBC_conn1 JDBC_conn2 nnnn"
REM
REM Note: Use JDBC connection format 127.0.0.1:1521:xe
REM


del f%3_1.sql
del f%3_2.sql

cd G:\oracleXE_11\apex_listener\WEB-INF\lib
set CLASSPATH=%CLASSPATH%;.\;.\ojdbc6.jar;.\apex.jar


java oracle.dbtools.apex.utilities.APEXExport -db %1 -user gary -password gary -application %3
rename f%3.sql f%3_1.sql


java oracle.dbtools.apex.utilities.APEXExport -db %2 -user gary -password gary -application %3
rename f%3.sql f%3_2.sql


fc /c /l /n /w f%3_1.sql f%3_2.sql

How to prevent your SQL Developer sessions from being killed

A while back I worked on a site where the network would terminate connections that were idle too long (around 20-30 minutes). It is pretty frustrating when you've been working on something for a while then you go grab a coffee or have a meeting and come back to find the connection had been killed. It was even more frustrating when the session was still alive in the database and had to be killed there, so that it could rollback the work so that it could be repeated all over again.


What I'd like is a SQL Developer feature that would periodically send a lightweight request over to the database so the connection is kept alive. Alas, the powers that be seem averse to providing such a function.


I also accept that there are barriers in trying to get people to change their opinions.


The good news is that SQL Developer has the concept of extensions. 
If something you want is too specialised to be included by Oracle themselves, or they haven't got around to it or simply don't want to do it, then an extension can help.


In this case, a bloke called MinChen Chai has published an extension to add KeepAlive functionality to SQL Developer.


Just download it and add the .jar to the directory sqldeveloper\sqldeveloper\extensions and the Right-click menu on a connection will include a Keep-Alive item. 


If you want to be on the cautious side, you can use unzip or similar to extract the class files from the JAR file. Then you can use the Java Decompiler (or similar) and view the source from the class files. The SQL used is in the Pinger.class and is a pretty inoffensive SELECT SYSDATE FROM DUAL. 


Warning : I haven't tested to see if it works if you spawn out an unshared worksheet.


If your environment is locked down to prevent extensions being added, then all I can suggest is a User-Defined Report with a refresh set to the maximum delay. Unfortunately this grabs focus in Windows and the maximum delay is just two minutes which is much too frequent in my opinion, but it is hard coded. As an aside, the 'reports' component also sits in the extensions directory (oracle.sqldeveloper.report.jar).


Should we be doing this ?


I accept there are situations where it is appropriate for a DBA to terminate sessions. And this won't stop an explicit ALTER SYSTEM KILL SESSION.


However I think terminating sessions automatically based on 'being idle' is something that is abnormal, and is inappropriate for most environments.  
Before terminating a session, several factors should be considered. These include 

• Is the session actually doing something important - like the monthly pay cheques
• Is the session blocking other sessions (and which session is more vital to the business)
• Is there a need to preserve session state (PL/SQL global variables, temporary tables etc)
• Is there an active transaction
• Can the transaction be (safely) committed
• How much rollback would need to be applied if it is killed
• Is the session consuming lots of memory
• Can the user/client cope gracefully with a failure

If your DBA doesn't consider such factors and is happy to terminate sessions without worrying about the effect, then you have a problem. You'll just have to grin and bear it and hope that that there's some form of karma. 


It may help to read a DBA's blog post about a hosting provider admin deleting some of his log files . I love the comments that "anything with 'log' in the name is by convention disposable but useful for diagnosis" and "Log files do build up and unless there is meaningful data in them you can remove them."