Oracle / PLSQL: Grant/Revoke Privileges

This Oracle tutorial explains how to grant and revoke privileges in Oracle with syntax and examples.

Description

You can GRANT and REVOKE privileges on various database objects in Oracle. We'll first look at how to grant and revoke privileges on tables and then how to grant and revoke privileges on functions and procedures in Oracle.

Grant Privileges on Table

You can grant users various privileges to tables. These privileges can be any combination of SELECT, INSERT, UPDATE, DELETE, REFERENCES, ALTER, INDEX, or ALL.

Syntax

The syntax for granting privileges on a table in Oracle is:

GRANT privileges ON object TO user;

privileges

The privileges to assign. It can be any of the following values:

Privilege	Description
SELECT	Ability to perform SELECT statements on the table.
INSERT	Ability to perform INSERT statements on the table.
UPDATE	Ability to perform UPDATE statements on the table.
DELETE	Ability to perform DELETE statements on the table.
REFERENCES	Ability to create a constraint that refers to the table.
ALTER	Ability to perform ALTER TABLE statements to change the table definition.
INDEX	Ability to create an index on the table with the create index statement.
ALL	All privileges on table.

object

The name of the database object that you are granting privileges for. In the case of granting privileges on a table, this would be the table name.

user

The name of the user that will be granted these privileges.

Example

Let's look at some examples of how to grant privileges on tables in Oracle.

For example, if you wanted to grant SELECT, INSERT, UPDATE, and DELETE privileges on a table called suppliers to a user name smithj, you would run the following GRANT statement:

GRANT SELECT, INSERT, UPDATE, DELETE ON suppliers TO smithj;

You can also use the ALL keyword to indicate that you wish ALL permissions to be granted for a user named smithj. For example:

GRANT ALL ON suppliers TO smithj;

If you wanted to grant only SELECT access on your table to all users, you could grant the privileges to the public keyword. For example:

GRANT SELECT ON suppliers TO public;

Revoke Privileges on Table

Once you have granted privileges, you may need to revoke some or all of these privileges. To do this, you can run a revoke command. You can revoke any combination of SELECT, INSERT, UPDATE, DELETE, REFERENCES, ALTER, INDEX, or ALL.

Syntax

The syntax for revoking privileges on a table in Oracle is:

REVOKE privileges ON object FROM user;

privileges

The privileges to revoke. It can be any of the following values:

Privilege	Description
SELECT	Ability to perform SELECT statements on the table.
INSERT	Ability to perform INSERT statements on the table.
UPDATE	Ability to perform UPDATE statements on the table.
DELETE	Ability to perform DELETE statements on the table.
REFERENCES	Ability to create a constraint that refers to the table.
ALTER	Ability to perform ALTER TABLE statements to change the table definition.
INDEX	Ability to create an index on the table with the create index statement.
ALL	All privileges on table.

object

The name of the database object that you are revoking privileges for. In the case of revoking privileges on a table, this would be the table name.

user

The name of the user that will have these privileges revoked.

Example

Let's look at some examples of how to revoke privileges on tables in Oracle.

For example, if you wanted to revoke DELETE privileges on a table called suppliers from a user named anderson, you would run the following REVOKE statement:

REVOKE DELETE ON suppliers FROM anderson;

If you wanted to revoke ALL privileges on a table for a user named anderson, you could use the ALL keyword as follows:

REVOKE ALL ON suppliers FROM anderson;

If you had granted ALL privileges to public (all users) on the suppliers table and you wanted to revoke these privileges, you could run the following REVOKE statement:

REVOKE ALL ON suppliers FROM public;

Grant Privileges on Functions/Procedures

When dealing with functions and procedures, you can grant users the ability to EXECUTE these functions and procedures.

Syntax

The syntax for granting EXECUTE privileges on a function/procedure in Oracle is:

GRANT EXECUTE ON object TO user;

EXECUTE

The ability to compile the function/procedure. The ability to execute the function/procedure directly.

object

The name of the database object that you are granting privileges for. In the case of granting EXECUTE privileges on a function or procedure, this would be the function name or the procedure name.

user

The name of the user that will be granted the EXECUTE privileges.

Example

Let's look at some examples of how to grant EXECUTE privileges on a function or procedure in Oracle.

For example, if you had a function called Find_Value and you wanted to grant EXECUTE access to the user named smithj, you would run the following GRANT statement:

GRANT EXECUTE ON Find_Value TO smithj;

If you wanted to grant ALL users the ability to EXECUTE this function, you would run the following GRANT statement:

GRANT EXECUTE ON Find_Value TO public;

Revoke Privileges on Functions/Procedures

Once you have granted EXECUTE privileges on a function or procedure, you may need to REVOKE these privileges from a user. To do this, you can execute a REVOKE command.

Syntax

The syntax for the revoking privileges on a function or procedure in Oracle is:

REVOKE EXECUTE ON object FROM user;

EXECUTE

The ability to compile the function/procedure. The ability to execute the function/procedure directly.

object

The name of the database object that you are revoking privileges for. In the case of revoking EXECUTE privileges on a function or procedure, this would be the function name or the procedure name.

user

The name of the user that will be revoked the EXECUTE privileges.

Example

Let's look at some examples of how to revoke EXECUTE privileges on a function or procedure in Oracle.

If you wanted to revoke EXECUTE privileges on a function called Find_Value from a user named anderson
, you would run the following REVOKE statement:

REVOKE execute ON Find_Value FROM anderson;

If you had granted EXECUTE privileges to public (all users) on the function called Find_Value and you wanted to revoke these EXECUTE privileges, you could run the following REVOKE statement:

REVOKE EXECUTE ON Find_Value FROM public;

用PLSQL Developer，剛進入時，選擇一個表,----->edit data有個提示： Dynamic performance Table not accessible Automatic Statistics disbled this seesion you can Statistics in the preference menu ,obtain select priviliges on the V$session , $sessstat and v$statname table 我的用戶是 resource角色， 為什麼有這樣提示，要如何改？？

這是PL/SQL DEVELOPER在執行SQL時會進行本SESSION統計信息收集的問題，根本原因是該用戶對字典表沒有權限，可以按上述改變PL/SQL設置，或者使用簡單的授權語句：grant SELECT ANY DICTIONARY to username 當然還有你還有其他兩種選擇：

1.讓PL/SQL 不收集統計信息：在tools->preferences->Options中取消Automatic Statistics 選項

2.增加當前登錄用戶的權限開通PL/SQL 收集統計信息：

開通權限的方法可以為

grant select on V_$SESSION to DX;

grant select on V_$SESSTAT to DX;

grant select on V_$STATNAME to DX;

也可以直接給DBA的權限給這個用戶

grant dba to DLYX;

個人建議，如果是開發測試數據庫，可以給用戶加權限，開通收集統計信息。這樣對於優化SQL性能比較方便，僅供參考。

=====================================================================

今天在使用PL/SQL Developer工具登陸一個新創建的用戶進行查詢時，報出以下錯誤（PL/SQL Developer版本：7.1.5 1403）：

Dynamic Performance Tables not accessible, Automatic Statistics disabled for this session

You can disable statistics in the preference menu, or obtain select priviliges on the V$session,V$sesstat and V$statname tables

這個報錯信息在不同的PL/SQL Developer版本都會出現，從上面詳細的報錯提示信息中我們可以判斷得到，報錯原因不在工具本身。

在此，詳細記錄一下這個小問題的三種處理方法。

1.第一種處理方法（不推薦 ）

就是在報錯的Error對話框中將「Don't show this message again」選項選中，下次就不在提示這個錯誤了。

這種方法應該可以叫做「鴕鳥方式」的處理方法。沒有從根本上解決這個問題。

2.第二種處理方法（可以採納）

報錯信息中描述的非常詳細，原因是動態性能表沒有權利被訪問導致的問題，因此，我們通過把所需訪問權限賦予給具體用戶的方法來解決這個問題。

這裡給出我能想到的三種具體處理方法。大家可以繼續補充。

1）如果只是某一具體用戶有權限查詢這三個動態性能視圖，可以如下進行操作

這裡注意一下：我們授權的視圖是V_$session不是V$session，因為V$session是同名不是具體的視圖。否則您會收到下面這個錯誤。

sys@ora10g> grant select on V$session to user_sec;

grant select on V$session to user_sec

*

ERROR at line 1:

ORA-02030: can only select from fixed tables/views

正確的授權方法如下：

SQL> grant select on V_$session to user_sec;

SQL> grant select on V_$sesstat to user_sec;

SQL> grant select on V_$statname to user_sec;

2）可以使用下面這個「簡單粗暴」的方法處理之。

SQL> grant SELECT ANY DICTIONARY to user_sec;

3）以上兩種方法是針對特定用戶的處理方法，如果想讓所有用戶（不局限在上面的user_sec用戶）都能夠查詢這三個動態性能視圖，可以通過將查詢權限授權給public方法來實現，操作如下。這樣就可以保證所有開發人員都不會再出現上述的報錯信息了。

SQL> grant select on V_$session to public;

SQL> grant select on V_$sesstat to public;

SQL> grant select on V_$statname to public;

3.第三種方法（推薦）

徹底禁掉PL/SQL Developer的這個功能。

方法如下：

導航到Tools --> Preferences --> Options

找到「Automatic Statistics」選項，將其前面的小對勾去掉，然後點擊「Apply」和「OK」保存退出。

4.小結

之所以書寫這個文章，只是給出一個處理問題的一般方法，這就是：「充分挖掘具體報錯信息，從各種表面現象入手，逐步深入，最終得到滿意的處理結果。」

最後談一下DBA與數據庫管理開發工具（如PL/SQL Developer、Toad等等）的關係。

如果您是純開發DBA，那麼強烈建議您認真的研究這些優秀高級工具的每一個細節，因為這樣可以大大的提高您的工作效率。

附：有關動態性能表的說明

【1】Dynamic Performance Tables：用於記錄當前數據庫活動信息，反映數據庫操作的實時狀況（Dynamic Performance Tables 存放變化較頻繁的信息，而DD則存放相對比較穩定的信息）。

【2】Dynamic Performance Tables是一組的虛擬表，它把內存中的信息和控制文件（Control file）的信息以表的形式展示出來，內存是主要的信息來源。

【3】絕大多數的用戶是不能訪問動態性能表的，只用擁有DBA才能查詢這些視圖，比如：SYS賬號。DBA可以對動態性能表進行查詢，授予select權限，以及創建views（視圖），但DBA也無法直接更改或刪除views（所以這些views也稱為fixed views），因為這些views不是真正的表，它們是由內存和控制文件的數據臨時組成的，所以不能刪/改，並且當數據庫關閉時，Dynamic Performance Tables也不復存在。

【4】Dynamic Performance Tables基本上均以V$開頭，具體有哪些Dynamic Performance Tables可以查詢V$FIXED_TABLE。

【5】Dynamic Performance Tables可使我們瞭解如下信息：

‧ 該對象是否處於聯機狀態並可用？
‧ 該對象是否已打開？
‧ 目前持有哪些鎖？
‧ 該會話是否處於活動狀態？