oracle读书笔记(五)-用户与用户权限的管理

一、数据库超级用户

在创建数据库时，会如下图所示的那样自动创建2个超级用户－－SYS和SYSTEM，另外的SYSMAN是用来管理OEM的，DBSNMP是OEM的一个agent，用来监视和管理DB，没用到OEM的可以把它们锁住。

1.1SYS用户

SYS拥有oracle所有的数据字典的基表和视图。SYS用户具有dba，sysdba，sysoper等角色或权限，是oracle权限最高的用户。

在使用sys用户连接数据时，应该使用sysdba或是sysoper权限来连接（即as sysdba/sysoper），否则系统会报错。

如：

[oracle@localhost ~]$ sqlplus /nolog

idle> conn sys/oracle   

ERROR:

ORA-28009: connection as SYS should be asSYSDBA or SYSOPER 

SQL> connect sys/passwd as sysdba/sysper

Connected. 

 

1.2 SYSTEM用户

System用户拥有由oracle工具所使用的附加的内部表和视图，拥有普通dba角色权限。

[oracle@localhost ~]$sqlplus /nolog 

idle> connect system/oracle 

Connected. 

或是：SQL> connect system/passwd as sysdba 

Connected.

 

可以从dba_sys_privs中查看各用户的具体权限：

SQL>select * from dba_sys_privs wheregrantee='SYSTEM';

二、创建用户

语法：

CREATE USER user 

IDENTIFIED {BY password |EXTERNALLY|GLOBALLY AS} 

[ DEFAULT TABLESPACE tablespace ] 

[ TEMPORARY TABLESPACE tablespace ] 

[ QUOTA {integer [K | M ] | UNLIMITED } ONtablespace 

[ QUOTA {integer [K | M ] | UNLIMITED } ONtablespace 

]...] 

[ PASSWORD EXPIRE ]  

[ ACCOUNT { LOCK | UNLOCK }] 

[ PROFILE { profile | DEFAULT }] 

 

• User：是用户名

• BY password：用户密码

• EXTERNALLY：使用操作系统验证方式

• GLOBALLY AS：对用户进行全局验证

• DEFAULT TABLESPACE：指定缺省的表空间

• TEMPORARY TABLESPACE：指定缺省的临时表空间

• QUOTA：定义用户在表空间中允许拥有对象的最大空间，单位为K或M；UNLIMIT为缺省值，表示不作限制。

• PASSWORD EXPIRE：密码过期，强制用户在使用SQL*Plus 登录到数据库时重置口令。

• ACCOUNT LOCK/UNLOCK：锁定或解除锁定用户帐户，缺省为UNLOCK。

• PROFILE：用户配置文件，管理口令、控制用户可使用的系统和数据库资源。

 

示例：

 

sys@DEMO> create user abc idenfified byfortest   -- 密码为fortest

 2  default tablespace ecss                   -- 缺省表空间ecss

 3  temporary teblespace tmp                -- 缺省临时表空间tmp

  4  quota 100M on ecss                     -- 最多可使用100Mecss表空间。注意，10g的临时表空间不能用quota分配限额。

 

验证刚建好的用户：

sys@DEMO> selectusername,default_tablespace,temporary_tablespace 

 2  from dba_users whereusername='ABC'; 

USERNAME  DEFAULT_TABLESPACE  TEMPORARY_TABLESPACE

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

ABC               ECSS                 TMP

 

如果使用最简单的创建用户语法，用户缺省的数据表空间、临时表空间将使用全局缺省的数据/临时表空间，如

sys@DEMO>select * fromdatabase_properties;

PROPERTY_NAME           PROPERTY_VALUE            DESCRIPTION

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

.......

DEFAULT_TEMP_TABLESPACE        TEMP    Name of default temporary tablespace

DEFAULT_PERMANENT_TABLESPACE   USERS   Name of default permanent tablespace

.......

sys@DEMO> create user bcd identified byfortest;

User created.

sys@DEMO> select username,default_tablespace,temporary_tablespace

  2  from dba_users where username='BCD';

USERNAME  DEFAULT_TABLESP TEMPORARY_TABLE 

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

BCD         USERS             TEMP       

 

实际应用中应该在创建用户就为用户指定缺省的数据/临时表空间，不同的用户建立独立的表空间。

2.1创建操作系统认证的用户

操作系统认证也叫称为外部认证，通过在创建用户时使用EXTERNALLY参数，而不是直接设定密码，指定用户必须通过操作系统进行验证。当用户直接登录到运行Oracle 服务器的计算机上时，该选项通常很有用；

 

示例：

（1）将系统用户orcl加入oracle用户组

[root@localhost ~]#usermod -G oinstall -Uorcl

（2）将oracle用户与数据库相关的变量（如ORACLE_HOME等）添加到orcl的配置文件

（3）创建外部认证用户

sys@DEMO>create user ops$orcl identifiedexternally;

sys@DEMO>grant connect,resource toops$orcl;

sys@DEMO>select username,password fromdba_users where username='OPS$ORCL';

USERNAME  PASSWORD

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

OPS$ORCL  EXTERNAL

 

用户名前缀OPS$是以便与Oracle 服务器的早期版本向后兼容，通过参数OS_AUTHENT_PREFIX指定。可将前缀设置为NULL 值，可将这个初始化参数的值设为空值以使用户名更加简单。

sys@DEMO>alter system setos_authent_prefix='' scope=spfile;

sys@DEMO>startup force;

更改并重启完成后，创建外部认证用户时就不需要在用户名前加上前缀ops$了。

 

（4）用orcl登录系统并测试

[root@localhost ~]su - orcl

[orcl@oracle ~]$ sqlplus /

 

SQL*Plus: Release 10.2.0.4.0 - Productionon Mon Jul 30 14:56:13 2012

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.

Connected to:

Oracle Database 10g Enterprise EditionRelease 10.2.0.4.0 - 64bit Production

With the Partitioning, OLAP, Data Miningand Real Application Testing options

SQL> show user;

USER is "OPS$ORCL"

SQL>

 

操作系统认证的用户口令没有存储在数据库中，只要用户操作系统验证通过，Oracle就认为这是一个可信任的用户，在登录数据库时不需要数据库口令。

 

另外要注意的是，在使用系统认证的用户时，为安全起见，参数remote_os_authent应该为false以禁止系统认证的用户通过远程登录。

 

SQL> show parameter remote_os_authent;

NAME                 TYPE      VALUE

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

remote_os_authent      boolean   FALSE

 

2.2创建代理用户

如果忘记数据库的用户名密码，在不修改用户密码情况下，可以通orcle的企业用户代理（Enterprise User Proxy）的方式来连接到数据库

语法：

ALTER USER target_user GRANT CONNECTTHROUGH proxy_user;

 

示例：

##数据库ecss的密码忘了，现在要通过用户代理ecss_proxy切换到目标用户ecss

SQL> create user ecss_proxy identifiedby eproxy;

User created.

SQL> grant connect to ecss_proxy;

Grant succeeded.

SQL>alter user ecss grant connectthrough ecss_proxy;

User altered.

 

SQL> conn ecss_proxy[ecss]/eproxy;

Connected.

SQL> show user;

USER is "ECSS"

SQL> select * from session_privs;

PRIVILEGE

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

CREATE SESSION

UNLIMITED TABLESPACE

CREATE TABLE

CREATE CLUSTER

CREATE SEQUENCE

CREATE PROCEDURE

CREATE TRIGGER

CREATE TYPE

CREATE OPERATOR

CREATE INDEXTYPE

 

10 rows selected

 

## 可以看到用户ecss_proxy具有ecss的所有权限

三、修改、删除用户

3.1 修改用户

语法：

ALTER USER user 

[ DEFAULT TABLESPACE tablespace] 

[ TEMPORARY TABLESPACE tablespace] 

[ QUOTA {integer [K | M] | UNLIMITED } ONtablespace 

[ QUOTA {integer [K | M] | UNLIMITED } ONtablespace ] 

...] 

 

示例：

（1）修改用户密码

SQL> alter user ecss_proxy identified byabc;

（2）修改用户表空间

SQL> alter user ecss_proxy defaulttablespace user01 temporary tablespace temp01;

（3）修改表空间配额

通过表dba_ts_quotas来察看用户所使用的表空间的配额：

SQL>alter user scott quota 0 on users; 

SQL>alter user scott quota unlimited onusers; 

 3.2 删除用户

语法：

DROP USER user [CASCADE]

注意，CASCADE  选项将删除用户方案中的所有对象，如果方案中包含任何对象，则必须指定该选项。另外不能删除当前与Oracle 服务器连接的用户。

 

四、用户权限管理

4.1权限分类

• 系统权限：系统权限限定了可以操作的功能，具备系统权限的用户可以连接到数据库进行系统级操作活动，如连接数据库，更改用户会话、建立表或建立用户等等。

• 对象权限：对象权限限定了具体某个对像可以操作的功能，具备对象权限的用户可访问并操纵特定对象，如在表、视图、序列、过程、函数或包等对象上执行动作。

4.2系统权限

可以在数据字典视图SYSTEM_PRIVILEGE_MAP上获得完整的系统权限，oracle10g有160 多种不同的系统权限。

sys@DEMO> select count(*) fromsystem_privilege_map;

 COUNT(*)

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

      166

sys@DEMO> select name from system_privilege_map;

NAME

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

CREATE EXTERNAL JOB

CHANGE NOTIFICATION

READ ANY FILE GROUP

MANAGE ANY FILE GROUP

MANAGE FILE GROUP

EXEMPT IDENTITY POLICY

CREATE ANY SQL PROFILE

ADMINISTER ANY SQL TUNING SET

ADMINISTER SQL TUNING SET

ALTER ANY SQL PROFILE

DROP ANY SQL PROFILE

SELECT ANY TRANSACTION

MANAGE SCHEDULER

EXECUTE ANY CLASS

EXECUTE ANY PROGRAM

CREATE ANY JOB

............

权限中的关键字ANY 表示用户在任何方案（用户）中都具备这种权限。

 

4.2.1 常用的系统权限

• 序列

1. CREATE (ANY)SEQUENCEE可以在自己（任意）的模式中创建新的序列

2. ALTER (ANY)SEQUENCE修改自己（任意）的模式中序列的属性

3. DROP ANY SEQUENCE从数据库内的任意一个模式中删除任意一个序列

4. SELECT ANY SEQUENCE

 

• 会话

1. CREATE SESSION可连接到数据库

2. ALTER SESSION  可执行ALTER SESSIONS语句

3. ALTER RESOURCE COST允许被授权者修改ORACLE为一个概况中的资源约束计算资源成本的方式。

4. RESTRICTED SESSION允许数据库在RESTRICTED SESSION模式时连接到数据库。

 

• 同义词

1. CREATE SYNONYM允许在自己的对象模式中创建同义词

2. CREATE ANY SYNONYM允许在任意对象模式中创建新的同义词

3. CREATE PUBLIC SYNONYM允许被授权者创建新的公用同义词。这些同义词对数据库中的所有用户都是可访问的。

4. DROP ANY SYNONYM允许从任意对象模式中删除任意一个同义词

5. DROP PUBLIC SYNONYM允许被授权者从数据库中删除任意一个公用同义词

 

• 索引

1. CREATE (ANY) INDEX  从自已（任意）模式中创建索引

2. ALTER (ANY)INDEX    修改自已（任意）模式中的索引

3. DROP (ANY) INDEX    删除自已（任意）模式中的索引

 

1. 表

2. CREATE TABLE允许在自己的对象模式中创建表

3. CREATE ANY TABLE允许在任意一个对象模式中创建表

4. ALTER ANY TABLE允许更改任意一个对象模式中的表

5. DROP ANY TABLE允许从任意一个对象模式中删除表

6. COMMENT ANY TABLE允许给任意一个对象模式中的任意一个表或列注释

7. SELECT ANY TABLE允许查询任意表

8. INSERT ANY TABLE允许插入新行到任意表

9. UPDATE ANY TABLE允许更新任意表

10. DELETE ANY TABLE允许删除任意表中的行

11. LOCK ANY TABLE允许执行一条LOCKTABLE来明确锁定任意一个表

12. FLASHBACK ANY TABLE允许使用AS OF 语法对任意一个对象模式的任意一个表或视图执行一个SQL回闪查询。

 

• 表空间

1. CREATE TABLESPACE允许创建新的表空间

2. ALTER TABLESPACE允许被授权者更改现有表空间

3. DROP TABLESPACE允许删除表空间

4. MANAGE TABLESPACE允许更改表空间。例如ONLINE、OFFILE、BEGIN BACKUP或END BACKUP

5. UNLIMITED TABLESPACE允许消耗任意一个表空间中的磁盘限额。相当于给指定授权者每个表空间中的无限限额。以上介绍Oracle系统特权。

 

• 其他系统权限

1. CREATE VIEW  在自己的模式中创建视图

2. CREATE PROCEDURE 在自己的模式中创建过程

3. 等等

4.2.2系统权限的授予

语法：

GRANT {system_privilege|role} 

[, {system_privilege|role} ]... 

TO {user|role|PUBLIC} 

[, {user|role|PUBLIC} ]... 

[WITH ADMIN OPTION] 

 

其中：

system_privilege：指定要授予的系统权限

Role：指定要授予的角色名

PUBLIC：将系统权限授予当前与未来新建的所有用户

WITH ADMIN OPTION：允许被授予者进一步为其他用户或角色授予权限或角色

 

如果新建的用户没有授权，在连接时会被拒绝：

sys@DEMO> create user user01 identifiedby test;

User created.

 

sys@DEMO> conn user01/test

ERROR:

ORA-01045: user USER01 lacks CREATE SESSIONprivilege; logon denied

Warning: You are no longer connected toORACLE.

##说明用户user01没有CREATESESSION系统权限

 

sys@DEMO>  grant create session,select any table to user01;

Grant succeeded.

sys@DEMO> conn user01/test

Connected.

user01@DEMO> select count(*) fromscott.emp;

 COUNT(*)

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

       14

user01@DEMO> select count(*) fromecss.sms_friends;

 COUNT(*)

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

       88

授予用户create session权限后user01可以成功连接数据库，select any table权限使user01用户能查看任何方案（用户）的表；为安全起见，具有select any table权限普通用户不能查询数据字典，如：

user01@DEMO> select * from v$session;

select * from v$session

         *

ERROR at line 1:

ORA-00942: table or view does not exist

 

user01@DEMO> select count(*) fromdba_sys_privs;

select count(*) from dba_sys_privs

    *

ERROR at line 1:

ORA-00942: table or view does not exist

 

这是因为从oracle8i开始，oracle引入了参数O7_DICTIONARY_ACCESSIBILITY

sys@DEMO> show parameter DICTIONARY;

NAME                           TYPE         VALUE

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

O7_DICTIONARY_ACCESSIBILITY      boolean       FALSE

 

VALUE为TRUE时，具有SELECTANY TABLE权限的用户可以查询数据字典，为FALSE（缺省），表示具有SELECT ANY TABLE权限的用户不可以查询数据字典；DBA用户则不受该参数的限制，数据库管理员在任何情况下都可以访问数据字典。

可以从dba_sys_privs查询及验证用户的系统权限

SQL> select * from dba_sys_privs 

 2    where grantee='USER01' orgrantee='USER02';

4.2.3系统权限的传递

如果在给用户授权时加上WITH ADMIN OPTION了选项，那么用户得到的权限可以授予其他用户。

//没有加上WITH ADMIN OPTION选项之前

user01@DEMO> grant create session,selectany table to user02; 

grant connect,select any table to user02

*

ERROR at line 1:

ORA-01031: insufficient privileges

 

//加上WITH ADMIN OPTION选项之后，user01用户可以把自己的系统权限createsession、select any table传递（授予）给用户user02

sys@DEMO> grant create session,selectany table to user01 with admin option;

Grant succeeded.

user01@DEMO> grant create session,selectany table to user02; 

Grant succeeded.

user01@DEMO> conn user02/test;

Connected.

user02@DEMO> select count(*) fromecss.sms_friends;

 COUNT(*)

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

       88

如果user01用户在授权user02的时候也带上了WITH ADMIN OPTION选项，那么用户user02也可以把从user01中得到的系统权限（create session，select any table）传递给其他用户，以此类推。

4.2.4系统权限的回收

语法：

REVOKE {system_privilege|role} 

[, {system_privilege|role} ]... 

FROM {user|role|PUBLIC} 

[, {user|role|PUBLIC} ]... 

 

注意：

（1）REVOKE  命令只能撤消使用GRANT命令直接授予的权限。

（2）撤消系统权限可能对一些相关对象有影响。例如，如果将SELECT ANY TABLE 授予某用户，而该用户已创建了使用其它方案中的表的过程或视图，则撤消该权限将使这些过程或视图无效

（3）如果使用WITH ADMIN OPTION为某个用户授予系统权限，那么对于被这个用户授予相同权限的所有用户来说，取消该用户的系统权限并不会级联取消这些用户的相同权限。

（4）系统权限无级联，即USER01授予USER02权限，USER02授予USER03权限，如果USER01收回USER02的权限，USER03的权限不受影响；系统权限可以跨用户回收，即USER03可以直接收回T3用户的权限。

4.3 对象权限

oracle 有9种对象权限，如下表所示：

对象可授予一个或多个权限，或用关建字ALL来授予或撤销对象的所有权限。如TABLE的ALL权限就包括了ALTER、DELETE、INDEX、INSERT、REFERENCE、SELECT、UPDATE。

 

4.3.1 授予对象权限

语法：

GRANT { object_privilege [(column_list)] 

[, object_privilege [(column_list)] ]... 

|ALL [PRIVILEGES]} 

ON [schema.]object 

TO {user|role|PUBLIC}[, {user|role|PUBLIC}]... 

[WITH GRANT OPTION] 

其中：

object_privilege：指定要授予的对象权限

column_list：指定表或视图列（只在授予INSERT、REFERENCES 或UPDATE 权限时才指定。）

ALL：将所有权限授予已被授予WITHGRANT OPTION 的对象

ON object：标识将要被授予权限的对象

WITH GRANT OPTION：使被授予者能够将对象权限授予其他用户或角色

 

示例：

将用户scott用户的表emp的字段（列）sal的update权限赋给用户USER01和USER02，并允许他们将该权限赋给其他用户

sys@DEMO>GRANT UPDATE(sal) ON scott.empTO user01,user02 WITH GRANT OPTION; 

 

4.3.2撤消对象权限

语法：

REVOKE { object_privilege 

[, object_privilege ]...  

| ALL [PRIVILEGES] } 

ON [schema.]object 

FROM {user|role|PUBLIC} 

[, {user|role|PUBLIC} ]... 

[CASCADE CONSTRAINTS] 

 

其中：

object_privilege：指定将撤消的对象权限

ALL：撤消已授予用户的所有对象权限

ON：标识将撤消其对象权限的对象

FROM：标识将撤消其对象权限的用户或角色

CASCADE CONSTRAINTS删除撤消使用REFERENCES或ALL权限定义的任何引用完整性约

束限制：授予者只能对其已经授予权限的用户撤消对象权限。

 

注意：

当通过WITHGRANT OPTION参数获得的特权被取消时会发生级联作用。例如下面示例，SYS授予USER01在表sys_emp上的select特权，同时USER01又把该特权授予USER02。当SYS取消USER01的特权时，USER03的特权也被取消

 

示例：

//创建示例表

sys@DEMO> create table sys_emp as select* from scott.emp; 

Table created

 

//给user01表sys_emp的select权限，并允许它将此权限授予其他用户

sys@DEMO> grant select on sys_emp touser01 with grant option; 

Grant succeeded.

 

//用user01连接数据库，测试、并将select权限授予user02

sys@DEMO>conn user01/test

user01@DEMO> select count(*) fromsys.sys_emp;

 COUNT(*)

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

       14

user01@DEMO> grant select on sys.sys_empto user02 with grant option;

Grant succeeded.

 

//用user02连接数据库，测试

user01@DEMO> conn user02/test 

Connected.

user02@DEMO> select count(*) fromsys.sys_emp;

 COUNT(*)

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

       14

 

//取消user01的sys.sys_emp表的select权限并测试，可以看到由于级连作用，user02也没有了表sys.sys_emp的select权限

sys@DEMO> revoke select on sys_emp fromuser01;

Revoke succeeded.

 

sys@DEMO> conn user01/test

Connected.

user01@DEMO> select count(*) fromsys.sys_emp;

select count(*) from sys.sys_emp

                         *

ERROR at line 1:

ORA-00942: table or view does not exist

## user01用户已不能查询表sys_emp

 

user01@DEMO> conn user02/test

Connected.

user02@DEMO> select count(*) fromsys.sys_emp;

select count(*) from sys.sys_emp

                         *

ERROR at line 1:

ORA-00942: table or view does not exist

##发生级联作用，user02也不能查询表sys_emp

 

另外如果用户USER02同时从多个用户获得同一个特权，当其中一个用户的特权被取消时，USER02的特权不会被取消，直至授予USER02授权的所有用户均被取消该特权。

 

4.3 与权限相关的视图

ORACLE中数据字典视图分为3大类,  可以用前缀USER，ALL 和DBA区别，许多数据字典视图包含相似的信息。

 

USER_*:有关用户所拥有的对象信息，即用户自己创建的对象信息

 

ALL_*：有关用户可以访问的对象的信息，即用户自己创建的对象的信息及其他用户创建的对象但该用户有权访问的信息

 

DBA_*：有关整个数据库中对象的信息，这类视图要以SYS用户登录来查看

 

下面为常用与用户权限相关的视图：

1. DBA_SYS_PRIVS   查询某个用户所拥有的系统权限

2. USER_SYS_PRIVS   查询当前用户所拥有的系统权限

3. ALL_SYS_PRIVS    查询用户所拥有的全部系统权限

4. SESSION_PRIVS     查询当前用户所拥有的全部权限

5. DBA_TAB_PRIVS   查询某个用户所拥有的对象权限

6. USER_TAB_PRIVS 查询当前用户所拥有的对象权限