授予系统权限

1.创建4个用户，分别是:dog，口令为wangwang；
cat,口令为miaomiao;
pig,口令为hengheng
fox，口令为loveyou
///
create user dog
identified by wangwang;


create user cat
identified by miaomiao;


create user pig 
idntified by hengheng;


create userfox
identified by lovayou


2.尝试用cat用户登录
SQL> conn cat/miaomiao
ERROR:
ORA-01045: user CAT lacks CREATE SESSION privilege; logon denied




Warning: You are no longer connected to ORACLE.


由于刚创建一个用户，最初没有任何权限.


3.授权操作
SQL> conn system/oracle
Connected.


SQL> grant create session,select any table,create table,create view to cat;


Grant succeeded.




4.将用户cat的权限分给dog
SQL> conn cat/miaomiao
Connected.
SQL> grant create session,select any table to dog;
grant create session,select any table to dog
*
ERROR at line 1:
ORA-01031: insufficient privileges


发现：授权失败，说明cat用户无权授权给dog
如果在grant语句中使用了with admin option子句，那么，被授权的用户可以将这些
系统权限授予其用户。
如下:


SQL> revoke create session,select any table,create table,create view from cat;


Revoke succeeded.


使用with admin option授权:SQL> grant create session,select any table,create table,create view to
  2  cat with admin option;


Grant succeeded.




查询是否授权成功:




SQL> select * from dba_sys_privs
  2  where grantee='CAT';


GRANTEE                        PRIVILEGE                ADM
------------------------------ ------------------------ ---
CAT                            CREATE SESSION           YES
CAT                            CREATE VIEW              YES
CAT                            CREATE TABLE             YES
CAT                            SELECT ANY TABLE         YES


查询显示:cat用户只有刚授权的4个系统权限并可以将这些权限授予其它用户，因为ADM一列的值都是YES


由cat用户授权给dog:


SQL> conn cat/miaomiao
Connected.


SQL> grant create session,select any table,create table,create view to dog
  2  with admin option;


Grant succeeded.


由dog用户授权给fox:
SQL> conn dog/wangwang
Connected.
SQL> grant create session,select any table,create table,create view to fox
  2  with admin option;


Grant succeeded.


现在，使用system用户登录，查询刚授权的这些用户权限:




SQL> select * from dba_sys_privs
  2  where grantee in('CAT','DOG','FOX','PIG');


GRANTEE                        PRIVILEGE                ADM
------------------------------ ------------------------ ---
DOG                            CREATE SESSION           YES
CAT                            CREATE SESSION           YES
CAT                            CREATE VIEW              YES
CAT                            CREATE TABLE             YES
DOG                            SELECT ANY TABLE         YES
CAT                            SELECT ANY TABLE         YES
DOG                            CREATE TABLE             YES
FOX                            CREATE TABLE             YES
DOG                            CREATE VIEW              YES
FOX                            CREATE VIEW              YES
FOX                            SELECT ANY TABLE         YES


GRANTEE                        PRIVILEGE                ADM
------------------------------ ------------------------ ---
FOX                            CREATE SESSION           YES


12 rows selected.


注：使用with admin option子句要非常谨慎，因为如果使用不当，可能造成系统安全的失控。