Schema Management

In oracle a schema is a collection of logical structures of data, or schema objects, it is mostly associated with a user that owns the schema pertaining to a specific application. A schema can contain tables, PL/SQL procedures, functions, packages, views, sequences, synonyms and clusters. This logical separation of objects allows considerable flexibility in managing and securing your oracle database. The user who owns the objects within the schema has to assign access rights to other users in order for other users to run commands against those objects such as select, insert, etc. Basically when an owner of a schema creates a object only that user has access to that object.

Tablespaces

Before a schema can be created a tablespace must exists, the tablespace is a logical entity that contains schemas. Normally a dedicated tablespace is created specifically for application schema making sure that it is separate from the system tablespace. Tablespaces themselves will contain one of more data files depending on the size.

See tablespaces for more information about how to create, drop, etc.

Users

A schema is normally associated with a user which is then normally associated with an application. Oracle uses the least privilege rule, thus when a user gets created he has no privileges at all, the user will not even be allowed to connect to the database.

See users for information about how to create, drop and to give privileges to users.

Data Access

Oracle uses several means to control data access and the best way is to assign privileges and roles to users.

See data access for information about how to grant and revoke access and creating roles.