词汇:
* system privileges、role、object privileges 统称"权限",分别称为"系统权限"、"角色"、"对象权限"。
* grantee、grantor 分别称为"被授权者"、"授权者"
* creater、owner 分别称为"创建者"、"拥有者"
GRANT 语句的作用
* 把 system privileges 授给 user、role、PUBLIC
* 把 role 授给 user、role
* 把某个数据库对象的 object privileges 授给 users、roles、PUBLIC
GRANT 语句之外的授权方法:
* oracle 预定义的 PL/SQL packages、Java packages 内包含了很多授权工作
* 初始化参数 OS_ROLES 供操作系统给用户授权。采用 OS_ROLES 授权,就无法使用 GRANT 语句授权。
授权条件:
* 授权 system privilege 时:
* 授权者必须拥有对应的 system privilege 且有 ADMIN OPTION
* 或授权者有 GRANT ANY PRIVILEGE 系统权限。
* 授权 role 时
* 授权者必须拥有对应的 role 且有 ADMIN OPTION
* 或授权者有 GRANT ANY ROLE 系统权限
* 或授权者是这个 role 的创建者
* 授权 object privilege 时
* 授权者必须是 object 的拥有者
* 或 object 的拥有者给予授权者对应的 object privilege 且有 GRANT OPTION
* 或授权者有 GRANT ANY OBJECT PRIVILEGE 系统权限
WITH ADMIN OPTION 和 WITH GRANT OPTION 和 WITH HIERARCHY OPTION
* GRANT system privileges 或 role 可以加 WITH ADMIN OPTION;
* GRANT object privilege 可以加 WITH GRANT OPTION;
WITH ADMIN OPTION: grantee can
* Grant the role to another user or role, unless the role is a GLOBAL role
* Revoke the role from another user or role
* Alter the role to change the authorization needed to access it
* Drop the role
* If you grant a system privilege or role to a user without specifying WITH ADMIN OPTION, and then subsequently grant the
privilege or role to the user WITH ADMIN OPTION, then the user has the ADMIN OPTION on the privilege or role.
* To revoke the ADMIN OPTION on a system privilege or role from a user, you must revoke the privilege or role from the user altogether and then grant the privilege or role to the user without the ADMIN OPTION.
WITH GRANT OPTION:
* Specify WITH GRANT OPTION to enable the grantee to grant the object privileges to other users and roles.
* You can specify WITH GRANT OPTION only when granting to a user or to PUBLIC, not when granting to a role.
dba_sys_privs 和 dba_role_privs 和 dba_tab_privs 和 DBA_COL_PRIVS
* DBA_SYS_PRIVS describes system privileges granted to users and roles
* DBA_ROLE_PRIVS describes the roles granted to all users and roles in the database.
* DBA_TAB_PRIVS describes all object grants in the database.
PUBLIC 的含义
* 表示所有用户
* 授权给 PUBLIC 后,所有用户都有此权限
* 授权给 PUBLIC 后,3 个视图中增加了 PUBLIC 的权限,而不是加给每个用户
参考
[1] Oracle Database SQL Reference \ GRANT 语句
http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/statements_9013.htm
[2] Oracle Database Reference \ DBA_SYS_PRIVS
http://download.oracle.com/docs/cd/B19306_01/server.102/b14237/statviews_4143.htm#i1627521
[3] Oracle Database Reference \ DBA_ROLE_PRIVS
http://download.oracle.com/docs/cd/B19306_01/server.102/b14237/statviews_4064.htm#i1625775
[4] Oracle Database Reference \ DBA_TAB_PRIVS
http://download.oracle.com/docs/cd/B19306_01/server.102/b14237/statviews_4151.htm#i1627646
[5] Oracle Database Reference \ DBA_USERS
http://download.oracle.com/docs/cd/B19306_01/server.102/b14237/statviews_4174.htm#i1628672
[6] Oracle Database Reference \ DBA_ROLES
http://download.oracle.com/docs/cd/B19306_01/server.102/b14237/statviews_4065.htm#sthref1798
问题
[1] role 是什么
[2] PUBLIC 是什么
[3] local, global, external 是什么
附录:
system privileges 列表:
http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/statements_9013.htm#BABEFFEE
oracle 预定义 role 列表:
http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/statements_9013.htm#g2199949
各种数据库对象可用的 object privileges:
http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/statements_9013.htm#BGEJEBCJ
各种数据库对象 object privileges 的含义:
http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/statements_9013.htm#BGEJEBCJ
Leave a comment