Shulou(Shulou.com)06/01 Report--

Today, while reading the official Oracle documentation, I read the basic concepts of schema, which makes me wonder: what is the difference between user and schema? To gain a deeper understanding of the difference and relationship between the two, the following is an explanation of user and schema in the official documentation:

"A schema is a collection of database objects. A schema is owned by a database user and has the same name as that user. Schema objects are the logical structures that directly refer to the database's data. Schema objects include structures like tables, views, and indexes. (There is no relationship between a tablespace and a schema. Objects in the same schema can be in different tablespaces, and a tablespace can hold objects from different schemas."

"A user is a name defined in the database that can connect to and access objects. Schemas and users help database administrators manage database security."

It is quite clear in the official document:

Schema is a collection of data objects, including tables, views, indexes, synonyms, and so on, which can be said to be objects of schema. But not vivid enough, there is an online article in which the relationship between schema and user is described very thoroughly in a vivid metaphor, which is quoted as follows:

"user is the user in Oracle, which is similar to the concept of user in all systems, in which users hold the permissions and resources of the system, while schema covers a variety of objects, including the" location "of tables, functions, packages, and so on, and does not include permission control over them.

Schema in Oracle refers to the collection of all objects under a user. Schema itself cannot be understood as an object, oracle does not provide the syntax for creating schema, and schema is not created when user is created, but after the first object is created under that user, schema also comes into being. As long as there is an object under user, schema must exist, and if there is no object under user, schema does not exist. If you create a new user, if there is no object under that user, the schema does not exist, and if you create an object, a schema with the same name as the user is created. In fact, in use, shcema is exactly the same as user, with no difference, and the user name can also appear where the schema name appears.

The full name of an object in the database is schema.object, not user.object. Similarly, if we do not specify the schema of the object when we create the object, the schema of the object is the user's default schema.

It is like a house full of furniture. It is the owner of the house (user), not the house (schema), who has control over the furniture. You can also be the owner of a house (user) and own your own house (schema). You can enter other people's houses through alter session. If you don't specify it, everything you do is aimed at what's in your current house. Whether you have the right to use (select), move (update) or take away (delete) the furniture depends on whether the owner of the house gives you such permission, or you are the DBA of the whole DB. Alter session set schema can be used instead of synonyms. If you want to call other schema objects (if you have permission), but do not build synonym, but do not want to put other schema names in the code, you can use alter session set schema= first. "

This passage is very vivid and clearly illustrates the difference between user and schema. Here are some concrete examples to deepen the understanding of the difference between user and schema:

The first step is to log in to SQL as a sys user and set up an ordinary user wjq and seiang:

[oracle@seiang11g ~] $sqlplus / as sysdba

SYS@seiang11g > create user wjq identified by wjq

User created.

SYS@seiang11g > create user seiang identified by seiang

User created.

The second step is to give some basic permissions to the new users wjq and seiang:

SYS@seiang11g > grant connect,create table,resource to wjq,seiang

Grant succeeded.

Step 3, log in as a wjq user, create a table and insert data:

SYS@seiang11g > conn wjq/wjq

Connected.

WJQ@seiang11g > create table t (id int)

Table created.

WJQ@seiang11g > insert into t values (1)

1 row created.

WJQ@seiang11g > commit

Commit complete.

Step 4, log in as a seiang user to see if you can query the data in the table created by the wjq user:

SYS@seiang11g > conn seiang/seiang

Connected.

SEIANG@seiang11g > select table_name from user_tables

No rows selected

SEIANG@seiang11g > show user

USER is "SEIANG"

SEIANG@seiang11g > select * from wjq.t

Select * from wjq.t

*

ERROR at line 1:

ORA-00942: table or view does not exist

As can be seen from the above results, user seiang cannot view the contents of the table created by user wjq, and is even told that there is no such table.

Step 5: change the current schema to wjq, and continue to query:

SEIANG@seiang11g > alter session set current_schema=wjq

Session altered.

SEIANG@seiang11g > show user

USER is "SEIANG"

SEIANG@seiang11g > select * from wjq.t

Select * from wjq.t

*

ERROR at line 1:

ORA-00942: table or view does not exist

It still can't be viewed.

Step 6, log in as wjq user and give seiang user the right to view t-table:

SYS@seiang11g > conn wjq/wjq

Connected.

WJQ@seiang11g > grant select on t to seiang

Grant succeeded.

Step 7, log in as a seiang user and view the t table of the wjq user:

SYS@seiang11g > conn seiang/seiang

Connected.

SEIANG@seiang11g > select * from wjq.t

ID

-

one

More simply, changing the current schema to seiang simplifies the query process:

SEIANG@seiang11g > alter session set current_schema=wjq

Session altered.

SEIANG@seiang11g > select * from t

ID

-

one

After this experiment, we should have a better understanding of the difference and relationship between user and schema.

Author: SEian.G (hard practice changes in 72, but it is difficult to laugh at 81)

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.