Difference between a user and a schema in Oracle

Question

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

User · Answer

Based on my little knowledge of Oracle    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

User · Answer

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

User · Answer

Well  I read somewhere that if your database user has the DDL privileges then it s a schema  else it s a user

User · Answer

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

User · Answer

A user account is like relatives who holds a key to your home  but does not own anything i e  a user account does not own any database object   no data dictionary     Whereas a schema is an encapsulation of database objects  It s like the owner of the house who owns everything in your house and a user account will be able to access the goods at the home only when the owner i e  schema gives needed grants to it

User · Answer

A schema and database users are same but if schema has owned database objects and they can do anything their object but user just access the objects  They can t DO any DDL operations until schema user give you the proper privileges

User · Answer

It s very simple   If USER has OBJECTS then call it SCHEMA else      call it USER end if    A user may be given access to schema objects owned by different Users

User · Answer

This answer does not define the difference between an owner and schema but I think it adds to the discussion   In my little world of thinking   I have struggled with the idea that I create N number of users where I want each of these users to  consume   aka  use  a single schema   Tim at oracle-base com shows how to do this  have N number of users and each of these users will be  redirected  to a single schema   He has a second  synonym  approach  not listed here   I am only quoting the CURRENT SCHEMA version  one of his approaches  here      CURRENT SCHEMA Approach      This method uses the CURRENT SCHEMA session attribute to automatically   point application users to the correct schema       First  we create the schema owner and an application user   CONN sys password AS SYSDBA  -- Remove existing users and roles with the same names  DROP USER schema owner CASCADE  DROP USER app user CASCADE  DROP ROLE schema rw role  DROP ROLE schema ro role   -- Schema owner  CREATE USER schema owner IDENTIFIED BY password   DEFAULT TABLESPACE users   TEMPORARY TABLESPACE temp   QUOTA UNLIMITED ON users   GRANT CONNECT  CREATE TABLE TO schema owner   -- Application user  CREATE USER app user IDENTIFIED BY password   DEFAULT TABLESPACE users   TEMPORARY TABLESPACE temp   GRANT CONNECT TO app user        Notice that the application user can connect  but does not have any   tablespace quotas or privileges to create objects       Next  we create some roles to allow read-write and read-only access   CREATE ROLE schema rw role  CREATE ROLE schema ro role        We want to give our application user read-write access to the schema   objects  so we grant the relevant role   GRANT schema rw role TO app user        We need to make sure the application user has its default schema   pointing to the schema owner  so we create an AFTER LOGON trigger to   do this for us   CREATE OR REPLACE TRIGGER app user after logon trg AFTER LOGON ON app user SCHEMA BEGIN   DBMS APPLICATION INFO set module USER   Initialized      EXECUTE IMMEDIATE  ALTER SESSION SET current schema SCHEMA OWNER   END          Now we are ready to create an object in the schema owner   CONN schema owner password  CREATE TABLE test tab     id          NUMBER    description VARCHAR2 50     CONSTRAINT test tab pk PRIMARY KEY  id      GRANT SELECT ON test tab TO schema ro role  GRANT SELECT  INSERT  UPDATE  DELETE ON test tab TO schema rw role        Notice how the privileges are granted to the relevant roles  Without   this  the objects would not be visible to the application user  We now   have a functioning schema owner and application user   SQL gt  CONN app user password Connected  SQL gt  DESC test tab  Name                                                  Null     Type  ----------------------------------------------------- -------- ------------------------------------  ID                                                    NOT NULL NUMBER  DESCRIPTION                                                    VARCHAR2 50   SQL gt        This method is ideal where the application user is simply an   alternative entry point to the main schema  requiring no objects of   its own

User · Answer

I believe the problem is that Oracle uses the term schema slightly differently from what it generally means    Oracle s schema  as explained in Nebakanezer s answer   basically the set of all tables and other objects owned by a user account  so roughly equivalent to a user account Schema in general  The set of all tables  sprocs etc  that make up the database for a given system   application  as in  Developers should discuss with the DBAs about the schema for our new application      Schema in sense 2  is similar  but not the same as schema in sense 1  E g  for an application that uses several DB accounts  a schema in sense 2 might consist of several Oracle schemas  -    Plus schema can also mean a bunch of other  fairly unrelated things in other contexts  e g  in mathematics    Oracle should just have used a term like  userarea  or  accountobjects   instead of overloadin  schema

User · Answer

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

User · Answer

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

User · Answer

--USER and SCHEMA  The both words user and schema are interchangeble thats why most people get confusion on this words below i explained the difference between them  --User User is a account to connect database Server   we can create user by using CREATE USER user name IDENTIFIED BY password    --Schema  Actually Oracle Database contain logical and physical strucutre to process the data The Schema Also Logical Structure to process the data in Database Memory Component   Its 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 createts a schema called santhosh oracle stores all objects created by user santhosh in santhosh schema   We can create schema by CREATE SCHEMA statement  but Oracle Automatically create a user for that schema   We can Drop the schema by using DROP SCHEMA schama name RESTRICT statement but it can not delete scehema contains objects so to drop schema it must be empty here the restrict word forcely specify that schema with out objects   If we try to drop a user contain objects in his schema we must specify CASCADE word because oracle does not allow you to delete user contain objects   DROP USER user name CASCADE  so oracle deletes the objects in schema and then it drops the user automatically Objects refered to this schema objects from other schema like views and private synonyms goes to invalid state   I hope now you got the difference between them if you have any doubts on this topic please feel free to ask   Thank you

User · Answer

Schema is an encapsulation of DB objects about an idea domain of intrest  and owned by ONE user  It then will be shared by other users applications with suppressed roles  So users need not own a schema  but a schema needs to have an owner

User · Answer

Schema is a container of objects  It is owned by a user

User · Answer

User  Access to resource of the database  Like a key to enter a house   Schema  Collection of information about database objects  Like Index in your book which contains the short information about the chapter   Look here for details

[oracle] Difference between a user and a schema in Oracle?

Examples related to oracle