Oracle對象許可權

標籤：blog   http   io   ar   使用   strong   sp   資料   on   

對象許可權指訪問其他方案的權利，使用者直接存取自己的方案的對象，但是如果要訪問別的方案的 對象，則必須具有對象的許可權。

比如smith使用者要訪問scott.emp表（scott：方案，emp：表），則不需再scott.emp 表上具有對象的許可權。

常用的對象許可權：

Alter --修改(修改表結構) delete --刪除

Select --查詢 insert --添加

Update --修改(更新資料) index --索引

References --引用 execute --執行

顯示對象許可權

通過資料字典視圖可以顯示使用者或角色所具有的對象許可權：dba_tab_privs;

授予對象許可權

在oracle9i前，授予對象許可權是由對象的所有者來完成的，如果用其他的使用者來操 作，則需要使用者具有相應的(with grant option)許可權，從oracle9i開始，dba 使用者(sys,system)可以將任何對象上的對象許可權授予其他使用者，授予對象許可權是用 grant命令來完成的。

對象許可權可以授予使用者，角色，和public。在授予許可權時，如果帶有with grant option選項，則可以將該許可權轉授給其他使用者，但是要注意with grant option 選項不能被授予角色。

小案例

1.monkey使用者要操作scott.emp表，則必須授予相應的對象許可權

a) 希望monkey可以查詢scott.emp的表資料，怎樣操作？

首先建立一個monkey使用者

SQL> create user monkey identified by m123;

User created

給monkey使用者授權

SQL> grant create session to monkey;

Grant succeeded

可以查看monkey使用者具有串連資料庫的許可權

SQL> conn monkey/m123;

Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.1.0

Connected as monkey

用monkey使用者查看scott方案的emp表，得到了失敗的提示

SQL> select * from scott.emp;

select * from scott.emp

ORA-00942: 表或視圖不存在

串連到scott使用者，讓scott使用者為monkey使用者授權

SQL> conn scott/tiger;

Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.1.0

Connected as scott

SQL> grant select on emp to monkey;

Grant succeeded

下面就是我們想要的結果：

SQL> select * from scott.emp;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

----- ---------- --------- ----- ----------- --------- --------- ------

8881 test使用者 MANAGER 7782 2010-12-21 23.00 23.00 10

......

15 rows selected

b) 希望monkey可以修改scott.emp表的資料，怎樣操作？

Sql> Grant update on emp to monkey;

c) 希望monkey可以刪除scott.emp表的資料，怎樣操作？

Sql> Grant delete on emp to monkey;

d) 一次性將所有對scott.emp表的資料操作的許可權授予monkey

Sql> Grant all on mep to monkey;

2.能否對monkey存取權限更加精細控制（授予列許可權）

a) 希望monkey只可以修改scott.emp表的sal欄位，怎樣操作？

Grant update on emp(sal) to monkey;

b) 希望monkey只可以查詢scott.emp表的ename，sal資料，怎樣操作？

Grant select on emp(ename,sal) to monkey;

3.授予alter許可權

如果monkey使用者要修改scott.emp表的結構，則必須授予alter對象許可權

Sql> conn scott/tiger;

Sql> grant alter on emp to monkey;

當然也可以由sys,system來完成此事。

4.授予execute許可權

如果使用者想要執行其他方案的包/過程/函數，則必須有execute許可權。

比如為了讓monkey使用者可以執行dbms_transaction，可以授execute許可權

Sql> conn system/manger;

Sql> grant execute on dbms_transaction to monkey;

5.授予index許可權

如果想在別的方案的表上建立索引，則必須具有index對象許可權，如：為了讓monkey 使用者可以子scott.emp上建立索引，就給其index的對象許可權

Sql> conn scott/tiger;

Sql> grant index on scott.emp to monkey with grant option

6.使用with grant option選項

該選項用於轉授對象許可權，但是該選項只能被授予使用者，而不能授予角色

Sql> conn scott/tiger;

Sql> grant select on emp to monkey with grant option;

Sql> conn monkey/m123;

Sql> grant select on scott.emp to anybody;

回收對象許可權

在oracle9i中，收回對象的許可權可由對象的所有者來完成，也可以由dba使用者 (sys,system)來完成。

注意：收回對象許可權後，使用者就不能執行相應的sql命令，對象許可權可以級聯回收。

文法：revoke 對象許可權 on 對象 from 使用者；

1.定義

2.對象許可權有哪些

如何賦給對象許可權

系統許可權

系統許可權：用於控制使用者可以執行的一個或者一組資料庫操作。比如當使用者具有create table許可權時，可以在其他方案中建表；當使用者具有create any table許可權時，可以在任何方案中建表。Oracle提供了100多種系統許可權。

常用的有：

Create session --串連資料庫

Create view --建立視圖

Create table --建表

Create procedure --建過程、函數、包

Create trigger --鍵觸發器

Create cluster --鍵簇

Create public synonym --鍵同義字

顯示系統許可權

可以通過查詢資料字典視圖system_privilege_map;可以顯示所有的系統許可權：

Select * from system_privilege_map order by name;

授予系統許可權

一般情況下，授予系統許可權是由dba完成的，如果由其他使用者來授予系統許可權，則要 求該使用者必須具有grant any privilege的系統許可權；在授予時可以帶with admin option選項，這樣被授予系統許可權的使用者或角色可以將該系統許可權授予其他使用者戶角 色。

SQL> create user ken identified by m123;

User created

SQL> grant create session,create table to ken with admin option;

Grant succeeded

SQL> grant create view to ken;

Grant succeeded

SQL> conn ken/m123;

Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.1.0

Connected as ken

回收系統許可權

一般情況下，回收系統許可權是dba來完成的，如果其他使用者來回收系統許可權，要求該 使用者必須具有相應的系統許可權以及轉授系統許可權的選項(with admin option)。回 收系統許可權使用revoke命令來完成。系統許可權是不能級聯回收。

SQL> revoke create session from ken;

Revoke succeeded

 

 

 

 

商店售貨系統設計案例：

 

現有一個商店的資料庫，記錄客戶及其購物情況，有下面三個表組成：

 

商品goods（商品號goodsId，商品名goodsName，單價unitprice，商品類別category，供應商provider）；

客戶表customer（客戶號customerId，姓名name，住址address，電郵email，性別sex，省份證cardID）;

購買purchase（客戶號customerId，商品號goodsId，購買數量nums）；

 

用sql語言完成下列功能:

1.建表，在定義中要求聲明：

a) 每個表的主鍵

b) 客戶的姓名不可為空

c) 單價必須大於0，購買數量必須在1~30之間

d) 電郵不能重複

e) 客戶的性別必須是男或者女，預設為男

 

Goods表

SQL> create table goods(goodsId char(8) primary key,

2 goodsName varchar2(30),

3 unitprice number(10,2) check(unitprice >0),

4 category varchar2(8),

5 provider varchar2(30));

Table created

 

Customer表

SQL> create table customer(customerId char(8) primary key,

2 name varchar2(50) not null,

3 address varchar2(50),

4 email varchar2(50) unique,

5 sex char(2) default ‘男‘ check(sex in(‘男‘,‘女‘)),

6 cardId char(18) );

Table created

 

Purchase表

SQL> create table purchase( customerId char(8) references customer(customerId),

2 goodsId char(8) references goods(goodsId),

3 nums number(10) check(nums between 1 and 30) );

Table created

 

如果在建立表時忘記建立必要的約束，則可以在建表後使用alter table命令為表增加約束。但是注意，增加not null約束時，需要使用modify選項，而增加其他四種約束使用add選項。

 

2.修改表

 

a)每個表的主外碼

b)客戶的姓名不可為空；增加商品名也不可為空

SQL> alter table goods modify goodsName not null;

Table altered

 

c)單價必須大於0,購買數量必須在1~30之間

d)電郵不能重複；增加省份證不能重複

SQL> alter table customer add constraint cardUnique unique(cardId);

Table altered

 

e)客戶的性別必須是男或者女，預設為男

f)增加客戶的住址只能是海澱、朝陽、東城、西城、通州、崇文。

SQL> alter table customer add constraint addressCheck check(address in(‘東城‘,‘西城‘,‘海澱‘,‘朝陽‘,‘通州‘,‘崇文‘));

Table altered

Oracle對象許可權