108

What is the difference (if any) between a database, a user, and a schema?

Improve this question
1

8 Answers 8

Reset to default
111

In Oracle, users and schemas are essentially the same thing. You can consider that a user is the account you use to connect to a database, and a schema is the set of objects (tables, views, etc.) that belong to that account.

See this post on Stack Overflow: difference between a User and a Schema in Oracle? for more details and extra links.

You create users with the create user statement. This also "creates" the schema (initially empty) - you cannot create a schema as such, it is tied to the user. Once the user is created, an administrator can grant privileges to the user, which will enable it to create tables, execute select queries, insert, and everything else.

The database is the thing that contains all the users you've created, and their data (and a bunch of predefined system users, tables, views, etc. that make the whole thing work). You should look at the Oracle Database Architecture documentation in the Concepts Guide (actually, that whole page is worth a read - there's a section about users and schemas higher up in that page) to get an introduction to what a database is, and what a database instance is - two important concepts.

You can create a database with the create database statement, once you've installed the Oracle software stack. But using dbca (database creation assistant) is easier to get started.

Improve this answer
5
  • 11
    "users and schemas are essentially the same thing" -- If that's true, that the stupidest naming I've ever heard of.
    – user64141
    Nov 20, 2019 at 21:51
  • 2
    Then how do you add users (as in separate logins) with different privileges without creating new schema per user? Are you supposed to use just one login credential per schema?
    – Dojo
    Dec 27, 2019 at 8:48
  • 1
    @Priyank: you create as many users as you want and give them whatever privileges they need. It is not unusual to gave a lot of users without any tables/views/whatever - whether their schema "exists" in that case is mostly a philosophical debate.
    – Mat
    Dec 28, 2019 at 12:18
  • 1
    @Priyank You don't. In Oracle, user and schema is the same thing. That's how the architecture of the Oracle database works. You can create a user who can just login to the database and cannot create objects in it, or you can create a schema as a container for objects without being able to login to the database, by setting appropriate privileges to the user; but in either case, the schema/user exists in the database.
    – Mike Rosoft
    Sep 3, 2021 at 10:42
  • 3
    @Dojo : (probably you already figure it out, but for future reference) To add another user with access to another user's table, it will be something like: grant create session to new_user identified by newpassword and then: grant select, insert, update, delete on other_user.other_table to new_user. More in this blog from oracle
    – lepe
    Jan 21, 2022 at 3:37
11

To begin to understand the difference I think we must start saying that this nomenclature was a mistake from Oracle.

I assume that if you have an ERP called TRITON you want your database to be named TRITON, or if my company it's called JENNY EXPORT I might decide my database to be called JENNYEXP or something like that, no need for any user with the same name.

In Oracle, you need to create a USER before creating the tables and weird enough this collection of TABLES is called SCHEMA.

Then you might create the actual users and grant proper permissions to work with such database as TRITON as per this example, by the way, if you connect thru SQL DEVELOPER you must NAME A CONNECTION.

I know I sound kind of frustrated but it makes more sense to me the MS SQL SERVER naming convention and I could add that I worked with Oracle earlier than SQL SERVER.

Improve this answer
2
  • 1
    Prior to Microsoft SQL Server 2005, the behavior in SQL Server was the same. Your Oracle experience that predates SQL Server may explain your perception here. User and schema were equivalent prior to 2005 in MSSQL. I hope this helps.
    – Pittsburgh DBA
    May 5, 2022 at 13:58
  • 1
    As someone with knowledge of other database systems but is new to Oracle - this is ridiculous. I'm not a fan of this oddity in Oracle design.
    – mikato
    Mar 22 at 19:56
7

Data Base.

The Data base is a collection of data,where actually data stored. Its a physical Memory Component Connected to a Computer Installed with DBMS Software to manipulate the data in that Memory component,the computer installed with dbms software is called Server or Database Server.The both Server(Computer) and Database(memory component) are different but Most of times refered as Same based on situation.

USER and SCHEMA

The both words, user and schema, are interchangeable, that's why most people get confused on these words.

User

User is a account to connect database(Server). we can create user by using CREATE USER IDENTIFIED BY .

Schema

Actually Oracle Database contain logical and physical structure to process the data. The Schema Also Logical Structure to process the data in Database(Memory Component). It's created automatically by Oracle when user created. It contains all objects created by the user associated to that schema.For Example if i created a user with name santhosh then oracle creates a schema called santhosh, Oracle stores all objects created by user santhosh in the santhosh schema.

We can create schema by using the CREATE SCHEMA statement, but Oracle automatically creates a user for that schema.

We can drop the schema by using the DROP SCHEMA <name> RESTRICT statement, but it can not delete schema contained objects, so to drop the schema, it must be empty. here the restrict word forces you to specify that schema with out objects.

If we try to drop a user containing objects in his schema we must specify CASCADE word because Oracle does not allow you to delete a user containing objects.

DROP USER <name> CASCADE

so Oracle deletes the objects in schema and then it drops the user automatically, Objects referred to these schema objects from other schema like views and private synonyms goes to invalid state.

Improve this answer
1
  • 1
    Users and schemas may be interchangeable in Oracle databases - but NOT in other rdbms like MS SQL Server.
    – Simon
    Feb 28, 2022 at 17:36
5

According to the way the terminology is sometimes used, a USER and a SCHEMA are somewhat similar. But there is also a major difference. A USER can be called a SCHEMA if the "USER" owns any object, otherwise it will only remain a "USER". Once the USER owns at least one object then by virtue of all of your definitions above... the USER can now be called a SCHEMA.

Improve this answer
1
  • I think what you're pointing out here is that the term 'SCHEMA' is multi-faceted. It is a namespace for database objects on one hand. Also, in Oracle (and others?) but not PostgreSQL, it is a database object namespace containing exactly the objects created by the user sharing the name of the schema. From another perspective, it is the relationships and structure of objects in a database, often ignoring both ownership and namespace.
    – Andrew Wolfe
    Jan 22, 2016 at 16:35
0

I would add that the statements above apply to Oracle's implementation but other databases including SQL Server and PostgreSQL use schema as just a namespace, i.e. a way to group objects. For example, the Staging schema could group all object used in staging data, the Accounting schema could group all objects related to Accounting.

Improve this answer
1
  • 1
    Not sure to what you refer when you say "statements above" but schema in SQL and in Oracle is a permissions grouping. In other words it is not just a way to group like objects together, but a way to group objects which will ease the management of permissions as you can grant permissions to a schema and therefore avoid having to grant permissions to each object within the schema.
    – Thronk
    Nov 23, 2015 at 2:29
-1

From my point of view some confusion regarding the terms "database", "schema" and "user" also comes from the differences between Microsoft SQL Server and Oracle.

  1. "Inside" Oracle the difference between schema and user/login is not to be understood without further explanation. "User" is an account to log in to the database (usually without own objects like tables or views), it can be called "schema" if it "owns" objects. But this has already been explained above.
  2. In MS SQL Server, the term "database" describes an object that is most comparable with "schema" in Oracle. Both are a combination of objects that are "qualified" by "database/schemaowner.object", note the "." as a separator between the ""combination"" and the actual object.

BTW: Note also the different meaning of "instance" in Microsoft SQL Server and Oracle.

Improve this answer
1
  • 1
    I believe you are confused. In SQL Server, the hierarchy is Instance > Database > Schema > Table. In Oracle, "instance" is not a collection of databases, but below that the schema is the same: Database > Schema > Table.
    – Jon of All Trades
    Jul 24, 2020 at 18:18
-3

User!= Schema , User and Schema are not same but they are used interchangeably

Improve this answer
2
  • 4
    Please add details regarding how they're different.
    – Erik
    Sep 10, 2015 at 14:55
  • This topics focuses on Oracle so they are interchangeable - but only for Oracle databases. In most other RDBMS like Postgresql, MS SQL and Co. a user is something you can log in and establish a connection with your database. Schemas are as mentioned above like namespaces to group users, tables, views, stored procedures and co.
    – Simon
    Feb 28, 2022 at 17:39
-4

I am telling you according to concepts not based on any type of database management system you are using.

Conceptually: Database : is Just a pile of data, mostly un-managed related or unrelated data.

Schema: Schema refers to formally structuring the unmanaged related/unrelated data so that it can be managed by some management system which understands the formal definition provided by the schema. Schema is basically a scheme to to provide overview. For example you have XSD which defines the schema to create XML. You have RDBMS which defines its own schemas which are based on Codd Rules which basically defines schemes to create RDBMS.

Now if you want to know more about is refer this Link.

User's use the formal language specified by the schema to access the Database

Improve this answer
0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.