oracle9i学习笔记之十二 控制用户访问

1.创建用户
  DBA用CREATE USER 语句创建用户
  CREATE USER user
  INDENTIFIED BY password;

  例：CREATE USER test IDENTIFIED BY clerk;

2.修改用户口令
  ALTER USER user
  IDENTIFIED BY password;

  例：ALTER USER test IDENTIFIED BY test;

3.授权
  GRANT privilege [,privilege...]
  TO    user[,user | role,PUBLIC...];
 
  例：GRANT create session,create table,create sequence,create view
      TO    test;
 
  注：应用程序的开发者，可能有下面的系统权限：
  -CREATE SESSION
  -CREATE TABLE
  -CREATE SEQUENCE
  -CREATE VIEW
  -CREATE PROCEDURE

4.创建角色
  CREATE ROLE manager;

5.授权给一个角色
  GRANT DROP ANY table,DROP ANY view
  TO    manager;

6.授一个角色给用户
  GRANT manager TO test;

7.关于角色的数据字典
  ROLE_SYS_PRIVS   ----授予给角色的系统权限  
  USER_ROLE_PRIVS  ----可由用户访问的角色
  ROLE_ROLE_PRIVS  ----授予角色的角色权限

8.对象权限
  对象权限        表      视图       序列        过程，函数，包
  ALTER           Y                   Y
  SELECT          Y        Y          Y
  DELETE          Y        Y       
  INSERT          Y        Y
  UPDATE          Y        Y    
  INDEX           Y      
  EXECUTE                                               Y

9.给对象授权
   GRANT    object_priv[(columns)]
   ON       object
   TO       {user|role|PUBLIC}
   [WITH GRANT OPTION];

   例1：给EMP表授予查询权
        GRANT select
        ON    emp
        TO    test;
   例2：授予列的更新权限到用户和角色
        GRANT update(sal,comm)
        ON    emp
        TO    test,manager;
   例3：给一个用户授权以级联权限授予
        GRANT select,insert
        ON    dept
        TO    test
        WITH  GRANT OPTION;
   例4：允许所有在系统上的用户从scott的dept表中查询数据
        GRANT  select
        ON     scott.dept
        TO     PUBLIC;

10.确认已授予的权限

   数据字典视图                      说明
   ROLE_SYS_PRIVS              授予角色的系统权限
   USER_SYS_PRIVS              授予用户的系统权限
   ROLE_ROLE_PRIVS             授予角色的角色权限
   USER_ROLE_PRIVS             可由用户访问的角色
   ROLE_TAB_PRIVS              授予角色的表权限
   USER_TAB_PRIVS              授予用户的对象权限
   USER_COL_PRIVS              授予用户在对象列上的权限

11.撤销对象权限
   REVOKE {privilege[,privilege...]|ALL}
   ON     object
   FROM   {user[,user...]|role|PUBLIC};
  
   注:通过WITH GRANT OPTION子句授予其他用户的权限也被撤消

   例：用户scott撤消在DEPT表上给予用户test的SELECT和INSERT权限
       REVOKE  select,insert
       ON      dept
       FROM    test;

12.撤消系统权限
   REVOKE {privilege[,privilege...]}
   FROM   user[,user|role,PUBLIC...]

   例:撤消test用户的create view系统权限
      REVOKE create view
      FROM   test;

13.创建数据库连接
   CREATE DATABASE LINK remote
   USING  'qqq';

14.写使用数据库连接的SQL语句
   SELECT * FROM emp@remote;

15.总结
  
   语句                       作用
   CREATE USER         创建用户（通常由DBA执行）
   ALTER USER          改变用户口令
   CREATE ROLE         创建一个权限的集合（通常由DBA执行）
   GRANT               给予其他用户权限来访问本用户的对象
   REVOKE              删除在用户对象上的权限
  

练习
1.创建用户soft，密码为yang
 
  CONN sys/sys@orcl as sysdba;

  CREATE USER soft IDENTIFIED BY yang;

2.修改用户soft的口令为soft

  ALTER USER soft IDENTIFIED BY soft;

3.授予soft用户登录权限、create table、create any view权限

  GRANT create session,create table,create any view
  TO    soft;

4.查看用户soft被授予的权限

   CONN soft/soft@orcl;

   SELECT *
   FROM   USER_SYS_PRIVS;

5.创建角色CLERK

   CONN sys as sysdba;
   password:sys
  
   CREATE ROLE clerk;

6.授予角色CLERK以下系统权限：alter any table、drop any view

   GRANT alter any table,drop any view TO clerk;

7.将角色clerk授予soft用户

   GRANT clerk TO soft;

8.查询所授予的角色

   CONN soft/soft@orcl;
 
   SELECT * FROM ROLE_SYS_PRIVS;
   SELECT * FROM USER_ROLE_PRIVS;

9.授予soft用户查询、更新（dname列）、插入scott用户的dept表的权限

   CONN scott/tiger@orcl;

   GRANT select,update(dname),insert
   ON    dept
   TO    soft;

10.soft用户插入、查询scott用户的dept表中数据

   CONN soft/soft@orcl;
   
   SELECT * FROM scott.dept;

   INSERT INTO scott.dept
   VALUES      (70,'coding','shenyang');

11.更新dept表中数据

   UPDATE scott.dept
   SET    dname='dep'
   WHERE  deptno=70;

12.scott用户将emp表的查询权限使用WITH GRANT OPTION授予soft用户，然后soft用户将该查询权限再授予test用户

   CONN scott/tiger@orcl;

   GRANT select ON emp
   TO    soft
   WITH GRANT OPTION;

   CONN sys/sys@orcl as sysdba;

   CREATE USER test IDENTIFIED BY test;

   CONN soft/soft@orcl;
     
   GRANT select ON scott.emp TO test;

13.在soft用户下创建视图dept_v，查询scott用户下的dept表

   CREATE OR REPLACE VIEW dept_v
   AS SELECT *
      FROM   scott.dept;

14.查看该视图信息

   DESC dept_v;
 
15.删除soft用户对于scott用户的dept表的select权限

   CONN scott/tiger@orcl;  

   REVOKE select ON dept
   FROM   soft;

16.查看soft用户的dept_v视图

   CONN sys/sys@orcl as sysdba;
   或：
   CONN soft/soft@orcl;

   DESC soft.dept_v;
   注：scott用户无法查看

17.删除soft用户CREATE TABLE的系统权限

   REVOKE create table
   FROM   soft;

18.删除用户soft
  
   DROP USER soft;
   GRANT select ON emp
   TO    soft
   WITH GRANT OPTION;

   CREATE USER test IDENTIFIED BY test;
   GRANT  create session  TO test;
   GRANT  select   ON   emp  TO test;

13.在soft用户下创建视图dept_v，查询scott用户下的dept表

   CREATE OR REPLACE VIEW dept_v
   AS SELECT *
      FROM   scott.dept_v;

14.查看该视图信息

   DESC dept_v;
 
15.删除soft用户对于scott用户的dept表的select权限

   REVOKE select ON scott.dept
   FROM   soft;

16.查看soft用户的dept_v视图

   DESC soft.dept_v;

17.删除soft用户CREATE TABLE的系统权限

   REVOKE create view
   FROM   soft;