Oracle Privileges and Roles

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

PrivilegeDescriptionSelectAbility to query the table with a select statement.InsertAbility to add new rows to the table with the insert statement.UpdateAbility to update rows in the table with the update statement.DeleteAbility to delete rows from the table with the delete statement.ReferencesAbility to create a constraint that refers to the table.AlterAbility to change the table definition with the alter table statement.IndexAbility 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的权限类别

PrivilegeDescriptionExecuteAbility 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;

Roles

角色（Role）是一系列权限的集合，通过权限可以大大简化权限的授予和回收工作。通常创建一个用户之后，我们授予用户两个角色就足够使用了，这两个角色是CONNECT和RESOURCE。

SQL> select * from DBA_SYS_PRIVS where grantee = 'CONNECT';
GRANTEE                        PRIVILEGE                                ADMIN_OPTION
------------------------------ ---------------------------------------- ------------
CONNECT                        CREATE SESSION                           NO

SQL> select * from DBA_SYS_PRIVS where grantee = 'RESOURCE';
GRANTEE                        PRIVILEGE                                ADMIN_OPTION
------------------------------ ---------------------------------------- ------------
RESOURCE                       CREATE TRIGGER                           NO
RESOURCE                       CREATE SEQUENCE                          NO
RESOURCE                       CREATE TYPE                              NO
RESOURCE                       CREATE PROCEDURE                         NO
RESOURCE                       CREATE CLUSTER                           NO
RESOURCE                       CREATE OPERATOR                          NO
RESOURCE                       CREATE INDEXTYPE                         NO
RESOURCE                       CREATE TABLE                             NO
8 rows selected
SQL> 

创建一个可以访问数据库的用户，大体步骤:
SQL> create user tianpan identified by welcome
  2  default tablespace users
  3  temporary tablespace temp;
User created

SQL> select * from dba_sys_privs where grantee = 'TIANPAN';
GRANTEE                        PRIVILEGE                                ADMIN_OPTION
------------------------------ ---------------------------------------- ------------

SQL> grant connect,resource to tianpan;
Grant succeeded

SQL> select * from dba_sys_privs where grantee = 'TIANPAN';
GRANTEE                        PRIVILEGE                                ADMIN_OPTION
------------------------------ ---------------------------------------- ------------
TIANPAN                        UNLIMITED TABLESPACE                     NO

SQL> conn tianpan/welcome
Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 
Connected as tianpan

SQL> create table test_priv (name varchar2(10));
Table created

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