(oracle)ORACLE创建用户、角色及权限管理

用户及权限管理

合理的用户和权限管理对于数据库系统的高效、安全、可靠是很关键的。ORACLE在用户及权限管理上有许多新的概念和特性。

& 说明：

对用户及权限的管理需要进入SQL*Plus交互工具。每一个SQL语句后要以分号“；”结束。退出交互工具命令为：quit

SQL命令语句及可选项不区分大小写，本文中出现大写的地方是强调作用。

的用户管理

每个ORACLE数据库都有许多合法用户，这些用户可以根据用户名和口令登录数据库，并使用SQL语言存取数据。

(1)      创建用户

 

创建用户命令格式

 

说 明

 

create user 用户名;

 

创建用户的操作必须由DBA来做，一般用户无权创建用户。用户名必须是唯一的，即同一数据库中不能有两个相同的用户。

 

identified by 口令;

 

为用户设置口令

 

default tablespace 表空间名;

 

表示该用户存放数据的缺省表空间

 

temporary tablespace 表空间名;

 

表明用户使用的缺省临时表空间名

 

quota 大小 on 表空间名;

 

quota 可以限制用户在某个表空间上最多可使用多少字节

 

profile 资源文件;

 

profile 为用户指定各种资源的使用

 

下面是一个创建用户的完整例子：

 

create user scott；

 

identified by tiger；

 

default tablespace data_ts；

 

temporary tablespace temp_ts；

 

quota 500K on data_ts；

 

profile newprofile；

(1)      修改用户

对用户的修改包括：口令字、缺省表空间、临时表空间、表空间限量、profile、缺省角色。角色是ORACLE7的一个新概念，我们在“权限管理”里再讨论。在这里可把角色看成具有某些权限的一个特殊用户。修改用户的缺省角色也就是为用户指明另一个权限的集合。下面举例说明修改用户的操作：

 

任   务

 

命   令

 

将scott的口令改为hello

 

ALTER USER scott IDENTIFIED BY hello；

 

将scott的缺省表空间改为data2_ts

 

ALTER USER scott DEFAULT TABLESPACE data2_ts；

 

将scott的临时表空间修改为temp2_ts

 

ALTER USER scott TEMPORARY TABLESPACE temp2_ts；

 

将scott的资源文件改为otherprofile

 

ALTER USER scott PROFILE otherprofile；

 

将scott的缺省角色改为DEVELOPER

 

ALTER USER scott DEFAULT ROLE DEVELOPER；

 

将当前系统所有角色都授予scott，除Payroll外

 

ALTER USER scott DEFAULT ROLE ALL EXCEPT Payroll；

 

(2)      删除用户

删除用户的命令为：

DROP USER 用户名 [CASCADE]；

若不使用CASCADE选项，则必须在该用户的所有实体都删除之后，才能删除该用户。使用CASCADE后，则不论用户实体有多大，都一并删除。

的权限管理

ORACLE的安全机制，是由系统权限、实体权限和角色权限这三级体系结构组成的。

 

权限类型

 

说   明

 

系统权限

 

是指对数据库系统及数据结构的操作权，例如创建/删除用户、表、同义词、索引等等

 

实体权限

 

是指用户对数据的操作权，如查询、更新、插入、删除、完整性约束等等

 

角色权限

 

是把几个相关的权限组成角色，角色之间可以进一步组合而成为一棵层次树，以对应于现实世界中的行政职位。角色权限除了限制操作权、控制权外，还能限制执行某些应用程序的权限。

这样的安全控制体系，使得整个系统的管理人员及程序开发人员能控制系统命令的运行、数据的操作及应用程序的执行。

 

(1)      系统权限

系统权限的授予命令为GRANT，例如把创建任何表视图的权限授予scott用户：

GRANT create any view TO scott；

系统权限的回收命令为REVOKE，例如将create any view 权限从scott用户手中收回：

REVOKE create any view FROM scott；

 

(2)      实体权限

每种类型的实体有与之相关的实体权限。

授予实体权限的命令举例(将EMP表上的Select和Insert权限授给scott)：

GRANT select，insert ON emp TO scott；

回收实体权限的命令举例(将EMP表上的Select权限从scott手中回收)：

REVOKE select ON emp FROM scott；

 

(3)      管理角色

角色是许多权限和角色的组合，它极大地方便了ORACLE的权限管理。

l           创建角色，如创建一个名为dept1的角色，口令字为hello：

CREATE ROLE dept1 IDENTIFIED BY hello；

l           使用角色，可以通过修改用户的缺省角色来使用角色，或通过授权的方法来将角色授予其它角色或用户。如将scott用户的缺省角色修改为DEVELOPER：

ALTER USER scott DEFAULT ROLE DEVELOPER；

将角色dept1授予manager角色：

GRANT manager TO scott；

l           使角色生效或失效，DBA可以通过控制角色的生效或失效，来暂时回收用户的一部分权限。如使dept1角色失效：

SET ROLE dept1 DISABLE；

l           删除角色，这将会影响到拥有该角色的用户和其它角色的权限。用DROP ROLE命令删除角色，如：

DROP ROLE dept1；

数据库的备份与恢复

ORACLE系统提供的Export/转入（备份）、Import/转出（恢复）使用程序实现备份与恢复功能。

Export是在数据库打开并能使用的情况下备份数据库数据的实用程序。用Export将数据库中的数据写到以二进制形式表示的操作系统文件中（ORACLE），该文件叫卸出文件，它可以是磁盘文件，也可以是磁带文件。这些文件独立于数据库存在。卸出文件在需要时能被再装入到ORACLE数据库中，也可装入到另一个CPU上的ORACLE数据库上，还可把它装入到不同ORACLE版本的数据库上。用Export可实现应用程序失败时的恢复，例如可把某个表或某些表恢复到执行该Export时的状态。

由于卸出文件的特殊格式，所以只能用Import实用程序将其读入数据库中。

转入程序

ORACLE数据库有两类备份方法，第一类为物理备份，该方法实现数据库的完整恢复，但数据库必须运行在归档模式下，且需要极大的外部存储设备，例如磁带机；第二类备份方式为逻辑备份，客户服务中心业务数据库就是采用这种方式，这种方法不需要数据库运行在归档模式下，不但备份简单，而且可以不需要外部存储设备。

逻辑备份又分为三种模式。

表模式（T）：这种模式可以卸出当前用户数据库模式下的表，甚至是所有的表。具有特权的用户可根据所指定的数据库模式来（限制表）卸出他们所包含的表。缺省情况是卸出属于当前正在进行卸出的用户的所有表。

用户模式（U）：这种模式可以卸出当前用户数据库模式下的所有实体（表、数据和索引）。

全数据库模式（F）：只有具有EXP_FULL_DATABASE角色的用户才可能以这种模式卸出。以这种模式进行卸出的用户，除SYS模式下的内容之外，数据库中所有实体都可以卸出。 下面列出给用户赋予EXP_FULL_DATABASE角色的方法。

 

# su – ORACLE

 

假设当前为超级用户身份

 

$ sqlplus sys/change_on_install

 

以sys用户登录并进入Sql*Plus

 

SQL> GRANT     "EXP_FULL_DATABASE"      TO    用户名

 

在Sql*Plus下执行此命令

要选择表、用户或全数据库方式，可相应指定TABLES＝tablelist、OWNER＝userlist或FULL＝y。

 

EXP ICDMAIN/ICD BUFFER=8192(或64000）  

FILE=EXP_ICDMAIN_SERVICEINFO.DMP 或（磁带设备/dev/rmt0）

TABLES=ICDMAIN.SERVICEINFO（或ICDMAIN.COMMONINFORMATION，ICDMAIN.DEALINFO .....)

ROWS=Y COMPRESS=N

LOG= EXP_ICDMAIN_SERVICEINFO.LOG

参数说明：

 

BUFFER

 

缓冲区大小

 

FILE

 

由Export创建的输出文件的名字

 

TABLES

 

将要卸出的表名列表

 

ROWS

 

指明是否卸出表中数据的行数，缺省为“Y”。

 

COMPRESS

 

指明在装入期间是否将表中数据压缩到一个区域中。如果在卸出数据时，指定参数COMPRESS=Y，那么装入时，就会将数据压缩到一个初始区域中。这种选择可以保持初始化区域的原始大小。缺省为“Y”。

 

LOG

 

指定一个接收有用信息和错误信息的文件

 

 

EXP ICDMAIN/ICD OWNER=ICDMAIN BUFFER=8192（或64000）

FILE=EXP_ICDMAINDB.DMP 或（磁带设备/dev/rmt0）

ROWS=Y

COMPRESS=N

LOG= EXP_ICDMAINDB.LOG

参数说明：

 

OWNER

 

将要卸出的用户名列表

 

BUFFER、FILE、ROWS、COMPRESS、LOG

 

同上

 

 

EXP ICDMAIN/ICD BUFFER=8192(或64000)

FILE=EXP_ICDMAIN_DB.DMP （或磁带设备/dev/rmt0）

FULL=Y    ROWS=Y   COMPRESS=N

LOG= EXP_ICDMAIN_DB.LOG

对于数据库备份，建议采用增量备份，即只备份上一次备份以来更改的数据。

增量备份命令：

EXP ICDMAIN/ICD BUFFER=8192（或64000）

FILE=EXP_ICDMAIN_DB.DMP （或磁带设备/dev/rmt0）

FULL=Y INCTYPE=INCREMENTAL ROWS=Y COMPRESS=N

LOG=EXP_ICDMAIN_DB.LOG

参数说明：

 

BUFFER、FILE、ROWS、COMPRESS、LOG

 

同上

 

FULL

 

指明是否卸出完整的数据库。如果FULL=Y，将以全数据库模式进行卸出。

 

INCTYPE

 

增加卸出的类型，有效值有complete（完全）、comulative（固定）和incremental（增量）。

 

complete

 

输出所有表

 

comulative

 

将输入第一次完全输出后修改过的表

 

incremental

 

将输出前一次输出后修改过的表

 

& 说明：

关于增量备份必须满足下列条件：

只对数据库备份有效，且第一次需要FULL=Y参数，以后需要INCTYPE=INCREMENTAL参数。

用户必须有EXP_FULL_DATABASE。

话务量较小时方可采用数据库备份。

使用Export备份数据时，可以使用联机帮助命令取得帮助信息，命令如下：

exp help=y

如果磁盘有空间，建议备份到磁盘，然后再备份到磁带。

 

转入程序

Import和Export是两个相配套的实用程序，Export把数据库中的数据卸出到操作系统文件中，而Import实用程序则把Export卸出的数据恢复到数据库中。

按备份方案确定恢复方案，例如：采用表逻辑备份方案，则恢复方案也采用恢复到表的方式（不应恢复到用户）。

要使用Import，必须具有CREATE SESSION特权，以便能注册到ORACLE RDBMS中去。这一特权属于在数据库创建时所建立的CONNECT角色。

如果卸出文件是由某用户利用EXP_FULL_DATABASE角色创建的全数据库卸出，那么只有具有IMP_FULL_DATABASE角色的用户才能装入这样的文件。下面给用户赋予IMP_FULL_DATABASE角色的方法。

 

# su – ORACLE

 

假设当前为超级用户身份

 

$ sqlplus sys/change_on_install

 

以sys用户登录并进入Sql*Plus

 

SQL> GRANT     "IMP_FULL_DATABASE"      TO    用户名

 

在Sql*Plus下执行此命令

 

数据库的逻辑恢复分为表、用户、数据库三种模式。

 

恢复方法为：

IMP ICDMAIN/ICD FILE=文件名 LOG=LOG文件名

ROWS=Y COMMIT=Y BUFFER=Y IGNORE=Y

TABLES=（表名1，表名2，表名3，表名4，.......）   

参数说明：

 

BUFFER

 

缓冲区大小

 

FILE

 

用于装入的卸出文件名字

 

TABLES

 

将要装入的表名列表

 

ROWS

 

指明是否装入表数据的行数，缺省为“Y”。

 

IGNORE

 

指明如何处理实体创建错误。指定IGNORE=Y，当试图创建数据库实体时，忽略实体存在错误。对除了表之外的其他实体，指定IGNORE=Y，Import不报告错误，继续执行。而指定IGNORE=N时，Import在继续执行前报告实体创建错误。

 

COMMIT

 

指明在每个矩阵插入之后是否提交。缺省时，Import在装入每个实体之后提交。指定COMMIT=N时，如有错误产生，Import在记录装入下一个实体之前，完成一个回退。指定COMMIT=Y时，可以抑制回滚字段无限制增大，并改善大量装入时的性能，表具有唯一约束时，这种选择比较好。如果再次开始装入，将拒绝装入已经装入的任何行，原因是非致命性错误。表具有非唯一约束时，指定COMMIT=N可能是比较好的选择。因为重新装入可能会产生重复行。

 

LOG

 

指定一个接收有用信息和错误信息的文件

 

 

如果备份方式为用户模式，采用下列恢复方法：

IMP SYSTEM/MANAGER FROMUSER=ICDMIAN TOUSER=ICDMAIN

FILE=文件名 LOG=LOG文件名 ROWS=Y COMMIT=Y

BUFFER=Y IGNORE=Y

参数说明同上。

 

如果备份方式为数据库模式，采用下列恢复方法：

IMP SYSTEM/MANAGER FULL=Y

FILE=文件名 LOG=LOG文件名 ROWS=Y COMMIT=Y

BUFFER=Y IGNORE=Y

 

对于单字节字符集（例如US7ASCII），恢复时，数据库自动转换为该会话的字符集（NLA_LANG参数）；对于多字节字符集（例如ZHS168CGB），恢复时，应尽量使字符集相同（避免转换），如果要转换，目标数据库的字符集应是输出数据库字符集的超集。

装入

下面介绍利用Export/Import实用程序对ORACLE数据库进行备份、恢复的方法：增量卸出/装入。增量卸出是一种常用的数据备份方法，包括3个子类：

(1)      “完全”增量卸出

就是对整个ORACLE数据库进行完全卸出。如：

$ exp system/口令 inctype=complete full=y file=today.dmp

(2)      “增量型”增量卸出

即从ORACLE数据库中卸出上次卸出操作之后所有数据库的变化信息。如：

$exp system/口令 inctype=incremental file=today.dmp

增量型卸出文件的大小，可能只是完全卸出文件大小的1%，具体要看“新信息或更新过的信息”的总量而定。

 

累积型卸出方式只是卸出自上次“完全” 卸出之后数据库中变化了的信息。用法如下：

$exp system/口令 inctype=cumulative file=today.dmp

DBA可以排定一个备份日程表，用数据卸出的三个不同方式合理高效地完成数据库的备份任务。比如DBA作如下安排：

星期一：完全卸出(F1)

星期二：增量卸出(I1)

星期三：增量卸出(I2)

星期四：累积卸出(C1)

星期五：增量卸出(I3)

星期六：增量卸出(I4)

如果在星期日，数据库遭到意外破坏，DBA可按以下步骤来恢复数据库：

l           用命令CREATE DATABASE重新生成你的数据库结构；

l           最近增量装入I4：$imp system/口令 inctype=system full=y file=I4

l           完全增量装入F1：$imp system/口令 inctype=restore full=y file=F1

l           累积增量装入C1：$imp system/口令 inctype=restore full=y file=C1

l           对于由累积装入或完全装入尚未能装入的信息，作增量装入：

         $imp system/口令 inctype=restore full=y file=I3

         $imp system/口令 inctype=restore full=y file=I4

注意：

在I1和I2中的信息已包括在C1中了

 

关于Oracle用户权限的5个方面

Oracle用户根据所被授予的权限分为系统权限和对象权限。这里我们主要介绍介绍创建角色、角色赋权、回收角色权限等方面。下面我们来一一进行解析。
一、创建Oracle用户
Create role clerk identified by bicentennial;
以上命令创建一个名为clerk的角色，他的密码为bicentennial，当角色被授予某个用户时，必须使用密码进行验证。如果不指定identified子句，则数据库使用not identified，这时激活角色不需要认证。
二、Oracle用户权限
1.授予角色系统权限和角色
如果要给某个角色授权，你必须需要具有以下权限：
1）如果要授予系统权限，你自已必须被授与此权限时，包括Admin Option或拥有GRANT ANY PRIVILEGE系统权限。
2）如果授与角色，你必须被授予与此角色时包括Admin Option或拥有GRANT ANY ROEL 系统角色。Grant create session to jward;
a)授予Admin Option
Grant new_dba to michal with admin option;
如果一个用户或角色被授权的同时指定with admin option子句，则有以下效果：
i.该用户或角色可以将此权限赋与任何其他用户，或从其他用户回收此权限。
ii.这个用户或角色可以将此权限的管理权传递给任何用户。
iii.被授予角色的用户可以修改或drop角色。
b)在赋权的同时创建用户
Oracle允许你在grant的同时创建一个新用户，如果你通过一个identified by子句指定一个密码，而用户名/密码不存在数据库中，一个新用户就将被创建。
c)Grant connect to ssmith identified by p1q2r3;
2.授予object角色
如果你要将某个object的权限授予某个用户，你必须符合以下条件：你拥有指定的object；你拥有GRANT ANY OBJECT PRIVELEGE系统权限；你被赋与此Object权限包括WITH GRANT OPTION。
Grant select,insert,delete om emp to jfee,tsmith;
Grant all on emp to jfee;
a)授予Grant Option
如你指定with grant option，被授权的用户将获得以下Oracle用户权限：
i被授权的用户可以授予任何用户此对像的权限
ii如果被授权的用户拥有Create view或Create any view系统权限，则用户可以在此表上创建一个视图，同时赋权给其他用户访问。
3.在列上授权
你可以在单独的列上授予用户插入、修改、删除权限：Grant insert (acct_no) on accounts to scott;
三、回收Oracle用户权限
1.回收系统权限
任何拥有指定系统权限的ADMIN OPTION或拥有权限管理权限的用户都可以从任何用户上回收指定权限Revoke create table from tsmith;
这里要注意，ADMIN OPTION不能作为权限单独回收。
2.回收Object权限
为了回收Object权限，必须符合以下条件：此权限是由你之前赋与用户或角色的；你拥有GRANT ANY OBJECT PRIVILEGE权限。
注意：一个拥有GRANT OPTION的用户，如果Object对像被回收，则所有由它赋权的用户的相应权限都将被回收
Revoke all on dept from human_resource;
四、赋权与回收的生效时间
关于系统权限或Object权限的赋权与回收都是立即生效的。将角色同授予某个用户或从用户回收将在下一次新建session时生效，除非使用Set指定。在session中，用户可以使用set role来改变role：Set role ckerk identified by bicentennial; 如果用户有密码则必须使用identified by 指定关闭所有角色Set role none。
五、指定用户缺省权限
当用户登陆时，oracle自动将用户缺省角色中的权限赋与用户。Alter user jane default role payclerk,pettycash