Difference between a user and a schema in Oracle?

Viewed 279523

What is the difference between a user and a schema in Oracle?

15 Answers

From Ask Tom

You should consider a schema to be the user account and collection of all objects therein as a schema for all intents and purposes.

SCOTT is a schema that includes the EMP, DEPT and BONUS tables with various grants, and other stuff.

SYS is a schema that includes tons of tables, views, grants, etc etc etc.

SYSTEM is a schema.....

Technically -- A schema is the set of metadata (data dictionary) used by the database, typically generated using DDL. A schema defines attributes of the database, such as tables, columns, and properties. A database schema is a description of the data in a database.

From WikiAnswers:

  • A schema is collection of database objects, including logical structures such as tables, views, sequences, stored procedures, synonyms, indexes, clusters, and database links.
  • A user owns a schema.
  • A user and a schema have the same name.
  • The CREATE USER command creates a user. It also automatically creates a schema for that user.
  • The CREATE SCHEMA command does not create a "schema" as it implies, it just allows you to create multiple tables and views and perform multiple grants in your own schema in a single transaction.
  • For all intents and purposes you can consider a user to be a schema and a schema to be a user.

Furthermore, a user can access objects in schemas other than their own, if they have permission to do so.

Think of a user as you normally do (username/password with access to log in and access some objects in the system) and a schema as the database version of a user's home directory. User "foo" generally creates things under schema "foo" for example, if user "foo" creates or refers to table "bar" then Oracle will assume that the user means "foo.bar".

For most of the people who are more familiar with MariaDB or MySQL this seems little confusing because in MariaDB or MySQL they have different schemas (which includes different tables, view , PLSQL blocks and DB objects etc) and USERS are the accounts which can access those schema. Therefore no specific user can belong to any particular schema. The permission has be to given to that Schema then the user can access it. The Users and Schema is separated in databases like MySQL and MariaDB.

In Oracle schema and users are almost treated as same. To work with that schema you need to have the permission which is where you will feel that the schema name is nothing but user name. Permissions can be given across schemas to access different database objects from different schema. In oracle we can say that a user owns a schema because when you create a user you create DB objects for it and vice a versa.

Related