Difference between a user and a schema in Oracle?

2019-01-03 07:16发布

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

标签: oracle
15条回答
Ridiculous、
2楼-- · 2019-01-03 07:56

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

查看更多
兄弟一词,经得起流年.
3楼-- · 2019-01-03 07:57

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

查看更多
Lonely孤独者°
4楼-- · 2019-01-03 07:59

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.

查看更多
女痞
5楼-- · 2019-01-03 07:59

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.

查看更多
成全新的幸福
6楼-- · 2019-01-03 07:59

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.

查看更多
够拽才男人
7楼-- · 2019-01-03 07:59

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

查看更多
登录 后发表回答