用户管理与权限分配5.20

1、方案与用户 方案是一些数据库对象（如：表、索引、视图、序列、过程、函数、触发器等）的集合，一个方案下的对象归一个用户所有。在Oracle中方案和用户基本是一个等同的概念，用户名即方案名。 当访问数据库对象时，需要注意如下一些事项： (1) 同一方案

1、方案与用户

方案是一些数据库对象（如：表、索引、视图、序列、过程、函数、触发器等）的集合，一个方案下的对象归一个用户所有。在Oracle中方案和用户基本是一个等同的概念，用户名即方案名。

当访问数据库对象时，需要注意如下一些事项：

(1) 同一方案中不能存在同名对象，但是不同方案中数据库对象可以重名。

(2) 用户可以直接访问自己方案中的数据对象，但如果要访问其他方案的数据对象，则必须具有对该对象的相应访问权限。

(3) 当用户要访问其他方案的对象时，必须附加方案名作为前缀。

2、创建用户

2 语法

Createuser 用户名 identified by 密码

[defaulttablespace 表空间名1]

[temporarytablespace表空间名2]

[quota[integer k|m] |[unlimited] on tablespace表空间名1]

[account lock| account unlock]

2 参数说明

l 用户名和密码：一般为字母、数字、“#”、“_”符号。

l default tablespace：表示该用户在创建数据对象时使用的默认表空间。

l temporary tablespace：表示该用户使用的临时表空间。

l quota integer k|m ontablespace：表示该用户在指定的表空间中允许占用的最大空间。

l quota unlimited ontablespace：表示该用户在指定的表空间中可以占用的空间大小没有上限。

l account lock：表示该用户被锁定。默认情况是account unlock。

2 举例：

(1) 创建一个user1用户，口令为123，并设置默认表空间为users，临时表空间为temp。

SQL> conn /as sysdba

SQL> createuser user1 identified by 123 default tablespace users temporarytablespace temp;

(2) 创建一个user2用户，口令为123，并设置默认表空间为users且最多可使用大小为1m，临时表空间为temp且不限制其大小，user2用户创建时被锁定。

SQL> createuser user2 identified by 123 default tablespace users temporarytablespace temp quota 1m on users accountlock;

2 说明：

(1) 如果建立用户时不指定defaulttablespace子句，Oracle会将SYSTEM表空间作为用户默认表空间。

(2) 如果建立用户时不指定temporarytablespace子句，Oracle会将数据库临时表空间作为用户临时表空间。

(3) 初始建立的用户没有任何权限，不能执行任何数据库操作，所以为了使用户可以连接到数据库，必须授权其create session 权限。

SQL> conn user1/123 --是否能登陆？

SQL> conn / as sysdba

SQL> grant createsession to user1;

(4) 如果建立用户时没有为表空间指定quota子句，那么用户在特定表空间上的配额为0，用户将不能在相应的表空间上建立数据对象。

SQL> connuser1/123 --是否能登陆？

SQL> createtable S(sno char(2)); --是否能建表？

SQL> conn /as sysdba

SQL> grantcreate table to user1;

SQL> connuser1/123

SQL> create table S(snochar(2)); --是否能建表？

SQL> insert into Svalues('11'); --是否能插入数据？

3、修改用户

管理员对以创建的用户进行修改，包括：修改用户口令，改变用户默认表空间、临时表空间、磁盘配额等。

2 语法

Alter user 用户名identified by 密码

[default tablespace 表空间名1]

[temporary tablespace表空间名2]

[quota [integer k|m]|[unlimited] on tablespace表空间名1]

[account lock| accountunlock]

2 举例：

(1) 修改用户的磁盘限额

SQL> conn /as sysdba

SQL> alter user user1quota 1m on users;

SQL> connuser1/123

SQL> insert into Svalues('11'); --是否能插入数据？

SQL> conn / as sysdba

SQL> create tableuser1.tj as select * from dba_objects where 1=2;

SQL> insert intouser1.tj select * from dba_objects;

(2) 修改用户口令

SQL> conn /as sysdba

SQL> alteruser user1 identified by 321;

SQL> connuser1/123

SQL> connuser1/321

(3) 锁定或解锁被锁定的用户

SQL> conn /as sysdba

SQL> grant createsession to user2;

SQL> conn user2/123 --是否能登陆？

SQL> conn / as sysdba

SQL> alter user user2account unlock;

SQL> connuser2/123 --是否能登陆？

SQL> conn / as sysdba

SQL> alter user user2account lock;

SQL> connuser2/123 --是否能登陆？

4、删除用户

2 语法

Drop user 用户名[cascade];

2 说明：如果用户拥有数据库对象，则删除用户时必须加cascade选项，此时连同该用户所拥有的对象一起删除。

2 举例

SQL> conn / as sysdba

SQL> drop user user2; --是否能删除用户user2？

SQL> drop user user1; --是否能删除用户user1？

SQL> conn user1/321

SQL> drop table S;

SQL> conn / as sysdba

SQL> drop useruser1; --是否能删除用户user1？

SQL> conn /as sysdba

SQL> createuser user1 identified by 123 default tablespace users temporarytablespace temp;

SQL> grant createsession to user1;

SQL> grantcreate table to user1;

SQL> connuser1/123

SQL> create table S(snochar(2));

SQL> conn /as sysdba

SQL> drop user user1cascade; --是否能删除用户user1？

5、用户权限管理

2 权限介绍

权限是指执行特定类型sql命令或是访问其它方案对象的权利。Oracle数据库中将权限分为两大类：系统权限和对象权限。如果要执行某种特定的数据库操作，则必须为用户授予系统权限；如果用户要访问其他方案的对象，则必须为用户授予对象的权限。

2 系统权限

系统权限指对数据库管理的操作以及对数据对象的操作（创建、修改、删除），其中sysdba和sysoper就是其中的两种。比如当用户具有create table权限时，可以在其方案中建表，当用户具有create any table权限时，可以在任何方案中建表。

Oracle11g中含有200多种系统特权，所有的这些系统特权均被列举在SYSTEM_PRIVILEGE_MAP数据目录视图中。

常用的系统权限有：createsession create table create view create procedure

Create public synonym 建同义词 create trigger create cluster 建簇

2 显示系统权限

SQL> select * fromsystem_privilege_map order by name;

--显示所有系统权限，oracle 11g有208种。

2 授予系统权限

(1) 一般情况，授予系统权限是由DBA完成的。

(2) 如果用其他用户授予系统权限，则要求该用户必须具有grant any privilege的系统权限。

(3) 可以带有with admin option选项，这样被授予权限的用户或角色还可以将该系统权限授予其它用户或角色。

2 举例

(1) 创建两个用户ken, dell和tom，并指定密码。初始阶段它们没有任何权限。

Create user ken identified by ken;

Create user dell identified by dell;

Create user tom identified by tom;

(2) 给ken和dell授予createsession和create table权限时带with admin option；授予createview时不带with admin option。

Grant create session, create table token, dell with admin option;

Grant create view to ken;

(3) 通过ken给tom授权。

Grant create session, create table totom;

2 回收系统权限

(1) 一般情况下，回收系统权限是DBA完成的。

(2) 如果其它用户来回收系统权限，要求该用户必须具有相应系统权限及转授系统权限的选项(with admin option)。Ken、dell都可以回收tom的权利。

(3) 注意：系统权限级联收回的问题-----不是级联回收。

Revoke create session from ken; --tom仍可以连接

2 对象权限介绍

对象权限指对数据对象的操作（select,update,insert,delete）

(1) 指访问其它方案对象的权利，用户可以直接访问自己方案的对象，但是如果要访问别的方案的对象，则必须具有对象的权限。

(2) 常用的有 alter delete select insert update index

references引用

execute执行：如果用户想要执行其它方案的包/过程/函数，则须有execute权限。

(3) Selectdistinct privilege from dba_tab_privs; --dba查看oracle提供的所有的对象权限

2 授予对象权限

(1) 在oracle9i前，授予对象权限是由对象的所有者来完成的，如果用其它用户来操作，则需要用户具有相应的（with grant option）权限。

(2) 从oracle9i开始，sys和system可以将任何对象上的对象权限授予其它用户。

2 回收对象权限——级联回收

2 查询用户与权限

用户被授予的系统权限和对象权限都被记录在Oracle数据字典中，了解某个用户被授予哪些系统权限和对象权限是确保应用系统安全的重要工作。Oracle 11g用于存放用户、系统权限、对象权限有关的数据字典如下：

数据字典表	说明
DBA_USERS	存储数据库用户基本信息表
DBA_SYS_PRIVS	存储用户或角色的系统权限
DBA_TAB_PRIVS	存储用户的对象权限
USER_SYS_PRIVS	存储登陆用户的系统权限
ROLE_SYS_PRIVS	存储登陆用户的角色信息
ALL_TABLES	存储登陆用户可以查询的基本表信息
USER_TAB_PRIVS	存储登陆用户将哪些基本表权限授予哪些用户的信息
ALL_TAB_PRIVS	表示对象上的授权，用户是PUBLIC或被授予者或用户是属主

2 举例

select *from dba_role_privs where grantee='SCOTT'; --查询scott所拥有的角色

select * from dba_tab_privswhere grantee='DBA'; --一个角色包含的对象权限

6、角色管理

2 角色介绍

角色就是相关权限的命令集合。使用角色的主要目的就是为了简化权限的管理。包括：预定义角色和自定义角色。

2 预定义角色

预定义角色是指oracle所提供的角色，每种角色都用于执行一些特定的管理任务。常用的预定义角色connect，resource，dba。

--查询oracle中所有的角色(一般是DBA查看)

Select * from dba_roles;

其中，resource角色隐含了unlimited tablespace系统权限，即用户可以无限制的使用表空间。DBA角色具有所有的系统权限，及with admin option选项，默认的dba用户为sys和system，但是dba角色不具备启动和关闭数据库的权限。

2 自定义角色

一般是由具有DBA角色的用户来建立，如果用别的用户来建立，则需要具有create role的系统权限。在建立角色时可以指定验证方式（不验证，数据库验证等）。

2 建立角色（不验证）

如果角色是公用的角色，可以采用不验证的方式建立角色。

Create role 角色名not identified;

2 建立角色（数据库验证）

采用这样的方式时，角色名、口令存放在数据库中。当激活该角色时，必须提供口令，在建立这种角色时，需要为其提供口令。

Create role 角色名identified by 口令

2 举例

Conn / as sysdba

Create role r1;

Create role r2 identified by r2;

Create role r3 identified by r3;

Create user u1 identified by u1;

Grant connect to r1;

Grant select on scott.emp to r2;

Grant insert on scott.emp to r3;

Grant r1, r2, r3 to u1;

Conn u1/u1

Select * from session_roles; --查看当前用户所有生效的角色

Set role r2 identified by r2,r3identified by r3; --使带有口令的角色生效

Select * from scott.emp;

Set role none; --使所有角色失效

Select * from session_roles;

2 补充：

以sys用户登陆Oracle，执行select * fromV_$PWFILE_USERS;可查询到具有sysdba权限的用户，如：

SQL> select * from V_$PWFILE_USERS;

USERNAME SYSDBA SYSOPER

SYS TRUE TRUE

sysdba拥有最高的系统权限，登陆后是 sys

sysoper主要用来启动、关闭数据库，sysoper 登陆后用户是public

sysdba和sysoper属于systemprivilege，也称为administrative privilege，拥有例如数据库开启关闭之类一些系统管理级别的权限sysdba和sysoper具体的权限可以看下表：

系统权限	sysdba	sysoper
区别	Startup(启动数据库)	startup
	Shutdown(关闭数据库)	shutdown
	alter database open/mount/backup	alter database open/mount/backup
	改变字符集	none
	create database(创建数据库)	None不能创建数据库
	drop database(删除数据库)	none
	create spfile	create spfile
	alter database archivelog(归档日志)	alter database archivelog
	alter database recover(恢复数据库)	只能完全恢复，不能执行不完全恢复
	拥有restricted session(会话限制)权限	拥有restricted session权限
	可以让用户作为sys用户连接	可以进行一些基本的操作，但不能查看用户数据
	登录之后用户是sys	登录之后用户是public

节日快乐。。。