【九】用戶訪問控制

9.1 認識Oracle用戶

Oracle數據庫是多用戶系統，每個用戶一個賬戶。

9.1.1查看數據庫裡有多少用戶？

SQL>select username from DBA_users;

9.1.2用戶默認的存儲空間

每個用戶賬戶都可以指定默認的表空間，用戶創建的任何對象（如表或索引）将缺省保存在此表空間中，如果創建用戶時沒有指定默認表空間，那麼該用戶缺省使用數據庫級的默認表空間。

9.1.3數據庫默認表空間

SQL> select * from database_properties where rownum<=10;

設置數據庫的默認表空間。

SQL> ALTER database default tablespace TABLESPACENAME;

9.2密碼認證介紹

用戶登錄時需要密碼認證，認證主要有幾種方法：

1）OS認證 用于sys用戶本地登錄

2）口令文件認證 用于sys用戶遠程登錄

3）數據庫（字典）認證 普通用戶本地和遠程登錄

以上三種用戶認證是最常用的，另外還有一些認證方式，比如：

4）外部密碼認證 Oracle用戶關聯OS用戶登錄

如果配置了os_authent_prefix參數，如缺省值為'ops$'，當數據庫中存在用戶'ops$tim'，且對該用戶啟用了外部驗證。那麼在操作系統上以tim用戶登錄成功後，就可以直接鍵入sqlplus / 登錄用戶是ops$tim，密碼由操作系統外部提供，不是數據字典認證。

示例：

1)用戶前綴缺省是OPS$

sqlplus / as sysdba

SQL> show parameter os_authent_prefix

NAME TYPE VALUE

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

os_authent_prefix string ops$

2) 建立Oracle的用戶，密碼是操作系統提供（外部）

SQL> create user ops$tim identified externally;

SQL> GRANT connect to ops$tim;

SQL> select USERNAME,PASSWORD from dba_users where username='OPS$TIM';

USERNAME PASSWORD

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

OPS$TIM EXTERNAL

3) 建立操作系統用戶

[root@cuug ~]# useradd tim

4）設置用戶環境變量

[root@cuug ~]#su - tim

[tim@cuug ~]$ vi .bash_profile (添加環境變量)

ORACLE_BASE=/u01

ORACLE_HOME=$ORACLE_BASE/oracle

ORACLE_SID=prod

PATH=$ORACLE_HOME/bin:$PATH

export ORACLE_BASE ORACLE_HOME ORACLE_SID PATH

[tim@cuug ~]$ source .bash_profile

5）以操作系統身份登錄

[tim@cuug ~]$ id

uid=501(tim) gid=502(tim) groups=502(tim)

[tim@cuug ~]$ sqlplus /

SQL*Plus: Release 11.2.0.1.0 Production on Sat Jul 2 22:25:12 2016

Copyright (c) 1982, 2009, Oracle. All rights reserved.

......

SQL> show user;

USER is "OPS$TIM"

SQL>

9.3空間配額的概念

配額（quota)是表空間中為用戶的對象使用的空間量，dba建立用戶時就應該考慮限制用戶的磁盤空間配額，否則無限制配額的用戶可能把你的表空間撐爆（甚至損壞system表空間）。創建用戶時，可以指定用戶的缺省表空間及其配額，然後才可以建表。

單一的用戶不需要臨時表空間和UNOD表空間的配額：

更改用戶配額的一些命令：

ALTER USER tim QUOTA 10m ON test_tbs;

給tim用戶在test_tbs上使用了10m空間的配額

ALTER USER tim QUOTA unlimited on test_tbs --不受限制

ALTER USER tim QUOTA 0 ON test_tbs; 收回剩餘配額

9.4概要文件的概念

9.4.1作用：對用戶訪問數據庫做一些限制。

1）概要文件（profile)具有兩個功能，一個是KERNEL資源（如CPU資源）限制，另一個是PASSWORD資源（如登錄）限制。

2）始終要實施口令控制，而對于KERNEL資源限制，則隻有實例參數RESOURE_LIMIT為TRUE時（默認是FALSE)才會實施。

3）系統自動使用概要文件，有一個默認的default profile,限制很寬松，作用較小。

4）可以使用create profile為用戶創建它自己的概要文件，沒有指定值的參數，其值就從default profile的當前版本中提取。

9.4.2概要文件示例

創建一個概要文件，對tim用戶施加兩個限制。

1）如出現兩次口令失誤，将賬戶鎖定。

2）tim用戶最多同時以兩個session登錄

步驟一、創建概要文件并命名two_error

SQL> create profile two_error limit failed_login_attempts 2;

SQL> alter profile two_error limit Sessions_per_user 2;

dba_profiles視圖可以列出所有profile資源;

SQL> select * from dba_profiles where PROFILE='TWO_ERROR';

步驟二、将概要文件分配給tim用戶

SQL> alter user tim profile two_error;

步驟三、tim用戶嘗試兩次登錄使用錯誤密碼

SQL> conn tim/fdfd

ERROR: ORA-28000: 賬戶已被鎖定

步驟四、sys為tim解鎖

SQL> alter user tim account unlock;

步驟五、resource_limit=true才可以使得KERNEL資源限制對profile起作用(考點)

SQL> alter system set resource_limit=true;

步驟六、測試tim隻能同時有兩個session登錄

步驟七、sys删掉了two_error概要文件

SQL> drop profile two_error cascade;

删除two_error後tim用戶又綁定到default profile上。

9.5權限和角色

9.5.1數據庫的安全問題

1）系統安全：

用戶名、口令、概要文件、磁盤配額等

2）數據庫安全：

對數據庫系統和數據庫對象的訪問及操作，用戶具備系統權限才能夠訪問數據庫，

具備對象權限才能訪問數據庫中的對象。

9.5.2系統權限

針對于database的相關權限，通常由DBA授予（11g 已有200多種）

典型的DBA權限：

CREATE USER

DROP USER

SELECT ANY TABLE

CREATE ANY TABLE

典型的一般用戶的系統權限：

CREATE SESSION

CREATE TABLE

CREATE VIEW

CREATE PROCEDURE

9.5.3 角色

1）為什麼會有角色

系統權限太過繁雜，Oracle建議将系統權限打包成角色，通過角色授權權限，目的就是為了簡化用戶訪問管理。

Oracle有一些預定義角色，如：connect、resource、dba角色等

也可以創建和删除自定義角色

SQL> CREATE role myrole;

SQL> DROP role myrole;

2）授予和回收權限的語法

1、授予系統權限和角色的語法

GRANT sys_privs,[role] TO user|role|PUBLIC [WITH ADMIN OPTION]

2、回收系統權限和角色的語法

Revoke sys_prvs[role] FROM user|role|PUBLIC

3）使用預定義角色的考慮

connect和resource兩個角色，可以滿足一般用戶大多數需求。

SQL> grant connect, resource to tim;

SQL> revoke unlimited tablespace from tim;

SQL> alter user tim quota 10m on test_tbs;

查看用戶表空間配額：

SQL> select tablespace_name,username,max_bytes from DBA_TS_QUOTAS where username='TIM';

當unlimited tablespace 和quota共存時，以unlimited為準。

關于權限與空間配額的考慮，用戶有空間配額才能建表。

空間配額不是權限的概念，而是用戶屬性的概念。

作為DBA，在生産系統上一定要為普通用戶分配空間配額，限制使用存儲空間。

有兩種辦法分配空間配額：

第一種：在建立用戶時指定空間配額，這個方法隻能分配用戶缺省的表空間上的配額，不是很靈活。

第二種：授予 resource角色，包含有unlimited tablespace權限此權限對所有表空間不設限，包括系統表空間，請立即收回該權限，然後再分配空間配額。

系統權限：sys, system 擁有普通用戶的所有對象權限，并有代理授權資格。

系統權限裡的any含義：

SQL> conn / as sysdba;

SQL> grant create any table to tim;

tim可以為SCOTT建表，這張表是屬于SCOTT用戶下的一個對象。

SQL> conn tim/tim

SQL> create table scott.t100 (id int);

9.5.4對象權限

1、授予對象權限的語法

GRANT object_privs ON object TO user|role|PUBLIC [WITH GRANT OPTION]

2、回收對象權限的語法

REVOKE object_prvs ON object FROM user|role|PUBLIC

角色授權的示例

9.5.5、授權注意事項

1）系統權限和對象權限語法格式不同，不能混合使用

SQL>grant create table,select on emp to tim 【錯】

2）系統權限和角色語法相同可以并列授權

SQL>grant connect,create table to tim;

3、可以使update對象權限精确到列

SQL> grant select, update(sal) on scott.emp to tim;

4）可以一條語句并列授予多個用戶

SQL>grant connect to tim,ran;

5）可以通過授權建立用戶,如ran用戶不存在

SQL>grant connect,resource to ran identified by ran;

9.5.6關于WITH ADMIN OPTION和WITH GRANT OPTION選項

1）系統權限的管理權限WITH ADMIN OPTION

用戶在獲得權限時一旦獲得WITH ADMIN OPTION，就意味着可以将該權限對其他用戶進行授予、回收（不管誰授予的）的管理權限操作。

2）對象權限的管理權限WITH GRANT OPTION

用戶在獲得對象權限時一旦獲得WITH GRANT OPTION，就意味着該用戶可以授予、回收（隻能它授予的）該對象權限。

3）系統權限和對象權限的級聯收回

User1有with admin option，可以單獨對user2用戶或user3用戶revoke，但不能級聯收回，User1有with grant option隻能對它授予的user2用戶revoke，并會級聯收回user3。

9.5.7對象權限在存儲過程中的使用

存儲過程proc1中包含了一些tim沒有權限的DML操作，scott将proc1的execute權限賦給tim，，那麼tim能成功地執行存儲過程proc1嗎？這個問題涉及到了create procedure時invoker_rights_clause的兩個選項：

1、AUTHID CURRENT_USER

當執行存儲過程時，檢查用戶DML操作的對象權限。

2、AUTHID DEFINER（默認）

當執行存儲過程時，不檢查用戶DML操作的對象權限。

測試：默認情況下AUTHID DEFINER

第一步

SQL> create table scott.a (d1 date);

SQL> grant connect,resource to tim identified by tim;

SQL> conn scott/scott

第二步

create or replace procedure proc1 as

begin

insert into scott.a values(sysdate);

commit;

end;

第三步

SQL> grant execute on proc1 to tim;

第四步

SQL> conn tim/tim

SQL> exec scott.proc1;

測試AUTHID CURRENT_USER選項

第一步

SQL>conn scott/scott

SQL>create or replace procedure proc1

AUTHID CURRENT_USER as

begin

insert into scott.a values(sysdate);

commit;

end;

第二步，結果報錯!

SQL> conn tim/tim

SQL> exec scott.proc1;

第三步，讓scott在execute權限和insert權限都具備的情況下再執行上次操作

SQL> grant all on a to tim;

第四步，重複第二步，結果OK!

9.5.8有關權限的常用視圖

the end ！！！

@jackman 共築美好！

更多精彩资讯请关注tft每日頭條，我们将持续为您更新最新资讯!