对象的拥有者可以把一些对象的权限授予其它用户。如果具有grant any object
privilege系统权限,也可以像对象的主人一样将对象的权限授予其它用户或从其它用户那里收回这些对象权限。
将对象的权限授予其它用户的grant语句的格式如下:
grant 对象的权限 |all [(列名[,列名..])]
on 对象名
to [用户名|角色名|public]
[with grant option]
其中:
public:指明对象权限要授予系统的所有用户
with grant option:允许被授予的用户再将这些对象权限授予其它用户。
例如:
为了演示,先授予cat用户create session系统权限,使用如下的DCL语句:
SQL> grant create session to cat;
Grant succeeded.
SQL> revoke select any table from cat,dog;
Revoke succeeded.
SQL> revoke select any table from fox;
Revoke succeeded.
以sysdba登录:
将dbms_space_admin的执行权限授予system用户.
使用数据字典dba_tab_privs查询一下:
SQL> select * from dba_tab_privs
2 where privilege like 'EX%'
3 and table_name like 'DBMS_SPACE_ADMIN%';
GRANTEE OWNER TABLE_NAME GRANTOR PRIVILEGE GRA HIE
---------- --------------- --------------- ---------- --------------- --- ---
SYSTEM SYS DBMS_SPACE_ADMI SYS EXECUTE NO NO
N
接下来以scott用户登录:
SQL> conn scott/tiger
Connected.
SQL> grant select on emp to public;
Grant succeeded.
查询一下,是否授权成功:
SQL> select * from user_tab_privs_made;
GRANTEE TABLE_NAME GRANTOR PRIVILEGE GRA HIE
---------- --------------- ---------- --------------- --- ---
PUBLIC EMP SCOTT SELECT NO NO
继续授权:
SQL> grant update(sal) on emp to cat;
Grant succeeded.
SQL> grant update(job) on emp to cat with grant option;
Grant succeeded.
查询一下是否授权成功:
SQL> select * from user_col_privs_made;
GRANTEE TABLE_NAME COLUMN_NAM GRANTOR PRIVILEGE GRA
---------- --------------- ---------- ---------- --------------- ---
CAT EMP SAL SCOTT UPDATE NO
CAT EMP JOB SCOTT UPDATE YES
使用cat登录,进行授权操作:
SQL> conn cat/miaomiao
Connected.
SQL> grant update(job) on scott.emp to pig,dog with grant option;
Grant succeeded.
为了继续检测with grant option子句的功能,使用dog登录:
SQL> conn dog/wangwang
Connected.
SQL> grant update(job) on scott.emp to fox;
Grant succeeded.
然后,我们再使用scott用户查询数据字典user_col_privs_made.
SQL> conn scott/tiger
Connected.
SQL> select * from user_col_privs_made;
GRANTEE TABLE_NAME COLUMN_NAM GRANTOR PRIVILEGE GRA
---------- --------------- ---------- ---------- --------------- ---
CAT EMP SAL SCOTT UPDATE NO
CAT EMP JOB SCOTT UPDATE YES
PIG EMP JOB CAT UPDATE YES
DOG EMP JOB CAT UPDATE YES
FOX EMP JOB DOG UPDATE NO
如果我们收回cat在emp表上的update权限:
SQL> revoke update on emp from cat;
Revoke succeeded.
我们再查询一下:
SQL> select * from user_col_privs_made;
no rows selected
发现用户的权限都没有了,说明对象权限的回收时级联的。
privilege系统权限,也可以像对象的主人一样将对象的权限授予其它用户或从其它用户那里收回这些对象权限。
将对象的权限授予其它用户的grant语句的格式如下:
grant 对象的权限 |all [(列名[,列名..])]
on 对象名
to [用户名|角色名|public]
[with grant option]
其中:
public:指明对象权限要授予系统的所有用户
with grant option:允许被授予的用户再将这些对象权限授予其它用户。
例如:
为了演示,先授予cat用户create session系统权限,使用如下的DCL语句:
SQL> grant create session to cat;
Grant succeeded.
SQL> revoke select any table from cat,dog;
Revoke succeeded.
SQL> revoke select any table from fox;
Revoke succeeded.
以sysdba登录:
将dbms_space_admin的执行权限授予system用户.
使用数据字典dba_tab_privs查询一下:
SQL> select * from dba_tab_privs
2 where privilege like 'EX%'
3 and table_name like 'DBMS_SPACE_ADMIN%';
GRANTEE OWNER TABLE_NAME GRANTOR PRIVILEGE GRA HIE
---------- --------------- --------------- ---------- --------------- --- ---
SYSTEM SYS DBMS_SPACE_ADMI SYS EXECUTE NO NO
N
接下来以scott用户登录:
SQL> conn scott/tiger
Connected.
SQL> grant select on emp to public;
Grant succeeded.
查询一下,是否授权成功:
SQL> select * from user_tab_privs_made;
GRANTEE TABLE_NAME GRANTOR PRIVILEGE GRA HIE
---------- --------------- ---------- --------------- --- ---
PUBLIC EMP SCOTT SELECT NO NO
继续授权:
SQL> grant update(sal) on emp to cat;
Grant succeeded.
SQL> grant update(job) on emp to cat with grant option;
Grant succeeded.
查询一下是否授权成功:
SQL> select * from user_col_privs_made;
GRANTEE TABLE_NAME COLUMN_NAM GRANTOR PRIVILEGE GRA
---------- --------------- ---------- ---------- --------------- ---
CAT EMP SAL SCOTT UPDATE NO
CAT EMP JOB SCOTT UPDATE YES
使用cat登录,进行授权操作:
SQL> conn cat/miaomiao
Connected.
SQL> grant update(job) on scott.emp to pig,dog with grant option;
Grant succeeded.
为了继续检测with grant option子句的功能,使用dog登录:
SQL> conn dog/wangwang
Connected.
SQL> grant update(job) on scott.emp to fox;
Grant succeeded.
然后,我们再使用scott用户查询数据字典user_col_privs_made.
SQL> conn scott/tiger
Connected.
SQL> select * from user_col_privs_made;
GRANTEE TABLE_NAME COLUMN_NAM GRANTOR PRIVILEGE GRA
---------- --------------- ---------- ---------- --------------- ---
CAT EMP SAL SCOTT UPDATE NO
CAT EMP JOB SCOTT UPDATE YES
PIG EMP JOB CAT UPDATE YES
DOG EMP JOB CAT UPDATE YES
FOX EMP JOB DOG UPDATE NO
如果我们收回cat在emp表上的update权限:
SQL> revoke update on emp from cat;
Revoke succeeded.
我们再查询一下:
SQL> select * from user_col_privs_made;
no rows selected
发现用户的权限都没有了,说明对象权限的回收时级联的。