Call: +44 (0)1904 557620 Call

Pete Finnigan's Oracle Security Weblog

This is the weblog for Pete Finnigan. Pete works in the area of Oracle security and he specialises in auditing Oracle databases for security issues. This weblog is aimed squarely at those interested in the security of their Oracle databases.

[Previous entry: "BBED - Oracle Block Browser and EDitor - A hacker tool?"] [Next entry: "Comments are enabled on this blog again"]

Users and Schemas

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> @afiedt.buf

User dropped.

User created.

Grant succeeded.

Grant succeeded.

Grant succeeded.


Schema created.


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.