List/Grant/Revoke Oracle Privileges

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;

转载请注明出处：http://blog.csdn.net/pan_tian/article/details/11763889