批量建Oracle用户

需求分析：

1.一次性批量创建100个左右用户；

2.每个用户拥有独立的表空间；

3.权限方面，既要满足教学上机练习的需求，又要严格限制权限，以防初学者对系统

造成意外破坏。

4.对于服务器资源，作以下阐述。由2013下半年实验总结，监测到的数据：数据库

会话数的峰值：300~320，此时由第三方软件测试数据显示，服务器资源使用达到80%左右。作为学习数据库服务器，应该尽可能使用服务器资源，最大可能的提供服务，以供教学。

解决方案：

1.概要文件：概要文件用于口令和资源限制（由13年12月2日那次实验吸取教

训，对与口令，最好不好限制尝试登录次数等等，一旦出问题，无论怎么修改概要文件都无济于事，再说，口令限制也是为了防御外来攻击，作为学习数据库没必要那么严格控制）。以下只做资源限制（以下数据参考来自Oracle DBA 培训教程-何明-清华大学出版社，数据有待进一步测试验证）

A.会话

资源：服务器最大可提供300个对外会话数（下次实验可以使用Spotlight on Oracle 9.6这款软件检测物理连接数是多少）。

需求：100个左右用户同时连接服务器。

结论：每个用户的最大会话数限制在三个。

B.CPU占用时间

防止一个会话消耗过多处理器时间，每个会话所使用的处理器时间不能超过

16800个1%S（168S）。

C.数据块

防止用户狂搞数据（如笛卡尔积）产生大量输入输出，每个会话最多的逻辑阅读量为23688个数据块。

D.挂机时间

每个用户最多挂3个小时

E.空闲时间

防止长期不干活，占用资源，超过28分钟不干活就踢出系统并回滚该用户没有提交的事务。

2.角色：教学过程中，学生需要用到大量权限，在数据库中进行练习，以往经验可

知，权限授予要慎重，以免意外破外系统，导致实验无法进行。为了统一管理，创建一个ORACLE_STUDENT_ROLE角色；

经过裴学长一系列测试，决定赋予学生用户以下权限：

CONNECT CREATE SESSION

创建会话

RESOURCE CREATE CLUSTER

创建簇

RESOURCE CREATE INDEXTYPE

创建索引类型

RESOURCE

REATE OPERATOR

创建控制器

RESOURCE

REATE PROCEDURE

创建过程

RESOURCE

REATE SEQUENCE

创建序列

RESOURCE

REATE TABLE

创建表

RESOURCE

REATE TRIGGER

创建触发器

RESOURCE CREATE TYPE

创建复合类型

CREATE USER

创建用户

CREATE ROLE

创建角色

CREATE VIEW

创建视图

EBUG ANY

PROCEDUREDEBUG

CONNECT SESSION

调试PL/SQL用

DROP USER 删除用户

删除用户（由于教学需要，删除用户这个权限不得不给，但是存在一个问题，一个学生用户不仅可以删除自己创建的用户，还可以删除其他人，对此我的想法是写一个DDL事件触发器，这样，对他们的行为加以限制，在系统级的操作（如

CREATE,ALTER,DROP）都加以审核。

3.表空间

这个是教学数据库，所有数据量不会像生产数据库那么大，默认表空间（缺省表空间）给1MB，临时表空间给2M（排序用），其实1M还是2M都无所谓，反正已经满足需求了。

实现步骤明细：

总体思路，不用写存储过程，函数，包等等，只利用SQL*PLUS工具，使用SQL（父SQL）生成SQL（子SQL）。本质上讲：使用SQL（父SQL）生产SQL（子SQL）就是将一条SQL语句的输出作为另一条SQL语句的输入。

编写能够生成更多SQL（父SQL）代码的SQL（子SQL）很容易。

First：编写生成SQL（子SQL）的SQL（父SQL）；

Second：创建一个为SQL后缀名的文件，用于捕获第一个SQL（父SQL）脚本的输

出；

Then：执行这个生产SQL（父SQL）代码；

Finally：执行生成的SQL代码的脚本文件。

由于每个批量的过程都使用相同得方法，为了不赘述，这里以批量

授权为例：

第一步：写好你的SQL（父SQL），注意字符拼接很有讲究，不要错误。

SELECT 'GRANT ORACLE_STUDENT_ROLE TO T'||SNO||' with admin

option;' FROM STUDENT; --授权

解释：注意红色单引号，和双竖线||，这是在拼接字符。SNO是表STUDENT的列，

STUDENT里面装的学生的信息。（这个表自己提前建好，数据也要导入，过程不再赘

述，一切字符拼接都是基于这个表之上的）；

第二步：在SQL*PLUS中输入spool F:\grant.sql这个路径和名字随便取，后缀

名最好用sql。

第三步：执行你第一步写的SQL（父SQL）；

结果如下（sql*plus窗口中）:

SQL> SELECT 'GRANT ORACLE_STUDENT_ROLE TO T'||SNO||' with

admin option;' FROM STUDENT;

---------------------华丽的分割线------------------------

GRANT ORACLE_STUDENT_ROLE TO T20100230117 with admin option;

GRANT ORACLE_STUDENT_ROLE TO T20100230122 with admin option;

GRANT ORACLE_STUDENT_ROLE TO T20110230103 with admin option;

GRANT ORACLE_STUDENT_ROLE TO T20110230106 with admin option; ………………………………………………………………………………………..(中间省略)

GRANT ORACLE_STUDENT_ROLE TO T20110230210 with admin option; GRANT ORACLE_STUDENT_ROLE TO T20110230213 with admin option;