十四：Oracle索引、權(quán)限
管理索引-原理介紹
介紹
索引是用于加速數(shù)據(jù)存取的數(shù)據(jù)對(duì)象。合理的使用索引可以大大降低i/o次數(shù)，從而提高數(shù)據(jù)訪問性能。索引有很多種我們主要介紹常用的幾種：
為什么添加了索引后，會(huì)加快查詢速度呢？
創(chuàng)建索引
單列索引
單列索引是基于單個(gè)列所建立的索引，比如：
create index 索引名 on 表名(列名);
復(fù)合索引
復(fù)合索引是基于兩列或是多列的索引。在同一張表上可以有多個(gè)索引，但是要求列的組合必須不同，比如：
create index emp_idx1 on emp (ename, job);
create index emp_idx1 on emp (job, ename);
使用原則
使用原則
1. 在大表上建立索引才有意義
2. 在where子句或是連接條件上經(jīng)常引用的列上建立索引
3. 索引的層次不要超過4層
這里能不能給學(xué)生演示這個(gè)效果呢？
如何構(gòu)建一個(gè)大表呢？
索引的缺點(diǎn)
索引缺點(diǎn)分析
索引有一些先天不足：
1. 建立索引，系統(tǒng)要占用大約為表1.2倍的硬盤和內(nèi)存空間來保存索引。
2. 更新數(shù)據(jù)的時(shí)候，系統(tǒng)必須要有額外的時(shí)間來同時(shí)對(duì)索引進(jìn)行更新，以維持?jǐn)?shù)據(jù)和索引的一致性。
實(shí)踐表明，不恰當(dāng)?shù)乃饕坏谑聼o補(bǔ)，反而會(huì)降低系統(tǒng)性能。因?yàn)榇罅康乃饕谶M(jìn)行插入、修改和刪除操作時(shí)比沒有索引花費(fèi)更多的系統(tǒng)時(shí)間。
比如在如下字段建立索引應(yīng)該是不恰當(dāng)?shù)模?
1. 很少或從不引用的字段；
2. 邏輯型的字段，如男或女（是或否）等。
綜上所述，提高查詢效率是以消耗一定的系統(tǒng)資源為代價(jià)的，索引不能盲目的建立，這是考驗(yàn)一個(gè)DBA是否優(yōu)秀的很重要的指標(biāo)。
其它索引
介紹
按照數(shù)據(jù)存儲(chǔ)方式，可以分為B*樹、反向索引、位圖索引；
按照索引列的個(gè)數(shù)分類，可以分為單列索引、復(fù)合索引；
按照索引列值的唯一性，可以分為唯一索引和非唯一索引。
此外還有函數(shù)索引，全局索引，分區(qū)索引...
對(duì)于索引我還要說：
在不同的情況，我們會(huì)在不同的列上建立索引，甚至建立不同種類的索引，請(qǐng)記住，技術(shù)是死的，人是活的。比如：
B*樹索引建立在重復(fù)值很少的列上，而位圖索引則建立在重復(fù)值很多、不同值相對(duì)固定的列上。
顯示索引信息
顯示表的所有索引
在同一張表上可以有多個(gè)索引，通過查詢數(shù)據(jù)字典視圖dba_indexs和user_indexs，可以顯示索引信息。其中dba_indexs用于顯示數(shù)據(jù)庫所有的索引信息，而user_indexs用于顯示當(dāng)前用戶的索引信息：
select index_name, index_type from user_indexes where table_name = '表名';
顯示索引列
通過查詢數(shù)據(jù)字典視圖user_ind_columns,可以顯示索引對(duì)應(yīng)的列的信息
select table_name, column_name from user_ind_columns where index_name = 'IND_ENAME';
你也可以通過pl/sql developer工具查看索引信息
管理權(quán)限和角色
介紹
介紹
這一部分我們主要看看oracle中如何管理權(quán)限和角色，權(quán)限和角色的區(qū)別在那里。
當(dāng)剛剛建立用戶時(shí)，用戶沒有任何權(quán)限，也不能執(zhí)行任何操作。如果要執(zhí)行某種特定的數(shù)據(jù)庫操作，則必須為其授予系統(tǒng)的權(quán)限；如果用戶要訪問其它方案的對(duì)象，則必須為其授予對(duì)象的權(quán)限。為了簡化權(quán)限的管理，可以使用角色。這里我們會(huì)詳細(xì)的介紹。看圖：
權(quán)限
權(quán)限
權(quán)限是指執(zhí)行特定類型sql命令或是訪問其它方案對(duì)象的權(quán)利，包括系統(tǒng)權(quán)限和對(duì)象權(quán)限兩種。
系統(tǒng)權(quán)限
系統(tǒng)權(quán)限介紹
系統(tǒng)權(quán)限是指執(zhí)行特定類型sql命令的權(quán)利。它用于控制用戶可以執(zhí)行的一個(gè)或是一組數(shù)據(jù)庫操作。比如當(dāng)用戶具有create table權(quán)限時(shí)，可以在其方案中建表，當(dāng)用戶具有create any table權(quán)限時(shí)，可以在任何方案中建表。oracle提供了100多種系統(tǒng)權(quán)限。
常用的有：
create session 連接數(shù)據(jù)庫 create table 建表
create view 建視圖 create public synonym 建同義詞
create procedure 建過程、函數(shù)、包 create trigger 建觸發(fā)器
create cluster 建簇
顯示系統(tǒng)權(quán)限
oracle提供了100多種系統(tǒng)權(quán)限，而且oracle的版本越高，提供的系統(tǒng)權(quán)限就越多，我們可以查詢數(shù)據(jù)字典視圖system_privilege_map，可以顯示所有系統(tǒng)權(quán)限。
select * from system_privilege_map order by name;
授予系統(tǒng)權(quán)限
一般情況，授予系統(tǒng)權(quán)限是由dba完成的，如果用其他用戶來授予系統(tǒng)權(quán)限，則要求該用戶必須具有grant any privilege的系統(tǒng)權(quán)限。在授予系統(tǒng)權(quán)限時(shí)，可以帶有with admin option選項(xiàng)，這樣，被授予權(quán)限的用戶或是角色還可以將該系統(tǒng)權(quán)限授予其它的用戶或是角色。為了讓大家快速理解，我們舉例說明：
1.創(chuàng)建兩個(gè)用戶ken，tom。初始階段他們沒有任何權(quán)限，如果登錄就會(huì)給出錯(cuò)誤的信息。
create user ken identfied by ken;
2 給用戶ken授權(quán)
1). grant create session, create table to ken with admin option;
2). grant create view to ken;
3 給用戶tom授權(quán)
我們可以通過ken給tom授權(quán)，因?yàn)?span lang="EN-US">with admin option是加上的。當(dāng)然也可以通過dba給tom授權(quán)，我們就用ken給tom授權(quán)：
1. grant create session, create table to tom;
2. grant create view to ken; --ok嗎？不ok
回收系統(tǒng)權(quán)限
一般情況下，回收系統(tǒng)權(quán)限是dba來完成的，如果其它的用戶來回收系統(tǒng)權(quán)限，要求該用戶必須具有相應(yīng)系統(tǒng)權(quán)限及轉(zhuǎn)授系統(tǒng)權(quán)限的選項(xiàng)（with admin option）。回收系統(tǒng)權(quán)限使用revoke來完成。
當(dāng)回收了系統(tǒng)權(quán)限后，用戶就不能執(zhí)行相應(yīng)的操作了，但是請(qǐng)注意，系統(tǒng)權(quán)限級(jí)聯(lián)收回的問題?[不是級(jí)聯(lián)回收！]
system --------->ken ---------->tom
(create session)(create session)( create session)
用system執(zhí)行如下操作：
revoke create session from ken; --請(qǐng)思考tom還能登錄嗎？
答案：能，可以登錄
對(duì)象權(quán)限
對(duì)象權(quán)限介紹
指訪問其它方案對(duì)象的權(quán)利，用戶可以直接訪問自己方案的對(duì)象，但是如果要訪問別的方案的對(duì)象，則必須具有對(duì)象的權(quán)限。
比如smith用戶要訪問scott.emp表（scott：方案，emp：表）
常用的有：
alter 修改 delete 刪除 select 查詢 insert 添加
update 修改 index 索引 references 引用 execute 執(zhí)行
顯示對(duì)象權(quán)限
通過數(shù)據(jù)字段視圖可以顯示用戶或是角色所具有的對(duì)象權(quán)限。視圖為dba_tab_privs
SQL> conn system/manager;
SQL> select distinct privilege from dba_tab_privs;
SQL> select grantor, owner, table_name, privilege from dba_tab_privs where grantee = 'BLAKE';
1.授予對(duì)象權(quán)限
在oracle9i前，授予對(duì)象權(quán)限是由對(duì)象的所有者來完成的，如果用其它的用戶來操作，則需要用戶具有相應(yīng)的（with grant option）權(quán)限，從oracle9i開始，dba用戶（sys，system）可以將任何對(duì)象上的對(duì)象權(quán)限授予其它用戶。授予對(duì)象權(quán)限是用grant命令來完成的。
對(duì)象權(quán)限可以授予用戶，角色，和public。在授予權(quán)限時(shí)，如果帶有with grant option選項(xiàng)，則可以將該權(quán)限轉(zhuǎn)授給其它用戶。但是要注意with grant option選項(xiàng)不能被授予角色。
1.monkey用戶要操作scott.emp表，則必須授予相應(yīng)的對(duì)象權(quán)限
1). 希望monkey可以查詢scott.emp表的數(shù)據(jù)，怎樣操作？
grant select on emp to monkey;
2). 希望monkey可以修改scott.emp的表數(shù)據(jù)，怎樣操作？
grant update on emp to monkey;
3). 希望monkey可以刪除scott.emp的表數(shù)據(jù)，怎樣操作？
grant delete on emp to monkey;
4). 有沒有更加簡單的方法，一次把所有權(quán)限賦給monkey？
grant all on emp to monkey;
2.能否對(duì)monkey訪問權(quán)限更加精細(xì)控制。（授予列權(quán)限）
1). 希望monkey只可以修改scott.emp的表的sal字段，怎樣操作？
grant update on emp(sal) to monkey
2).希望monkey只可以查詢scott.emp的表的ename，sal數(shù)據(jù)，怎樣操作？
grant select on emp(ename,sal) to monkey
...
3.授予alter權(quán)限
如果black用戶要修改scott.emp表的結(jié)構(gòu)，則必須授予alter對(duì)象權(quán)限
SQL> conn scott/tiger
SQL> grant alter on emp to blake;
當(dāng)然也可以用system，sys來完成這件事。
4.授予execute權(quán)限
如果用戶想要執(zhí)行其它方案的包/過程/函數(shù)，則須有execute權(quán)限。
比如為了讓ken可以執(zhí)行包dbms_transaction，可以授予execute權(quán)限。
SQL> conn system/manager
SQL> grant execute on dbms_transaction to ken;
5.授予index權(quán)限
如果想在別的方案的表上建立索引，則必須具有index對(duì)象權(quán)限。
如果為了讓black可以在scott.emp表上建立索引，就給其index的對(duì)象權(quán)限
SQL> conn scott/tiger
SQL> grant index on scott.emp to blake;
6.使用with grant option選項(xiàng)
該選項(xiàng)用于轉(zhuǎn)授對(duì)象權(quán)限。但是該選項(xiàng)只能被授予用戶，而不能授予角色
SQL> conn scott/tiger;
SQL> grant select on emp to blake with grant option;
SQL> conn black/shunping
SQL> grant select on scott.emp to jones;
回收對(duì)象權(quán)限
在oracle9i中，收回對(duì)象的權(quán)限可以由對(duì)象的所有者來完成，也可以用dba用戶（sys，system）來完成。
這里要說明的是：收回對(duì)象權(quán)限后，用戶就不能執(zhí)行相應(yīng)的sql命令，但是要注意的是對(duì)象的權(quán)限是否會(huì)被級(jí)聯(lián)收回？【級(jí)聯(lián)回收】
如：scott------------->blake-------------->jones
select on emp select on emp select on emp
SQL> conn scott/tiger@accp
SQL> revoke select on emp from blake
請(qǐng)大家思考，jones能否查詢scott.emp表數(shù)據(jù)。
答案：查不了了（和系統(tǒng)權(quán)限不一樣，剛好相反）
聯(lián)系客服
