Oracle常用命令07(约束,索引,权限,角色)

期望目标：
①掌握维护oracle数据完整性的技巧
②理解索引概念，会建立索引【优化数据库的第一步就是看有没有建立索引】
③管理oracle的权限和角色

维护数据的完整行 - 介绍
数据的完整性用于确保数据库数据遵从一定的商业和逻辑规则。
在oracle中，数据完整性可以使用约束、触发器、应用程序(过程、函数)
三种方法来实现，在这三种方法中，因为约束易于维护，并且具有最好的性能
，所以作为维护数据完整性的首选。
【约束：数据不合理我就不让你放进来】

【约束】
约束用于确保数据库满足特定的商业规则。
在oracle中，约束包括：
①not null【非空】,
②unique【唯一】,
③primary key【主键】,
④foreign key 【外键】,
⑤check 【检查，如年龄大于5岁小于100岁】

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

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

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

【primary key】主键
用于唯一的表示行的数据，当定义主键约束后，该列不但不能重复而且
不能为null。
需要说明的是：一张表最多只能有一个主键，但是可以有多个unique约束。

【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 建表，在定义中要求声明：
① 每个表的主外键；
② 客户的姓名不能为空值；
③ 单价必须大于0，购买数量必须在1到30之间；
④电邮不能够重复；
⑤客户的性别必须是男或者女，默认是男

【不知道这个为什么会有问题？匪夷所思】
create table customer(
customerId char(8) primary key, --主键
name varchar2(30) not null, --不为空
address varchar2(50),
email varchar2(50) unique, --不能重复
sex char(2) defaut '男' check(sex in ('男','女')), --1个char只能存半个汉字
cardId char(18) --第2代身份证就18位
);

【下面这个和上面明明一样，为什么呢？真是活见鬼】
create table customer(
customerId char(8) primary key, --主键
name varchar2(50) not null, --不为空
address varchar2(50),
email varchar2(50) unique, --不能重复
sex char(2) default '男' check (sex in ('男','女')), --1个char只能存半个汉字
cardId char(18) --第2代身份证就18位
);

create table purchase (
customerId char(8) references customer(customerId),
goodsId char(8) references goods(goodsId),
nums number(10) check (nums between 1 and 30)
);

【商品售货系统表设计案例2】
如果在建表时忘记建立必要的约束，则可以在建表后使用
alter table 命令为表增加约束。但是要注意：增加not null约束时，需要使用
modify选项，而增加其他四种约束使用add选项。
①每个表的主外码；
②客户的姓名不能为空值；--增加商品名也不能为空
在plsql中进行可视化操作时最简洁的~
alter table goods modify goodsName not null;
③单价必须大于0，购买数量必须在1到30之间；
④电邮不能够重复；--增加身份证也不能重复
alter table customer add constraint cardunique unique(cardId);
⑤客户的性别必须是 男 或者 女，默认是男
⑥ 增加客户的住址只能是‘海淀’、‘朝阳’、‘东城’、‘西城’...
alter table customer add constraint addresscheck check (address in ('东城','西城'));

【删除约束】
但不再需要某个约束时，可以删除。
alter table 表名 drop constraint 约束名称;
【特别说明】
在删除主键约束的时候，可能有错误(某些表的外键可能对被删除的主键有所依赖)，比如：
alter table 表名 drop primary key;
这是因为如果在两张表存在主从关系，那么在删除主表的主键约束时，必须带上cascade选项，如像
alter table 表名 drop primary key cascade;

【显示约束信息】
①显示约束信息
通过查询数据字典视图uesr_constraints，可以显示当前用户所有的约束的信息。
select constraint_name,constraint_type,status,validated from
user_constraints where table_name='GOODS';
②显示约束列
通过查询数据字典视图user_cons_columns，可以显示约束所对应的表列信息。
select column_name,position from user_cons_columns where
constraint_name='ADDRESSCHECK';
③当然也有更容易的方法，直接用pl/sql developer查看即可。

【表级定义和列级定义】
【列级定义】
列级定义是在定义列的同时定义约束。
如在department 表定义主键约束
create table department4(
dept_id number(2) constraint pk_department primary key,
name varchar2(12),
loc varchar2(12)
);
【表级定义】
表级定义是指在定义了所有列后，再定义约束。这里需要注意：
not null 约束只能在列级上定义。
以在建立employee2表时定义主键约束和外键约束为例：
create table employee2(
emp_id number(4),
name varchar2(15),
dept_id number(2),
constraint pk_employee primary key (emp_id),
constraint fk_department foreign key (dept_id) reference department4(dept_id)
);

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

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

【单列索引】
单列索引是基于单个列所建立的索引，比如：
create index 索引名 on 表名(列名)

【复合索引】
复合索引是基于两列或是多列的索引。在同一张表上可以有多个索引，但是要求列的
组合必须不同，比如：【筛选范围大的放在后面，高效】
create index emp_idx1 on emp (ename,job);
create index emp_idx1 on emp (job,ename);

【索引缺点分析】
索引有一些先天不足：
①建立索引，系统要占用大约为表的1.2倍的硬盘和内存空间来保存索引；
②更新数据的时候，系统必须要有额外的时间来同时对索引进行更新，以维持
数据和索引的一致性。
实践表明，不恰当的索引不但于是无补，反而会降低系统性能。因为大量索引
在进行插入、修改和删除操作时比没有索引花费更多的系统时间。

比如在如下字段建立索引应该是不恰当的：
①很少或从不引用的字段；
②逻辑型的字段，如男或女（是或否）等。综上所述，提高查询效率是
以消耗一定的系统资源为代价的，索引不能盲目建立，这是考验一个DBA
是否优秀的很重要的指标。

【其他索引】
按照数据存储方式，可以分为B*树、反向索引、位图索引；
按照索引列的个数分类，可以分为单列索引、复合索引；
按照索引列值的唯一性，可以分为唯一索引和非唯一索引。
此外还有函数索引，全局索引，分区索引...

对于所引还有：
在不同的情况我们会在不同的列上建立索引，甚至建立不同种类的索引，
技术是死的，人是活的。比如：
B*树索引建立在重复值很少的列上，而位图索引则建立在重复值很多、
不同值相对固定的列上。

【显示表的索引】
在同一张表上可以有多个索引，通过查询数据字典视图
dba_indexs 和 user_indexs，可以显示索引信息。其中dba_indexs用于显示数据库
所有的索引信息，而user_indexs用于显示当前用户的索引信息：
select indexs_name,index_type from user_indexs where
table_name='表名';

【显示索引列】
通过查询数据字段视图 user_ind_columns，可以显示索引对应的列的信息
select table_name,column_name from user_ind_columns
where index_name='IND_ENAME';
也可以通过pl/sql developer工具查看索引信息。

【管理权限和角色 - 介绍】
这一部分我们主要看看oracle中如何管理权限和角色，权限和角色的区别在哪里
当刚刚建立用户时，用户没有任何权限，也不能执行任何操作。如果要执行某种特定
的数据库操作，则必须为其授予系统的权限；如果用户要访问其他方案的对象，则必须
为其授予对象的全乡。为了简化权限的管理，可以使用角色。

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

【系统权限介绍】
系统权限是指执行特定类型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 * from system_privilege_map order by name;

【授予系统权限】
一般情况，授予系统权限是由dba完成的，如果用其他用户来授予系统权限，
则要求该用户必须具有grant any privilege 的系统权限在授予系统权限时，
可以带有with admin option选项，这样，被授予权限的用户或是角色还可以
将该系统权限授予其他的用户或是角色。
1.创建两个用户ken,tom。初始阶段他们没有任何权限，如果登录就会给出
错误的信息：
create user ken identified by ken;
2.给用户 ken 授权
grant create session,create table to ken with admin option;
grant create view to ken
3.给用户 tom 授权
我们可以通过ken给tom授权，因为with admin option是加上的。
当然也可以通过dba给tom授权，我们就用ken给tom授权：
①grant create session,create table to tom;ok!
②grant create view to tom;不OK!

【回收系统权限 -- 不是级联回收】
一般情况下，回收系统权限时dba来完成的，如果其他的用户来回收系统权限，要求
该用户必须具有相应系统权限及传授系统权限的选项(with admin option)。回收
系统权限使用revoke来完成。
当回收了系统权限后，用户就不能执行相应的操作了，
但是请注意，系统权限级联回收问题？
system -----------------> ken ---------------> tom
(create session) (create session) (create session)
用system执行如下操作：
revoke create session from ken; 请思考，tom 还能登陆吗？能！！

【对象权限】
指访问其他方案对象的权利，用户可以直接访问自己方案的对象，
但是如果要访问别的方案的对象，则必须具有对象的权限，比如
smith用户要访问scott。emp表(scott：方案，emp：表)
则必须自爱scott.emp 表上具有对象的权限
常用的有：
alter修改
delete删除
select查询
insert添加
upate修改
index索引
references引用
execute执行

【显示对象权限】
通过数据字段视图可以显示用户或是角色所具有的对象权限。
视图为dba_tab_privs
conn system/manager
select distinct privilege from dba_tab_privs;
select grantor,owner,table_name,privilege from dba_tab_privs
where grantee='BLAKE';

【授予对象权限】
在oracle9i前，授予对象权限是由对象的所有者来完成的，如果用其他的用户
来操作，则需要用户具有相应的(with grant option)权限，从头oracle9i
开始，dba用户(sys,sytem)可以将任何对象上的对象权限授予其他用户。
授予对象权限时用grant命令来完成的。
对象权限可以授予用户，角色，和public。在授予权限时，如果带有
with grant option 选项，则可以将该权限传授给其他用户。
但是要注意 with grant option 选项不能被授予角色，
1.monkey用户要操作scott.emp表，则必须授予相应的对象权限
① 希望monkey可以查询scott.emp 的表数据，怎样操作？
grant select on scott.emp to monkey;
② 希望monkey可以修改scott.emp 的表数据，怎样操作？
grant update on scott.emp to monkey;
③ 希望monkey可以删除scott.emp的表数据，怎样操作？
grant delete on scott.emp to monkey;
④ 有没有跟家简单的方法，一次吧所有权限赋给monkey？
grant all on scott.emp to monkey;

能否对monkey访问权限更加精细的控制(授予列权限)
①希望monkey只可以修改scott.emp表的sal字段，怎样操作？
grant update on emp(sal) to monkey;
②希望monkey只可查询scott.emp表的ename，sal数据，怎样操作？
grant select on emp(ename,sal) to monkey;

3.授予alter权限
如果black用户要修改scott.emp表的结构，则必须授予 alter 对象权限
conn scott/tiger
grant alter on emp to black;
当然也可以用system，sys来完成这件事

4.授予execute权限
如果用户想要执行其他方案的包/过程/函数，则须有execute 权限。
比如为了让ken可以执行包 dbms_transaction,可以授予execute权限
conn system/manager
grant execute on dbms_transaction to ken;

5.授予index权限
如果想在别的方案的表上建立索引，则必须具有index对象权限，
如为了让black可以在scott。emp上建立索引，就给其index的对象权限
conn scott/tiger
grant index on scott.emp to black

6.使用with grant option 选项
该选项用于传授对象权限。但是该选项只能被授予用户，而不能授予角色。
conn scott/tiger;
grant select on emp to black with grant option;
conn black/shunping;
grant select on scott.emp to jones;

【回收对象权限】
在oracle9i中，收回对象的权限可以由对象的所有者来完成，
也可以用dba用户(sys, system)来完成
这里要说明的是：收回对象权限后，用户就不能执行相应的sql命令，
但是要注意的是对象的权限是否会被级联回收？
如：
scott -------------> black ---------------> jones
(select on emp) (select on emp) (select on emp)

conn scott/tiger@accp
revoke select on emp from black;
请大家思考，jones能否查询 scott.emp 表数据？不能(和系统权限相反)！

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

【管理权限和角色-角色】
角色就是相关权限的命令集合，使用角色的主要目的就是为了简化权限的而管理。
假定有用户a,b,c为了让他们都拥有权限
①连接数据库
②在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,resource,dba

①connect角色
connect角色具有一般应用开发人员需要的大部分权限，当建立了一个用户后，
多数情况下，只要给用户授予connect和resource角色就够了，那么connect角色
具有哪些系统权限呢？
alter session
create cluster
create database link
create session
create table
create view
create sequence

②resource角色
resource角色具有应用开发人员所需要的其他权限，比如建立存储过程、触发器等。
这里需要注意的是resource角色隐含了unlimitied 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 的特权(启动和关闭数据库)

【自定义角色】
顾名思义就是自己定义的角色，根据自己的需要来定义。一般是dba来建立，如果用的
别的用户来建立，则需要具有create role的系统权限。在建立角色时可以指定验证方式
(不验证，数据库验证等)
①建立角色(不验证)
如果角色是公用的角色，可以采用不验证的方式建立角色。
create role 角色名 not identified;
②建立角色(数据库验证)
采用这样的方式时，角色名、口令存放在数据库中。当激活该角色时，必须提供口令。
在建立这种角色时，需要为其提供口令
create role 角色名 identified by bruce;

【角色授权】
当建立角色时，角色没有任何权限，为了使得角色完成特定任务，必须为其授予相应的
系统权限和对象权限。
①给角色授权
给角色授予权限和给用户授权没有太多区别，但是要注意，系统权限的unlimited tablespace
和对象权限的with grant option 选项是不能授予角色的。
conn system/manager;
grant create session to 角色名 with admin option;
conn scott/tiger@myora1;
grant select on scott.emp to 角色名
grant insert,update,delete on scott.emp to 角色名
通过上面的步骤，就给角色授权了。

②分配角色给某个用户
一般分配角色是由dba来完成的，如果要以其他用户身份分配角色，则要求
用户必须具有grant any role 的系统权限。
conn system/manger
grant 角色名 to black with admin option;
因为我给了with admin option 选项
所以，black可以吧 system 分配个他的角色分配给别的用户。

---------------------------------------------------------
SQL> show user;
User is "SYS"

SQL> create role myrole1 not identified;

Role created

SQL> grant create session to myrole1 with admin option;

Grant succeeded

SQL> conn scott/m123;
Connected to Oracle Database 10g Enterprise Edition Release 10.1.0.2.0
Connected as scott

SQL> grant select on emp to myrole1;

Grant succeeded

SQL> grant update on emp to myrole1;

Grant succeeded

SQL> conn system/876521963
Connected to Oracle Database 10g Enterprise Edition Release 10.1.0.2.0
Connected as system

SQL> grant myrole1 to y3w;

Grant succeeded

SQL> conn y3w/m123;
Connected to Oracle Database 10g Enterprise Edition Release 10.1.0.2.0
Connected as y3w

SQL> select * from scott.emp;
成功取得scott.emp表的数据。

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

【删除角色】
使用drop role，一般是dba来执行，如用其他用户则要求该用户具有drop
any role 系统权限
conn system/manager
drop role 角色名

如果角色被删除，那么被赋予该角色的用户是否还可以继
续使用这种角色带来的特权？否【皮之不存，毛将焉附】
①删除myrole1角色【一般是system来做的】
drop role myrole1;【角色已丢弃】

【显示角色的信息】
①显示所有角色
select * from dba_roles;
②显示角色具有的系统权限【貌似有点儿问题】
select privilege,admin_option from role_sys_privs where role='CONNECT';
③显示角色具有的对象权限
通过查询数据字典视图dba_tab_privs可以查看角色具有的对象权限或是列
的权限。
④显示用户具有的角色，及默认角色
当以用户的身份连接到数据库时，oracle会自动激活默认的角色，通过查询数据
字典视图dba_role_privs 可以显示某个用户具有的所有角色及当前默认的角色
【如查看 SCOTT 具有怎样的角色】
select granted_role,default_role from dba_role_privs
where grantee='SCOTT';

【精细访问控制】
是指用户可以使用函数、策略实现更加细微的安全访问控制。如果使用精细访问控制，
则当在客户端发出sql语句(select,insert,update,delete)时，oracle会自动在sql
语句后追加谓词(where子句)，并执行新的sql语句。通过这样的控制，可以使得不同的
数据库用户在访问相同表时，返回不同的数据信息，如图
用户：scottblackjones
策略：emp_access
数据库表emp
如上图所示：通过策略emp_access，用户scott，black，jones在执行相同的sql语句
时，可以返回不同的结果。例如，当执行select ename from emp;时，根据实际情况
可以返回不同的结果