Oracle 中的用户和模式之间的区别？

Difference between a user and a schema in Oracle?

提问by sengs

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

Oracle 中的用户和模式有什么区别？

采纳答案by 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.

SCOTT 是一个模式，包括 EMP、DEPT 和 BONUS 表以及各种授权和其他内容。

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

SYS 是一个包含大量表、视图、授权等的模式。

SYSTEM is a schema.....

SYSTEM 是一个模式.....

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.

从技术上讲——模式是数据库使用的一组元数据（数据字典），通常使用 DDL 生成。模式定义了数据库的属性，例如表、列和属性。数据库模式是对数据库中数据的描述。

回答by sleske

I believe the problem is that Oracle uses the term schemaslightly differently from what it generally means.

我认为问题在于 Oracle 使用的术语模式与其通常的含义略有不同。

1. 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
2. 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.")

1. Oracle 的模式（如 Nebakanezer 的回答中所述）：基本上是用户帐户拥有的所有表和其他对象的集合，因此大致相当于用户帐户
2. 架构一般：构成给定系统/应用程序数据库的所有表、进程等的集合（如“开发人员应与 DBA 讨论我们新应用程序的架构。”）

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

含义 2. 中的架构类似，但与含义 1 中的架构不同。例如，对于使用多个 DB 帐户的应用程序，含义 2 中的架构可能包含多个 Oracle 架构:-)。

Plus schemacan also mean a bunch of other, fairly unrelated things in other contexts (e.g. in mathematics).

Plus模式也可以表示其他上下文（例如数学）中的一堆其他相当不相关的事物。

Oracle should just have used a term like "userarea" or "accountobjects", instead of overloadin "schema"...

Oracle 应该只使用“userarea”或“accountobjects”之类的术语，而不是“schema”中的重载......

回答by 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.

• 模式是数据库对象的集合，包括逻辑结构，例如表、视图、序列、存储过程、同义词、索引、集群和数据库链接。
• 用户拥有一个架构。
• 用户和模式具有相同的名称。
• CREATE USER 命令创建一个用户。它还会自动为该用户创建架构。
• CREATE SCHEMA 命令并不像它暗示的那样创建“模式”，它只允许您在单个事务中创建多个表和视图并在您自己的模式中执行多个授权。
• 出于所有意图和目的，您可以将用户视为架构，将架构视为用户。

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

此外，如果用户有权限，他们可以访问模式中的对象而不是他们自己的模式。

回答by 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".

像往常一样考虑用户（具有登录和访问系统中某些对象的访问权限的用户名/密码）和模式作为用户主目录的数据库版本。用户“foo”通常在模式“foo”下创建事物，例如，如果用户“foo”创建或引用表“bar”，那么Oracle将假定用户的意思是“foo.bar”。

回答by granadaCoder

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.

我一直在为创建 N 个用户的想法而苦苦挣扎，我希望这些用户中的每一个都“使用”（也就是使用）一个模式。

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.

oracle-base.com 上的 Tim 展示了如何做到这一点（有 N 个用户，这些用户中的每一个都将“重定向”到单个模式。

He has a second "synonym" approach (not listed here). I am only quoting the CURRENT_SCHEMA version (one of his approaches) here:

他有第二个“同义词”方法（此处未列出）。我在这里只引用 CURRENT_SCHEMA 版本（他的方法之一）：

CURRENT_SCHEMAApproach

This method uses the CURRENT_SCHEMAsession 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.

CURRENT_SCHEMA方法

此方法使用CURRENT_SCHEMA会话属性自动将应用程序用户指向正确的架构。

首先，我们创建架构所有者和应用程序用户。

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;

请注意，应用程序用户可以连接，但没有任何表空间配额或创建对象的权限。

接下来，我们创建一些角色以允许读写和只读访问。

CREATE ROLE schema_rw_role;
CREATE ROLE schema_ro_role;

我们希望授予我们的应用程序用户对架构对象的读写访问权限，因此我们授予相关角色。

GRANT schema_rw_role TO app_user;

我们需要确保应用程序用户的默认架构指向架构所有者，因此我们创建了一个 AFTER LOGON 触发器来为我们执行此操作。

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;
/

现在我们准备在架构所有者中创建一个对象。

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;

请注意权限是如何授予相关角色的。没有这个，应用程序用户将看不到这些对象。我们现在有一个正常运行的架构所有者和应用程序用户。

SQL> CONN app_user/password
Connected.
SQL> DESC test_tab
 Name                                                  Null?    Type
 ----------------------------------------------------- -------- ------------------------------------
 ID                                                    NOT NULL NUMBER
 DESCRIPTION                                                    VARCHAR2(50)

SQL>

当应用程序用户只是主模式的替代入口点，不需要自己的对象时，此方法是理想的。

回答by shall

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.

可以授予用户访问不同用户拥有的模式对象的权限。

回答by 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.

Schema 是 DB.objects 关于一个想法/兴趣域的封装，由一个用户拥有。然后它将被其他具有抑制角色的用户/应用程序共享。所以用户不需要拥有模式，但模式需要有一个所有者。

回答by Prashant Mishra

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.

而模式是数据库对象的封装。这就像房子的主人拥有你房子里的所有东西，只有当主人即架构向它提供所需的授权时，用户帐户才能访问家中的货物。

回答by 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 .

--User 用户是连接数据库（服务器）的帐户。我们可以使用 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.

实际上Oracle数据库包含处理数据的逻辑和物理结构。模式也是逻辑结构来处理数据库中的数据（内存组件）。它在用户创建时由 oracle 自动创建。它包含由与该模式关联的用户创建的所有对象。例如，如果我创建了一个名为 santhosh 的用户，则 oracle 创建一个名为 santhosh 的模式，oracle 将用户 santhosh 创建的所有对象存储在 santhosh 中架构。

We can create schema by CREATE SCHEMA statement ,but Oracle Automatically create a user for that schema.

我们可以通过 CREATE SCHEMA 语句创建模式，但是 Oracle 会自动为该模式创建一个用户。

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.

我们可以使用 DROP SCHEMA schama_name RESTRICT 语句来删除模式，但它不能删除包含对象的模式，因此要删除模式它必须为空。这里的限制词强制指定没有对象的模式。

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.

如果我们尝试删除用户包含对象的模式，我们必须指定 CASCADE 字，因为 oracle 不允许您删除用户包含对象。DROP USER user_name CASCADE 所以oracle 删除模式中的对象，然后它自动删除用户，从其他模式（如视图和私有同义词）引用此模式对象的对象进入无效状态。

I hope now you got the difference between them,if you have any doubts on this topic,please feel free to ask.

我希望现在您已经了解了它们之间的区别，如果您对此主题有任何疑问，请随时提问。

Thank you.

谢谢你。

回答by 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.

对于大多数熟悉 MariaDB 或 MySQL 的人来说，这似乎并不令人困惑，因为在 MariaDB 或 MySQL 中，它们有不同的模式（包括不同的表、视图、PLSQL 块和数据库对象等），而用户是可以访问这些模式的帐户架构。因此，没有特定用户可以属于任何特定模式。已授予该架构的权限，然后用户可以访问它。用户和架构在 MySQL 和 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.

在 Oracle 模式中，用户几乎被视为相同。要使用该架构，您需要获得许可，您会觉得架构名称只是用户名。可以跨架构授予权限以访问来自不同架构的不同数据库对象。在 oracle 中，我们可以说用户拥有架构，因为当您创建用户时，您会为其创建 DB 对象，反之亦然。