当一个员工被一个公司录用,要想进入公司通常你还需要得到授权,授权可能是一张ID卡或是一个工作证,当然在没有得到充分的信任之前,你能得到的权限可能还非常有限,在不断的工作和努力之后,你的权限可能会逐步提高,你可能可以翻阅公司的机密档案,还可能被授权打开公司的保险柜,然后你就可以……Ok,到这里打住,如果你不是一个间谍。
数据库的用户,情形和此类似,用户被创建并不意味着你就可以进入公司,在进入公司前你可能需要得到一些授权,在数据库中,这个权限是Create Session,这是进入数据库需要的最小权限。
继续前面的试验,如果以刚刚创建的用户登录就会收到一个错误提示:
C:\>sqlplus "/ as sysdba" SQL*Plus: Release 10.2.0.1.0 - Production on 星期五 1月 12 15:29:28 2007Copyright (c) 1982, 2005, Oracle. All rights reserved.SQL> create user eygle identified by eygle 2 default tablespace users 3 temporary tablespace temp; 用户已创建。 SQL> connect eygle/eygle ERROR: ORA-01045: user EYGLE lacks CREATE SESSION privilege; logon denied 警告: 您不再连接到 ORACLE。
这表示用户EYGLE缺少一个CREATE SESSION的权限。在将CREATE SESSION权限授予之后,用户就可以连接到数据库内部:
SQL> grant create session to eygle; 授权成功。 SQL> connect eygle/eygle 已连接。
好了进入公司内部了,可以随便看看了,我们可以翻翻字典,看看而已:
SQL> select count(*) from dict; COUNT(*) ---------- 628 SQL> select table_name from dict where rownum <6; TABLE_NAME ------------------------------ ALL_XML_SCHEMAS ALL_XML_SCHEMAS2 USER_RESOURCE_LIMITS USER_PASSWORD_LIMITS USER_CATALOG 已选择5行。
至于属于我自己的对象、表、视图等都还没有:
SQL> select count(*) from user_tables; COUNT(*) ---------- 0 SQL> select count(*) from user_views; COUNT(*) ---------- 0
如果此时试图创建数据表,则会收到权限不足的提示:
SQL> create table eygle (name varchar2(20)); create table eygle (name varchar2(20)) * 第 1 行出现错误: ORA-01031: 权限不足
此时又要去获得权限:
SQL> connect / as sysdba 已连接。 SQL> grant create table to eygle; 授权成功。
注意此时的权限还是不足够的,用户还没有获得空间授权:
SQL> connect eygle/eygle 已连接。 SQL> create table eygle (name varchar2(20)); create table eygle (name varchar2(20)) * 第 1 行出现错误: ORA-01950: 对表空间 'USERS' 无权限
需要继续授予用户使用USERS表空间的权限:
SQL> connect / as sysdba 已连接。 SQL> alter user eygle quota 10m on users; 用户已更改。 SQL> connect eygle/eygle 已连接。 SQL> create table eygle (name varchar2(20)); 表已创建。
终于数据表创建成功了。
回顾一下,如果用户需要连接数据库并创建数据表,那么需要获得至少两个授权:CREATE SESSION和CREATE TABLE。
Oracle的权限可以分为两类:
¡ 系统权限,每个系统权限允许用户对Schema对象执行一种或一类特定的数据库操作,例如CREATE SESSION、CREATE TABLESPACE、CREATE TABLE都是系统权限。
数据库中有超过100种不同的系统权限,DBA_SYS_PRIVS可以查询授予用户的系统权限。
¡ 对象权限,每个对象权限允许用户针对特定的对象执行一种特定的行为,例如对某个表的查询权限,如SELECT ON EYGLE就是对表EYGLE这个对象的查询权限。
常见的各类对象权限可以通过如下方式进行授予:
表权限:SELECT、DELETE、UPDATE、INSERT、ALTER
GRANT SELECT ON eygle TO scott;
视图权限:SELECT、DELTE、INSERT、UPDATE
GRANT SELECT ON v_eygle TO scott;
过程、函数、程序包权限:EXECUTE、DEBUG
GRANT EXECUTE ON dbms_sql TO scott;
GRANT和REVOKE是用来执行权限授予与回收的主要手段。
系统权限的授予情况可以通过Oracle的视图DBA_SYS_PRIVS来查询,这个视图包含被授予权限的用户信息(GRANTEE)以及具体的权限(PRIVILEGE)信息,如果授予权限时允许用户转授这个权限,那么ADMIN_OPTION就为YES:
SQL> desc dba_sys_privs 名称 是否为空? 类型 ----------------------------------------- -------- ------------- GRANTEE NOT NULL VARCHAR2(30) PRIVILEGE NOT NULL VARCHAR2(40) ADMIN_OPTION VARCHAR2(3)
可以看看此时EYGLE用户获得的权限:
SQL> col grantee for a10 SQL> select * from dba_sys_privs where grantee='EYGLE'; GRANTEE PRIVILEGE ADM ---------- ---------------------------------------- --- EYGLE CREATE SESSION NO EYGLE CREATE TABLE NO
进一步可以查询数据库中和TABLE有关的权限:
SQL> select distinct privilege from dba_sys_privs 2 where privilege like '%TABLE'; PRIVILEGE ---------------------------------------- CREATE TABLE ALTER ANY TABLE UNDER ANY TABLE COMMENT ANY TABLE UPDATE ANY TABLE INSERT ANY TABLE DELETE ANY TABLE FLASHBACK ANY TABLE CREATE ANY TABLE DROP ANY TABLE BACKUP ANY TABLE LOCK ANY TABLE SELECT ANY TABLE 已选择13行。
可以想象,如果数据库中存在大量用户,如果我们将这些权限一一授予这些用户,操作将会极其繁琐,于是Oracle设计了一个新的技术:角色(Role)。角色是一系列权限的集合,被授予某个角色的用户将获得这个集合中的所有权限,这大大简化了权限的授予和回收工作。
通常一个用户创建之后,我们授予用户两个角色就足够使用了,这两个角色是CONNECT和RESOURCE,接下来来看一下这两个角色是哪些权限的集合:
SQL> select * from dba_sys_privs where grantee='CONNECT'; GRANTEE PRIVILEGE ADM ---------- ---------------------------------------- --- CONNECT CREATE SESSION NO SQL> select * from dba_sys_privs where grantee='RESOURCE'; GRANTEE PRIVILEGE ADM ---------- ---------------------------------------- --- RESOURCE CREATE TRIGGER NO RESOURCE CREATE SEQUENCE NO RESOURCE CREATE TYPE NO RESOURCE CREATE PROCEDURE NO RESOURCE CREATE CLUSTER NO RESOURCE CREATE OPERATOR NO RESOURCE CREATE INDEXTYPE NO RESOURCE CREATE TABLE NO 已选择8行。
Oracle在10gR2中对角色权限进行了重新定义,Oracle 9i/Oracle 10gR1中CONNECT角色具有更多的权限:
$ sqlplus "/ as sysdba" Connected to: Oracle9i Enterprise Edition Release 9.2.0.4.0 - 64bit Production SQL> select * from dba_sys_privs where grantee='CONNECT'; GRANTEE PRIVILEGE ADM ------------------------------ ---------------------------------------- --- CONNECT CREATE VIEW NO CONNECT CREATE TABLE NO CONNECT ALTER SESSION NO CONNECT CREATE CLUSTER NO CONNECT CREATE SESSION NO CONNECT CREATE SYNONYM NO CONNECT CREATE SEQUENCE NO CONNECT CREATE DATABASE LINK NO 8 rows selected.
由于Oracle 10gR2之前,CONNECT角色的权限过多,比如CREATE DATABASE LINK、CREATE TABLE、CREATE VIEW等,都具有重要作用,可能并不需要授予特定用户,所以在后期版本中,这个角色的权限被缩减。
通常在权限授予时,应当遵守权限最小化原则,即仅授予用户完成工作所必须的权限,而且绝不过度授予权限。
现在回顾一下,为了创建一个可以访问数据库的用户,我们可能需要执行如下步骤:
SQL> select * from v$version where rownum <2; BANNER ---------------------------------------------------------------- Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod SQL> create user eygle identified by eygle 2 default tablespace users 3 temporary tablespace temp; 用户已创建。 SQL> grant connect,resource to eygle; 授权成功。 SQL> connect eygle/eygle 已连接。 SQL> create table eygle (name varchar2(20)); 表已创建。
注意到和前面测试的不同了么?此处不再需要对空间进行授权,因为在RESOURCE角色的授予过程中,Oracle潜在的给用户EYGLE分配了无限制使用默认表空间的权限(UNLIMITED TABLESPACE):
SQL> select * from dba_sys_privs where grantee='EYGLE'; GRANTEE PRIVILEGE ADM ---------- ---------------------------------------- --- EYGLE UNLIMITED TABLESPACE NO
具备了这个权限,用户也就获得了在所有数据表空间上不限制空间使用的权限,现在用户EYGLE甚至可以将数据表建立到SYSTEM表空间:
SQL> create table t tablespace system as select * from user_tables; Table created. SQL> select table_name,tablespace_name from user_tables; TABLE_NAME TABLESPACE_NAME ------------------------------ ------------------------------ T SYSTEM SQL> show user USER is "EYGLE"
这一权限是为了向后兼容保留的,如果被不当使用,则会对数据库造成严重影响。大家可能尝试过通过exp/imp进行数据迁移,迁移过程希望同时转换表空间,可是往往导入后发现,表空间并没有变化,这就是因为这个权限使得用户仍然能够将数据写入原来的表空间(很多时候就是SYSTEM表空间)。
如果为了更严谨的管理,可以回收用户的UNLIMITED TABLESPACE权限,然后对用户的空间限额进行单独授权:
SQL> connect / as sysdba Connected. SQL> revoke UNLIMITED TABLESPACE from eygle; Revoke succeeded. SQL> alter user eygle quota unlimited on users; User altered.
此时独立的空间授权可以通过dba_ts_quotas查询得到,可以看到用户EYGLE在表空间USERS上的最大空间使用限额(MAX_BYTES)为-1,也就是无限制:
SQL> select * from dba_ts_quotas where username='EYGLE'; TABLESPACE_NAME USERNAME BYTES MAX_BYTES BLOCKS MAX_BLOCKS DRO --------------- -------- ---------- ---------- ---------- ---------- --- USERS EYGLE 0 -1 0 -1 NO
这样当用户再试图在其他表空间创建对象时,就会收到错误提示:
SQL> connect eygle/eygle Connected. SQL> create table t tablespace system as select * from user_tables; create table t tablespace system as select * from user_tables * ERROR at line 1: ORA-01536: space quota exceeded for tablespace 'SYSTEM'
系统权限可以通过DBA_SYS_PRIVS视图查询,相应的,可以通过DBA_TAB_PRIVS来查询对象权限。
在数据库内部,不同用户的对象是彼此隔离的,通过授权才能使数据库内部相通,由于相通可能带来风险,所以应当严格控制权限的授予。
不同用户创建的对象,如表、视图等,通过授权可以将不同的访问权限转授给其他用户,实现跨用户对象的访问:
SQL> create user eygle identified by eygle; 用户已创建。 SQL> create user julia identified by julia; 用户已创建。 SQL> grant connect,resource to eygle,julia; 授权成功。 SQL> create table test as select * from dual; 表已创建。 SQL> grant select on test to julia; 授权成功。
以上对TEST数据表进行了跨用户授权,有了查询权限之后,用户julia才能够访问eygle用户的数据表:
SQL> connect julia/julia 已连接。 SQL> select * from eygle.test; D - X
这里的查询TEST表的对象权限,可以通过字典表查询得到:
SQL> connect / as sysdba SQL> SELECT * FROM DBA_TAB_PRIVS WHERE GRANTEE ='JULIA'; GRANTEE OWNER TABLE_NAME GRANTOR PRIVILEGE GRA HIE ---------- ---------- ---------- ---------- --------------- --- --- JULIA EYGLE TEST EYGLE SELECT NO NO
除了对象的所有者之外,具有系统权限GRANT ANY OBJECT PRIVILEGE或者WITH GRANT OPTION权限的用户也可以将对象权限进行跨用户授予:
SQL> grant delete on eygle.test to julia; 授权成功。 SQL> SELECT * FROM DBA_TAB_PRIVS WHERE GRANTEE ='JULIA'; GRANTEE OWNER TABLE_NAME GRANTOR PRIVILEGE GRA HIE ---------- ---------- ---------- ---------- --------------- --- --- JULIA EYGLE TEST EYGLE DELETE NO NO JULIA EYGLE TEST EYGLE SELECT NO NO
注意以上使用SYS进行授权,显示的GRANTOR仍然是对象属主。
回收权限可以使用REVOKE命令进行:
SQL> revoke delete on eygle.test from julia; 撤销成功