詳解oracle使用者建立(create user)(下)

使用者建立完成以後，如果不授予一定的特權，什麼也做不了，包括登入到資料庫與資料庫建立會話。簡單的建立一個使用者，授予登入的密碼很簡單，但是要建立符合實際要求的使用者，比較複雜一些，使用者的建立涉及到表空間的選擇與空間配額，profile，許可權的授予，賬戶的安全等方面。

特權的授予是使用者建立過程中一個很重要的組成部分，關係到使用者能否有足夠的許可權去完成所要做的工作，又確保使用者的許可權不會過高，濫用特權，成為一個安全問題。

oracle中的特權分為兩種：

系統特權（system privilege）:允許使用者執行某種操作，該操作會影響資料字典。影響整個資料庫。

物件特權（object privilege）：允許使用者執行某種操作，該操作影響的是schema中的資料。

常用的系統特權有：

create session

restricted session

alter database

alter system

create tablespace

create table

create any table

drop any table

insert any table

update any table

delete any table

select any table

grant any object privilege

系統特權授予與回收的語法格式：

grant systemprivilege [, systemprivilege, …]

to username|rolename [, username|rolename, …]

[with admin option];

revoke systemprivilege [,… systemprivilege]

from username|rolename;

使用者oracleusr只擁有create session特權，下面我們在給他授予create any table 特權。

SQL> grant create any table to oracleusr;

Grant succeeded

SQL> conn oracleusr

Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.1.0

Connected as oracleusr@ORACL

SQL> create table testtable (id char(5),name varchar2(20));

Table created

SQL> alter table testtable modify (name varchar2(15));

Table altered

SQL> insert into testtable values('00001','smith');

1 row inserted

SQL> update testtable

2 set name='jone'

3 where id='00001';

1 row updated

SQL> select * from testtable;

ID NAME

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

00001 jone

SQL> delete from testtable;

1 row deleted

SQL> create index testtable_id_idx on testtable(id);

Index created

SQL> drop index testtable_id_idx;

Index dropped

SQL> drop table testtable;

Table dropped

從上面的演示可以看出一個create any table 特權的許可權範圍真大，可以允許執行很多操作。

SQL> create table hr.test (id char(5),name varchar2(20));

Table created

SQL> drop table hr.test;

drop table hr.test

ORA-01031: 許可權不足

SQL> insert into hr.test values('00001','smith');

insert into hr.test values('00001','smith')

ORA-01031: 許可權不足

SQL> select * from hr.test;

select * from hr.test

ORA-01031: 許可權不足

在別的模式中特權的範圍又是不同了，所有在授權的時候，務必清楚的一點是，使用者許可權的需求

與對特權的瞭解。就像上面一個簡單的create any table 並不簡單。

給oracleusr增加select any table 特權.

SQL> grant select any table to oracleusr;

Grant succeeded

SQL> conn oracleusr

Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.1.0

Connected as oracleusr@ORACL

SQL> select first_name,last_name

2 from hr.employees

3 where employee_id='100';

FIRST_NAME LAST_NAME

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

Steven King

檢視hr中的表。ok。

SQL> select * from sys.dba_users;

select * from sys.dba_users

ORA-00942: 表或檢視不存在

但是sys模式下的表不行了，oracle對select any table 做了一定的限制，保護sys模式中的資訊。

從上面的兩個特權create any table，select any table 可以見識到帶any特權的威力，所以在授予特權以前必須對特權有足夠的瞭解。對帶any的特權要小心授予。可以依據授予某個使用者的系統特權可以查詢dba_sys_privs資料字典。

SQL> select * from dba_sys_privs

2 where grantee='ORACLEUSR';

GRANTEE PRIVILEGE ADMIN_OPTION

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

ORACLEUSR SELECT ANY TABLE NO

ORACLEUSR CREATE SESSION NO

ORACLEUSR

CREATE ANY TABLE NO

SQL> grant create tablespace to oracleusr with admin option;

Grant succeeded

with admin option 可使使用者將相應的系統特權再授予別人。並且在revoke的時候相應的系統特權是不會級聯收回的.

SQL> conn oracleusr

Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.1.0

Connected as oracleusr@ORACL AS SYSDBA

SQL> grant create tablespace to hr;

Grant succeeded

SQL> select * from dba_sys_privs

2 where grantee='ORACLEUSR';

GRANTEE PRIVILEGE ADMIN_OPTION

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

ORACLEUSR SELECT ANY TABLE NO

ORACLEUSR CREATE TABLESPACE YES

ORACLEUSR CREATE SESSION NO

ORACLEUSR CREATE ANY TABLE NO

從查詢中可以看出create tablespace 的admin_option項是yes的.

SQL> revoke create tablespace from oracleusr;

Revoke succeeded

SQL> select * from dba_sys_privs

2 where grantee='ORACLEUSR';

GRANTEE PRIVILEGE ADMIN_OPTION

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

ORACLEUSR SELECT ANY TABLE NO

ORACLEUSR CREATE SESSION NO

ORACLEUSR CREATE ANY TABLE NO

create tablespace特權被回收了。

SQL> select * from dba_sys_privs

2 where grantee='HR';

GRANTEE PRIVILEGE ADMIN_OPTION

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

HR CREATE VIEW NO

HR UNLIMITED TABLESPACE NO

HR CREATE DATABASE LINK NO

HR CREATE SEQUENCE NO

HR CREATE TABLESPACE NO

HR CREATE SESSION NO

HR ALTER SESSION NO

HR CREATE SYNONYM NO

由oracleusr授予hr的create tablespace特權並沒有被級聯收回.

物件特權授予的語法格式：

grant {objectprivilege|all} [(columnname),

objectprivilege (columnname)]

on objectname

to {username|rolename|public}

[with grant option];

all:關鍵字表示所有的物件特權.

On:後面接物件名.

with grant option:與with admin option類似,使用者可以把對獲得的相應物件特權授予其他使用者。

revoke objectprivilege [,… objectprivilege]

on objectname

from username|rolename;

常用物件特權：

select:允許展現表，檢視，序列的資訊，還包括使用nextval產生下一個序列值。

insert:允許使用者對錶，檢視進行插入操作。

update：允許使用者更新表，檢視中的資料。

delete:允許使用者刪除一個表或者檢視。

index:允許使用者建立一個索引。

alter：允許使用者修改表或者序列的定義。

references:在建立一個外來鍵約束的時候，允許使用者參照一張表。該特權只能授予使用者，不能授予角色。

execute：允許使用者執行一個儲存函式或者過程.

SQL> revoke select any table from oracleusr;

Revoke succeeded

SQL> grant select on hr.employees to oracleusr with grant option;

Grant succeeded

在回收oracleusr在hr的表employees上的select許可權的時候，oracleusr授予oracle11g的相同的

許可權也被一起回收了,這一點不同於with admin option。

SQL> conn oracleusr

輸入口令:

已連線。

SQL> select count(*) from hr.employees;

COUNT(*)

----------

107

SQL> grant select on hr.employees to oracle11g;

授權成功。

SQL> conn oracle11g

輸入口令:

已連線。

SQL> select count(*) from hr.employees;

COUNT(*)

----------

107

SQL> conn system as sysdba

輸入口令:

已連線。

SQL> revoke select on hr.employees from oracleusr;

撤銷成功。

SQL> conn oracleusr

輸入口令:

已連線。

SQL> select count(*) from hr.employees;

select count(*) from hr.employees

*

第 1 行出現錯誤:

ORA-01031: 許可權不足

SQL> conn oracle11g

輸入口令:

已連線。

SQL> select count(*) from hr.employees;

select count(*) from hr.employees

*

第 1 行出現錯誤:

ORA-00942: 表或檢視不存在

oracle11g從oracleusr中獲得的物件特權被級聯回收了。

另外在許可權的授予中角色（role）很重要,一般不直接給使用者授予特權，而是想將需要的特權授予角色，然後將角色分配給使用者。

角色：就是一組特權的代表。

角色相關的語法格式：

建立角色：

create role rolename;

分配與回收角色：

grant rolename [, rolename]

to username [, username];

revoke rolename

from username|rolename;

刪除角色；

drop role rolename;

詳解oracle使用者建立(create user)(下)

相關文章