List/Grant/Revoke Oracle Privileges

Oracle包括两大类权限，一种是系统权限，另外一种是对象权限。

System Privileges

Create session

Create table

Create view

Create procedure

Sysdba

Sysoper

Object Privileges Privileges can be assigned to any of the following types of database objects:

Tables: all, alter, debug, delete, flashback, insert, on commit refresh, query rewrite, references, select, update

Views: debug, delete, insert, flashback, references, select, under, update

Sequence: alter, select

Packages, Procedures, Functions: debug, execute

Materialized Views: delete, flashback, insert, select, update

Directories: read, write

Libraries: execute

User Defined Types: debug, execute, under

Operators: execute

Indextypes: execute

Privileges Tables/Views

System Privileges：DBA_SYS_PRIVS

Object Privileges:DBA_TAB_PRIVS

Data Dictionary Objects Related To Object Privileges

objauth$ objpriv$

dba_col_privs all_col_privs user_col_privs

- all_col_privs_made user_col_privs_made

- all_col_privs_recd user_col_privs_recd

dba_tab_privs all_tab_privs user_tab_privs

- all_tab_privs_made user_tab_privs_made

- all_tab_privs_recd user_tab_privs_recd

column_privileges table_privileges table_privilege_map

Roles granted to users or roles : DBA_ROLE_PRIVS

上述三表为DBA_表，普通用户可以使用下边的三张表

SELECT * FROM USER_SYS_PRIVS;

SELECT * FROM USER_TAB_PRIVS;

SELECT * FROM USER_ROLE_PRIVS;

List Privileges

列出其他用户授权给当前用户的表权限

SELECT * FROM USER_TAB_PRIVS_RECD;

查当前用户的角色

select * from user_role_privs;

查自己拥有哪些系统权限

select * from session_privs;

列出当前用户授权给其他用户的表权限

SELECT * FROM USER_TAB_PRIVS_MADE;

Grant Privileges on Tables

Privilege Description

Select Ability to query the table with a select statement.

Insert Ability to add new rows to the table with the insert statement.

Update Ability to update rows in the table with the update statement.

Delete Ability to delete rows from the table with the delete statement.

References Ability to create a constraint that refers to the table.

Alter Ability to change the table definition with the alter table statement.

Index Ability to create an index on the table with the create index statement.

语法：

grant privileges on object to user;

比如：如果你想把suppliers表的查询，插入，修改，删除的权限付给用户smithj,你可以执行下边的语句

grant select, insert, update, delete on suppliers to smithj;

你也可以直接使用all关键字来赋权，表示你想把对象所有的权限付给某个用户，比如：

grant all on suppliers to smithj;

如果你想让所有用户都可以查询某张表，那你可以使用public关键字，比如：

grant select on suppliers to public;

Revoke Privileges on Tables

授权(Grant)之后，你想取消授权，那就得revoke了。

语法：

revoke privileges on object from user;

比如，你想取消anderson删除supplier表的权利，可以执行：

revoke delete on suppliers from anderson;

你想取消anderson针对supplier表的所有权利，可以执行：

revoke all on suppliers from anderson;

如果你有授权给public（所有用户），现在想撤权，可以执行：

revoke all on suppliers from public;

Grant Privileges on Functions/Procedures

针对Functions/Procedures的授权。

Functions/Procedures的权限类别

Privilege Description

Execute Ability to compile the function/procedure.Ability to execute the function/procedure directly.

语法：

grant execute on object to user;

把Funciton Find_Value的执行权限付给用户smithj,可以执行：

grant execute on Find_Value to smithj;

把Funciton Find_Value的执行权限付给所有用户,可以执行：

grant execute on Find_Value to public;

Revoke Privileges on Functions/Procedures

针对Functions/Procedures的撤权。

语法：

revoke execute on object from user;

撤销anderson针对Find_Value Funciton的执行权限，可以执行：

revoke execute on Find_Value from anderson;

如果你赋权过所有用户，现在想撤销，可以执行：

revoke execute on Find_Value from public;