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.....


0 comments:

Post a Comment