Oracle之索引、权限

索引：

用于加速数据存取的数据对象。合理的使用索引可以大大降低i/o次数，从而提高数据访问性。

？为什么添加索引后可以提高查询速度

单列索引：

单列索引是基于单个列所建立的索引，比如:create   index  索引名   on   表名（列名）

复合索引

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

create   index   emp_idx1  on  emp   (ename,job);

create index emp_idx2  on  emp  (job,ename);

建立索引的原则：

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

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

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

索引缺点分析：

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

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

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

比如：

1、在很少用或者从来不用的字段；

2、逻辑型的字段，如男或者女

索引的种类：

按照数据存储的方式:可以分为B*数、反向索引，位图索引；（B*数索引建立在重复值很少的列，而位图索引则建立重复值很多，不同值相对固定的列上）

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

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

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

显示表的所有索引

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

SQL> select index_name ,index_type  from  user_indexes  where  table_name='customer';

显示索引列

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

SQL> select  table_name,column_name  from  user_ind_columns  where  index_name ='IND_ENAME';

管理权限和角色

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

查询oracle中所有的系统权限：【一般是system用户,亲测scott也可以？？？？？】

select  * from   system_privilege_map   order  by  name;

 PRIVILEGE NAME                                       PROPERTY
---------- ---------------------------------------- ----------
      -278 READ ANY FILE GROUP                               1
        -7 RESTRICTED SESSION                                0
      -236 RESUMABLE                                         0
      -312 SELECT ANY CUBE                                   0
      -307 SELECT ANY CUBE DIMENSION                         0
      -237 SELECT ANY DICTIONARY                             0
      -293 SELECT ANY MINING MODEL                           0
      -109 SELECT ANY SEQUENCE                               0
       -47 SELECT ANY TABLE                                  0
      -269 SELECT ANY TRANSACTION                            0
       -83 SYSDBA                                            0
       -84 SYSOPER                                           0
      -213 UNDER ANY TABLE                                   0
      -186 UNDER ANY TYPE                                    0
      -209 UNDER ANY VIEW                                    0
       -15 UNLIMITED TABLESPACE                              0
      -313 UPDATE ANY CUBE                                   0
      -322 UPDATE ANY CUBE BUILD PROCESS                     0
      -326 UPDATE ANY CUBE DIMENSION                         0
       -49 UPDATE ANY TABLE                                  0
 
208 rows selected

查询oracle中所有的对象权限：【一般是system用户】

SQL> select distinct privilege  from dba_tab_privs;
 
PRIVILEGE
----------------------------------------
EXECUTE
FLASHBACK
DEQUEUE
ON COMMIT REFRESH
ALTER
DELETE
UPDATE
DEBUG
QUERY REWRITE
SELECT
READ
USE
WRITE
INSERT
INDEX
REFERENCES
MERGE VIEW
 
17 rows selected

查询orcale中有多少角色？55

SQL> select * from dba_roles;

SQL> select * from dba_roles;
 
ROLE                           PASSWORD_REQUIRED AUTHENTICATION_TYPE
------------------------------ ----------------- -------------------
CONNECT                        NO                NONE
RESOURCE                       NO                NONE
DBA                            NO                NONE
SELECT_CATALOG_ROLE            NO                NONE
EXECUTE_CATALOG_ROLE           NO                NONE
DELETE_CATALOG_ROLE            NO                NONE
EXP_FULL_DATABASE              NO                NONE
IMP_FULL_DATABASE              NO                NONE
LOGSTDBY_ADMINISTRATOR         NO                NONE
DBFS_ROLE                      NO                NONE
AQ_ADMINISTRATOR_ROLE          NO                NONE
AQ_USER_ROLE                   NO                NONE
DATAPUMP_EXP_FULL_DATABASE     NO                NONE
DATAPUMP_IMP_FULL_DATABASE     NO                NONE
ADM_PARALLEL_EXECUTE_TASK      NO                NONE
GATHER_SYSTEM_STATISTICS       NO                NONE
JAVA_DEPLOY                    NO                NONE

权限：

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

图1

授予系统权限：

一般情况下，授予系统权限是有dba完成的，如果其他用户来授予系统权限，则要求该用户必须grant    any   privilege  的系统权限在授予权限时，可以带有with  admin   option 选项，被授予权限的用户或是角色还可以将系统权限授予其它的用户或是角色。

1、创建两个用户ken ，tom。初始阶段他们没有权限，如果登录就会给出错误的信息。

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  给授权：

grant  create  session  ，creatre  table  to  tom ；

grant create view  to  tom ;→错误

回收系统权限：

一般由DBA来完成，若其他的用户来回收系统权限，要求该用户必须要有的相应系统权限及传授系统权限的选项（with   admin  option ）。回收系统权限使用revoke来完成。当系统权限回收了用户就无法执行了。

注意：

系统权限回收后，用户就不能执行相应的操作了，但是注意，系统权限级联回收的问题？【不是级联回收的！：ken的权限被回收了，则tom的权限还存在】

system →ken→tom

用system执行以下操作

revoke  create  session  from  ken；（此时tom也无法登录了）

对象权限的介绍

指的是访问其他方案对象的权利，用户可以直接访问自己的对象但是要访问其他方案的对象，则必须要具有对象的权限。比如：smith用户要访问scott。emp表（scott：方案）则必须在scott.表上具有对象权限。

常用的有：

alter 修改   Delete 删除  select  查询 insert 插入      update    ，index索引    reference  引用     execute执行

all（所有权限）

显示对象

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

SQL> select distinct privilege from dba_tab_privs;

SQL> select grantor,owner,table_name,privilege from dba_tab_privs  where  grantee ='BLAKE';

授予对象权限

在oracle9i之前授予对象权限是对象所有者完成的，若用其他用户来操作，则需要用户具有相应的（with  grant   option）权限，之后dba用户（sys，system）可以将任何对象权限授予其他用户，授权对象权限使用grant 命令来完成。

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

①希望monkey可以查询scott.emp表数据，怎么操作？

grant  select  on emp   to   monkey

②希望monkey可以修改scott.emp表数据，怎么样操作？

grant  select  on  emp  to  monkey；

 2、能否对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对象权限

grant  alter  on  emp   to  balke ；

4、授予execute权限

如果用户想要执行其他方案的包/过程/函数，则必须有execute权限。比如为了让ken可以执行dbms_transaction,可以授予execute权限

  grant  execute  on  dbms_transaction  to  ken;

5、授予index权限

让用户在别的方案上建立索引，必须有index权限。让black在在scott.emp上建立索引，就给其index的对象权限

grant  index  on  scott.emp   to  blake；

6，使用with  grant   option 

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

回收对象权限：

在oracle9i中，收回对象权限可以由对象的所有者来完成，也可以由dba来完成。但是，收回对象后，用户就不能执行相应的sql命令，注意对象的权限是否级联收回？【级联回收：blake的权限被收回，则jones的权限也就消失了】

如：scott →blake→jones

drop user hfut   cascade;