oracle实战第五天--索引、管理权限和角色

内容介绍

1，维护数据的完整性。

2，管理索引。

3，管理权限和角色。

期望目标

1，掌握维护oracle数据完整性的技巧。

2，理解索引概念，会建立索引。

3，管理oracle 的权限和角色。

维护数据的完整性

介绍

数据的完整性用于确保数据库数据遵从一定的商业和逻辑规则。在oracle中，数据完整性可以使用约束、触发器、应用程序（过程、函数）三种方法来实现，在这三种方法中，因为约束易于维护，并且具有最好的性能，所以作为维护数据完整性的首选。

约束

约束用于确保数据库数据满足特定的商业规则。在oracle 中，约束包括：not null、unique、primary key、foreign key和check五种。

Not null（非空）

如果在列上定义了not null，那么当插入数据时，必须为列提供数据。

Unique（唯一）

当定义了唯一约束后，该列值是不能重复的。但是可以为null.

Primary key（主键）

用于唯一的标示表行的数据，当定义主键约束后，该列不但不能重复而且不能为null。

需要说明的是：一张表最多只能有一个主键，但是可以有多个unqiue约束。

Foreign key（外键）

用于定义主表和从表之间的关系。外键约束要定义在从表中，主表则必须具有主键约束或是unique约束，当定义外键约束后，要求外键列数据必须在主表的主键列存在或是为null。

Check

用于强制行数据必须满足的条件，假定在sal列上定义了check约束，并要求sal列值在1000-2000之间如果不再1000-2000之间就会提示出错。

使用

商店售货系统表设计案例

现有一个商店的数据库，记录客户及其购物情况，由下面三个表组成：商品goods(商品号goodsId, 商品名goodsName, 单价unitprice, 商品类别category, 供应商provider); 客户customer(客户号customerId, 姓名name, 地址address, 电邮email, 性别sex, 身份证cardId ); 购买purchase(客户号customerId, 商品号goodsId, 购买数量nums);

请用SQL语言完成下列功能：

1. 建表，在定义中要求声明：

1）每个表的主外键。

2）客户的姓名不能为空值。

3）单价必须大于0，购买数量必须在1到30之间；

4）电邮不能够重复；

5）客户的性别必须是男或者女，默认是男。

createtablegoods(
goodsIdchar(8)primarykey,
goodsNamevarchar2(30),
unitpricenumber(10,2)check(unitprice > 0),
categoryvarchar2(8),
providervarchar2(30)
);

createtablecustomer(
customerIdchar(8)primarykey,
namevarchar2(50)notnull,
addressvarchar2(50),
emailvarchar2(50)unique,
sexchar(4)default'ÄÐ'check(sexin('ÄÐ','Å®')),
cardIdchar(18)
);

createtablepurchase(
customerIdchar(8)referencescustomer(customerId),
goodsIdchar(8)referencesgoods(goodsId),
numsnumber(10)check(numsbetween1and30)
);

商店售货系统表设计案例（2）

如果在建表时忘记建立必要的约束，则可以在建表后使用alter table命令为表增加约束，但是要注意：增加not null 约束时，需要使用modify选项，而增加其它四种约束使用add选项。

1）每个表的主外码。

2）客户的姓名不能为空；--增加商品名也不能为空。

3）单价必须大于0，购买数量必须在1-30之间。

4）电邮不能够重复；--增加身份证也不重复

5）客户的性别必须是男或女，默认是男

6）增加客户的住址只能是‘海淀’、‘朝阳’、‘东城’、‘西城’、‘通州’、‘崇文’。

altertablegoodsmodifygoodsNamenotnull;

altertablecustomeraddconstraintcardnuiqueunique(cardId);

altertablecustomeraddconstraintaddresscheckcheck(addressin('海淀','朝阳','东城','西城','通州','崇文'));

删除约束

当不再需要某个约束时，可以删除 。

Alter table 表名 drop constraint 约束名称；

特别说明一下：

在删除主键约束的时候，可能有错误，比如：

Alter table 表名 drop primary key;

这是因为如果在两张表存在主从关系，那么在删除主表的主键约束时，必须带上cascade选项。

如：

Alter table 表名 drop primary key cascade;

显示约束信息

1. 1.显示约束信息

通过查询数据字典视图user_constraint, 可以显示当前用户所有的约束的信息。

Select constraint_name, constraint_type, status, validated from user_constraints where table_name=’表名’;

1. 2.显示约束列

通过查询数据字典视图user_cons_columns, 可以显示约束所对应的表列信息。

Select column_name,position from user_cons_columns where constraint_name=’ 约束名’;

1. 当然也有更容易的方法，直接用pl/sql developer查看即可，简单演示一下…

表级定义列级定义

列级定义

列级定义是在定义列的同时定义约束

如在department表定义主键约束

createtabledepartment4(
dept_idnumber(2)constraintpk_departmentprimarykey,
namevarchar2(12),
locvarchar2(12)
);

表级定义

表级定义是指在定义了所有列后，再定义约束，这里需要注意：

Not null约束只能在列级上定义。

以在建立employee2表时定义主键约束和外键约束为例：

createtableemployee2(
emp_idnumber(4),
namevarchar2(15),
dept_idnumber(2),
constraintpk_employeeprimarykey(emp_id),
constraintfk_departmentforeignkey(dept_id)referencesdepartment4(dept_id)
);

管理索引－原理介绍

介绍

索引是用于加速数据存取的数据对象。合理的使用索引可以大大降低i/o次数，从而提高数据访问性能。索引有很多种我们主要介绍常用的几种：

为什么添加了索引后，会加快查询速度呢？

创建索引

单列索引

单列索引是基于单个列所建立的索引，比如：

Create index 索引名 on 表名(列名)

复合索引

复合索引是基于两列或是多列的索引。在同一张表上可以有多个索引，但是要求的组合必须不同，比如：

Create index emp.indx1 on emp(ename,job);

Create index emp.idx1 on emp (job,ename);

使用原则：

1）在大表上建立索引才有意义。

2）在where子句或是连接条件上经常引用的列上建立索引。

3）索引的层次不要超过4层。

索引缺点分析

索引有一些先天不足：

1）建立索引，系统要占用大约表的1.2倍的硬盘和内存空间来保存索引。

2）更新数据的时候，系统必须要有额外的时间来同时对索引进行更新，以维持数据和索引的一致性。

实践表明，不恰当的索引不但于事无补，反而会降低系统性能。因为大量的索引在进行插入，修改和删除操作时比没有索引花费更多的系统时间。

比如在如下的字段建立索引应该是不恰当的。

1）很少或从不引用的字段。

2）逻辑型的字段，如男或女（是或否）等。综上所述，提高查询效率是以消耗一定的系统资源为代价的，索引不能盲目的建立，这是考验一个DBA是否优秀的很重要的指标。

其它索引

按照数据存储方式，可以分为B*树、反向索引、位图索引；

按照索引列的个数分类，可以分为单列索引、复合索引。

按照索引列值的唯一性，可以分为唯一索引和非唯一索引。

此外还有函数索引，全局索引，分区索引…

对于索引，特别说明：

在不同的情况我们会在不同的列上建立索引，甚至建立不同种类的索引，请记住，技术是死的，人是活的。比如：

B*树索引建立在重复值很少的列上，而位图索引建立在重复值很多、不同值相对固定的列上。

显示索引信息

显示表的所有索引

在同一张表上可以有多个索引，通过查询数据字典视图dba_indexs和user_indexs，可以显示索引信息。其中dba_indexs用于显示数据库所有的索引信息，而user_indexs用于显示当前用户的索引信息：

Selectindex_name,index_type

fromuser_indexes

wheretable_name =’表名’;

显示索引列

通过查询数据字段视图user_ind_columns,可以显示索引对应的列的信息。

Selecttable_name,column_name
fromuser_ind_columns
whereindex_name='IND_ENAME';

当然也可以通过pl/sql developer工具查看索引信息。

管理权限和角色

介绍

这一部分我们主要看看oracle 中如何管理权限和角色，权限和角色的区别在哪里。

当刚刚建立用户时，用户没有任何权限，也不能执行任何操作。如果要执行某种特定的数据库操作，则必须为其授予系统的权限；如果用户要访问其它方案的对象，则必须为其授予对象的权限，为了简化权限的管理，可以使用角色。

权限

权限是指执行特定类型sql命令或是访问其它方案对象的权利，包括系统权限和对象权限两种：

系统权限

是指执行特定类型sql的权利。它用于控制用户可以执行的一个或是一组数据库操作。比如当用户具有create table权限时，可以在其方案中建表，当用户具有create any table 权限时，可以在任何方案中建表，oracle提供了100多种系统权限。

常用的有：

Create session连接数据库

Create table建表

Create view建视图

Create public synonym建同义词

Create procedure建过程、函数、包

Create trigger建触发器

Create cluster建簇

显示系统权限

Oracle 提供了100多种系统权限，而且oracle的版本越高，提供的系统权限就越多，我们可以查询数据字典视图system_privilege_map，可以显示所有系统权限。

select*fromsystem_privilege_maporderbyname;

授予系统权限

一般情况，授予系统权限是由dba完成的，如果用其它用户来授予系统权限，则要求该用户必须具有grant any privilege的系统权限，在授予系统权限时，可以带有with admin option 选项，这样，被授予权限的用户或是角色还可以将该系统权限授予其它的用户或是角色。为了让大家快速理解，我们举例说明:

1.创建两个用户key,tom,初始阶段他们没有任何权限，如果登陆就会给出错误的信息

createuserkenidentifiedbyken;
createusertomidentifiedbytom;

2.给用户ken授权

grantcreatesession,createtabletokeywithadminoption;

grantcreateviewtoken;

3.给用户tom授权。我们可以通过ken给tom授权，因为with admin option 是加上的，当然也通过dba给tom授权，我们就用 key给tom授权。

conn ken/ken;
grantcreatesession,createtabletotom;
grantcreateviewtotom;--这里会报错，因为create view是不可以传递的

回收系统权限

一般情况下，回收系统权限是dba来完成的，如果其它用户来回收系统权限，要求该用户必须具有相应系统权限及转授系统权限的选项（with admin option）。回收系统权限使用revoke来完成。

当回收了系统权限后，用户就不能执行相应的操作了，但是请注意，系统权限级联回收问题。

Systemàkenàtom

用system执行如下操作：

revokecreatesessionfromken;

请思考tom还能登录吗？答案是能。所以系统权限不是级联回收。

对象权限介绍

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

比如smith用户要访问scott.emp表(scott:方案，emp: 表)，则必须在scott.emp表上具有对象的权限。

常用的有：

Alter delete select insert update index references execute

显示对象权限

通过数据字段视图可以显示用户或是角色具有的对象权限，视图为 dba_tab_privs

selectdistinctprivilegefromdba_tab_privs;

output:

PRIVILEGE

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

FLASHBACK

EXECUTE

ON COMMIT REFRESH

ALTER

DEQUEUE

UPDATE

DELETE

DEBUG

UNDER

QUERY REWRITE

SELECT

READ

INSERT

INDEX

WRITE

REFERENCES

MERGE VIEW

selectgrantor,owner,table_name,privilege
fromdba_tab_privswheregrantee='BLAKE';

授予对象权限

在oracle9i前，授予对象权限是由对象的所有者来完成的，如果用其它的用户来操作，则需要用户具有相应的(with grant option)权限，从oracle9i开始，dba用户（sys,system）可以将任何对象上的对象权限授予其它用户。授予对象权限是用grant命令来完成的。

对象权限可以授予用户，角色，和public。在授予权限时，如果带有with grant option选项，则可以将该权限转授其它用户，但是要注意with grant option选项不能被授予角色。

1.monkey用户要操作scoatt.emp表，则必须授予相应的对象权限。

connsystem/managerassysdba;
createusermonkeyidentifiedbymonkey;
grantcreatesessiontomonkey;
conn scott/tiger;

1）希望monkey用户可以查询scott.emp表的数据，怎样操作？

grantselectonemptomonkey;

2）希望monkey用户可以修改scott.emp表的数据，怎样操作？

grantupdateonemptomonkey;

3）希望monkey用户可以删除scott.emp表的数据，怎样操作？

grantdeleteonemptomonkey;

4）有没有更加简单的方法，一次把所有权限赋给monkey.

grant allonemptomonkey;

2.能否对monkey访问权限更加精细控制。（授予列权限）

1）希望monkey只可以修改scott.emp的表的sal字段，怎样操作？

grantupdateonemp(sal)tomonkey;

2）希望monkey只可查询scott.emp的表的eanme,sal数据，怎样操作？

grantselectonemp(ename,sal)tomonkey;

3.授予alter权限

如果black用户要修改scott.emp表的结构，则必须授予alter对象权限

conn scott/tiger;
grantalteronemptoblake;

当然也可以用system,sys来完成这件事

4.授予execute权限

如果用户想要执行其它方案的包、过程、函数，则须有execute权限，比如为了让ken可以执行包dbms.transaction,可能授execute权限

connsystem/manager;
grantexecuteondbms_transactiontoken;

5.授予index权限

如果想在别的方案的表上建立索引，则必须具有index对象权限，如为了让blake可以在scott.emp上建立索引，就给其index的对象权限

conn scott/tiger;
grantindexonscott.emptoblake;

6.使用with grant option选项

该选项用于转授对象权限，但是该选项只能被授予用户，而不能授予角色

conn scott/tiger;
grantselectonemptoblakewithgrantoption;
conn blake/123456;
grantselectonscott.emptojones;

回收对象权限

在oralce 9i中，收回对象的权限可以由对象的所有者来完成，也可以用dba用户（sys,system）来完成，这里要说明的是：收回对象权限后，用户就不能执行相应的sql命令，但是要注意的是对象的权限是否会被级联收加？[级联收加]

如： scott ----à blake -----à jones

Select on emp

conn scott/tiger;
revokeselectonempfromblake;

请大家思考：jones能否查询scott.emp表数据。

角色

角色就是相关权限的命令集合，使用角色的主要目的就是为了简化权限的管理，假定有用户A,B,C为了让他们都拥有权限

1）连接数据库

2）在scott.emp表上select,insert,update

如果采用直接授权操作，则需要进行12次授权。

我们如果采用角色就可以简化：

首先将create session,select on scott.emp,insert on scott.emp,update on scott.emp授予角色，然后将该角色授予a,b,c用户，这样就可以三次授权搞定，角色分为预定义和自定义角色两类：

这里我们可以考虑使用自定义角色来解决问题。

预定义角色：

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

1)connect角色

具有一般应用开发人员需要的大部分权限，当建立了一个用户后，多数情况下，只要用户授予connect和resourse角色就够了，那么connect角色具有哪些系统权限呢？

Alter session

Create cluster

Create database link

Create session

Create table

Create view

Create sequence

2)resourse角色

具有应用开发人员所需要的其它权限，比如建立存储过程，触发器等。这里需要注意的是resource角色隐含了unlimited tablespase系统权限：

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

Create cluster

Create indextype

Create table

Create sequence

Create type

Create procedure

Create trigger

3)dba角色

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

自定义角色

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

1）建立角色（不验证）

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

createrole角色名notidentified;

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

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

createrole角色名identifiedbyzhangsan;

角色授权

当建立角色时，角色没有任何权限，为了使得角色完成特定任务，必须为其授予相应的系统权限和对象权限。

1）给角色授予权限和给用户授权没有太多区别，但是要注意，系统权限的unlimited tablespace和对象权限的with grant option选项是不能授予角色的。

connsystem/manger;
grantcreatesessiontomyrolewithadminoption;
conn scott/tiger@orcl;
grantselectonscott.emptomyrole;
grantinsert,update,deleteonscott.emptomyrole;

通过上面的步骤，就给角色授权了。

2）分配角色给某个用户

一般分配角色是由DBA来完成的，如果要以其它用户身份分配角色，则要求用户必须具有grant any role 和系统权限。

connsystem/manager;
grantmyroletoblakewithadminoption;

因为我给了with admin option选项，所以，blake可以把system分配给它的角色分配给别的用户。

删除角色

使用drop role,一般是dba来执行，如用其它用户则要求该用户具有drop any role系统权限

connsystem/manager;
droprolemyrole;

显示角色信息

1）显示所有角色

select*fromdba_roles;

2）显示角色具有的系统权限

selectprivilege,admin_optionfromrole_sys_privswhererole='myrole';

3）显示角色具有的对象权限

通过查询数据字典视图dba_tab_privs可以查看角色具有的对象权限或是列的权限。

4）显示用户具有的角色，及默认角色

当以用户的身份连接到数据库时，oracle会自动的激活默认的角色，通过查询数据字典视图dba_role_privs可以显示某个用户具有的所有角色及当前默认的角色。

selectgranted_role,default_rolefromdba_role_privs
wheregrantee='scott’;

精细访问控制

是指用户可以使用函数，策略实现更加细微的安全访问控制。如果使用精细访问控制，则当在客户端发出sql语句(select,insert,update,delete)时，oracle会自动在sql语句后追加谓词（where子句）,并执行新的sql语句。通过这样的控制，可以使得不同的数据库用户在访问相同表时，返回不同的数据信息，如图：

用户： scott blake jones

策略： emp_access

数据库表emp

如上图所示：通过策略emp_access,用户scott,black,jones在执行相同的sql语句时，可以返回不同的结果。例如：当执行select ename form emp;时，更具实际情况可以返回不同的结果。