Schema, User and functional Id in Oracle

Question

I confused lot in oracle about schema, user and functional id. Let consider my two different cases

Case I :

Let us consider [email protected] we think SCOTT is user. while creating user alone it ll create a schema. Correct me If i am wrong. In this case while we were creating SCOTT user alone SCOTT schema was created. Suppose If we create another schema say X . Is this possible to SCOTT user owns X schema ?

Case II :

Let us consider [email protected] we think SCOTT is schema alone i-e which is created by schema command alone. If it is so then what is the use of schema w/o any user who is going to own it.

I heard oracle function ID is one which will connect several user/schema(i don't know whether I can put schema/user here ) in a data base. is there is difference b/w oracle functional ID with user/schema ?

Answer 1

Many people find this topic confusing, because we tend to bandy around USER and SCHEMA interchangeably, when they are in fact separate if related entities.

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

It is easy to demonstrate that USER and SCHEMA are distinct, because we change the current schema in the session. This just means we can reference objects in another user's schema without prefixing them with the owner's name.

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 doesn't have a table called T1 or he hasn't granted it to X. The only way X can see her own table is to prefix it with her own name, or switch the current schema back to herself.

---

To answer your first question, the schema always has the same name as the user. So it is not possible for SCOTT to own schema X; schema X is owned by user X.

To answer your second question, it is impossible to create a schema without a user.

True, there is a CREATE SCHEMA command, but this requires the prior creation of the user. It is actually not creating a schema but creating several database objects. In effect it is more of a ADD OBJECTS TO SCHEMA command.

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 pretty limited: we can create tables, views and indexes, and grant privileges on objects. The advantage of it is simply that we can create several objects in a single transaction, so that all the creates are rolled back if one fails. This is not possible when we run each create statement separately.

---

Not sure what you're thinking off when you mention "function ID". It's not a standard piece of Oracle functionality.

Answer 2

This does not define the difference between an owner and schema.

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

This guy shows how to do this (have N number of users...get "redirected" to a single schema.

I will paste his code as well, on the off-chance the URL link dies in the future.

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

He has a second "synonym" approach. But I am only pasting the CURRENT_SCHEMA version. AGAIN, I take NO credit for this. I just hate when someone says "your answer is at this link" and BOOM, the link is dead. :<

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

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

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.