Oracle11g用户管理

第一部分用户账户与权限

再讲之前先考虑一个问题，如果现在使用oracle数据库的时候忘记了密码怎么办？

可能有同学想到可以用sys用户找回密码，如果连sys用户密码都忘了呢？

还有什么办法吗？

1、数据库中存在的用户具有OPEN，LOCK,EXPIRE三种状态，查看当前的数据库已经启用的（OPEN）用户情况使用表dba_users

2、创建用户的基本命令：

create user 新的用户名 identified by密码;

创建一个test1的用户，密码为123

create user test1 identified by 123;

用户创建好了，大家试一下用新用户登陆数据库是否可以？

3、授予用户权限的命令：

grant 权限名/角色名 to 用户;

将create session的权限给test1用户

grant create session to test1;

4、修改用户密码命令：

alter user 用户名 identified by 新密码;

将用户test1的密码由123改为test1;（想一下谁有权利改？）

alter user test1 identified by test1;

5、设置用户密码过期命令：

alter user 用户名 password expire;

将用户test1的密码设置为过期状态，使用dba_tables查看一下当前账户的状态，现在test1使用test1的密码还能登陆吗，出现什么现象？

alter user test1 password expire;

6、设置用户锁定/解锁：

alter user 用户名 account lock/unlock;

将用户test1设置锁定,使用test1还能登陆吗？

alter user test1 account lock;

7、解锁test1用户，使用test1登陆，创建一张表:

create table mytab(

id number,

name varchar2(50));

可以创建吗？

8、为用户授与相关的权限：

grant 权限名 to 用户;

为用户授予创建表的权限：

g rant create table to test1;

再试一下可以创建表了吗？

9、使用test1用户查询一下scott用户下的emp表的ename,empno,job列：

s elect ename,empno,job from emp;

可以吗？

select ename,empno,job from scott.emp;

这样可以吗？

10、为用户test1授予查询scott.emp表的权限（使用sys用户授予）

grant select on scott.emp to test1;

试一下可以使用用户test1往scott.emp插入数据吗？

主要权限:insert、delete、update、select

grant select,insert on scott.emp to new;

11、回收test1用户的查询、插入scott.emp表的权限

revoke select,insert on scott.emp from new;

12、删除用户

drop user new cascade;（级联删除）

13、通过以上命令可以看出，创建一个用户再为这个用户授予相关的权限是比

较复杂的，所以oracle中引入了角色的概念，只要创建一个用户后将相应的权

限授予这个用户即可。

新建一个用户test2/123，为用户授予connect和resource权限：

create user test2 identified by 123;

grant connect,resource to test2;

现在试一下使用test2用户是否可以登录、创建表呢？

预定义角色

预定义角色是指Oracle所提供的角色，每种角色都用于执行一些特定的管理任务，下面我们介绍常用的预定义角色connect，resource，dba。

connect角色

connect角色具有一般应用开发人员需要的大部分权限，当建立了一个用户后，

多数情况下，只要给用户授予connect和resource角色就够了，那么connect角

色具有以下系统权限：

alter session

create cluster

create database link

create session

create view

create sequence

resource角色

resource角色具有应用开发人员所需要的其他权限，比如建立存储过程、触发器

等。这里需要注意的是resource角色隐含了unlimited tablespace系统权限。

resource角色包含以下系统权限：

create cluster

create indextype

create table

create sequence

create type

create procedure

create trigger

dba角色

dba角色具有所有的系统权限，及with admin option选项，默认的dba用户为sys和system他们可以将任何系统权限授予其他用户。但是要注意的是dba角色不具备sysdba和sysoper的特权（启动和关闭数据库）。

如果要了解用户拥有哪些角色，以及角色中包含哪些权限，可以查看表中所示的数据字典视图。

第二部分：用户配置文件

Oracle安装完成后，自动创建了名为default的资源配置文件，可以通过以下命令查看（查看dba_profiles表）：

从查询结果（profile文件名，resource_name资源名，limit资源限制）中可以看出在名为default的profile中有16个resource_name,每一个resource_name 都可以做限制。

下面让我们来认识以下这16个resource_name以及它们的作用:

1、COMPOSITE_LIMIT：由多个资源限制参数构成的复杂限制参数，利用该

参数可以对所有混合资源进行设置。

2、SESSIONS_PER_USER：每个用户可以拥有的会话数，即同时最多可以有

几个客户端使用这个用户登陆数据库（用户的并发个数）。

3、CPU_PER_SESSION：每个会话可以占用的CPU总时间，其单位为百分之

一秒。

4、CPU_PER_CALL：每条SQL语句可以占用的CPU的总时间，其单位为百

分之一秒。

5、LOGICAL_READS_PER_SESSION：每个会话期间可以读取的数据块数

量，从内存中读取的数据块和从磁盘中读取的数据块。

6、LOGICAL_READS_PER_CALL：每条SQL语句可以读取的数据块数量。

7、IDLE_TIME：空闲时间——用户非活动检测时间，多长时间没有做动作就

自动断开（单位：min）。

8、CONNECT_TIME：用户可以连接到数据库的总时间（单位：min），即连

接多久用户会自动退出。

9、PRIV ATE_SGA：每个用户占用多少内存区域(单位：bytes)，共享服务器模

式下有效。在专用服务器模式下，这个参数不起作用。

10、FAILED_LOGIN_ATTEMPTS：用户登录数据库时允许失败的次数，达到失败次数后，该用户将自动锁定，锁定时间取决于PASSWORD_LOCK_TIME。

11、PASSWORD_LIFE_TIME：用户口令的有效时间，单位为天。

12、PASSWORD_REUSE_TIME：用于设置一个失效口令多少天之内不允许使用。

13、PASSWORD_REUSE_MAX：用于设置一个已使的口令被重新使用之前，口令必修被修改的次数。

14、PASSWORD_VERIFY_FUNCTION：设置用于判断口令复杂性的函数，函数可以自动创建，也可以使用默认的或不使用。

15、PASSWORD_LOCK_TIME：用户登录失败次数达到限制数，用户将被锁定的时间，单位为天。

16、PASSWORD_GRACE_TIME：当口令的使用时间达到密码到期时间时，该口令还允许使用的“宽限时间”，用户登录时，Oracle会提示该时间。

使用下面的命令可以查看Oracle的用户所使用的参数文件（一般为default）

可以看到大多数的用户使用的都是DEFAULT的profile文件，就是我们在上面查看的哪一个。

下面我们自己创建一个profile文件：

注意要用DBA的身份才能创建profile文件，我们使用SYS用户来创建：

要求：

（1）限制用户允许拥有的会话数为1，对应的参数为SESSIONS_PER_USER （2）限制用户空闲时间的有效时间为1分钟，对应的参数为IDLE_TIME （3）限制用户登录数据库可以失败的次数为3次，对应的参数为FAILED_LOGIN_ATTEMPTS

（4）设置用户登录失败次数达到限制次数时，用户被锁定的时间为5分钟，对应的参数为PASSWORD_LOCK_TIME

（5）设置用户连接数据库的时间为5分钟，超出这个时间将断开数据库，对应的参数文件为CONNECT_TIME

（6）设置口令使用时间达到有效时间之后，口令任然可以使用的“宽限时间”

为3天，对应的参数为PASSWORD_GRACE_TIME

创建配置文件的格式为：

create profile 用户配置文件名称limit

参数1 限制大小

参数2 限制大小

参数3 限制大小

……

参数n 限制大小;

创建之后可以通过下面的命令来查看已经创建的profile，其中dba_profile表记录的是当前oracle之下的所用的profile，我们使用where筛选出刚刚创建的

myprofile

当然在绑定后要想让这个参数文件里的限制生效还要修改系统parameter resource_limit，使绑定的参数文件里的限制生效：

将用户绑定到参数文件有两种方法：

1、在创建用户的时候绑定参数文件：

Create user 用户名identified by 密码

Profile 绑定的参数名;

2、在之后为用户修改绑定的参数文件：

Alter user 用户名profile 要绑定的参数名;

关于PASSWORD_VERIFY_FUNCTION的使用：

profile的PASSWORD_VERIFY_FUNCTION参数，允许我们在创建用户或者更改用户密码时实施自定义的密码验证规则。

首先，我们需要创建一个自己的实现密码验证规则的函数，该函数必须创建在sys模式下，而且必须有三个类型为varchar2的输入参数，第一个参数，包含我们要修改的用户名，第二个参数包含新密码，第三个参数包含旧密码，并且返回类型为布尔类型。举一个简单的例子，如果我们需要使密码最少保6位，并在用户修改密码时在passwd_changed表中记录用户的旧密码和新密码：

首先创建表passwd_changed：

create table passwd_changed (

user_name varchar2(20) ,

old_passwd varchar2(20) ,

new_passwd varchar2(20)

) ;

创建实现密码自定义规则的函数my_password_verify：

CREATE OR REPLACE FUNCTION my_password_verify

(username V ARCHAR2 ,password V ARCHAR2 ,old_password V ARCHAR2) RETURN BOOLEAN IS

BEGIN

IF LENGTH(password) < 6 THEN

raise_application_error(-20001,'密码的长度至少6位');

END IF;

INSERT INTO passwd_changed

V ALUES(username,old_password,password);

RETURN(TRUE);

END;

/

第三部分：管理用户Session

数据库管理员可以在需要的时候使用ALTER SYSTEM语句终止用户的会话，其语法形式如下：

ALTER SYSTEM KILL SESSION ' sid , serial# ' ;

其中，sid与serial#的值可以通过查询动态视图v$session获得。

使用sqlplus在连接再打开一个session

再次使用V$session动态视图查看连接的用户：

使用sys窗口将scott用户kill,对应的SID为18，serial#为146

Scott账户已经不在登录