Schema, user and functional identifier in Oracle

Question

I confused the party in oracle about the schema, user and functional identifier. Consider two different cases:

Case I:

Consider SCOTT@ORCL. If we think SCOTT is a user. when creating a user, he will create a diagram. Correct me if I am wrong. In this case, when we created SCOTT, only the SCOTT SCOTT scheme was created. Suppose that if we create another circuit, say X. Is it possible that the user of SCOTT owns the X circuit?

Case II:

Consider SCOTT@ORCL. If we think SCOTT is just an ie schema, which is created only by the schema command. If so, what is the use of the scheme without any user who will own it.

I heard that the oracle function identifier is the one that will connect multiple users / schema (I don't know if I can put the schema / user here) into the database. is there a distinctive b / w oracle functional identifier with user / schema?

Answer 1

Many people find this topic confusing because we tend to be interchangeable between USER and SCHEMA when they are actually separate if related to them.

A schema is a collection of database objects owned by a user. When we create a user, we create our scheme at the same time. Initially, their scheme is empty.

It is easy to demonstrate that USER and SCHEMA are different because we are changing the current schema in the session. This means that we can refer to objects in another custom schema without specifying the name of the owner.

SQL> desc t1 Name Null? Type ----------------------------------------- -------- ------------- ID NUMBER SQL> alter session set current_schema=APC 2 / Session altered. SQL> desc t1 ERROR: ORA-04043: object t1 does not exist SQL> sho user USER is "X" SQL> 

In this case, either APC does not have a table called T1, or he did not provide it with X. The only way X can see its own table is to prefix it with its own name or switch the current scheme back to itself.

---

To answer your first question, the schema always has the same name as the user. Therefore, SCOTT cannot have circuit X; scheme X belongs to user X.

To answer the second question, it is impossible to create a diagram without a user.

True, there is a CREATE SCHEMA command, but this requires preliminary user creation. This does not actually create a schema, but creates several database objects. In fact, it is rather the ADD OBJECTS TO SCHEMA team.

 SQL> conn sys as sysdba Enter password: Connected. SQL> create user x identified by x 2 default tablespace users quota 10m on users 3 / User created. SQL> grant create session, create table to x 2 / Grant succeeded. SQL> conn x/x Connected. SQL> create schema authorization x 2 create table t1 (id number) 3 create table t2 (id number) 4 / Schema created. SQL> select table_name from user_tables 2 / TABLE_NAME ------------------------------ T1 T2 SQL> 

The CREATE SCHEMA command is quite limited: we can create tables, views and indexes and provide privileges for objects. The advantage of this is that we can create several objects in one transaction so that all created files are rolled back if they are not executed. This is not possible if we run each create statement separately.

---

Not sure what you think when you mention "Feature ID". This is not a standard part of Oracle functionality.

Answer 2

This does not distinguish between the owner and the scheme.

But I always struggled with the idea that I create N number of users .... when I want each of these users to "consume" (aka, use) one scheme.

This guy shows how to do this (for N number of users ... is "redirected" to one scheme.

I will also embed his code, through an independent link, the URL will die in the future.

http://www.oracle-base.com/articles/misc/schema-owners-and-application-users.php

He has a second synonym approach. But I only insert the version of CURRENT_SCHEMA. AGAIN, I do not relate to this. I just hate it when someone says “your answer is on this link” and BOOM, the link is dead .: & L;

.................................................. ....

(from http://www.oracle-base.com/articles/misc/schema-owners-and-application-users.php )

CURRENT_SCHEMA Approach

This method uses the session attribute CURRENT_SCHEMA to automatically point application users to the correct schema.

First, we create the schema owner and 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; 

Please note that the application user can connect, but does not have table quotas or privileges for creating objects.

Then we create several roles to allow read-only and read-only access.

 CREATE ROLE schema_rw_role; CREATE ROLE schema_ro_role; 

We want to provide the user with the ability to read and write circuit objects to users, so we provide an appropriate role.

 GRANT schema_rw_role TO app_user; 

We need to make sure that the user of the application has its own default scheme, indicating the owner of the scheme, so we create an AFTER LOGON trigger for this.

 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 from the owner of the circuit.

 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; 

Please note how privileges are granted to the respective roles. Without this, objects will not be visible to the application user. Now we have a current 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 when the user of the application is just an alternative entry point into the main circuit, without requiring their own objects.