背景環境
schema的特點
- schema概念像名稱空間
- schema下不能再有schema巢狀
- 各個物件比如表,函式等存放在各個schema下
- 同一個schema下不能有重複的物件名字,但在不同schema下可以重複
使用schema的作用
- 方便管理多個使用者共享一個資料庫,但是又可以互相獨立.
- 方便管理眾多物件,更有邏輯性
- 方便相容某些第三方應用程式,建立物件時是有schema的
比如要設計一個複雜系統,由眾多模組構成,有時候模組間又需要有獨立性。各模組存放單獨的資料庫顯然是不合適的。 這時候使用schema來分類各模組間的物件,再對使用者進行適當的許可權控制,這樣邏輯也非常清晰。
常用資料庫中的schema異同
大多數資料庫都有schema或者同等意義的概念,但是含義和具體操作不同。
- PostgreSQL中,一個database下可以有多個schema。可以給schema指定一個owner,如果沒有指定,那麼當前使用者就是schema的預設owner。
- 在Oracle資料庫中不能直接新建一個schema,系統在建立一個使用者的同時為這個使用者建立一個同名的schem並作為該使用者的預設shcema。即schema的個數同user的個數相同,而且schema名字同user名字一一 對應並且相同。
- 在MySQL中沒有schema,所以建立一個database的效果和建立一個schema是相同的。我們可以簡單的理解為,MySQL中的database就是schema。
本次測試軟體環境如下
CentOS 7 x64
PostgreSQL 11.1
許可權矩陣
我們需要在PostgreSQL中建立一個database,並且在這個db下建立多個schema。每個schema有自己的owner,並且db owner可以操作所有schema。
簡單的許可權關係矩陣如下
user \ schema | S00 | S01 | S02 |
---|---|---|---|
db_demo_owner (db owner 主使用者) | Y | Y | Y |
schema_owner_01(子使用者) | N | Y | N |
schema_owner_02(子使用者) | N | N | Y |
初始化資料庫
Superuser建立使用者和資料庫
-- 使用superuser登入(admin是提前建立的superuser,避免直接使用postgres)
psql --username=admin --dbname=postgres --password
複製程式碼
-- 新建使用者,這個使用者將成為Master使用者
drop user if exists db_demo_owner;
create user db_demo_owner with password 'xxx';
-- 新建一個子使用者
drop user if exists schema_owner_01;
create user schema_owner_01 with password 'xxx';
-- 通過設定許可權組的方式讓主使用者擁有子使用者的許可權
-- db_demo_owner is member of schema_owner_01,即主使用者db_demo_owner擁有schema_owner_01的許可權
grant schema_owner_01 to db_demo_owner;
-- 另一個子使用者
drop user if exists schema_owner_02;
create user schema_owner_02 with password 'xxx';
grant schema_owner_02 to db_demo_owner;
複製程式碼
-- 新建一個測試用DB並分配給主使用者
drop database if exists db_demo;
create database db_demo with encoding='utf8' owner=db_demo_owner;
複製程式碼
主使用者建立schema(重點)
-- 主使用者登入
psql --username=db_demo_owner --dbname=db_demo --password
複製程式碼
-- 不指定schema owner,預設是當前使用者(主使用者)
drop schema s00 cascade;
create schema s00 ;
-- 用主使用者建立schema並設定子使用者為owner
drop schema s01 cascade;
create schema s01 authorization schema_owner_01 ;
drop schema s02 cascade;
create schema s02 authorization schema_owner_02 ;
複製程式碼
主使用者建表
-- 主使用者登入
psql --username=db_demo_owner --dbname=db_demo --password
-- 主使用者在每個schema中建表t00
create table s00.t00(id int);
insert into s00.t00 values(1);
create table s01.t00(id int);
insert into s01.t00 values(1);
create table s02.t00(id int);
insert into s02.t00 values(1);
複製程式碼
子使用者建表
不同子使用者在自己的schema中建表
-- 子使用者1 登入
psql --username=schema_owner_01 --dbname=db_demo --password
create table s01.t01(id int);
insert into s01.t01 values(1);
複製程式碼
-- 子使用者2 登入
psql --username=schema_owner_02 --dbname=db_demo --password
create table s02.t02(id int);
insert into s02.t02 values(1);
複製程式碼
驗證Owner
確認db owner
-- 主使用者是DB Owner
postgres=# \l db_demo
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
---------+---------------+----------+-------------+-------------+-------------------
db_demo | db_demo_owner | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
(1 row)
-- 主使用者在子使用者組裡面,即已經擁有子使用者的許可權
postgres=# \du *owner*
List of roles
Role name | Attributes | Member of
-----------------+------------+-----------------------------------
db_demo_owner | | {schema_owner_01,schema_owner_02}
schema_owner_01 | | {}
schema_owner_02 | | {}
複製程式碼
確認schema owner
-- 三個schema分屬於不同使用者
db_demo=> \dn s*
List of schemas
Name | Owner
------+-----------------
s00 | db_demo_owner
s01 | schema_owner_01
s02 | schema_owner_02
(3 rows)
複製程式碼
確認表owner
-- 五張表分屬於不同的使用者
db_demo=> \dt s*.
List of relations
Schema | Name | Type | Owner
--------+------+-------+-----------------
s00 | t00 | table | db_demo_owner
s01 | t00 | table | db_demo_owner
s01 | t01 | table | schema_owner_01
s02 | t00 | table | db_demo_owner
s02 | t02 | table | schema_owner_02
(5 rows)
複製程式碼
驗證訪問許可權
子使用者
沒有單獨授權的時候,子使用者僅能訪問自己的表
-- 子使用者1 登入
psql --username=schema_owner_01 --dbname=db_demo --password
-- 無許可權
db_demo=> select * from s00.t00;
ERROR: permission denied for schema s00
db_demo=> select * from s02.t02;
ERROR: permission denied for schema s02
-- 有許可權
db_demo=> select * from s01.t01;
id
----
1
(1 row)
複製程式碼
主使用者
主使用者能夠訪問所有表
-- 主使用者登入
psql --username=db_demo_owner --dbname=db_demo --password
-- 都有許可權
db_demo=> select * from s00.t00;
id
----
1
(1 row)
db_demo=> select * from s01.t01;
id
----
1
(1 row)
db_demo=> select * from s02.t02;
id
----
1
(1 row)
複製程式碼
變更schema owner
s02原來屬於owner02,現在變更為owner01
-- 主使用者登入
psql --username=db_demo_owner --dbname=db_demo --password
-- 變更前
db_demo-> \dn s02
List of schemas
Name | Owner
------+-----------------
s02 | schema_owner_02
(1 row)
-- 變更schema的owner到另一個子使用者
alter schema s02 owner to schema_owner_01;
-- 變更後
db_demo=> \dn s02
List of schemas
Name | Owner
------+-----------------
s02 | schema_owner_01
(1 row)
複製程式碼
重要提示:僅僅變更owner並不能修改已有表的許可權,必須通過顯式賦權
-- 變更指定表owner
alter table s02.t02 owner to schema_owner_01;
-- 或者在不變更owner的情況下,批量賦權schema下的所有表
grant all on all tables in schema s02 to schema_owner_01;
複製程式碼
只讀許可權
經過前面的操作,子使用者02已經沒有任何訪問許可權了。我們希望這個子使用者在所有schema都有隻讀許可權。
-- 主使用者登入
psql --username=db_demo_owner --dbname=db_demo --password
-- 重要提示:這種方式僅對已經存在的表有效。以後建立的表不會自動有隻讀許可權
grant usage on schema s00, s01, s02 to schema_owner_02;
grant select on all tables in schema s00, s01, s02 to schema_owner_02;
複製程式碼
如果我們希望以後建立的所有新表都可以自動獲得只讀許可權(對已經存在的表無效),可以使用如下語句。
-- 對子使用者01,02以後在schema s00,s01,s02下新建的表都有效
alter default privileges
for user schema_owner_01, schema_owner_02
in schema s00, s01, s02
grant select on tables to schema_owner_02;
複製程式碼