Oracle Database:Connect與Resource之前世今生 (S )

Size: px

Start display at page:

Download "Oracle Database:Connect與Resource之前世今生 (S )"

差象冉
6 years ago
Views:

Oracle Database:Connect 與 Resource 之前世今生作者 : 申建忠審稿 : 張智凱文章編號 :S070406205 出刊日期 :2007/04/30 資料庫類型 :Oracle 8i/9i/10g 本文相關資訊 : 本文難易等級 : 易難前言城門城門雞蛋糕,36 把刀騎白馬帶把刀, 走進城門滑一跤這首童謠相信大家都耳熟能詳, 而且也可以琅琅上口

1 Oracle Database:Connect 與 Resource 之前世今生作者 : 申建忠審稿 : 張智凱文章編號 :S 出刊日期 :2007/04/30 資料庫類型 :Oracle 8i/9i/10g 本文相關資訊 : 本文難易等級 : 易難前言城門城門雞蛋糕,36 把刀騎白馬帶把刀, 走進城門滑一跤這首童謠相信大家都耳熟能詳, 而且也可以琅琅上口但是它的原文並非如此, 而是城門城門幾丈高, 三十六丈高騎大馬, 帶把刀, 走進城門繞一遭, 問你吃桔子吃香蕉那為什麼會有這樣大的差異呢? 其實因為這首童謠來自中國安徽, 是隨著早年由大陸撤退來台的小朋友所傳入, 可能是某個台灣小朋友, 在與大陸來的小朋友玩耍時, 聽到這首童謠時, 因為很喜歡, 所以便將其背起來並加以傳唱, 可是因為口音的問題, 便將幾丈高誤解為雞蛋糕, 繞一遭改為滑一跤於是多年之後, 全台灣的人都是唱著城門城門雞蛋糕,36 把刀騎白馬帶把刀, 走進城門滑一跤這個故事除掉討人厭的政治問題後, 其實還代表著一個現象, 那就是一般的人都因循茍且地依照過去的習慣, 而不加以深究, 為何城門與雞蛋糕有關係, 當然我也是其中一份子同樣的狀況套用在 Oracle 的世界中, 就好像 DBA 總是按照著不知道是何人所寫下的步驟, 不論 Oracle 的版本作業系統平台或資料量是否有改變, 就一步一步照本宣科般地下指令這樣的動作, 當然可能依然完成所要的工作, 但是若無法了解所下的指令或動作, 背後所其代表的真正含意一旦整體環境有所變化, 這樣的操作步驟有可能一樣可以完成工作, 但可能不再是最佳的方法因為有些操作在新的 Oracle 版本中, 使用新的指令, 可以更快速地完成工作或者是在不同的資料量下, 需要採用不同的操作指令, 才能最佳地完成工總編 : 張智凱主編 : 楊先民社務顧問 : 邱世萍技術編輯 : 羅慧真編輯顧問 : 許薰尹羅慧真張書源胡百敬許嘉仁申建忠楊先民陳俊宇高光弘鄭淑芬何致億美術設計 : 魏吉芝張鈴玉陳淑娟行銷企劃 : 曹雅瑩陳秀慧許克臻李苾芬發行服務 : 謝佩珊蔡美雲發行所 : 毅達行銷顧問股份有限公司客戶服務 :service@dbworld.com.tw 本電子刊物之所載標誌名稱分屬各該公司所有, 非經授權請勿轉載使用, 版權所有如經查證依法律追訴 1

作當然如果只是慢一點完成而已, 那麼因循茍且就算了但是有些是安全性的改變, 一定要採用最新的指令, 才能保護資料庫的安全, 那麼這樣的變化就不能不加以重視了所以以下主要是針對 Connect 及 Resource 這兩個常用的角色進行深入的剖析, 分析這兩個角色在過去與現在 Oracle 的版本中是否有所不同, 以及在安全性上的變化角色的用途在 Oracle 資料庫中,

2 作當然如果只是慢一點完成而已, 那麼因循茍且就算了但是有些是安全性的改變, 一定要採用最新的指令, 才能保護資料庫的安全, 那麼這樣的變化就不能不加以重視了所以以下主要是針對 Connect 及 Resource 這兩個常用的角色進行深入的剖析, 分析這兩個角色在過去與現在 Oracle 的版本中是否有所不同, 以及在安全性上的變化角色的用途在 Oracle 資料庫中, 安全性是最重要的一件事為了保護資料庫中的資料,Oracle 採用正面表列的方式, 即藉由權限 (privilege) 的授予, 控制使用者所能執行的操作, 如果沒有被授予權限或權限被收回, 則無法進行相關的操作因此當一個新的使用者被建立後, 預設是沒有任何權限, 也就是一開始使用者無法做任何事, 直到被授予正確的權限為止透過這樣的設計可以保護資料庫中資料的安全, 不過當授予或收回權限給個別使用者時, 通常需要授予或收回多次, 這樣會花費資料庫管理人員 (DBA) 大量的時間與心力因此 Oracle 利用角色 (Role) 來簡化權限的管理,DBA 先建立一個角色, 再將必要的權限授予給該角色, 最後將角色授予給使用者, 則使用者便擁有該角色所被授予的權限 2

3 事先定義的角色所謂事先定義的角色, 是指那些在建立資料庫的同時,Oracle 預先建立與授予權限的角色, 主要是為了減輕 DBA 的負擔以下是事先定義的角色出場的順序 : Oracle6:DBA,RESOURCE,EXP_FULL_DATABASE,IMP_FULL_DATABASE Oracle7:DBA,RESOURCE,EXP_FULL_DATABASE,IMP_FULL_DATABASE, Oracle8i: DBA,RESOURCE,EXP_FULL_DATABASE,IMP_FULL_DATABASE,,SELECT_CATALO G_,DELETE_CATALOG_,EXECUTE_CATALOG_ 這些角色中以與 RESOURCE 最為 DBA 所熟知一般 DBA 建立使用者後, 通常都會立刻授予角色給使用者, 這樣使用者就可以連線到資料庫建立表格 (TABLE) 等基本權限若此使用者是程式設計人員, 那麼 DBA 就會將 RESOURCE 角色也授予使用者, 讓其擁有一些開發資料庫程式所需要的權限事實上有許多 DBA 根本不知道他自己到底給予了使用者那些權限, 只是依循以前的步驟也不知道這些角色中, 其實隱藏著一些安全漏洞角色的變化如同之前所言, 角色是為了簡化權限的管理所以當 DBA 授予某個角色給使用者時, 當然必須先了解這個角色, 到底是由那些權限所組成的 DBA 可以查詢下列的視圖 (VIEW) 來得到這些資訊通常是查詢 _SYS_PRIVS( 系統權限授予給那些角色 ) 或 DBA_SYS_PRIVS( 系統權限授予給那些角色或使用者 ): SQL> desc role_sys_privs 名稱空值? 類型 NOT NULL VARCHAR2(30) NOT NULL VARCHAR2(40) ADMIN_OPTION VARCHAR2(3) SQL> desc dba_sys_privs 名稱空值? 類型 GRANTEE NOT NULL VARCHAR2(30) NOT NULL VARCHAR2(40) ADMIN_OPTION VARCHAR2(3) 不過有一點好玩的地方, 就是查詢 _SYS_PRIVS 中, 無法找到或 RESOURCE 兩個角色的資訊, 必須查詢 DBA_SYS_PRIVS 才可以得到有關的資訊 SQL> select privilege from role_sys_privs where role=''; SQL> select privilege from dba_sys_privs where grantee=''; CREATE VIEW ALTER SESSION 3

4 CREATE SYNONYM CREATE DATABASE LINK SQL> select privilege from role_sys_privs where role='resource'; SQL> select privilege from dba_sys_privs where grantee='resource'; 所以當使用者被授予角色後, 他便擁有 CREATE VIEW,,ALTER SESSION,,,CREATE SYNONYMN,,CREATE DATABASE LINK 等權限, 這也就是為何要授予角色給一般使用者的原因, 可以簡化權限管理的複雜度 SQL> create user frank identified by oracle; 已建立使用者. SQL> connect ERROR: ORA-01045: user FRANK lacks privilege; logon denied 警告 : 您已不再與 ORACLE 相連 SQL> connect system/oracle@o9ir2 SQL> grant connect to frank; SQL> connect frank/oracle@o9ir2 USER 為 "FRANK" ALTER SESSION 4

5 CREATE SYNONYM CREATE VIEW CREATE DATABASE LINK 但是在 Oracle10g R2 之後, 角色所內含的權限僅剩下一個, 其餘的權限都不再被預設授予給角色所以如果 DBA 還是因循過去的方法建立使用者, 那麼這個使用者只能建立連線但無法進行其他的動作, 若 DBA 不能深究授予角色的原因, 一旦出現權限不足的問題, 就無法很快地解決問題 SQL> select privilege from dba_sys_privs where grantee=''; SQL> create user frank identified by oracle; 已建立使用者. SQL> grant connect to frank; SQL> connect USER 為 "FRANK" 為了解決 Oracle10g R2 後角色的內容更動,DBA 可以利用下列兩種方式來解決所遇到的問題 : 1. DBA 另外建立一個新的角色, 將其餘的權限 (CREATE VIEW,,ALTER SESSION,, CREATE SYNONYMN,,CREATE DATABASE LINK) 授予給新建的角色, 之後找到有那些使用者或角色已經被授予角色, 最後將新建的角色授予給那些使用者即可 SQL> create role new_connect; SQL> grant create table,create view,create cluster,create synonym,create sequence,create database link, alter session to new_connect; SQL> select grantee from dba_role_privs where granted_role=''; GRANTEE WMSYS IX 5

6 PM OLAP_USER MDDATA FRANK SYS EXFSYS MDSYS SCOTT 已選取 10 個資料列. SQL> grant new_connect to frank; SQL> connect USER 為 "FRANK" NEW_ ALTER SESSION CREATE SYNONYM CREATE VIEW CREATE DATABASE LINK 2. 執行 %ORACLE_HOME%\rdbms\admin\rstrconn.sql(Windows 平台 ) 或 $ORACLE_HOME/rdbms/admin/rstrconn.sql(UNIX 平台 ), 將 CREATE VIEW,CREATE TABLE,ALTER SESSION,, CREATE SYNONYMN,CREATE SEQUENCE,CREATE DATABASE LINK 再授予給角色, 使角色的內容恢復到 O10gR2 之前的樣子 SQL> select privilege from dba_sys_privs where grantee=''; SQL>@c:\oracle\product\10.2.0\db_1\rdbms\admin\rstrconn.sql 順利授權確認完成 SQL> select privilege from dba_sys_privs where grantee=''; CREATE VIEW ALTER SESSION 6

7 CREATE SYNONYM CREATE DATABASE LINK RESOURCE 角色的秘密由之前的資訊可以得知,RESOURCE 角色擁有,,CREATE CLUSTER,,,,CREATE INDEXTYPE, 這些權限, 所以被授予這個角色的使用者, 就間接擁有這些權限這些權限大多數是當進行資料庫程式開發時, 建立一些資料庫物件所必須的權限, 所以一般的 DBA 通常會直接授予 RESOURCE 角色給程式設計人員, 讓他們可以順利地進行開發工作但是在建立資料庫物件之前, 使出者必須先要建立資料庫連線, 也就是同時必須擁有的權限也就是如此, 就形成文章前面所形容的情形, 當 DBA 建立一個新的使用者時, 便會順便授予與 RESOURCE 兩個角色給使用者, 讓使用者可以順利地進行他的工作不過在 Oracle 資料庫中, 建立 SEGMENT( 即需要空間放資料的物件, 如 TABLE,CLUSTER,INDEX 等等 ) 物件時, 除了要建立相關物件的權限外, 還要擁有該物件放所放置的表格空間 (TABLESPACE) 上的空間配額 (QUOTA) 未超過而空間配額的設定可以在建立使用者的同時或之後再更改配額, 不過重要是, 空間配額預設是沒有, 所以沒有設定就沒有配額 SQL> create user testuser1 identified by oracle; 已建立使用者. SQL> grant create table to testuser1; SQL> connect testuser/oracle@o10gr2 ERROR: ORA-01045: user TESTUSER lacks privilege; logon denied 警告 : 您已不再與 ORACLE 相連 SQL> connect system/oracle@o10gr2 SQL> grant create session to testuser1; SQL> connect testuser/oracle@o10gr2 USER 為 "TESTUSER1" SQL> create table test1 (a number) tablespace users; create table test1 (a number) tablespace users * ERROR 在行 1: 7

8 ORA-01950: 沒有表格空間 'USERS' 的權限 SQL> connect SQL> alter user testuser1 quota 10M on users; 已更改使用者. SQL> connect USER 為 "TESTUSER1" SQL> create table test1 (a number) tablespace users; 已建立表格. 當授予 RESOURCE 角色給使用者時, 有個相信是絕大多數的 DBA 都不知道的安全漏洞, 那就是使用者可以不需任何表格空間的配額, 只要有建立物件的權限, 即可在任何的表格空間上建立物件為什麼? 這是因為當授予 RESOURCE 角色時,Oracle 資料庫會順便授予 UNLIMITED TABLESPACE 權限給使用者, 擁有此權限的使用者不需要配額即可將物件建立在任何的表格空間上除了失去原本設計配額這項屬性的意義外, 同時有可能造成整個資料庫中, 物件胡亂放置的問題, 影響資料庫的管理與安全 SQL> create user testuser2 identified by oracle; 已建立使用者. SQL> select privilege from dba_sys_privs where grantee='testuser2'; SQL> grant connect,resource to testuser2; SQL> select privilege from dba_sys_privs where grantee='testuser2'; UNLIMITED TABLESPACE SQL> select privilege from dba_sys_privs where grantee=''; SQL> select privilege from dba_sys_privs where grantee='resource'; SQL> connect USER 為 "TESTUSER2" RESOURCE 8

9 UNLIMITED TABLESPACE 已選取 10 個資料列. 這個問題的解決方法有兩種 : 1. 直接將 UNLIMITED TABLESPACE 收回即可 SQL> select privilege from dba_sys_privs where grantee='testuser2'; UNLIMITED TABLESPACE SQL> revoke unlimited tablespace from testuser2; 順利撤銷. SQL> select privilege from dba_sys_privs where grantee='testuser2'; SQL> connect testuser2/oracle@o10gr2 USER 為 "TESTUSER2" RESOURCE 已選取 9 個資料列. 2. 新建一個角色, 取代 RESOURCE 原本的功能, 這樣便可以避免這個問題發生 9

10 SQL> create role new_resource; 已建立角色. SQL> select privilege from dba_sys_privs where grantee='resource'; SQL> grant create trigger,create sequence,create type,create procedure,create cl uster,create operator,create indextype,create table to new_resource; SQL> select privilege from dba_sys_privs where grantee='new_resource'; SQL> create user testuser3 identified by oracle; 已建立使用者. SQL> select privilege from dba_sys_privs where grantee='testuser3'; SQL> grant connect,new_resource to testuser3; SQL> select privilege from dba_sys_privs where grantee='testuser3'; SQL> connect USER 為 "TESTUSER3" NEW_RESOURCE 10

11 已選取 9 個資料列. 結論看過以上對及 RESOURCE 兩個角色的說明後, 相信可以了解這兩個角色背後所代表的真正意涵而且 Oracle10g R2 開始對這個角色進行權限的縮減, 表示未來角色將越來越不重要, 更有可能在未來的版本中消失不見同時未來 RESOURCE 角色也逐漸步入的後塵, 所以往後 DBA 在使用或 RESOURCE 角色時, 一定要先弄清楚後, 再使用它們, 這樣才可以一方面利用到角色所帶來的好處及便利, 另一方面卻不會造成安全有關的問題申建忠現任職恆逸資訊教育訓練處資深顧問講師, 擁有 Oracle9i OCM Oracle8i/9i OCP MCSD MCSE CCNA 認證, 專長為 Oracle 資料庫規劃 / 建置 / 管理 / 調校資料庫應用軟體開發資料倉儲設計等, 並為專欄作家 11

PowerPoint Presentation

PowerPoint Presentation 数据库培训项目研究 Oracle 索引探究 B*tree 索引与位图索引的特点作者 : 赵超 2008 年 12 月 18 日实验环境 Windows-server2003 内存 :2G Oracle 10.2.0 ORACLE_SID=orcl 索引类型 B*tree 索引 ( 默认方式 ) 位图索引 (bitmap) 反