Oracle使用者、角色、權限管理

概述

oracle資料庫的權限系統分為System privileges與Object privileges。

oracle官方文檔：

A system privilege is the right to perform a particular action or to perform an action on any object of a particular type. Objects include tables, views, materialized views, synonyms, indexes, sequences, cache groups, replication schemes and PL/SQL functions, procedures and packages. Only the instance administrator or a user with ADMIN privilege can grant or revoke system privileges.

谷歌翻譯：

系統特權是執行特定操作或對特定類型的任何對象執行操作的權限。對象包括表，視圖，物化視圖，同義詞，索引，序列，緩存組，複制方案和PL / SQL函數，過程和包。隻有執行個體管理者或具有ADMIN權限的使用者才能授予或撤消系統權限。

系統權限包含：CREATE TABLE，CREATE VIEW等。

An object privilege is the right to perform a particular action on an object or to access another user’s object. Objects include tables, views, materialized views, indexes, synonyms, sequences, cache groups, replication schemes and PL/SQL functions, procedures and packages.

An object’s owner has all object privileges for that object, and those privileges cannot be revoked. The object’s owner can grant object privileges for that object to other database users. A user with ADMIN privilege can grant and revoke object privileges from users who do not own the objects on which the privileges are granted.

翻譯：

對象權限是對對象執行特定操作或通路其他使用者對象的權限。對象包括表，視圖，物化視圖，索引，同義詞，序列，緩存組，複制方案和PL / SQL函數，過程和包。

對象的所有者擁有該對象的所有對象權限，并且無法撤消這些權限。對象的所有者可以将該對象的對象權限授予其他資料庫使用者。具有ADMIN權限的使用者可以授予和撤消不擁有此對象使用者的對象權限。

對象權限包含：DELETE,EXECUTE，SELECT等。

這些權限可以直接授予user，也可以授予role。角色相等于權限組，權限可以授權給使用者。

oracle内置的使用者有：SYS,SYSTEM,SCOTT(初始未激活)等。

oracle内置的角色有：CONNECT,RESOURCE,DBA等。

一、建立使用者

oracle内部有兩個建好的使用者：system和sys。使用者可直接用其一登陸以建立其他使用者。

文法[建立使用者]： create user 使用者名 identified by 密碼[即密碼]；

例子： create user test identified by test;

文法[更改使用者]: alter user 使用者名 identified by 密碼[改變的密碼];

例子： alter user test identified by 123456;

二、删除使用者

文法：drop user 使用者名;

例子：drop user test;

若使用者擁有對象，則不能直接删除，否則将傳回一個錯誤值。指定關鍵字cascade,可删除使用者所有的對象，并删除使用者。

文法： drop user 使用者名 cascade;

例子： drop user test cascade;

三、授權角色

oracle為相容以前版本，提供三種标準角色（role）:connect/resource和dba.

CONNECT:隻有連接配接權限。别人可以授權某些表的權限（如select）給你，你才可操作（如檢視）這些被授權的特定表。

系統權限為：CREATE SESSION

RESOURCE:資源操作，一般使用者授予connect和resource權限即可。

系統權限為：CREATE TRIGGER,CREATE SEQUENCE,CREATE TYPE,CREATE PROCEDURE,CREATE CLUSTER,CREATE OPERATOR,CREATE INDEXTYPE,CREATE TABLE

小插曲：列轉行函數

select wm_concat(privilege) as privilege from dba_sys_privs t where t.grantee = ‘RESOURCE’;

或者

select listagg(privilege, ‘,’) within group( order by privilege) as privilege from dba_sys_privs t where t.grantee = ‘RESOURCE’;

DBA:資料庫管理者。

授權指令

文法： grant connect, resource to 使用者名;

例子： grant connect, resource to test;

撤銷權限

文法： drop user 使用者名 cascade;

例子： revoke connect, resource from test;

備注：

connect,resource角色沒有建立view的權限,如需，則應再單獨授權

grant create view to test;

四、建立/授權/删除角色

除了前面講到的三種系統角色----connect、resource和dba，使用者還可以在oracle建立自己的role。使用者建立的role可以由系統權限、對象權限或兩者的組合構成。為了建立role，使用者必須具有create role系統權限。

1》建立角色

文法： create role 角色名;

例子： create role testRole;

2》删除角色

文法： drop role 角色名;

例子： drop role testRole;

3》授權角色

文法： grant select on mytable1 to 角色名;

例子： grant select on mytable1 to testRole;

4》撤銷授權

文法： revoke select on mytable1 from 角色名;

例子： revoke select on mytable1 from testRole;

五、相關表

select * from v$database; --檢視目前執行個體名

select * from v$session; --檢視目前所有使用者連接配接

select * from user_tables; – 檢視目前使用者能通路的表

select * from dba_tables where owner = ‘USERNAME’;

select * from user_users; – 檢視目前使用者資訊

select * from dba_users where username = ‘XCY’; --檢視資料庫裡面所有使用者

select * from dba_roles; – 檢視角色

select * from user_role_privs; – 檢視目前使用者的角色

select * from dba_role_privs t where t.grantee = ‘XCY’; – 查詢grantee被授權的角色(角色也可以被授權角色，如’DBA’被授權有java_admin,olap_dba等角色)

select * from user_sys_privs; --查詢使用者被授予的系統權限

select * from user_tab_privs; – 查詢使用者被授予的表相關權限

select * from dba_sys_privs t where t.grantee = ‘CONNECT’; – 查詢grantee(包含角色和使用者)被授予的系統權限

select * from dba_tab_privs t where t.grantee = ‘XCY’; – 查詢grantee(包含角色和使用者)被授予的表相關權限(insert,delete,update,selete,read,use,execute等)

select * from role_sys_privs where role =‘OLAP_DBA’; --查詢role所被授予的privilege

select * from role_tab_privs t where t.role=‘DBA’; --查詢role所被授予的表相關privilege

檢視所有表空間使用情況

select a.file_id “FileNo”,

a.tablespace_name “Tablespace_name”,

a.bytes “Bytes”,

a.bytes - sum(nvl(b.bytes, 0)) “Used”,

sum(nvl(b.bytes, 0)) “Free”,

sum(nvl(b.bytes, 0)) / a.bytes * 100 “%free”

from dba_data_files a, dba_free_space b

where a.file_id = b.file_id(+)

group by a.tablespace_name, a.file_id, a.bytes

order by a.tablespace_name;

完

happy coding!