oracle權限grant權限

oracle權限管理
精英翻到結尾看備注

一：用戶管理
1.1：用戶與模式
在oracle中模式與用戶是一一對應的，一個模式只能和被一個用戶所擁有且名稱和該用戶一樣；當用戶訪問自己模式中的對象時不需要指定模式名，如：scott用戶訪問自己的EMP表時SELECT * from emp不需要加模式名，經過授權的用戶還可以訪問其他模式的對象，訪問時要在對象名的前面加上模式名。
1.2：創建新用戶
要連接到Oracle數據庫，就需要創建一個用戶帳戶，每個用戶都有一個默認表空間和一個臨時表空間
擁有CREATE user 權限的用戶可以為數據庫創建新的用戶
col 字段名 for a10 //控制該字段長度
set timing on //開啟時間計時下一步的動作
創建用戶語法：
CREATE user user_name
IDENTIFIED [BY userpwd|EXTERNALLY|GLOBALLY|]
[DEFAULT TABLESPACE default_tablespace ]
[TEMPORARY TABLESPACE temp_tablespace]
[QUOTA size |UNLIMITED ON tablespace_name]
[PASSWORD EXPIRE]
[ACCOUNT LOCK|UNLOCK]
//user_name要創建的數據庫用戶的名稱，不能與現有的用戶名重復
//IDENTIFIED 用戶指定數據庫用戶的認證方式，password要創建用戶的密碼，即密碼認證方式，EXTERNALLY表示要使用外部認證方式，GLOBALLY表示要使用全局認證方式。
//DEFAULT TABLESPACE 指定創建用戶的默認表空間，以后創建的對象存儲在默認表空間內，默認表空間為USERS表空間。
//TEMPORARY TABLESPACE 指定用戶的臨時表空間，當會話期間執行的操作超過了PGA的可用空間，就會使用臨時表空間，默認臨時表空間為TEMP表空間。
//QUOTA限制用戶在某個表空間中使用的存儲空間大小，默認在表空間上的配額為0，用戶不能使用任何空間存儲任何對象，不能為用戶指定臨時表空間的配額。
//PASSWORD EXPIRE 指定用戶在第一次登錄時對密碼進行修改
//ACCOUNT 指定用戶的鎖定狀態，默認為unlock。
創建用戶后，oracle數據庫會自動創建一個相同名稱的模式，該用戶創建的對象都會保存該模式中。

創建用戶user1設置密碼123，其他默認
查詢dateabase_properties視圖，了解數據庫當前的默認表空間和臨時表空間
SELECT property_name,property_value from database_properties
where property_name like 'DEFAULT%'

--查詢用戶user1的默認表空間和臨時表空間，使用的是數據庫當前默認表空間和臨時表空間

1.3創建表空間
CREATE TABLESPACE test DATAFILE 'test.dbf' SIZE 10M;//創建表空間test
CREATE TEMPORARY TABLESPACE test_temp TEMPFILE 'test_temp.dbf' SIZE 10M;//創建臨時表空間test_temp

-創建用戶user2指定默認表空間test，臨時表空間test_temp，配額無限制。
CREATE user user2 IDENTIFIED BY 123
DEFAULT TABLESPACE test TEMPORARY TABLESPACE test_temp
QUOTA UNLIMITED ON test;

用戶user2可以使用表空間上任意大小的存儲數據對象，使用視圖dba_ts_quota可以查看用戶被分配的表空間配額情況，user1沒有被分配，所以查不到記錄。
1.4：修改用戶
語法：
-- ALTER user user1 ACCOUNT LOCK;//將用戶user1鎖定
-- ALTER user user1 PASSWORD EXPIRE ACCOUNT UNLOCK;//解鎖user1并要求登錄時改密碼
-- ALTER user user1 DEFAULT TABLESPACE test;//修改用戶的默認表空間test
-- ALTER user user1 QUOTA 5M ON test ;//修改用戶user1的配額為5M
1.5：刪除用戶
DROP user user1 [cascade] //刪除user1用戶
//當用戶沒有創建數據對象時，可以直接刪除，如果用戶創建了對象，要使用關鍵字cascade進行級聯刪除用戶創建的數據對象，會先刪除數據再刪除用戶。當用戶正在登陸時是不能刪除用戶的，強制刪除的話，需要先通過視圖V$SESSION查詢該用戶的會話ID等信息，然后使用ALTER SYSTEM KILL SESSION語句殺掉當前用戶的會話，最后使用DROP USER刪除用戶。




1.6通過數據字典查詢用戶信息
1.//user_users:顯示當前登錄用戶詳細信息，包括用戶名、用戶ID、帳號狀態、默認表空間、臨時表空間
2.//all_users:顯示可以被當前登錄的用戶所訪問的用戶，只包括用戶名、用戶ID、用戶創建的時間
3.//dba_users:顯示數據庫中所有的用戶信息，包括用戶名、用戶ID、帳號狀態、密碼、鎖定日期、失效日期、默認表空間臨時表空間。

二：權限管理

此視圖可以找到所需的權限。
以sysdba的身份登錄，登錄后用戶是SYS，SYS用戶除了擁有SYSDBA系統權限外，還擁有DBA等角色的權限；和擁有sysdba系統權限有很大的區別，普通用戶即使擁有了sysdba權限可能連數據庫都登錄不了。

2.1：權限有兩種類型，系統權限和對象權限
系統權限允許用戶執行某些數據庫操作，如創建表就是一個系統權限
對象權限允許用戶對數據庫對象（如表、視圖、序列等）執行特定操作
2.2：管理員授予普通用戶權限
語法：
GRANT sys_priv[,sys_priv]
TO user[,user ]|role |PUBLIC
[WITH ADMIN OPTION]
//sys_priv表示將要授予的系統權限，可以同時授予多個系統權限，他們之間用逗號隔開
//user表示要授予系統權限的用戶，role要授予權限的角色，PUBLIC表示PUBLIC用戶組，可以同時給多個用戶屬于系統權限，中間用逗號隔開。
//WITH ADMIN OPTION是否允許被授權的用戶將獲取的權限轉授給其他用戶，默認不能授予其他用戶
2.3系統權限，授予系統權限
1.授予用戶連接數據庫權限并創建數據庫對象: GRANT CONNECT TO tom;
2.允許用戶連接數據庫：GRANT CREATE SESSION TO tom
2.建立表的系統權限: GRANT CREATE TABLE TO tom ;
3.創建profile文件：CREATE PROFILE pro1 LIMIT
4.修改用戶的profile文件：ALTER user test1 PROFILE pro1;
5.允許用戶使用users表10M空間配額:ALTER user tom QUOTA 10M ON USERS;
6.Tom用戶任意使用表空間：GRANT UNLIMITED TABLESPACE TO tom;
7.收回系統權限revoke：REVOKE create session from tom;
2.4對象權限，授予對象權限
1.對象權限：訪問某個用戶的特定數據對象(如表、視圖等)的對象權限(SELECT on scott.student)
常用的對象權限：
1.select權限：執行查詢操作，能夠執行的對象是表，視圖和序列
2.insert權限：執行插入數據操作，能夠執行的對象是表和視圖
3.update權限：執行更新數據操作，能夠執行的對象是表和視圖
4.alter權限：執行修改對象的操作，能夠執行的對象是表和序列
5.delete權限：執行刪除數據的操作，能夠執行的對象是表和視圖
6.index權限：執行創建索引操作，能夠執行的對象是表
7.references權限：執行外鍵引用的操作，能夠執行的對象是表
8.execute權限：執行對象操作，能夠執行的對象是函數，過程和包。
語法：
GRANT object_privilege [column_name…]
ON obj_name
TO user [, user|role, PUBLIC…]
[WITH GRANT OPTION];
//obj_priv表示將要授予的對象權限，可以同時授予多個對象權限，他們之間用逗號隔開
//column_name表示要授予操作對象的列名，多個列名之間用逗號隔開
//obj_name表示對象權限操作的對象
//user表示要授予對象權限的用戶，role要授予對象權限的角色，PUBLIC表示PUBLIC用戶組，可以同時給多個用戶屬于對象權限，中間用逗號隔開。
//WITH ADMIN OPTION是否允許被授權的用戶將獲取的權限轉授給其他用戶，默認不能授予其他用戶
收回對象權限REVOKE：
REVOKE privilege ON object from {user|role|PUBLIC};
例：revoke SELECT on scott.student from test2
1.GRANT SELECT ON SCOTT.EMP TO tom WITH GRANT OPTION;//允許tom用戶查詢EMP表的記錄并且可以將獲得的權限授予其他用戶
2.GRANT UPDATE ON SCOTT.EMP TO tom；允許用戶更新EMP表的記錄
3.GRANT ALL ON SCOTT.EMP TO tom；允許用戶插入、刪除、更新、查詢表記錄
4.GRANT UPDATE(ename,sal)ON SCOTT.EMP TO tom;精確到允許用戶更新EMP中的ename,sal列

三：角色管理
角色管理：角色是一組相關權限的組合，可以將權限授予角色，再把角色授予用戶，以簡化權限管理。一個用戶可以擁有一個或多個角色，并可以將角色授予其他用戶或角色，無論在角色中增加或減少權限，該用戶的權限都會隨之變化。
3.1創建角色CREATE ROLE,應該具有CREATE ROLE系統權限。
CREATE ROLE role_name[NOT IDENTIFIEN]|[IDENTIFIED {BY password|USING [scheam.]package |EXTERNALLY|GLOBALLY}]
//role_name 表示要創建的角色的名稱
//NOT IDENTIFIED 表示角色被授予其他用戶和角色后立刻生效，這是默認值
//IDENTIFIED子句表示被授予其他用戶后不會立刻生效，以防止其他用戶隨意啟用和禁用該角色。BY password表示創建角色的同時為角色設置密碼，用戶不能立即擁有該角色權限，必須使用SET ROLE語句激活后才擁有；默認是沒有密碼，用戶可以立即擁有該角色權限。USING package表示創建的角色是一個應用程序角色，該角色只能在應用程序中使用授權的包來啟用。EXTERNALLY 表示要使用外部服務授權來啟用該角色。GLOBALLY表示通過企業服務授權的用戶來啟用該角色。

SCOTT用戶創建不成功是因為沒有權限

SYSDBA給SCOTT用戶創建角色的權限

SCOTT用戶創建3個角色

3.2 授予角色權限，可以是系統權限或者對象權限。
GRANT privilege TO ROLE;
例： GRANT create session ,create any table, drop any table TO role1;
為角色e_emp1授予scott模式的EMP表的查詢權限和在任何模式下創建，刪除視圖的系統權限。為角色e_emp2授予scott模式的EMP表的插入、更新和刪除的權限。為角色m_emp授予scott模式下的DEPT表的查詢、插入、更新、刪除權限。


3.3 將角色授予用戶:
GRANT role TO user;
例： grant role1 to test2;

3.4查看用戶被直接授予的角色信息
查詢視圖user_role_privs可以查看用戶被直接授予的角色的信息。

在用戶user1中查看user1用戶的角色。
由上圖可以看出，角色e_emp1被自動設置為用戶user1的默認角色，而M_EMP被設置了密碼因此不能自動成為默認角色。對于用戶user1只能查詢scott模式下EMP表的信息，不能查看DEPT表的信息。

用戶user1可以查看EMP表

不能查看dept表
3.5修改默認角色
ALTER user DEFAULT ROLE [role_name |ALL [EXCEPT role_name] |NONE]
//role_name為要設置的角色名，多個用逗號隔開
//ALL啟用當前用戶的所有角色作為默認角色，但不包括設置密碼的角色。
//EXCEPT除了指定的角色外，啟用當前用戶所有角色作為默認角色
//NONE禁用當前用戶的所有角色，即默認角色為零個。
ALTER user user1 DEFAULT ROLE NONE; //修改用戶默認角色為0個，登錄后不具有任何角色。
ALTER user user1 DEFAULT ROLE ALL EXCEPT e_emp1;//user1登錄后啟用除e_emp1角色外，其他所有角色為默認角色
ALTER user user1 DEFAULT ROLE ALL;//user1登錄后啟用所有角色為默認角色
ALTER user user1 DEFAULT ROLE e_emp1;//user1登錄后只啟用e_emp1角色為默認角色。
3.6啟用和禁用角色set
SET ROLE [role_name [IDENTIFIED BY password|ALL [EXCEPT role_name] |NONE]
//role_name為要設置的角色名，多個用逗號隔開
//IDENTIFIED BY 用于啟用或禁用角色時要使用的密碼
//ALL啟用當前用戶的所有角色作為默認角色，但不包括設置密碼的角色。
//EXCEPT除了指定的角色外，啟用當前用戶所有角色作為默認角色
//NONE禁用當前用戶的所有角色，即默認角色為零個。
SET ROLE m_emp IDENTIFIED BY 123; //啟用m_emp角色的同時輸入密碼

在user1用戶中啟用

并查看

3.7 從用戶收回角色:
REVOKE role1 from user2;//從用戶user2中回收role1角色
REVOKE CREATE ANY VIEW,DROP ANY VIEW from e_emp1; //回收e_emp1角色的系統權限
3.8刪除角色
DROP ROLE e_emp1
（5）GRANT RESOURCE TO tom：RESOURCE角色允許用戶使用數據庫中的存儲空間（系統自帶角色）

備注：
oracle查詢不用輸入前面的用戶名(a.b不用輸入a了)
可以使用下面四種方法：空間資源充足或者表數據量小可以使用
1.使用同意詞
grant CREATE SYNONYM to scott; //DBA或者有權限的用戶授權
create synonym emp_test for scott.emp;
2.使用視圖
create view emp_test as SELECT from scott.emp;
3.賬戶授權
grant SELECT on scott to test; //test用戶可以查看scott用戶的表
4.創建數據庫
create table emp_test1 as SELECT from scott.emp

給用戶賦權限
grant SELECT on DEMO_INVOICE_CLOUD.FMB_INVOICE to demo_pc_core01;
GRANT connect, resource TO test;
GRANT RESUMABLE TO TEST; --可以自動擴展空間
GRANT BACKUP ANY TABLE TO TEST;
GRANT EXECUTE ANY TYPE TO TEST;
GRANT SELECT ANY TABLE TO TEST;
GRANT READ ANY FILE GROUP TO TEST;
GRANT SELECT ANY SEQUENCE TO TEST;
GRANT EXECUTE ANY PROCEDURE TO TEST;
connect 用戶能登錄數據庫的權限
resource 用戶能創建一些數據庫對像的權限，表、視圖，存儲過程，一般是授予開發人員的
drop user cmsuser cascade;
使用cascade參數可以刪除該用戶的全部objects

connect 用戶能登錄數據庫的權限
resource 用戶能創建一些數據庫對像的權限，表、視圖，存儲過程，一般是授予開發人員的
drop user cmsuser cascade;
使用cascade參數可以刪除該用戶的全部objects

rowid是Oracle數據庫中行標識符，為rowid,它是一個18位數字，以64為基數，該徝包含了該行在oracle數據庫中的物理位置，查詢rowid如下：
SQL> SELECT rowid,id from test Where rownum< 5;
刪除表中重復記錄
Delete from test a
Where rowid < (SELECT Max(rowid)from test Where Id = a.Id);

將一個用戶下面的所有表的權限授予另一個用戶
1.查看出該用戶下面使用的表
SELECT * from dba_tables where owner='SCOTT' and status='VALID'
2.將該用戶下面的表拼接成授權語句
SELECT 'grant select,delete,update,insert '|| table_name||' to test ;' from dba_tables where owner='SCOTT' and status='VALID'
3.將步驟2中的輸出結果用dba或者有授權權限的用戶執行，當前用戶如有授權權限則可以執行。

注意：如果需要將當前登錄用戶數據庫中所有表授權給test用戶可以使用下面語句進行拼接
SELECT 'grant select,delete,update,insert '|| table_name||' to test ;' from user_tables where owner='SCOTT' and status='VALID'
將除sys外所有的表給PC_CORE01用戶查看
grant select any table to PC_CORE01;