Warning about whitespace and to_timestamp_tz

Quick post prompted by a stackoverflow question.

select to_timestamp_tz('11-OCT-10 04.59.59.999999999 AM -05:00',

                        'DD-MON-RR HH.MI.SSXFF AM TZR') ws,
       to_timestamp_tz('11-OCT-10 04.59.59.999999999 AM  -05:00',

                        'DD-MON-RR HH.MI.SSXFF AM TZR') no_ws
from dual

Do you notice the extra space in the second column, preceding the -05:00 timezone ?
Oracle notices it too, but isn't very intelligent in accommodating it.

WS
-------------------------------------------
NO_WS
-------------------------------------------
11-OCT-10 04.59.59.999999999 AM -05:00
11-OCT-10 04.59.59.999999999 AM +05:00

That is the result on XE and on the 11.2.0.2 edition on apex.oracle.com

If an article is posted on the Internet and Google doesn't index it, would it have an impact ?

I've been following posts about copyright in the last few weeks.

Tim Hall and Brent Ozar both take issue with plagarists stealing their content.
This is an expansion of a comment I made on a post by Jake at AppsLab

Firstly, I'm personally not worried about theft of my content. But then I'm not in the league of Tim or Brent. When I get around to it, I'll rejig the blog layout so that each page has this Create Commons licence.

But the wider issue is a technical one. The internet is a lot about digitizing content, duplicating it and distributing it. The music and movie industries have battled this for a while, and not done too well. But, through the CodingHorror blog, I see that YouTube is applying technical solutions.

Basically when material is uploaded, they run it through some algorithm to try to determine if it is copyright. So I figured, how could this apply to text-based content. The key is Google, who happen to own YouTube. Yes, there is Bing and Blekko and Wolfram Alpha. There was Cuil, which died quietly a few months back. But Google is search.

It would be quite feasible for Google to implement some plagiarism block. As I indicate in the title, if internet content isn't indexed by Google, it comes pretty darn close to not existing in any practical terms.

How I'd envisage it working is this:

I write a blog piece and run it through a Google Signature Recorder. It picks out, or has my assistance in picking out, some key phrases. Then, as they index the internet world they check out for other sites with those key phrases.

If they find one, they report it to me. I can either mark it as 'OK, licenced, whatever' or I hit the veto button. If I do that, they pull the offending pages from their search index. Worst case, if they find 80% of a site is offending content, they could decide to remove the entire site from the index.

Removing the offending site from their index doesn't hurt the search facility. The original content is still there. They can even do 'magic rewrites' to count pointers and links to offending content as pointers to the original for PageRank purposes.

What might their motivation be for this ?

With Youtube, where they are hosting the service, it may be part legal necessity. But they host Blogger and Google Docs too. More than that though, this could be a service they could sell. If my content is valuable enough for me to want to protect it, I can afford to pay Google $50 a year to monitor it. The advantage of making this a paid service is that, if there is a challenge (maybe Fred says I've registered his content), there is a financial paper trail to follow.

Ultimately, as YouTube isn't obliged to host your videos, Google isn't obliged to index your content.

Application Express: Workspaces and Schemas

Time for another chapter, or at least verse, in the Apex security model.

With Apex, the relationship between Workspace and Schema is flexible. A single schema can have multiple workspaces against it, and a single workspace may be assigned multiple schemas. I’ve only worked on a couple of small Apex jobs, and have kept a one-to-one mapping between workspace and schema. That seems simpler to me.

In this example, the GARY workspace has two schemas, GARY and GROWL.

Going down a level, when you create an application under a workspace with multiple schemas, you pick one of them as the base schema for the application. That schema will be the ‘effective database user’ in terms of privileges.

Things are a bit different in the Workshop. The workshop belongs to the workspace and doesn’t have a single schema. Instead you get the drop-down list near the top-right corner. You choose the schema (out of the allocated schemas) and it runs as that user. Except, perhaps, in Nuno’s installation where he is seeing some odd results. 

Note that in both above cases the CURRENT_SCHEMA and CURRENT_USER are the same. When I tried an ALTER SESSION SET CURRENT_SCHEMA=…, it wouldn’t ‘stick’ for the next query. I could only get it to work if I include it in the same call. I assume that the mechanism which sets the current schema when it sets the parsing schema

As an amusement, I tried fiddling with the HTML to see if I could run SQL as a schema not linked to the workspace. It didn’t work and just raised an application warning (ORA-20000, rather than a built-in warning) that stated I didn’t have the authority on that schema.

Finally, I did some playing with DBMS_TRACE.TRACE_ALL_CALLS and identified WWV_DBMS_SQL as the ‘missing link’ between Apex and DBMS_SYS_SQL. The system catalog shows that APEX_040000 has execute permission on WWV_DBMS_SQL and that package has a dependency on DBMS_SYS_SQL

The classic Oracle user security model

Following from comments on my last post, I thought I'd put into words my thoughts on the Oracle security model.

Oracle has database users with passwords. Each database user is, at least nominally, a schema with its own objects. Each user can be granted system privileges (such as CREATE SESSION), object privileges (such as INSERT into a particular table) and roles. Roles are a bunch of system and object privileges that come into effect for that user's sessions but which can't be leveraged for objects such as stored procedures in the user's schema.

I think that there are problems with this.

Firstly, having the database being the 'point of authentication' really only works if one single database underlies all the applications in the enterprise. In practice there's a bunch of other applications too, down to email and calendars, and you really want a unified authentication mechanism. Single sign-on or LDAP or something dedicated to the job. And of course Oracle provides for an array of external authentications (discussed here) and the simple 'password in the database' will one day be totally redundant.

Secondly, the one-to-one relationship between users and schemas doesn't quite work. If I have two end users, Fred and Barney, doing the same jobs with all the same rights and privileges, then I will never want FRED owning some tables in his schema and BARNEY having others in his schema. I don't even want the possibility of FRED having access to an object that BARNEY doesn't. Because of the potential for that, every sql you look at in V$SQL has a parsing schema id. Because there is the chance that the grants authorizing FRED can be revoked, requiring the query to invalidated and re-parsed, the SQL has to be separate from BARNEY's identical SQL on the same tables.

If you worked in the client/server days, you can probably remember seeing dozens of identical SQLs differentiated only by the logged in user. A pain.

Thirdly, the client/server days also had this idea of having a database session for every logged in user. It seems a bit scary these days and architects will come in and shout about how you can't scale to a million users in that sort of model. Its not that big a deal though, at least for most business apps. The number of users for your business application hasn't changed that much since the 80s. Smaller businesses may have ten or twenty, large businesses maybe a few hundred or thousands. Where an application is 'front and centre' of some-ones job, they will be using it steadily. If there is some sort of 'state', it needs preserving, whether that is in a database tier or application tier. 

The problem with this model is if you extend your application outside your business, to your customers or the world in general. Then you might get thousands of users, perhaps only using the application for a few seconds, perhaps not logging on and off properly so resources can't be released immediately.

Some form of connection pooling with generic users is generally considered the way to go. Looking down a step, this is pretty much what a database does anyway, with the all the writes to data and log files typically being done by a few processes under an abstracted 'oracle' user. 

I understand why the Apex security model is separate from the classic Oracle model. It generally follows the typical model for an application technology. Indeed, my opinion is that Application Express is an 'applications' technology or framework, not a database one. It just happens to be tied closer to the database.

What I don't like is the use of DBMS_SYS_SQL. This is a powerful, and undocumented, package. There's bits of code in Apex that aim to prevent the creation of workspaces on a number of standard schemas. I'd prefer it the other way, that schemas should need to be explicitly permitted to be used as Apex schemas. Network ACLs came into Oracle with 11g so that those packages that communicated over the internet/intranet (UTL_HTTP etc) be more tightly controlled, down to individual sites if necessary. A similar setup could be established to control access through DBMS_SYS_SQL.

EXPIRED AND LOCKED...but still DANGEROUS

Prompted by James Koopmann's post about default Oracle accounts.

"The good thing here is that many of these accounts, after a normal installation are expired and locked"

Being blunt, "EXPIRED & LOCKED" does not mean safe, unbreakable, unusable or ignorable.

EXPIRED means that you cannot use the account password to access the account. My trick from last week would still allow access through a proxy account.

LOCKED is a bit better, as is lacking a 'CREATE SESSION' privilege. But even if FRED is locked, nothing stops user BARNEY using any available grant to run a procedure owned by FRED, and if that procedure is the default DEFINER RIGHTS, then it runs with an privilege granted to FRED. This is why SQL injection vulnerabilities in code owned by these LOCKED schemas are still dangerous.

But there's more...
Lets have some fun with APEX.
Brand new user, nothing up my sleeves.
  create user exp_test identified by exp_test default tablespace users quota unlimited on users;
  grant create table to exp_test;
  grant create trigger to exp_test;
  grant create procedure to exp_test;
  grant create sequence to exp_test;
  grant create view to exp_test;
Five basic privileges and not even as many as recommended in the documentation.

Those privileges are sufficient to create an Apex workspace for the schema though. A Workspace has an ADMIN user and you can log into that workspace as the ADMIN user. You can have other users (developers or plain, apex-authenticated users) on the workspace too.

You can then take your apex-authenticated admin or developer and go into APEX's SQL Worksheet and execute SQL as the schema user. Try it. No CREATE SESSION required. Now try

  alter user exp_test account lock;
  alter user exp_test password expire ;

Still accessible through Apex. No worries about the account being locked or passwords being expired.

I've done that using XE with Apex 4.0 and the embedded PL/SQL Gateway. Obviously the account you use to connect to the database needs to be unlocked, with CREATE SESSION and an unexpired password.

But beyond that Apex uses magic (or rather, I believe, DBMS_SYS_SQL). You can log out of one apex application running in a workspace on schema FRED, log into another running against BARNEY and it's all using the same set of database sessions.  The apex builder/administration application is all running in Apex as well, on the same connections, with all its majestic power.

So an expired and locked account still has enough oomph to bite your legs off.