EnterpriseDB: GRANT Database Oracle Tips by Burleson Consulting

You can grant TEMP, CREATE or ALL permissions to a user.  TEMP allows the user to create temporary tables on the database, CREATE allows the user to create new schemas and ALL grants both TEMP and CREATE.

You can grant CREATE TO tom, TEMP to fred and ALL to bill on the database "edb":

GRANT CREATE ON DATABASE edb TO tom;
GRANT TEMP ON DATABASE edb TO fred;
GRANT ALL ON DATABASE edb TO bill; 

edb=# GRANT CREATE ON DATABASE edb TO tom;
GRANT
edb=# GRANT TEMP ON DATABASE edb TO fred;
GRANT
edb=# GRANT ALL ON DATABASE edb TO bill;
GRANT
edb=#
REVOKE DATABASE

You can revoke ALL from bill and ALL from fred:

REVOKE ALL ON DATABASE edb FROM fred;
REVOKE ALL ON DATABASE edb FROM bill; 

edb=# REVOKE ALL ON DATABASE edb FROM fred;
REVOKE
edb=# REVOKE ALL ON DATABASE edb FROM bill;
REVOKE
edb=#
DROP DATABASE
DROP DATABASE <database name>

You can drop the database with this command.

DROP DATABASE foo;

SCHEMA

As discussed in Chapter 2, a schema is a way to logically separate database objects.  The PUBLIC schema is automatically created and if you do not specify a schema, the PUBLIC schema is automatically used.

If you use the AUTHORIZATION keyword, you can create a default schema for a user.

CREATE SCHEMA
CREATE SCHEMA <schema name>
[ AUTHORIZATION <user name> ]
[ schema element(s) ]

You can create a schema named foo:

CREATE SCHEMA foo;

You can create a schema, tom, for user tom (tom should already exist as a user):

CREATE SCHEMA tom AUTHORIZATION tom;
ALTER SCHEMA
ALTER SCHEMA <schema name>
            RENAME TO <new schema name>
            OWNER TO <new schema owner>

You can alter the schema foo and rename it to bar:

ALTER SCHEMA foo RENAME TO bar;
edb=# CREATE SCHEMA foo;

CREATE SCHEMA
edb=# create user tom;

CREATE ROLE
edb=# CREATE SCHEMA tom AUTHORIZATION tom;

CREATE SCHEMA
edb=# ALTER SCHEMA foo RENAME TO bar;

ALTER SCHEMA
edb=#

This is an excerpt from the book "EnterpriseDB: The Definitive Reference" by Rampant TechPress.