همانطور که می دانید، user به منظور اتصال و مدیریت دیتابیس ایجاد می شود و schema هم مجموعه ای از objectها نظیر جدول، ایندکس، ویو و … تحت یک نام می باشد در این مطلب تفاوتهای user و schema را در دو دیتابیس پستگرس و اوراکل تشریح خواهیم کرد.

در دیتابیس اوراکل، با ایجاد user، به صورت خودکار schema هم ایجاد خواهد شد و به عبارتی دقیق تر، با ایجاد اولین object برای یک user، به آن user، اسکیما(schema) هم گفته می شود و دستور مجزایی برای ساخت schema وجود ندارد.

البته دستور CREATE SCHEMA که در اوراکل وجود دارد عملا schemaای را ایجاد نخواهد کرد و صرفا امکان ساخت چندین شی را از طریق یک دستور فراهم می سازد برای مثال با توجه به آنکه کاربر usef2 در دیتابیس موجود نیست، دستور زیر با خطا متوقف خواهد شد:

SQL> create schema authorization usef2

create table t1 (c1 number)

create table t2 (c2 number); 

ORA-02421: missing or invalid schema authorization identifier

در صورت اتصال به کاربر usef، خواهیم دید که دستور فوق بدون خطا اجرا خواهد شد:

SQL> conn usef/a

Connected.

SQL> create schema authorization usef

create table t1 (c1 number)

create table t2 (c2 number);

Schema created.

اوراکل هم نکته زیر را در مورد create schema تصریح کرده است:

البته در اوراکل 18c مفهوم جدیدی به نام Schema Only Account اضافه شد که امکان ساخت user را بدون تعیین متد AUTHENTICATION  فراهم خواهد کرد که استفاده از این قابلیت، منتج به عدم امکان لاگین مستقیم به این نوع از کاربران خواهد شد.

SQL> CREATE USER myschema NO AUTHENTICATION;

User created

SQL> conn myschema

ORA-01017: invalid username/password; logon denied

اما در دیتابیس پستگرس مسئله کمی متفاوت است در این محیط  user که در سطح database server(یا همان کلاستر) ایجاد می شود، مجزا از schema وبرای اتصال و مدیریت دیتابیس استفاده خواهد شد.

–لیست کاربران موجود در کلاستر:

postgres=# \du

                                   List of roles

 Role name |                         Attributes                         | Member of

———–+————————————————————+———–

 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}

–ایجاد کاربری با نام vahid:

postgres=# create user vahid with password ‘p’;

CREATE ROLE

postgres=# \du

                                   List of roles

 Role name |                         Attributes                         | Member of

———–+————————————————————+———–

 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}

 vahid     |                                                            | {}

با اتصال به دیگر دیتابیس موجود در کلاستر، خواهیم دید که کاربر vahid در این دیتابیس هم موجود است:

postgres=# \c usefdb

usefdb=# \du

                                   List of roles

 Role name |                         Attributes                         | Member of

———–+————————————————————+———–

 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}

 vahid     |                                                            | {}

برخلاف اوراکل، در پستگرس، دستور مجزایی برای ساخت schema وجود دارد و schema در این محیط، در سطح دیتابیس(نه در سطح database server) ایجاد خواهد شد.

usefdb=# create table tbl1 as select * from pg_tables;

SELECT 69

usefdb=# \d

        List of relations

 Schema | Name | Type  |  Owner

——–+——+——-+———-

 public | tbl1 | table | postgres

در صورت عدم تعیین نام schema، پستگرس به صورت پیش فرض از اسکیمای public استفاده خواهد کرد. ساخت schema با دستور create schema قابل انجام است:

usefdb=# create schema myschema;

CREATE SCHEMA

برای تغییر schemaی پیش فرض می توان از دستور زیر استفاده کرد(در سطح session):

usefdb=# set search_path = ‘myschema’,”$user”,public;

SET

postgres=# show search_path;

        search_path

—————————

 myschema, “$user”, public

usefdb=# create table tbltest as select * from pg_tables;

SELECT 71

usefdb=# \dt

           List of relations

  Schema  |  Name   | Type  |  Owner

———-+———+——-+———-

 myschema | tbl1    | table | vahid

 myschema | tbltest | table | vahid

 public   | t1      | table | postgres

زمانی که کاربر برای یک schema، شی ای را ایجاد می کند، owner آن شی هم خواهد شد.

postgres=# \c usefdb vahid

usefdb=# create table myschema.tbl1 as select * from pg_tables;

SELECT 69

usefdb=# \dt myschema.*

        List of relations

  Schema  | Name | Type  | Owner

———-+——+——-+——-

 myschema | tbl1 | table | vahid

(1 row)

همانطور که می بینید، در کنار schema، اصطلاح دیگری به نام owner هم داریم که نام کاربری که شی را ایجاد کرده است، مشخص می کند. تا زمانی که کاربری owner یک objectی باشد، نمی توان آن کاربر را حذف کرد:

usefdb=# \c usefdb postgres

usefdb=# drop user vahid;

ERROR:  role “vahid” cannot be dropped because some objects depend on it

DETAIL:  owner of table myschema.tbl1

البته با اجرای دستور زیر می توان همه objectهای متعلق به یک کاربر را به کاربر دیگری تخصیص داد.

usefdb=# REASSIGN OWNED BY vahid TO ali;

REASSIGN OWNED

usefdb=# \dt myschema.*

        List of relations

  Schema  | Name | Type  | Owner

———-+——+——-+——-

 myschema | tbl1 | table | ali

توجه: دستور REASSIGN در سطح دیتابیس اجرا می شود نه database server.