Pete Finnigan's Oracle Security Weblog

I saw Andrew Clarke's blog entry today titled "USER != SCHEMA" and read it with interest. I did quite a detailed blog post about CREATE SCHEMA over a year ago. The post is titled "CREATE SCHEMA - does it do what it says on the tin?" where I looked into CREATE SCHEMA in Oracle. Basically Oracle supports Schemas but they are tied directly to users. You can use the CREATE SCHEMA command to create a schema for an existing users account (you must be logged into that account for it to work) and it allows you to create tables, views and grant privileges all in one command, so creating a schema in one go. It doesnt create a seperate schema to the user account that already exists it simply creates a set of objects along side any existing ones.

I did a simple test to show how to use CREATE SCHEMA:


SQL> get afiedt.buf
1 connect system/manager@oradev
2 drop user t3 cascade;
3 create user t3 identified by t3;
4 grant create table to t3;
5 grant create session to t3;
6 grant unlimited tablespace to t3;
7 connect t3/t3@oradev
8 create schema authorization t3
9 create table a (colour varchar2(10))
10* grant select on a to t1
SQL>
SQL> @afiedt.buf
Connected.

User dropped.


User created.


Grant succeeded.


Grant succeeded.


Grant succeeded.

Connected.

Schema created.

SQL>


As you can see it created the table and did the grant and simply reported back "Schema created". It would be a useful addition to Oracle to actually create schemas that are seperate to user accounts. I.e. you could create a bunch of tables / views etc that are in a schema but no one can log in to it. This would remove, in E-Business Suite the need to have hundreds of default accounts that need to have default passwords changed and locked etc.