EnterpriseDB: GRANT SCHEMA Oracle Tips by Burleson Consulting

You can grant CREATE, USAGE or ALL permissions to a user.  CREATE allows the user to create new objects in the schema, USAGE allows a user to "see" objects in the schema and ALL grants both CREATE and USAGE.

You can grant CREATE TO tom, USAGE to fred and ALL to bill on the schema "foo":

GRANT CREATE ON SCHEMA foo TO tom;
GRANT USAGE ON SCHEMA foo TO fred;
GRANT ALL ON SCHEMA foo TO bill;
REVOKE SCHEMA

You can also revoke those from the same users:

REVOKE CREATE ON SCHEMA foo FROM tom;
REVOKE USAGE ON SCHEMA foo FROM fred;
REVOKE ALL ON SCHEMA foo FROM bill;

DROP SCHEMA

You would drop a schema with the DROP SCHEMA command.  If the schema has objects, and you want to drop those objects, use the cascade keyword.

To drop the schema foo and drop all objects within the schema:

DROP SCHEMA foo CASCADE;

TABLESPACE

As discussed in Chapter 2, a tablespace is a way to physically separate database objects.  The pg_default and pg_global tablespaces are automatically created and if you do not specify a tablespace on create table commands, the pg_default tablespace is automatically used.  These tablespaces are created on ALL platforms.

* User defined tablespaces are not available on MS-Windows.  Tablespaces use symbolic links and only operating systems that support symbolic links can create tablespaces.  Linux and Solaris both support symbolic links and can create tablespaces.

CREATE TABLESPACE
CREATE TABLESPACE <tablespace name>
            [ OWNER username ]
            LOCATION 'directory'

We can create a tablespace named foo with a default location of /bar:

CREATE TABLESPACE foo LOCATION '/bar';
ALTER TABLESPACE
ALTER TABLESPACE name
            RENAME TO newname
            OWNER TO newowner

We can rename tablespace foo to foobar:

ALTER TABLESPACE foo
  RENAME TO foobar;


GRANT TABLESPACE

You can grant CREATE or ALL permissions to a user.  CREATE allows the user to create tables and indexes in the tablespace, ALL does the same.

You can grant CREATE TO tom on tablespace "foo":

GRANT CREATE ON TABLESPACE foo TO tom;
REVOKE TABLESPACE

You can also revoke create from tom:

REVOKE CREATE ON TABLESPACE foo FROM tom;
DROP TABLESPACE

You may drop a tablespace with the DROP TABLESPACE command. 

* You cannot drop a tablespace that contains objects.  You MUST drop those objects manually or DROP TABLESPACE will return an error.

You can drop the tablespace foo:

DROP TABLESPACE foo;

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