oracle学习札记12-索引和权限
oracle学习笔记12--索引和权限
何谓索引
索引是用于加速数据存取的数据对象，合理的使用索引可以大大降低i/o次数，从而提高数据访问性能。
单列索引
单列索引是基于单个列多建立的索引，比如：
create index 索引名 on 表名（列名）；
复合索引
复合索引是基于两列或是多列的索引，在同一张表上可以有多个索引， 但是要求列的组合必须不同，比如：
create index emp_index1 on emp(ename,job);
create index emp_index2 on emp(job,ename);
上面两个索引是两个不同的索引，比如说第一个查询的时候会先按ename查，得到中间结果后再按照job查，而第二个则是先按照job查，中间结果再按照ename查。
使用原则
a． 在大表上建立索引才有意义
b． 在where子句或是连接条件上经常引用的列上建立索引
c． 索引的层次不要超过四层。
索引的缺点：
1. 建立索引，系统大约要占用为表的1.2倍的硬盘和内存空间来保存索引。
2. 更新数据的时候，系统要用额外的时间来同时对索引进行更新，以维持数据和索引的一致性。
实践表明：不恰当的索引不但于事无补， 反而会降低系统性能。因为大量的索引在进行插入、修改、删除操作时比没有索引花费更多的系统时间。
索引也是可以用命令行的方式查看的，不过最简单的方法还是使用我们可爱的PL/SQL DEVELOPER来查看……(*^__^*) ……
权限和角色
这部分在前面其实也已经或多或少的涉及到了
当刚刚建立用户时，用户没有任何权限，也不能执行任何操作，如果要执行某种特定的数据库操作，则必须为其授予系统的权限，如果用户要访问其他方案的对象，则必须为其授予对象的权限，为了简化权限的管理，可以使用角色。
权限是指执行特定类型的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版本越高，其系统权限就越多，那么我们如何查询呢?
select * from system_privilege_map order by name;
授予系统权限
一般情况下，授予系统权限是由DBA来完成的，如果用其他用户来授予系统权限，则要求该用户必须具有grant any privilege的系统权限，在授予系统权限时，可以带有with admin option选项，这样，被授予权限的用户或是角色还可以将该系统权限授予其他用户或是角色。
案例：
创建两个用户tom和ken，初始阶段他们没有任何权限，如果登录就会给出错误信息。
create user ken identified by ken;
create user tom identified by tom;
grant create session,create table to ken with admin option;
grant create view to ken;
此时我们创建了ken和tom用户，并且为ken分配了create session,create table权限，由于分配权限时后面带上了with admin option，意味着ken用户可以把这两个权限再授予其它用户，比如tom，而create view是不能由ken用户授予其它用户的。
conn ken/ken;
grant create session,create table to tom with admin option;
此时tom用户也拥有了create session,create table这两个权限，而且他也可以把这两个权限授予他人。
回收系统权限
回收系统权限一般是由DBA来完成的， 如果其它的用户来回收系统权限， 要求用户必须具有相应系统权限及转授系统权限的选项（with admin option）。回收系统权限使用revoke来完成。
当回收了系统权限后，用户就不能执行相应的操作了，但是请注意，系统权限不是级联回收的。
对象权限介绍
指访问其他方案对象的权利，用户可以直接访问自己方案的对象，但是如果要访问其他方案的对象，则必须具有对象的权限，比如SMITH要访问SCOTT.EMP（SCOTT:方案，EMP：表），则必须在SCOTT.EMP表上具有对象的权限。
常用的对象权限：
alter修改delete删除select查询insert插入update修改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用户可以将任何对象上的对象权限授予其它用户。授予对象权限是用grant命令来完成的。
对象权限可以授予用户、角色和public，在授予权限时，如果带有with grant option选项，则可以将该权限转授给其它用户。但是要注意：with grant option选项不能被授予角色。
conn scott/tiger;
grant select on emp to ken whith grant option;
grant update on emp to ken;
grant all on emp to ken;
conn ken/ken;
grant select on scott.emp to tom;
上面各个语句的意思很明显，我想我就不用解释了。
其实我们可以进行更精细的权限控制：
conn scott/tiger;
grant select on emp(ename) to ken;这样一来，ken就只能查看emp表的ename字段。
授予alter权限
如果black用户要修改Scott.emp表的结构， 则必须授予alter对象权限。
conn scott/tiger;
grant alter on emp to black;
当然也可以用system或sys来完成这件事。
授予execute权限
如果用户想要执行其它方案的包、过程、函数，则需有execute权限，比如为了让ken可以执行包：dbms_transaction,可以授execute权限
conn system/manager;
grant execute on dbms_transaction to ken;
授予index权限
如果想在别的方案的表上建立索引，则必须具有index对象权限。
conn scott/tiger;
grant index on emp to ken;
最后说明一点，对象权限的回收是级联的！
角色
说白了，角色就是相关权限的命令集合，使用角色的主要目的就是为了简化权限的管理。
角色分为预定义角色和自定义角色
预定义角色有connect/resource/dba……如何查看一个角色具有哪些个权限，在前面已经讲过，这里就不重复了。
dba角色具有绝大部分系统权限，但是不具有sysdba和sysoper的特权（启动和关闭数据库）
自定义角色
顾名思义就是自己定义的角色，根据自己的需要来定义。一般是dba来建立，如果用别的用户来建立，则需要具有create role 的系统权限。在建立角色时可以指定验证方式：
1. 建立角色（不验证）
如果角色是公用的角色，可以采用不验证的方式建立角色
create role 角色名 not identified;
2. 建立角色（数据库验证）
采用这样的方式时，角色名口令存放在数据库中，当激活该角色时，必须提供口令。
create role 角色名identified by morflame;
角色授权
当建立角色时，角色没有任何权限，为了使得角色完成特定任务，必须为其授予相应的系统权限和对象权限
给角色授权和给用户授权没有太多区别，但是要注意，系统权限的unlimited tablespace和对象权限的with grant option选项是不能授予角色的。
conn system/manager;
grant create session to 角色名 with admin option;
conn scott/tiger;
grant select on tmp to 角色名；
通过上面的步骤就给角色授权了
分配角色给某个用户
一般分配角色是由dba来完成的，如果要以其他用户身份分配角色，则要求用户必须具有grant any role的系统权限
conn system/manager;
grant 角色名 to black with admin option;
因为我给了with admin option，所以black可以把system分配给他的角色分配给别的用户。
删除角色
使用drop role，dba或是具有drop any role的系统权限的用户来完成此操作。
conn system/manager;
drop role 角色名；
角色删除之后，曾经被授予该角色的用户将不再具有角色赋予的权限。
显示角色信息
a． 显示所有角色
select * from dba_roles;
b.显示角色具有的系统权限
select privilege ,admin_option from role_sys_privs where role=’角色名’;
c.显示角色具有的对象权限。
通过查询数据字典视图dba_tab_privs可以查看角色具有的对象权限或是列的权限。
d． 显示用户具有的角色及默认角色
当以用户的身份连接到数据库时，oracle会自动的激活默认的角色， 通过查询数据字典视图dba_role_privs可以显示某个用户具有的所有角色及当前默认的角色。
select granted_role,default_role from dba_role_privs where grantee=’ 用户名’;
e． 精细访问控制
是指用户可以使用函数，策略实现更加细微的安全访问控制。如果使用精细访问控制，则当在客户端发出sql语句（select/insert/update/delete）时，oracle会自动在sql语句后追加谓词（where子句），并执行新的sql语句。通过这样的控制，可以使得不同的数据库用户，在访问相同表时，返回不同的数据信息。
何谓索引
索引是用于加速数据存取的数据对象，合理的使用索引可以大大降低i/o次数，从而提高数据访问性能。
单列索引
单列索引是基于单个列多建立的索引，比如：
create index 索引名 on 表名（列名）；
复合索引
复合索引是基于两列或是多列的索引，在同一张表上可以有多个索引， 但是要求列的组合必须不同，比如：
create index emp_index1 on emp(ename,job);
create index emp_index2 on emp(job,ename);
上面两个索引是两个不同的索引，比如说第一个查询的时候会先按ename查，得到中间结果后再按照job查，而第二个则是先按照job查，中间结果再按照ename查。
使用原则
a． 在大表上建立索引才有意义
b． 在where子句或是连接条件上经常引用的列上建立索引
c． 索引的层次不要超过四层。
索引的缺点：
1. 建立索引，系统大约要占用为表的1.2倍的硬盘和内存空间来保存索引。
2. 更新数据的时候，系统要用额外的时间来同时对索引进行更新，以维持数据和索引的一致性。
实践表明：不恰当的索引不但于事无补， 反而会降低系统性能。因为大量的索引在进行插入、修改、删除操作时比没有索引花费更多的系统时间。
索引也是可以用命令行的方式查看的，不过最简单的方法还是使用我们可爱的PL/SQL DEVELOPER来查看……(*^__^*) ……
权限和角色
这部分在前面其实也已经或多或少的涉及到了
当刚刚建立用户时，用户没有任何权限，也不能执行任何操作，如果要执行某种特定的数据库操作，则必须为其授予系统的权限，如果用户要访问其他方案的对象，则必须为其授予对象的权限，为了简化权限的管理，可以使用角色。
权限是指执行特定类型的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版本越高，其系统权限就越多，那么我们如何查询呢?
select * from system_privilege_map order by name;
授予系统权限
一般情况下，授予系统权限是由DBA来完成的，如果用其他用户来授予系统权限，则要求该用户必须具有grant any privilege的系统权限，在授予系统权限时，可以带有with admin option选项，这样，被授予权限的用户或是角色还可以将该系统权限授予其他用户或是角色。
案例：
创建两个用户tom和ken，初始阶段他们没有任何权限，如果登录就会给出错误信息。
create user ken identified by ken;
create user tom identified by tom;
grant create session,create table to ken with admin option;
grant create view to ken;
此时我们创建了ken和tom用户，并且为ken分配了create session,create table权限，由于分配权限时后面带上了with admin option，意味着ken用户可以把这两个权限再授予其它用户，比如tom，而create view是不能由ken用户授予其它用户的。
conn ken/ken;
grant create session,create table to tom with admin option;
此时tom用户也拥有了create session,create table这两个权限，而且他也可以把这两个权限授予他人。
回收系统权限
回收系统权限一般是由DBA来完成的， 如果其它的用户来回收系统权限， 要求用户必须具有相应系统权限及转授系统权限的选项（with admin option）。回收系统权限使用revoke来完成。
当回收了系统权限后，用户就不能执行相应的操作了，但是请注意，系统权限不是级联回收的。
对象权限介绍
指访问其他方案对象的权利，用户可以直接访问自己方案的对象，但是如果要访问其他方案的对象，则必须具有对象的权限，比如SMITH要访问SCOTT.EMP（SCOTT:方案，EMP：表），则必须在SCOTT.EMP表上具有对象的权限。
常用的对象权限：
alter修改delete删除select查询insert插入update修改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用户可以将任何对象上的对象权限授予其它用户。授予对象权限是用grant命令来完成的。
对象权限可以授予用户、角色和public，在授予权限时，如果带有with grant option选项，则可以将该权限转授给其它用户。但是要注意：with grant option选项不能被授予角色。
conn scott/tiger;
grant select on emp to ken whith grant option;
grant update on emp to ken;
grant all on emp to ken;
conn ken/ken;
grant select on scott.emp to tom;
上面各个语句的意思很明显，我想我就不用解释了。
其实我们可以进行更精细的权限控制：
conn scott/tiger;
grant select on emp(ename) to ken;这样一来，ken就只能查看emp表的ename字段。
授予alter权限
如果black用户要修改Scott.emp表的结构， 则必须授予alter对象权限。
conn scott/tiger;
grant alter on emp to black;
当然也可以用system或sys来完成这件事。
授予execute权限
如果用户想要执行其它方案的包、过程、函数，则需有execute权限，比如为了让ken可以执行包：dbms_transaction,可以授execute权限
conn system/manager;
grant execute on dbms_transaction to ken;
授予index权限
如果想在别的方案的表上建立索引，则必须具有index对象权限。
conn scott/tiger;
grant index on emp to ken;
最后说明一点，对象权限的回收是级联的！
角色
说白了，角色就是相关权限的命令集合，使用角色的主要目的就是为了简化权限的管理。
角色分为预定义角色和自定义角色
预定义角色有connect/resource/dba……如何查看一个角色具有哪些个权限，在前面已经讲过，这里就不重复了。
dba角色具有绝大部分系统权限，但是不具有sysdba和sysoper的特权（启动和关闭数据库）
自定义角色
顾名思义就是自己定义的角色，根据自己的需要来定义。一般是dba来建立，如果用别的用户来建立，则需要具有create role 的系统权限。在建立角色时可以指定验证方式：
1. 建立角色（不验证）
如果角色是公用的角色，可以采用不验证的方式建立角色
create role 角色名 not identified;
2. 建立角色（数据库验证）
采用这样的方式时，角色名口令存放在数据库中，当激活该角色时，必须提供口令。
create role 角色名identified by morflame;
角色授权
当建立角色时，角色没有任何权限，为了使得角色完成特定任务，必须为其授予相应的系统权限和对象权限
给角色授权和给用户授权没有太多区别，但是要注意，系统权限的unlimited tablespace和对象权限的with grant option选项是不能授予角色的。
conn system/manager;
grant create session to 角色名 with admin option;
conn scott/tiger;
grant select on tmp to 角色名；
通过上面的步骤就给角色授权了
分配角色给某个用户
一般分配角色是由dba来完成的，如果要以其他用户身份分配角色，则要求用户必须具有grant any role的系统权限
conn system/manager;
grant 角色名 to black with admin option;
因为我给了with admin option，所以black可以把system分配给他的角色分配给别的用户。
删除角色
使用drop role，dba或是具有drop any role的系统权限的用户来完成此操作。
conn system/manager;
drop role 角色名；
角色删除之后，曾经被授予该角色的用户将不再具有角色赋予的权限。
显示角色信息
a． 显示所有角色
select * from dba_roles;
b.显示角色具有的系统权限
select privilege ,admin_option from role_sys_privs where role=’角色名’;
c.显示角色具有的对象权限。
通过查询数据字典视图dba_tab_privs可以查看角色具有的对象权限或是列的权限。
d． 显示用户具有的角色及默认角色
当以用户的身份连接到数据库时，oracle会自动的激活默认的角色， 通过查询数据字典视图dba_role_privs可以显示某个用户具有的所有角色及当前默认的角色。
select granted_role,default_role from dba_role_privs where grantee=’ 用户名’;
e． 精细访问控制
是指用户可以使用函数，策略实现更加细微的安全访问控制。如果使用精细访问控制，则当在客户端发出sql语句（select/insert/update/delete）时，oracle会自动在sql语句后追加谓词（where子句），并执行新的sql语句。通过这样的控制，可以使得不同的数据库用户，在访问相同表时，返回不同的数据信息。