0. 前言
由於目前的專案採用 Oracle, 且整個資料庫是由客戶的 DBA 在管理, 為了安全上的考量, 在存取客戶現行使用者的資料表時, 只會提供廠商新的使用者帳號及密碼, 而不會提供現行使用者的帳號及密碼.
舉例而言, 假設 table: T2 由客戶的 USER2 所建立, 而客戶提供給廠商的使用者為 USER1; 雖然客戶已將 USER2.T2 這個 table 的權限開放給 USER1, 當然可以採用 USER2.T2 去存取.
但這樣會造成 參考文件1 提到的狀況:
- 每次使用用 Table 時都要加上 "USER2.".
- 程式碼寫死了 Schema 名稱, 一旦Schema名稱調整, 將有改不完的程式.
在網路上查了一下, 看來應該可以透過 Synonym (別名) 的方式來處理. Synonym 的用途如下:
- 主要用在縮短資料庫物件 (table, view, sequence or program unit) 的名稱, 不需實際指定 schema.
- 亦可用在如上述的資安議題.
以下茲分幾個步驟, 進行狀況模擬實測:
1. STEP 1: 客戶 DBA 的工作: 建立 Table Space
建立 TBS_USER1 及 TBS_USER2, 供模擬測試用.
-- =================================
-- TABLE SPACES
-- =================================
-- 查詢異動前的 Tablespace, 結果如 [圖1]
SELECT TABLESPACE_NAME, FILE_NAME, bytes / 1024/ 1024 MB FROM DBA_DATA_FILES;
-- 建立各個 TABLE SPACES
-- DROP TABLESPACE TBS_USER1 INCLUDING CONTENTS AND DATAFILES CASCADE CONSTRAINTS;
CREATE TABLESPACE TBS_USER1
DATAFILE 'D:\APP\ADMINISTRATOR\ORADATA\MYDB\DATAFILE\TBS_USER1_FILE01.DBF'
SIZE 20M AUTOEXTEND ON;
-- DROP TABLESPACE TBS_USER2 INCLUDING CONTENTS AND DATAFILES CASCADE CONSTRAINTS;
CREATE TABLESPACE TBS_USER2
DATAFILE 'D:\APP\ADMINISTRATOR\ORADATA\MYDB\DATAFILE\TBS_USER2_FILE01.DBF'
SIZE 20M AUTOEXTEND ON;
-- 查詢異動後的 Tablespace, 結果如 [圖2]
SELECT TABLESPACE_NAME, FILE_NAME, bytes / 1024/ 1024 MB FROM DBA_DATA_FILES;
-- ==============
-- 預期結果
-- ==============
/*
TABLESPACE_NAME FILE_NAME MB
------------------- -------------------------------------------------------------------------- -------
SYSTEM D:\APP\ADMINISTRATOR\ORADATA\MYDB\DATAFILE\O1_MF_SYSTEM_HWCSDHM4_.DBF 890
SYSAUX D:\APP\ADMINISTRATOR\ORADATA\MYDB\DATAFILE\O1_MF_SYSAUX_HWCSGGRZ_.DBF 500
USERS D:\APP\ADMINISTRATOR\ORADATA\MYDB\DATAFILE\O1_MF_USERS_HWCSJCSH_.DBF 5
UNDOTBS1 D:\APP\ADMINISTRATOR\ORADATA\MYDB\DATAFILE\O1_MF_UNDOTBS1_HWCSHZG8_.DBF 65
4 個資料列已選取.
TABLESPACE TBS_USER1 已建立.
TABLESPACE TBS_USER2 已建立.
TABLESPACE_NAME FILE_NAME MB
------------------- -------------------------------------------------------------------------- -------
SYSTEM D:\APP\ADMINISTRATOR\ORADATA\MYDB\DATAFILE\O1_MF_SYSTEM_HWCSDHM4_.DBF 890
SYSAUX D:\APP\ADMINISTRATOR\ORADATA\MYDB\DATAFILE\O1_MF_SYSAUX_HWCSGGRZ_.DBF 500
TBS_USER1 D:\APP\ADMINISTRATOR\ORADATA\MYDB\DATAFILE\TBS_USER1_FILE01.DBF 20
USERS D:\APP\ADMINISTRATOR\ORADATA\MYDB\DATAFILE\O1_MF_USERS_HWCSJCSH_.DBF 5
TBS_USER2 D:\APP\ADMINISTRATOR\ORADATA\MYDB\DATAFILE\TBS_USER2_FILE01.DBF 20
UNDOTBS1 D:\APP\ADMINISTRATOR\ORADATA\MYDB\DATAFILE\O1_MF_UNDOTBS1_HWCSHZG8_.DBF 65
6 個資料列已選取.
*/
[圖1] Tablespace 的狀況 (before)
[圖2] Tablespace 的狀況 (after)
2. STEP 2: 客戶 DBA 的工作: 建立使用者及權限
加入使用者, 並賦與權限 (USER1, USER2).
-- =================================
-- USERS
-- =================================
-- DROP USER USER1 CASCADE;
CREATE USER USER1 IDENTIFIED BY USER1 DEFAULT TABLESPACE TBS_USER1 QUOTA UNLIMITED ON TBS_USER1;
GRANT CONNECT,RESOURCE TO USER1;
GRANT CREATE ANY SYNONYM TO USER1;
-- DROP USER USER2 CASCADE;
CREATE USER USER2 IDENTIFIED BY USER2 DEFAULT TABLESPACE TBS_USER2 QUOTA UNLIMITED ON TBS_USER2;
GRANT CONNECT,RESOURCE TO USER2;
GRANT CREATE ANY SYNONYM TO USER2;
-- ==============
-- 預期結果
-- ==============
/*
User USER1 已建立.
Grant 成功.
Grant 成功.
User USER2 已建立.
Grant 成功.
Grant 成功.
*/
3. STEP 3: 客戶 USER2 的工作: 建立 Table
由 USER2 建立 table: T2, 並授權給 USER1 使用.
-- =================
-- 建立表格
-- =================
-- DROP TABLE T1;
CREATE TABLE T2(
C_ID INT GENERATED ALWAYS AS IDENTITY,
C_UID VARCHAR2(32),
C_NAME VARCHAR2(50)
) ;
-- =================
-- 新增資料
-- =================
INSERT INTO T2(C_UID, C_NAME)
VALUES(SYS_GUID(), 'JASPER');
COMMIT;
-- =================
-- GRANT table:T2 的權限
-- =================
GRANT SELECT, INSERT, UPDATE, DELETE
ON USER2.T2
TO USER1;
-- ==============
-- 預期結果
-- ==============
/*
Table T2 已建立.
1 資料列 已插入.
確認完成.
Grant 成功.
*/
4. STEP 4: 廠商 USER1 的工作: 建立 Synonym
由 USER1 建立對 USER2.T2 這個 table 的別名 (Synonym), 並進行存取.
-- =====================
-- 新增資料
-- =====================
INSERT INTO USER2.T2(C_UID, C_NAME)
VALUES(SYS_GUID(), 'JACK');
COMMIT;
-- =====================
-- 查詢資料
-- =====================
SELECT * FROM USER2.T2;
-- =====================
-- 雖然可以直接指定 schema name, 佰有沒有什麼其它的方式, 可以不用指定呢?
-- 方式1: View: 可以視為是 table or view 的重組
-- 方式2: Synonym: 可以視為是物件的別名, 例如: table, view, sequence or program unit
-- 這裡採用方式2, 且採用 PRIVATE SYNONYM
-- =====================
-- 這裡會發生權限不足的狀況, 要事先由 DBA 賦與權限
-- https://stackoverflow.com/questions/24830023/create-synonym-ora-01031-insufficient-privileges
-- GRANT CREATE ANY SYNONYM TO SYSCOM;
CREATE SYNONYM T2 FOR USER2.T2;
-- =====================
-- 新增資料
-- =====================
INSERT INTO T2(C_UID, C_NAME)
VALUES(SYS_GUID(), 'JASON');
COMMIT;
-- =====================
-- 查詢資料
-- =====================
SELECT * FROM T2;
-- ==============
-- 預期結果
-- ==============
/*
1 資料列 已插入.
確認完成.
C_ID C_UID C_NAME
---------- -------------------------------- --------------------------------------------------
1 7E62485357A04BFCB662DD72CD3AB75C JASPER
2 4F80105925A448C2B2CE434AF75215B4 JACK
Synonym T2 已建立.
1 資料列 已插入.
確認完成.
C_ID C_UID C_NAME
---------- -------------------------------- --------------------------------------------------
1 7E62485357A04BFCB662DD72CD3AB75C JASPER
2 4F80105925A448C2B2CE434AF75215B4 JACK
3 99494D08EF2D497DA90976CD6DC9900B JASON
*/
5. 結論
透過該專案, 增加了筆者對於 Oracle Synonym 的認識, 也算是一個收穫.
至於 參考文件1 所提 Oracle 版本的 Entity Framework 不支援 Synonym 的問題, 後續有時間, 再來試一下. 本專案主要以 Java 進行開發, 未採 Entity Framework, 筆者只是協助 ORACLE 資料庫的部份; 後續有時間再依 參考文件2, 3 來試試 C# 及 Entity Framework.
6. 參考文件
- 01. 黑暗執行緒, Entity Framework筆記：使用Oracle Synonym
- 02. 黑暗執行緒, 千呼萬喚始出來! Oracle官方版Entity Framework問市，邁入開發新時代
- 03. Yowko's Notes, 使用 Entity Framework 連線 Oracle - Database First
- 04. Oracle Help Center, SQL Language Reference: CREATE SYNONYM
- 05. StackOverflow, create synonym ora-01031 insufficient privileges
So well explained. I love to code in Java and I have compiled many Java programs in my life. This is the most sorted blog I have read about how to compile Java program. If you are a freelancer and looking for some challenging projects or high bid projects then sign up with Eiliana.com which is a global freelancing portal
回覆刪除