ChatGPT解决这个技术问题 Extra ChatGPT

Difference between a user and a schema in Oracle?

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

+1 I've always wondered about the distinction as well :-/.
There is an Interesting article below which clears all the doubts: http://radiofreetooting.blogspot.com/2007/02/user-schema.html
Oracle schemas are like My Documents folders in the Windows OS. A user can grant permissions to other users to see things in their schema. Oracle schema is essentially a user's workspace.

M
Mark Sherretta

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 the same page : for all intents and purposes just consider user = schema = user = schema = the same thing.
But Can I have two users using the same schema?
If you mean "can objects in a single schema be 'owned' by multiple users" the answer is No. If you mean "can objects in a single schema be used by multiple users" the answer is most certainly Yes
s
sleske

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"...


@djangofan Afaik this question is about Oracle, and not about MS SQL.
I agree, Oracle should not have used the term "schema", it makes it awkward for people new to Oracle. Something like "schema access" since it gives (re-usable) access to part (or whole) of a (real) schema.
h
harto

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.


good point re CREATE SCHEMA - a poorly chosen name for the command I would think!
"The CREATE SCHEMA command does not create a "schema" as it implies". I think 99% of the confusion comes from this. And this sentence fragment clears it up very well. Thank you.
I think this is best answer.
A
Andru Luvisi

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".


neat description but why have you used "foo" for both the user and the schema?! Do they have to be the same?
In Oracle, USER is the account name, SCHEMA is the set of objects owned by that user. Even though, Oracle creates the SCHEMA object as part of the CREATE USER statement and the SCHEMA has the same name as the USER but they are note the same thing. Of course, the confusion stems partly from the fact that there is a one-to-one correspondence between USER and SCHEMA, and a user's schema shares its name.
A
Air

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> CONN app_user/password Connected. SQL> DESC test_tab Name Null? Type ----------------------------------------------------- -------- ------------------------------------ ID NOT NULL NUMBER DESCRIPTION VARCHAR2(50) SQL> This method is ideal where the application user is simply an alternative entry point to the main schema, requiring no objects of its own.


Note the use of roles may not resolve permission issues for PL/SQL code. Object grants to roles are not propagated in an intuitive manner when stored procedures are compiled are run. However, I upvoted this answer because this approach is really great, but it's little-known and rarely used as far as I can tell.
P
Peter Mortensen

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.


Actually the confusion is created when people call - User is Schema. As user may not be schema as you explained. A user could simple be a user accessing some other User's Schema.
S
Sudheer

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.


S
SanthoshReddy

--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.


P
Peter Mortensen

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.


M
Mark Hall

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.


M
Michele La Ferla

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.


V
VINOTH ENERGETIC

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


p
pritampanhale

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.


P
Peter Mortensen

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


That implies that a user can own multiple schemas. I don't believe that's possible (in Oracle); while user A may have full admin rights over schema B, the latter will always be owned by user B, even if no one ever logs in with such a user name.
P
Peter Mortensen

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


This is actually a useful distinction - users with CREATE privs as distinct from those with no CREATE privs