这个答案没有定义所有者和模式之间的区别，但我认为它增加了讨论的内容。

在我小小的思想世界里:

我一直纠结于这样一个想法:我创建了N个用户，其中每个用户都“消费”(也就是使用)单个模式。

Tim在oracle-base.com上演示了如何做到这一点(有N个用户，每个用户将被“重定向”到单个模式。

他还有第二种“同义词”方法(这里没有列出)。这里我只引用了CURRENT_SCHEMA版本(他的方法之一):

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.

模式和数据库用户是一样的，但如果模式拥有数据库对象，他们可以对对象做任何事情，但用户只能访问对象，他们不能做任何DDL操作，直到模式用户给你适当的特权。

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

我在某处读到，如果您的数据库用户拥有DDL特权，那么它就是一个模式，否则它就是一个用户。

这个答案没有定义所有者和模式之间的区别，但我认为它增加了讨论的内容。

在我小小的思想世界里:

我一直纠结于这样一个想法:我创建了N个用户，其中每个用户都“消费”(也就是使用)单个模式。

Tim在oracle-base.com上演示了如何做到这一点(有N个用户，每个用户将被“重定向”到单个模式。

他还有第二种“同义词”方法(这里没有列出)。这里我只引用了CURRENT_SCHEMA版本(他的方法之一):

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.

一个用户帐户就像持有你家钥匙的亲戚，但不拥有任何东西，即一个用户帐户不拥有任何数据库对象…没有数据字典…

而模式是数据库对象的封装。这就像房子的主人拥有你房子里的所有东西，只有当主人(即模式)给它所需的授权时，用户帐户才能访问家里的商品。