Scheming anyone?

Ever run the create schema statement in Oracle? No - i thought not. It's one of the least used commands available i would think - I've only seen it used once in all my years of doing this job.

The basic premise of it sounds quite useful though - you can create all of your schema in one command - any errors and the whole thing rolls back - it's all within a single transaction. For creation of a base schema this is quite nice - you can keep running the script from scratch until it finishes 100% without error.

Lets do a quick demo of it (before i come back and tell you why nobody uses this... :-))

As a simple example - here is a new schema with a table a view and a grant (note how the terminating semicolon only appears once at the very end)

CREATE SCHEMA AUTHORIZATION dummyuser
   CREATE TABLE demotab 
      (col1 VARCHAR2(10) ) 
   CREATE VIEW demoview 
      AS SELECT col1 from demotab WHERE col1 > 1 
   GRANT select ON demoview TO missinguser; 

If you run that as is you get this

ERROR at line 1:

ORA-02421: missing or invalid schema authorization identifier

Right.......

Now in turns out that the schema you are creating has to be associated to an existing 'user' - so lets create that.

create user dummyuser identified by dummyuser;

grant connect,resource,create view to dummyuser;

Now try again and we get

ERROR at line 1:

ORA-02421: missing or invalid schema authorization identifier

again - lets check what the error code message actually tells us

oerr ora 2421

02421, 00000, "missing or invalid schema authorization identifier"

// *Cause: the schema name is missing or is incorrect in an authorization

//         clause of a create schema statement.

// *Action: If the name is present, it must be the same as the current

//          schema.

OK - so we need to set current schema to that name - lets try that

alter session set current_schema=dummyuser;

Session altered.

and we try again

ERROR at line 1:

ORA-02421: missing or invalid schema authorization identifier

Right..... lets try connecting as the user

SYS@DB>conn dummyuser/dummyuser

Connected.

DUMMYUSER@DB>CREATE SCHEMA AUTHORIZATION dummyuser

   CREATE TABLE demotab

      (col1 VARCHAR2(10) )

   CREATE VIEW demoview

      AS SELECT col1 from demotab WHERE col1 > 1

   GRANT select ON demoview TO missinguser;   2    3    4    5    6

   GRANT select ON demoview TO missinguser

   *

ERROR at line 6:

ORA-02426: privilege grant failed

ORA-01917: user or role 'MISSINGUSER' does not exist

OK - that works i expected that error

is we now check the table does not exist as everything was rolled back

desc demotab

ERROR:

ORA-04043: object demotab does not exist

If i now change the grant to be to system as this user exists it should work

DUMMYUSER@DB>CREATE SCHEMA AUTHORIZATION dummyuser

   CREATE TABLE demotab

      (col1 VARCHAR2(10) )

   CREATE VIEW demoview

      AS SELECT col1 from demotab WHERE col1 > 1

   GRANT select ON demoview TO system;  2    3    4    5    6

Schema created.

It works - and the objects are created - see the table below

DUMMYUSER@DB>desc demotab

 Name                                      Null?    Type

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

 COL1                                               VARCHAR2(10)

So it does work - but it has a few limitations - some you've already seen - but others seem to be that it only includes tables/views/grants and nothing else.......

So in it's current form not really going to be used i don't think - it it was enhanced it could become more useful.

What would be really useful is some command like this - i.e. things that operate at the schema level

1) drop schema (leaving user intact)

2) grant select on schema to userx

3) grant all on schema to userx; (grant everything but drop rights)

4) alter schema x readonly;

etc

That would really enhance working with oracle and i'm surprised these features never got introduced.....