Oracle Privileges
Oracle包括两大类权限,一种是系统权限,另外一种是对象权限。
System Privileges
- Create session
- Create table
- Create view
- Create procedure
- Sysdba
- Sysoper
- 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
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;
SELECT * FROM USER_TAB_PRIVS;
SELECT * FROM USER_ROLE_PRIVS;
List Privileges
列出其他用户授权给当前用户的表权限
SELECT * FROM USER_TAB_PRIVS_RECD;
SELECT * FROM USER_TAB_PRIVS_RECD;
查当前用户的角色
select * from user_role_privs;
查自己拥有哪些系统权限
select * from session_privs;
select * from user_role_privs;
查自己拥有哪些系统权限
select * from session_privs;
列出当前用户授权给其他用户的表权限
SELECT * FROM USER_TAB_PRIVS_MADE;
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
转载于:http://blog.itpub.net/26687597/viewspace-1207644/