ORACLE用户权限管理

陈晓龙

一 用户管理

1、创建用户
create user dylan
identified by 123
default tablespace users  --指定表空间
quota 100m on users       --指定配额
temporary tablespace temp  --指定临时表空间
profile default;           --指定概要文件


grant create session to dylan;  --授予创建会话权限


--create session 创建会话
--unlimited tablespace 不限制使用表空间


2、修改用户
alter user dylan
identified by 234
default tablespace users_new  --指定表空间
quota 100m on users_new       --指定配额
temporary tablespace temp  --指定临时表空间
profile default;


3、删除用户
drop user dylan cascade;


4、查询用户及概要文件信息
DBA_USERS 描述所有数据库用户
ALL_USERS 列出对于当前用户可见的数据库用户，单不进行描述
USER_USERS 仅描述当前用户
DBA_TS_QUOTAS    描述用户表空间配额
USER_TS_QUOTAS   描述当前用户密码概要文件参数
USER_RESOURCE_LIMITS 展示当前用户资源限制
DBA_PROFILES 展示所有概要文件及其限制
RESOURCE_COST 列出每个资源消耗
V$SESSION 列出每个当前会话信息包括用户名
V$SESSTAT 列出用户会话统计数据
V$STATNAME 展示V$SESSTAT统计数据提供翻译
PROXY_USERS 描述可以代表其他用户身份的用户


SELECT USERNAME, VALUE || 'bytes' "Current UGA memory"
   FROM V$SESSION sess, V$SESSTAT stat, V$STATNAME name
WHERE sess.SID = stat.SID
   AND stat.STATISTIC# = name.STATISTIC#
   AND name.NAME = 'session uga memory';


5 创建概要文件profile
create profile query_limit
sessions_per_user 1
idle_time 30
connect_time 60;


alter user dylan profile query_limit;


△概要文件选项含义：
COMPOSITE_LIMIT 综合资源限制（一个会话可消耗的资源总限额）
SESSIONS_PER_USER 每个用户最大会话数
CPU_PER_SESSION 每个会话CPU占用时间（1/100s）
CPU_PER_CALL 每次调用CPU占用时间（1/100s）
LOGICAL_READS_PER_SESSION 每个会话读取最大数据块数量
LOGICAL_READS_PER_CALL 每次调用读取最大数据块数量
IDLE_TIME 最大空闲时间（分钟）
CONNECT_TIME 最大连接时间（分钟）
PRIVATE_SGA 私有SGA（系统全局区，SQL区和PLSQL区）
FAILED_LOGIN_ATTEMPTS 最多失败登录尝试（超出用户被锁）
PASSWORD_LIFE_TIME 密码生命周期（天）
PASSWORD_REUSE_TIME 密码修改后必须经过多少天才能重用
PASSWORD_REUSE_MAX 密码再被重新使用前必须经过多少次修改
PASSWORD_VERIFY_FUNCTION 密码验证函数
PASSWORD_LOCK_TIME 密码锁定时间
PASSWORD_GRACE_TIME 修改密码宽限期天数


--详见oracle概要文件详解.ppt


DROP PROFILE query_limit CASCADE;  --cascade后当前连接用户自动切换至default profile


二 用户权限和角色
1、系统权限
  --△参数：O7_DICTIONARY_ACCESSIBILITY  默认值:false   数据字典保护
  SELECT_CATALOG_ROLE(role) 普通用户访问SYS数据字典视图
  EXECUTE_CATALOG_ROLE(role) 执行目录角色,能够执行所有系统包
  DELETE_CATALOG_ROLE(role) 删除系统审计表记录(AUD$)
  SELECT ANY DICTIONARY 访问SYS模式下任何对象
  
  
2、对象权限

3、用户角色
   --预定义角色
   CONNECT CREATE SESSION
   RESOURCE CREATE CLUSTER, CREATE INDEXTYPE, CREATE OPERATOR, CREATE PROCEDURE, CREATE SEQUENCE, CREATE TABLE, CREATE  TRIGGER, CREATE TYPE
   DBA 所有系统权限WITH ADMIN OPTION
   EXP_FULL_DATABASE 执行全库导出权限；SELECT ANY TABLE, BACKUP ANY TABLE, EXECUTE ANY PROCEDURE, EXECUTE ANY TYPE, ADMINISTER RESOURCE MANAGER, 和 INSERT, DELETE,and UPDATE on the tables SYS.INCVID, SYS.INCFIL, and SYS.INCEXP.
                                还有角色: EXECUTE_CATALOG_ROLE and SELECT_CATALOG_ROLE
   IMP_FULL_DATABASE 执行全库导入权限；详见DBA_SYS_PRIVS
   RECOVERY_CATALOG_OWNER 恢复目录拥有者权限；CREATE SESSION, ALTER SESSION, CREATE SYNONYM, CREATE VIEW, CREATE DATABASE LINK, CREATE                             TABLE, CREATE CLUSTER, CREATE SEQUENCE, CREATE TRIGGER, and CREATE PROCEDURE
   HS_ADMIN_ROLE 保护异构服务数据字典表访问；SELECT_CATALOG_ROLE and EXECUTE_CATALOG_ROLE
   AQ_USER_ROLE 已淘汰；仅保留以兼容8.0；提供DBMS_AQ and DBMS_AQIN包的执行权限
   AQ_ADMINISTRATOR_ROLE 提供管理高级队列权限；ENQUEUE ANY QUEUE,DEQUEUE ANY QUEUE, and MANAGE ANY QUEUE, SELECT privileges on AQ                             tables and EXECUTE privileges on AQ packages


  1、创建角色
  create role dml identified by dml123;
  drop role dml;
  grant select, insert, update, select on hr.employees to dml;
  grant connect to dml;
  
  grant dml to dylan;
  --column级别授权
  grant insert(ename, job) on scott.emp to dml;
  
  revoke connect from dml;
  revoke select, insert, update, select on hr.employees from dml;
  revoke insert(ename, job) on scott.emp from dml;
  
  --REVOKE REFERENCES ON dept FROM jward CASCADE CONSTRAINTS;
  
  --启用role
  SET ROLE dml IDENTIFIED BY dml123;
  --禁用所有role
  SET ROLE NONE;
  --指定role
  ALTER USER dylan DEFAULT ROLE dml;
  
  2、删除角色
  drop role dml;  
  
  3、with admin option 和 with grant option
  with admin option  对应系统权限赋权而言，赋权后不可级联收回权限；
  with grant option  对应对象权限赋权而言，赋权后可级联收回权限；
  
  4、查看授权信息：
  SELECT GRANTEE, OWNER, GRANTOR, PRIVILEGE, GRANTABLE
   FROM DBA_TAB_PRIVS
   WHERE TABLE_NAME = 'EMPLOYEES' and OWNER = 'HR';


  5、权限信息数据字典：
  DBA_COL_PRIVS 描述数据库中所有列对象授权
  ALL_COL_PRIVS
  USER_COL_PRIVS
  ALL_COL_PRIVS_MADE 列出当前用户下列对象授权。（当前用户为对象拥有者或授权者）
  USER_COL_PRIVS_MADE
  ALL_COL_PRIVS_RECD 描述列对象授权（针对当前用户或PUBLIC为被授予者）
  USER_COL_PRIVS_RECD
  
  DBA_TAB_PRIVS DBA视图列出数据库中所有对象授权；ALL视图针对用户或PUBLIC为被授予者；USER视
   图针对当前用户是被授予者。
  ALL_TAB_PRIVS
  USER_TAB_PRIVS
  ALL_TAB_PRIVS_MADE ALL视图列出所有对象授权（针对当前用户是授权者或对象拥有者）；
  USER_TAB_PRIVS_MADE           USER视图列出所有对象授权（针对当前用户是对象拥有者）
  ALL_TAB_PRIVS_RECD ALL视图列出对象授权（针对用户或PUBLIC是被授权者）.                       USER视图列出对象授权（针对当前用户为被授权者）
  USER_TAB_PRIVS_RECD
  DBA_ROLES 列出数据库中所有角色
  DBA_ROLE_PRIVS 列出角色拥有的权限
  USER_ROLE_PRIVS
  DBA_SYS_PRIVS 列出授予用户和角色的所有系统权限
  USER_SYS_PRIVS 列出授予当前用户的系统权限
  ROLE_ROLE_PRIVS    描述被授予角色的角色
  ROLE_SYS_PRIVS 包含角色的系统权限
  ROLE_TAB_PRIVS                包含角色的对象权限
  SESSION_PRIVS                 列出当前启用的权限
  SESSION_ROLES        列出当前启用的角色
  
  --查看用户对象权限
  SELECT TABLE_NAME, PRIVILEGE, GRANTABLE FROM DBA_TAB_PRIVS
    WHERE GRANTEE = 'JWARD';