ORACLE之用户访问控制（oracle给用户访问表的权限）

【九】用户访问控制

9.1 认识Oracle用户

Oracle数据库是多用户系统，每个用户一个账户。

9.1.1查看数据库里有多少用户？

SQL>select username from dba_users;

9.1.2用户默认的存储空间

每个用户账户都可以指定默认的表空间，用户创建的任何对象（如表或索引）将缺省保存在此表空间中，如果创建用户时没有指定默认表空间，那么该用户缺省使用数据库级的默认表空间。

9.1.3数据库默认表空间

SQL> select * from database_properties where rownum<=10;

设置数据库的默认表空间。

SQL> alter database default tablespace TABLESPACENAME;

9.2密码认证介绍

用户登录时需要密码认证，认证主要有几种方法：

1）OS认证 用于sys用户本地登录

2）口令文件认证 用于sys用户远程登录

3）数据库（字典）认证 普通用户本地和远程登录

以上三种用户认证是最常用的，另外还有一些认证方式，比如：

4）外部密码认证 Oracle用户关联OS用户登录

如果配置了os_authent_prefix参数，如缺省值为'ops#39;，当数据库中存在用户'ops$tim'，且对该用户启用了外部验证。那么在操作系统上以tim用户登录成功后，就可以直接键入sqlplus / 登录用户是ops$tim，密码由操作系统外部提供，不是数据字典认证。

示例：

1)用户前缀缺省是OPS$

sqlplus / as sysdba

SQL> show parameter os_authent_prefix

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

os_authent_prefix string ops$

2) 建立Oracle的用户，密码是操作系统提供（外部）

SQL> create user ops$tim identified externally;

SQL> grant connect to ops$tim;

SQL> select USERNAME,PASSWORD from dba_users where username='OPS$TIM';

USERNAME PASSWORD

------------------------------ ------------------------------

OPS$TIM EXTERNAL

3) 建立操作系统用户

[root@cuug ~]# useradd tim

4）设置用户环境变量

[root@cuug ~]#su - tim

[tim@cuug ~]$ vi .bash_profile (添加环境变量)

ORACLE_BASE=/u01

ORACLE_HOME=$ORACLE_BASE/oracle

ORACLE_SID=prod

PATH=$ORACLE_HOME/bin:$PATH

export ORACLE_BASE ORACLE_HOME ORACLE_SID PATH

[tim@cuug ~]$ source .bash_profile

5）以操作系统身份登录

[tim@cuug ~]$ id

uid=501(tim) gid=502(tim) groups=502(tim)

[tim@cuug ~]$ sqlplus /

SQL*Plus: Release 11.2.0.1.0 Production on Sat Jul 2 22:25:12 2016

Copyright (c) 1982, 2009, Oracle. All rights reserved.

......

SQL> show user;

USER is "OPS$TIM"

SQL>

9.3空间配额的概念

配额（quota)是表空间中为用户的对象使用的空间量，dba建立用户时就应该考虑限制用户的磁盘空间配额，否则无限制配额的用户可能把你的表空间撑爆（甚至损坏system表空间）。创建用户时，可以指定用户的缺省表空间及其配额，然后才可以建表。

单一的用户不需要临时表空间和UNOD表空间的配额：

更改用户配额的一些命令：

ALTER USER tim QUOTA 10m ON test_tbs;

给tim用户在test_tbs上使用了10m空间的配额

ALTER USER tim QUOTA unlimited on test_tbs --不受限制

ALTER USER tim QUOTA 0 ON test_tbs; 收回剩余配额

9.4概要文件的概念

9.4.1作用：对用户访问数据库做一些限制。

1）概要文件（profile)具有两个功能，一个是KERNEL资源（如CPU资源）限制，另一个是PASSWORD资源（如登录）限制。

2）始终要实施口令控制，而对于KERNEL资源限制，则只有实例参数RESOURE_LIMIT为TRUE时（默认是FALSE)才会实施。

3）系统自动使用概要文件，有一个默认的default profile,限制很宽松，作用较小。

4）可以使用create profile为用户创建它自己的概要文件，没有指定值的参数，其值就从default profile的当前版本中提取。

9.4.2概要文件示例

创建一个概要文件，对tim用户施加两个限制。

1）如出现两次口令失误，将账户锁定。

2）tim用户最多同时以两个session登录

步骤一、创建概要文件并命名two_error

SQL> create profile two_error limit failed_login_attempts 2;

SQL> alter profile two_error limit Sessions_per_user 2;

dba_profiles视图可以列出所有profile资源;

SQL> select * from dba_profiles where PROFILE='TWO_ERROR';

步骤二、将概要文件分配给tim用户

SQL> alter user tim profile two_error;

步骤三、tim用户尝试两次登录使用错误密码

SQL> conn tim/fdfd

ERROR: ORA-28000: 账户已被锁定

步骤四、sys为tim解锁

SQL> alter user tim account unlock;

步骤五、resource_limit=true才可以使得KERNEL资源限制对profile起作用(考点)

SQL> alter system set resource_limit=true;

步骤六、测试tim只能同时有两个session登录

步骤七、sys删掉了two_error概要文件

SQL> drop profile two_error cascade;

删除two_error后tim用户又绑定到default profile上。

9.5权限和角色

9.5.1数据库的安全问题

1）系统安全：

用户名、口令、概要文件、磁盘配额等

2）数据库安全：

对数据库系统和数据库对象的访问及操作，用户具备系统权限才能够访问数据库，

具备对象权限才能访问数据库中的对象。

9.5.2系统权限

针对于database的相关权限，通常由DBA授予（11g 已有200多种）

典型的DBA权限：

CREATE USER

DROP USER

SELECT ANY TABLE

CREATE ANY TABLE

典型的一般用户的系统权限：

CREATE SESSION

CREATE TABLE

CREATE VIEW

CREATE PROCEDURE

9.5.3 角色

1）为什么会有角色

系统权限太过繁杂，Oracle建议将系统权限打包成角色，通过角色授权权限，目的就是为了简化用户访问管理。

Oracle有一些预定义角色，如：connect、resource、dba角色等

也可以创建和删除自定义角色

SQL> CREATE role myrole;

SQL> DROP role myrole;

2）授予和回收权限的语法

1、授予系统权限和角色的语法

GRANT sys_privs,[role] TO user|role|PUBLIC [WITH ADMIN OPTION]

2、回收系统权限和角色的语法

Revoke sys_prvs[role] FROM user|role|PUBLIC

3）使用预定义角色的考虑

connect和resource两个角色，可以满足一般用户大多数需求。

SQL> grant connect, resource to tim;

SQL> revoke unlimited tablespace from tim;

SQL> alter user tim quota 10m on test_tbs;

查看用户表空间配额：

SQL> select tablespace_name,username,max_bytes from DBA_TS_QUOTAS where username='TIM';

当unlimited tablespace 和quota共存时，以unlimited为准。

关于权限与空间配额的考虑，用户有空间配额才能建表。

空间配额不是权限的概念，而是用户属性的概念。

作为DBA，在生产系统上一定要为普通用户分配空间配额，限制使用存储空间。

有两种办法分配空间配额：

第一种：在建立用户时指定空间配额，这个方法只能分配用户缺省的表空间上的配额，不是很灵活。

第二种：授予 resource角色，包含有unlimited tablespace权限此权限对所有表空间不设限，包括系统表空间，请立即收回该权限，然后再分配空间配额。

系统权限：sys, system 拥有普通用户的所有对象权限，并有代理授权资格。

系统权限里的any含义：

SQL> conn / as sysdba;

SQL> grant create any table to tim;

tim可以为SCOTT建表，这张表是属于SCOTT用户下的一个对象。

SQL> conn tim/tim

SQL> create table scott.t100 (id int);

9.5.4对象权限

1、授予对象权限的语法

GRANT object_privs ON object TO user|role|PUBLIC [WITH GRANT OPTION]

2、回收对象权限的语法

REVOKE object_prvs ON object FROM user|role|PUBLIC

角色授权的示例

9.5.5、授权注意事项

1）系统权限和对象权限语法格式不同，不能混合使用

SQL>grant create table,select on emp to tim 【错】

2）系统权限和角色语法相同可以并列授权

SQL>grant connect,create table to tim;

3、可以使update对象权限精确到列

SQL> grant select, update(sal) on scott.emp to tim;

4）可以一条语句并列授予多个用户

SQL>grant connect to tim,ran;

5）可以通过授权建立用户,如ran用户不存在

SQL>grant connect,resource to ran identified by ran;

9.5.6关于WITH ADMIN OPTION和WITH GRANT OPTION选项

1）系统权限的管理权限WITH ADMIN OPTION

用户在获得权限时一旦获得WITH ADMIN OPTION，就意味着可以将该权限对其他用户进行授予、回收（不管谁授予的）的管理权限操作。

2）对象权限的管理权限WITH GRANT OPTION

用户在获得对象权限时一旦获得WITH GRANT OPTION，就意味着该用户可以授予、回收（只能它授予的）该对象权限。

3）系统权限和对象权限的级联收回

User1有with admin option，可以单独对user2用户或user3用户revoke，但不能级联收回，User1有with grant option只能对它授予的user2用户revoke，并会级联收回user3。

9.5.7对象权限在存储过程中的使用

存储过程proc1中包含了一些tim没有权限的DML操作，scott将proc1的execute权限赋给tim，，那么tim能成功地执行存储过程proc1吗？这个问题涉及到了create procedure时invoker_rights_clause的两个选项：

1、AUTHID CURRENT_USER

当执行存储过程时，检查用户DML操作的对象权限。

2、AUTHID DEFINER（默认）

当执行存储过程时，不检查用户DML操作的对象权限。

测试：默认情况下AUTHID DEFINER

第一步

SQL> create table scott.a (d1 date);

SQL> grant connect,resource to tim identified by tim;

SQL> conn scott/scott

第二步

create or replace procedure proc1 as

begin

insert into scott.a values(sysdate);

commit;

end;

第三步

SQL> grant execute on proc1 to tim;

第四步

SQL> conn tim/tim

SQL> exec scott.proc1;

测试AUTHID CURRENT_USER选项

第一步

SQL>conn scott/scott

SQL>create or replace procedure proc1

AUTHID CURRENT_USER as

begin

insert into scott.a values(sysdate);

commit;

end;

第二步，结果报错!

SQL> conn tim/tim

SQL> exec scott.proc1;

第三步，让scott在execute权限和insert权限都具备的情况下再执行上次操作

SQL> grant all on a to tim;

第四步，重复第二步，结果OK!

9.5.8有关权限的常用视图

the end ！！！

@jackman 共筑美好！