Oracle之管理权限

 

权限(Privilege)是指执行特定类型SQL命令或访问其他方案对象的权利,权限包括系统权限和对象权限两种类型.系统权限(System Privilege)是指执行特定类型SQL命令的权利,它用于控制用户可以执行的一个或一组数据库操作.常用的系统权限:

CREATE SESSION 连接到数据库

CREATE TABLE 建表

CREATE VIEW 建立视图

CREATE PUBLIC SYNONYM 建立同义词

CREATE SEQUENCE 建立序列

CREATE PROCEDURE 建立过程,函数和包

CREATE TRIGGER 建立触发器

CREATE CLUSTER 建立簇

CREATE TYPE 建立对象类型

CREATE DATABASE LINK 建立数据库链

另外,oracle还提供了一类ANY系统权限,当用户具有该类系统权限时,可以在任何方案中执行相应操作.如果用户具有SELECT ANY TABLE系统权限,那么用户可以查询任何方案的表.

需要注意,当初始化参数O7_DICTIONARY_ACCESSIBILITY被设置为FALSE时,除了SYSDBA和DBA用户外,即使用户具有SELECT ANY TABLE权限,也不能访问数据字典基表和数据字典视图DBA_XXX.

对象权限(Object privilege)是指访问其他方案对象的权利,它用于控制用户对其他方案对象的访问.

管理系统权限

授予系统权限

GRANT system_priv[,system_priv,…]

To {user | role | PUBLIC},[,{user | role | PUBLIC} ] …

[WITH ADMIN OPTION];

System_priv 用于指定系统权限.授权用户组PUBLIC,在授予系统权限时可以带有WITH ADMIN OPTION选项,带有该选项后,被授权的用户,角色还可以将相应的权限授予其他用户,角色.需要注意,系统权限UNLIMITED TABLESPACE不能授予角色.

CRANT CREATE SESSION,CREATE TABLE TO blake WITH ADMIN OPTION;

GRANT GREATE VIEW TO blake;

显示系统权限.

1,显示所有系统权限.

SELECT name FROM system_privilege_map;

2,显示用户或角色所具有的系统权限.

查询数据字典视图DBA_SYS_PRIVS,可以显示所有用户或角色所具有的系统权限.通过查询数据字典视图USER_SYS_PRIVS,可以显示当前用户所具有的系统权限.

SELECT * FROM dba_sys_privs WHERE GRANTEE=’BLAKE’;

GRANTEE用于标识权限拥有者(用户或角色),PRIVILEGE用于标识系统权限.ADMIN_OPTION用于标识系统权限传授选项,其中YES表示可以转授该系统权限,NO表示不能转授该系统权限.

3,显示当前会话所具有的系统权限.

通过查询数据字典视图session_privs,可以显示当前会话所具有的系统权限.

SELECT * FROM session_privs;

收回系统权限

收回系统权限一般是由DBA完成的,如果要以其他用户身份收回系统权限,要求该用户必须具有相应系统权限及其转授系统权限选项(WITH ADMIN OPTION).收回系统权限使用REVOKE命令完成.

REVOKE system_priv[,system_priv] ….

FROM {user | role | PUBLIC}[ , { user | role | public}]…

需要注意的是,系统权限不会被级联收回.

管理对象权限.

对 象权限是指访问其他用户方案对象的权利,在oracle数据库中,用户可以直接访问其方案对象,单如果要访问其他用户的方案对象时,必须具有相应的对象权 限.对象权限包括ALTER ,DELETE,EXECUTE,INDEX,INSERT,REFERENCES,SELECT和UPDATE等8种.默认情况下,直接授予对象权限时,将访问所有列的权限都授予用户.如果只允许用户更新某些列,必须授予列权限.只能在INSERT,UPDATE和REFERENCES上授予列权限.

授 予或收回对象权限时,可以使用AL L选项,执行GRANT ALL命令后,会将对象上的所有权限授予用户,而执行REVOKE ALL命令则可以收回对象上的所有权限.如果不同方案表之间具有REFERENCES关系,使用REVOKE ALL收回主表上的所有对象权限时需要带由CASCADE CONSTRAINTS选项.

授予对象权限:

GRANT {object_priv [{column_list}]

[,object_priv [{column_list}]]…

| ALL [PRIVILEGES]} ON [Schema.]object

TO {user | role |PUBLIC}[,{user | role | PUBLIC}]…

[WITH GRANT OPTION]

Column_list用于标识列权限所对应列的列表.schema用于标识方案名.

1,授予ALTER 权限

如果用户在其他方案的表或序列上执行ALTER TABLE 或 ALTER SEQUENCE命令,必须具有ALTER 对象权限.

GRANT ALTER ON emp TO blake;

2,授予EXECUTE权限.

如果用户要执行其他方案的包,过程和函数,必须在包,过程和函数上具有EXECUTE对象权限.例如:为了使所有用户都可以执行包DBMS_TRANSACTION,可以将执行该包的权限授予PUBLIC

GRANT EXECUTE ON dbms_transaction TO public;

将EXECUTE ON dbms_transaction对象授予PUBLIC后,所有数据库用户都可以执行该包中的过程和函数.

Execute dbms_transaction.read_only;

3,授予INDEX权限

如果用户要在其他方案的表上建立索引,必须具有INDEX对象权限.

GRANT INDEX ON scott.emp TO blake;

4,授予REFERENCES权限.

用户可以直接在其方案表之间定义主从关系.但在不同方案之间定义主从关系,必须将主表的REFERENCES对象权限授予从表所对应的数据库用户.

GRANT REFERENCES ON dept TO blake;

5.授予列权限.

列 权限用于控制用户对其他方案对象列的访问.只能在INSERT,UPDATE和REFERENCES上授予列权限.直接授予对象权限时,用户可以访问对象 的所有列.假定执行授权操作GRANT UPDATE ON SCOTT.EMP TO BLACK,此时BLACKE可以更新SCOTT.EMP表上的所有列,为了使用户BLAKE只能更新SAL列,必须为其授予列权限.

GRANT UPDATE(sal) ON emp TO blake;

6,使用WITH GRANT OPTION选项.

WITH GRANT OPTION选项用于转授对象权限.该选项只能授予用户,而不能授予角色.

Conn scott/tiger

GRANT SELECT ON emp TO blake WITH GRANT OPTION;

Conn blake/blake

GRANT SELECT ON scott.emp TO jones;

显示对象权限

1,显示对象权限.

数据字典视图DBA_TAB_PRIVS,可以显示所有用户或角色的对象权限信息.

数据字典视图ALL_TAB_PRIVS.可以显示当前用户或PUBLIC的对象权限.

数据字典视图USER_TAB_PRIVS.可以显示当前用户对象权限的信息.

下面以显示BLAKE用户被授予的所有权限为例,说明使用数据字典视图DBA_TAB_PRIVS的方法.

Conn system/manager

Col grantor format a10

Col object format a15

Col privilege format a10

SELECT grantor, owner ||’.’||table_name oject,privilege

FROM dba_tab_privs

WHERE grantee=’BLAKE’;

Grantor用于标识授权用户,owner用于标识对象所有者,table_name用于标识数据库对象,privilege用于标识对象权限,grantee用于标识被授权的用户或角色.

2,显示列权限.

数据字典视图DBA_COL_PRIVS,显示所有用户或角色的列权限信息.

数据字典视图ALL_COL_PRIVS,显示当前用户或PUBLIC的列权限信息;

数据字典视图USER_COL_PRIVS,显示当前用户的列权限信息.

下面以显示BLAKE用户被授予的所有列权限为例,说明使用数据字典视图DBA_COL_PRIVS的方法.

Conn system/manager

Col owner format a10

Col table_column format a15

Col privilege format a10

SELECT owner,table_name||’.’||column_name table_column,

Privilege FROM dba_col_privs

WHERE grantee=’BLAKE’;

owner用于标识对象所有者,table_name用于标识数据库对象,Column_name用于标识列名,privilege用于标识对象权限,grantee用于标识被授权的用户或角色.

3,显示用户授出的列权限.

数据字典视图ALL_COL_PRIVS_MADE,显示对象所有者或授权用户授出的所有列权限.

数据字典视图USER_COL_PRIVS_MADE,显示当前用户授出的所有列权限.

SELECT grantee,privilege,table_name||’.’||column_name

Tab_column FROM user_col_privs_made;

4,显示用户所具有的权限.

ALL_COL_PRIVS_RECD可以显示用户或PUBLIC组被授予的列权限.

USER_COL_PRIVS_RECD可以显示当前被授予的列权限.

下面显示BLAKE用户被授予的列权限为例,说明使用数据字典视图ALL_COL_PRIVS_RECD的方法.

SELECT privilege, table_name||’.’||column_name tab_column,

Grantor FROM all_col_privs_recd WHERE grantee=’BLAKE’;

5,显示用户所授出的对象权限.

ALL_TAB_PRIVS_MADE,可以显示对象所有者或授权用户所授出的所有对象权限.

USER_TAB_PRIVS_MADE,可以显示当前用户所授出的所有对象权限.

6,显示用户所具有的对象权限

ALL_TAB_PRIVS_RECD可以显示用户或PUBLIC组被授予的对象权限.

USER_TAB_PRIVS_RECD可以显示当前用户被授予的对象权限.

收回对象权限.

收回对象权限一般情况下是有对象所有者完成的,如果已其他用户身份收回对象权限,则要求该用户必须是权限授予者

REVOKE { object_priv[, object_priv ] … | all [privileges]}

ON [schema.]object

FROM {user | role | PUBLIC} [, {user | role | PUBLIC} ] …

[CASCADE CONSTRAINTS];

Object_priv用于指定对象权限,schema用于指定方案名,object用于指定对象名,user用于指定用户名,role用于指定角色,PUBLIC用于指定PUBLIC用户组.

收回了用户所具有的对象权限后,用户将不能执行该对象权限所对应的SQL命令

需要注意.对象权限会被级联收回.

例子:

REVOKE SELECT ON emp FROM blake;

若收回BLAKE用户的权限,也会级联收回BLAKE授予其他用户的权限

权限(Privilege)是指执行特定类型SQL命令或访问其他方案对象的权利,权限包括系统权限和对象权限两种类型.系统权限(System Privilege)是指执行特定类型SQL命令的权利,它用于控制用户可以执行的一个或一组数据库操作.常用的系统权限:

CREATE SESSION 连接到数据库

CREATE TABLE 建表

CREATE VIEW 建立视图

CREATE PUBLIC SYNONYM 建立同义词

CREATE SEQUENCE 建立序列

CREATE PROCEDURE 建立过程,函数和包

CREATE TRIGGER 建立触发器

CREATE CLUSTER 建立簇

CREATE TYPE 建立对象类型

CREATE DATABASE LINK 建立数据库链

另外,oracle还提供了一类ANY系统权限,当用户具有该类系统权限时,可以在任何方案中执行相应操作.如果用户具有SELECT ANY TABLE系统权限,那么用户可以查询任何方案的表.

需要注意,当初始化参数O7_DICTIONARY_ACCESSIBILITY被设置为FALSE时,除了SYSDBA和DBA用户外,即使用户具有SELECT ANY TABLE权限,也不能访问数据字典基表和数据字典视图DBA_XXX.

对象权限(Object privilege)是指访问其他方案对象的权利,它用于控制用户对其他方案对象的访问.

管理系统权限

授予系统权限

GRANT system_priv[,system_priv,…]

To {user | role | PUBLIC},[,{user | role | PUBLIC} ] …

[WITH ADMIN OPTION];

System_priv 用于指定系统权限.授权用户组PUBLIC,在授予系统权限时可以带有WITH ADMIN OPTION选项,带有该选项后,被授权的用户,角色还可以将相应的权限授予其他用户,角色.需要注意,系统权限UNLIMITED TABLESPACE不能授予角色.

CRANT CREATE SESSION,CREATE TABLE TO blake WITH ADMIN OPTION;

GRANT GREATE VIEW TO blake;

显示系统权限.

1,显示所有系统权限.

SELECT name FROM system_privilege_map;

2,显示用户或角色所具有的系统权限.

查询数据字典视图DBA_SYS_PRIVS,可以显示所有用户或角色所具有的系统权限.通过查询数据字典视图USER_SYS_PRIVS,可以显示当前用户所具有的系统权限.

SELECT * FROM dba_sys_privs WHERE GRANTEE=’BLAKE’;

GRANTEE用于标识权限拥有者(用户或角色),PRIVILEGE用于标识系统权限.ADMIN_OPTION用于标识系统权限传授选项,其中YES表示可以转授该系统权限,NO表示不能转授该系统权限.

3,显示当前会话所具有的系统权限.

通过查询数据字典视图session_privs,可以显示当前会话所具有的系统权限.

SELECT * FROM session_privs;

收回系统权限

收回系统权限一般是由DBA完成的,如果要以其他用户身份收回系统权限,要求该用户必须具有相应系统权限及其转授系统权限选项(WITH ADMIN OPTION).收回系统权限使用REVOKE命令完成.

REVOKE system_priv[,system_priv] ….

FROM {user | role | PUBLIC}[ , { user | role | public}]…

需要注意的是,系统权限不会被级联收回.

管理对象权限.

对 象权限是指访问其他用户方案对象的权利,在oracle数据库中,用户可以直接访问其方案对象,单如果要访问其他用户的方案对象时,必须具有相应的对象权 限.对象权限包括ALTER ,DELETE,EXECUTE,INDEX,INSERT,REFERENCES,SELECT和UPDATE等8种.默认情况下,直接授予对象权限时,将访问所有列的权限都授予用户.如果只允许用户更新某些列,必须授予列权限.只能在INSERT,UPDATE和REFERENCES上授予列权限.

授 予或收回对象权限时,可以使用AL L选项,执行GRANT ALL命令后,会将对象上的所有权限授予用户,而执行REVOKE ALL命令则可以收回对象上的所有权限.如果不同方案表之间具有REFERENCES关系,使用REVOKE ALL收回主表上的所有对象权限时需要带由CASCADE CONSTRAINTS选项.

授予对象权限:

GRANT {object_priv [{column_list}]

[,object_priv [{column_list}]]…

| ALL [PRIVILEGES]} ON [Schema.]object

TO {user | role |PUBLIC}[,{user | role | PUBLIC}]…

[WITH GRANT OPTION]

Column_list用于标识列权限所对应列的列表.schema用于标识方案名.

1,授予ALTER 权限

如果用户在其他方案的表或序列上执行ALTER TABLE 或 ALTER SEQUENCE命令,必须具有ALTER 对象权限.

GRANT ALTER ON emp TO blake;

2,授予EXECUTE权限.

如果用户要执行其他方案的包,过程和函数,必须在包,过程和函数上具有EXECUTE对象权限.例如:为了使所有用户都可以执行包DBMS_TRANSACTION,可以将执行该包的权限授予PUBLIC

GRANT EXECUTE ON dbms_transaction TO public;

将EXECUTE ON dbms_transaction对象授予PUBLIC后,所有数据库用户都可以执行该包中的过程和函数.

Execute dbms_transaction.read_only;

3,授予INDEX权限

如果用户要在其他方案的表上建立索引,必须具有INDEX对象权限.

GRANT INDEX ON scott.emp TO blake;

4,授予REFERENCES权限.

用户可以直接在其方案表之间定义主从关系.但在不同方案之间定义主从关系,必须将主表的REFERENCES对象权限授予从表所对应的数据库用户.

GRANT REFERENCES ON dept TO blake;

5.授予列权限.

列 权限用于控制用户对其他方案对象列的访问.只能在INSERT,UPDATE和REFERENCES上授予列权限.直接授予对象权限时,用户可以访问对象 的所有列.假定执行授权操作GRANT UPDATE ON SCOTT.EMP TO BLACK,此时BLACKE可以更新SCOTT.EMP表上的所有列,为了使用户BLAKE只能更新SAL列,必须为其授予列权限.

GRANT UPDATE(sal) ON emp TO blake;

6,使用WITH GRANT OPTION选项.

WITH GRANT OPTION选项用于转授对象权限.该选项只能授予用户,而不能授予角色.

Conn scott/tiger

GRANT SELECT ON emp TO blake WITH GRANT OPTION;

Conn blake/blake

GRANT SELECT ON scott.emp TO jones;

显示对象权限

1,显示对象权限.

数据字典视图DBA_TAB_PRIVS,可以显示所有用户或角色的对象权限信息.

数据字典视图ALL_TAB_PRIVS.可以显示当前用户或PUBLIC的对象权限.

数据字典视图USER_TAB_PRIVS.可以显示当前用户对象权限的信息.

下面以显示BLAKE用户被授予的所有权限为例,说明使用数据字典视图DBA_TAB_PRIVS的方法.

Conn system/manager

Col grantor format a10

Col object format a15

Col privilege format a10

SELECT grantor, owner ||’.’||table_name oject,privilege

FROM dba_tab_privs

WHERE grantee=’BLAKE’;

Grantor用于标识授权用户,owner用于标识对象所有者,table_name用于标识数据库对象,privilege用于标识对象权限,grantee用于标识被授权的用户或角色.

2,显示列权限.

数据字典视图DBA_COL_PRIVS,显示所有用户或角色的列权限信息.

数据字典视图ALL_COL_PRIVS,显示当前用户或PUBLIC的列权限信息;

数据字典视图USER_COL_PRIVS,显示当前用户的列权限信息.

下面以显示BLAKE用户被授予的所有列权限为例,说明使用数据字典视图DBA_COL_PRIVS的方法.

Conn system/manager

Col owner format a10

Col table_column format a15

Col privilege format a10

SELECT owner,table_name||’.’||column_name table_column,

Privilege FROM dba_col_privs

WHERE grantee=’BLAKE’;

owner用于标识对象所有者,table_name用于标识数据库对象,Column_name用于标识列名,privilege用于标识对象权限,grantee用于标识被授权的用户或角色.

3,显示用户授出的列权限.

数据字典视图ALL_COL_PRIVS_MADE,显示对象所有者或授权用户授出的所有列权限.

数据字典视图USER_COL_PRIVS_MADE,显示当前用户授出的所有列权限.

SELECT grantee,privilege,table_name||’.’||column_name

Tab_column FROM user_col_privs_made;

4,显示用户所具有的权限.

ALL_COL_PRIVS_RECD可以显示用户或PUBLIC组被授予的列权限.

USER_COL_PRIVS_RECD可以显示当前被授予的列权限.

下面显示BLAKE用户被授予的列权限为例,说明使用数据字典视图ALL_COL_PRIVS_RECD的方法.

SELECT privilege, table_name||’.’||column_name tab_column,

Grantor FROM all_col_privs_recd WHERE grantee=’BLAKE’;

5,显示用户所授出的对象权限.

ALL_TAB_PRIVS_MADE,可以显示对象所有者或授权用户所授出的所有对象权限.

USER_TAB_PRIVS_MADE,可以显示当前用户所授出的所有对象权限.

6,显示用户所具有的对象权限

ALL_TAB_PRIVS_RECD可以显示用户或PUBLIC组被授予的对象权限.

USER_TAB_PRIVS_RECD可以显示当前用户被授予的对象权限.

收回对象权限.

收回对象权限一般情况下是有对象所有者完成的,如果已其他用户身份收回对象权限,则要求该用户必须是权限授予者

REVOKE { object_priv[, object_priv ] … | all [privileges]}

ON [schema.]object

FROM {user | role | PUBLIC} [, {user | role | PUBLIC} ] …

[CASCADE CONSTRAINTS];

Object_priv用于指定对象权限,schema用于指定方案名,object用于指定对象名,user用于指定用户名,role用于指定角色,PUBLIC用于指定PUBLIC用户组.

收回了用户所具有的对象权限后,用户将不能执行该对象权限所对应的SQL命令

需要注意.对象权限会被级联收回.

例子:

REVOKE SELECT ON emp FROM blake;

若收回BLAKE用户的权限,也会级联收回BLAKE授予其他用户的权限