Ponder Stibbons Limited

Creating a schema owner

What is a schema?

A schema is a collection of database objects (tables, views, stored procedures etc) owned by a single user. 10g Concepts Manual defines it here. Typically, all of an applications objects will be in a single schema and application users will access those objects through a combination of grants and synonyms.

When to use a schema.

As a rule, each application should be in its own schema. If you are used to Microsoft SQL Server or some other databases, when you would want to create a new database in a server, in Oracle you would create a schema.

The ANSI 'CREATE SCHEMA' statement.

The ANSI SQL standard includes a 'CREATE SCHEMA' statement which in theory allows the creation of all the objects in schema in a single statement. In practice it is useless because (at least as implemented by Oracle) it limits you to ANSI standard syntax within the statement. This means that any oracle extensions to the ANSI standard (for example, storage options) can not be used. Steer well clear.

Creating the schema owner.

The main difference between a schema owner and an application user is that the schema owner requires system privileges (such as create table) whereas an application user only requires object privileges (such as select, insert,delete on a table).

The short method.

The simple way to do this is as follows

C:\>sqlplus "sys/********@xe as sysdba"
SQL*Plus: Release 9.2.0.1.0 - Production on Sun Nov 19 10:18:39 2006
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Connected to:
Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production
sys@XE> CREATE USER BLOG IDENTIFIED BY BLOG;
User created.
sys@XE> GRANT CONNECT,RESOURCE TO BLOG;
Grant succeeded.

This has the merit of simplicity, but not much else. The problems with this are -

The RESOURCE role includes the "unlimited tablespace" privilege which means that the schema owner can create objects in any part of the database, including the system tablespace. While the DBAs who will be creating the schema objects know that you shouldn't create objects just anywhere, accidents will happen.

As currently defined (in 9i and 10g), the CONNECT and RESOURCE roles combined do not include all the system privileges which might be needed. If I try to use the user created above...

sys@XE> CONNECT BLOG/BLOG@XE
Connected.
sys@XE> SELECT * FROM SESSION_PRIVS;
PRIVILEGE
----------------------------------------
CREATE SESSION
UNLIMITED TABLESPACE
CREATE TABLE
CREATE CLUSTER
CREATE SEQUENCE
CREATE PROCEDURE
CREATE TRIGGER
CREATE TYPE
CREATE OPERATOR
CREATE INDEXTYPE
10 rows selected.
sys@XE> CREATE TABLE X ( COL1 NUMBER(10) );
Table created.
sys@XE> CREATE VIEW Y AS SELECT * FROM X;
CREATE VIEW Y AS SELECT * FROM X
*
ERROR at line 1:
ORA-01031: insufficient privileges

The select from SESSION_PRIVs shows the privileges which are currently active and are the union of the privileges in the granted roles. This particular example would have worked in 9i because the connect role included the CREATE VIEW privilege, but there are other privileges which are missing.
This table lists the system privileges which a schema owner might need, with those provided by the connect and resource roles highlighted.

ALTER SESSION²

CREATE CLUSTER¹²

CREATE DATABASE LINK²

CREATE EVALUATION CONTEXT

CREATE EXTERNAL JOB

CREATE INDEXTYPE¹

CREATE JOB

CREATE LIBRARY

CREATE MATERIALIZED VIEW

CREATE OPERATOR¹

CREATE PROCEDURE¹

CREATE PUBLIC DATABASE LINK

CREATE PUBLIC SYNONYM

CREATE RULE

CREATE RULE SET

CREATE SEQUENCE¹²

CREATE SESSION

CREATE SYNONYM²

CREATE TABLE¹²

CREATE TRIGGER¹

CREATE TYPE¹

CREATE VIEW²

¹ Granted as part of RESOURCE role

² Granted as part of CONNECT role prior to 10gR2

Note that (apart from ALTER SESSION), no ALTER <OBJECT> or DROP <OBJECT> privileges are required. An object owner can always drop or alter its own objects.

In general, it is not a good idea to use system defined roles. The vendor is free to change the definition of the role at any time which could break your applications, or leave you exposed to an unknown risk. With the release of Oracle 10g, oracle drastically changed the definition of the CONNECT role. The original definition included privileges beyond those needed to simply connect and the change was a long overdue improvement. However, unless you were aware of the change, your upgrade could have gone badly wrong.

The right method.

There are three steps to creating the schema owner.

Create the user
```
sys@XE> create user blog identified by blog;
User created.
```
At this stage, the user exists, but has no privileges (not even the right to connect to the database) and no resources.

Specify which resources it can use. When setting up an oracle user, you can specify what storage it can use, using tablespace quotas, and what machine resources (cpu, clock time etc) using profiles. I won't cover profiles here in any detail here. The tablespaces required should be created in advance.

sys@XE> alter user blog
2 temporary tablespace temp -- specify which temp tablespace to use
3 default tablespace blogdata1 -- ensure uncontrolled creates go somewhere safe
4 quota unlimited on blogdata1 -- allow the creation of objects in a tablespace
5 quota unlimited on blogdata2 -- there can be as many tablespaces here as you like.
6 quota unlimited on blogdata3
7 profile schemaowner_profile; -- security and resource control
User altered.

Grant the privileges required. You can either grant the required privileges directly to the user;

sys@XE> grant create session to blog;
Grant succeeded.
sys@XE> grant create table to blog;
Grant succeeded.
sys@XE> grant create view to blog;
Grant succeeded.

or you can create a role, grant the privileges to the role and then grant the role to the user.

sys@XE> create role schema_owner;
Role created.
sys@XE> grant create session to schema_owner;
Grant succeeded.
sys@XE> grant create table to schema_owner;
Grant succeeded.
sys@XE> grant create view to schema_owner;
Grant succeeded.
sys@XE> grant schema_owner to blog;
Grant succeeded.

What this does is create your own customised and more useful version of the resource role. A usable sample script is here

When to use the schema owner.

The schema owner account should only ever be used to create and modify the application objects. Ideally it should be locked in when not required and only unlocked as part of your change control procedures. Summary.

A schema is a collection of objects owned by a single database user.
The schema owner requires physical resources and system privileges
The default roles provided by Oracle do not provide all the required controls and privileges

Fear of Commitment

by Jim Smith 7th April 2006

A poster on the comp.databases.oracle.server usenet group (CDOS) posted a piece of PL/SQL code and asked when the explicit commit occurred. This prompt a flurry of short-tempered answers to the effect that there was no such thing as an implicit commit in oracle.

While in a narrow sense this is true, the actual situation is more complicated. If you consider only the database engine then Oracle never commits DML statements. This contrasts with some other databases (MSSQL,Sybase) where the default mode is statement level auto-commit. That is, unless you explicitly begin a transaction, every statement is followed by a commit. It is therefore reasonable to say that oracle doesn't have an implicit commit. This is a key difference between oracle and some other databases.

However, in oracle, all DDL statements ARE preceded by an implicit commit. Given that DDL has no place in production code it is probably still reasonable, if not entirely accurate, to say that oracle has no implicit commit.

When you expand tne viewpoint beyond the database engine to include client tools, the picture is even less clearcut. SQL*plus has two forms of implicit commit.

The SET AUTOCOMMIT ON/OFF options toggles statement level commit. Tne default is off, but if autocommit is enabled then each statement sent to the database is followed by a commit. This makes SQL*plus behave like isql in MSSQL/Sybase, except tnat you get two pieces of feedback.

jim@XE> create table commit_test ( ct_key varchar2(10));
 
Table created.
 
 
jim@XE> set autocommit off
 
jim@XE> 
 
insert into commit_test values ('asdf');
 
1 row created.
 
jim@XE> insert into commit_test values ('asdf');
 
1 row created.
 
jim@XE> insert into commit_test values ('asdf');

1 row created.

jim@XE> select * from commit_test;

CT_KEY
----------
asdf
asdf
asdf

3 rows selected.

jim@XE> rollback;

Rollback complete.

jim@XE> select * from commit_test;

no rows selected

jim@XE> set autocommit on
jim@XE> insert into commit_test values ('asdf');

1 row created.

Commit complete.

jim@XE> insert into commit_test values ('asdf');

1 row created.

Commit complete.

jim@XE> insert into commit_test values ('asdf');

1 row created.

Commit complete.

jim@XE> rollback;

Rollback complete.

jim@XE> select * from commit_test;

CT_KEY
----------
asdf
asdf
asdf
 
3 rows selected.

Note the two feedback messages - 'Statement processed" and "Commit complete". The latter is the same message you get when you issue a manual commit - underlining the fact that it is SQL*Plus issuing the commit, not the database.

Another important point to note here is the meaing of the word statement in the phrase 'statements sent to the database'. Each PL/SQL block is considered a single statement, so even if a PL/SQL block contains several DML statements, there is only one commit. Again, this is because it is SQL*Plus which is issuing a commit statemeht, not the database.

Tne second implicit commit is more sneaky. When you exit from SQL*Plus, a commit is issued.

This can have unintended consequences

Other tools have different behaviour. Oracles OCI and OCCI have no implicit commit. The default for JDBC is autocommit (statement level commit) .