oracle 用户创建及权限设置 和 同义词

   oracle 用户创建及权限设置

 

权限：权限和同义词一般要一起设置，设置同义词后，查其他用户的表就不需要写其他用户的用户名。因此

在 user1 下 有表 table1，现在 user2 要使用 table1

1.设权限 ：grant select on table1to user2; (设置之后：select * from  user1.table1 可以查询)

2.设同义词： create public synonym table1 for A.table1; (设置之后：select * from table1 可以直接查询)

 

　　create session

 

　　create table

 

　　unlimited tablespace

 

　　connect

 

　　resource

 

　　dba

 

　　例：

 

　　#sqlplus /nolog

 

　　SQL> conn / as sysdba;

 

　　SQL>create user username identified by password

 

　　SQL> grant dba to username;

 

　　SQL> conn username/password

 

　　SQL> select * from user_sys_privs;

 

　　我们将从创建Oracle用户权限表开始谈起，然后讲解登陆等一般性动作，使大家对Oracle用户权限表有个深入的了解。

 

一、创建

 

　　sys;//系统管理员，拥有最高权限

 

　　system;//本地管理员，次高权限

 

　　scott;//普通用户，密码默认为tiger,默认未解锁

 

二、登陆

 

　　sqlplus / as sysdba;//登陆sys帐户

 

　　sqlplus sys as sysdba;//同上

 

　　sqlplus scott/tiger;//登陆普通用户scott

 

三、管理用户

 

　　create user zhangsan;//在管理员帐户下，创建用户zhangsan

 

　　alert user scott identified by tiger;//修改密码

       

       //创建session会话的权限、不然无法登入

       grant create session to facc_fields_sel;

 

四，授予权限

 

　　1、默认的普通用户scott默认未解锁，不能进行那个使用，新建的用户也没有任何权限，必须授予权限

 

　　

 

　　grant create session to zhangsan;//授予zhangsan用户创建session的权限，即登陆权限

 

　　grant unlimited tablespace to zhangsan;//授予zhangsan用户使用表空间的权限

 

　　grant create table to zhangsan;//授予创建表的权限

 

　　grante drop table to zhangsan;//授予删除表的权限

 

　　grant insert table to zhangsan;//插入表的权限

 

　　grant update table to zhangsan;//修改表的权限

 

　　grant all to public;//这条比较重要，授予所有权限(all)给所有用户(public)

 

　　2、oralce对权限管理比较严谨，普通用户之间也是默认不能互相访问的，需要互相授权

 

　　

 

　　grant select on tablename to zhangsan;//授予zhangsan用户查看指定表的权限

 

　　grant drop on tablename to zhangsan;//授予删除表的权限

 

　　grant insert on tablename to zhangsan;//授予插入的权限

 

　　grant update on tablename to zhangsan;//授予修改表的权限

 

　　grant insert(id) on tablename to zhangsan;

 

　　grant update(id) on tablename to zhangsan;//授予对指定表特定字段的插入和修改权限，注意，只能是insert和update

 

　　grant alert all table to zhangsan;//授予zhangsan用户alert任意表的权限

 

五、撤销权限

 

　　基本语法同grant,关键字为revoke

 

六、查看权限

 

　　select * from user_sys_privs;//查看当前用户所有权限

 

　　select * from user_tab_privs;//查看所用用户对表的权限

 

七、操作表的用户的表

 

　　

 

　　select * from zhangsan.tablename

 

八、权限传递

 

　　即用户A将权限授予B，B可以将操作的权限再授予C，命令如下：

 

　　grant alert table on tablename to zhangsan with admin option;//关键字 with admin option

 

　　grant alert table on tablename to zhangsan with grant option;//关键字 with grant option效果和admin类似

 

九、角色

 

　　角色即权限的集合，可以把一个角色授予给用户

 

　　create role myrole;//创建角色

 

　　grant create session to myrole;//将创建session的权限授予myrole

 

　　grant myrole to zhangsan;//授予zhangsan用户myrole的角色

 

 

　　drop role myrole;删除角色

 

 

Oracle的同义词（synonyms）详解

从字面上理解就是别名的意思，和视图的功能类似。就是一种映射关系。

    同义词语法：CREATE [PUBLIC] SYNONYM synonym FOR object;

1.创建同义词语句：

　 create public synonym table_name for user.table_name; 

    其中第一个user_table和第二个user_table可以不一样。

    此外如果要创建一个远程的数据库上的某张表的同义词，需要先创建一个Database Link(数据库连接)来扩展访问，然后在使用如下语句创建数据库同义词：create synonym table_name for table_name@DB_Link;

    当然，你可能需要在user用户中给当前用户(user2)授权： grant select/delete/update on user2

   注意：public 共用的同义词，如果要建立在哪个用户下面，请使用 (推荐使用这个)

          create  synonym user2.table1 for user.table1; 

          在 user2 中建立 名为 table1 的同义词，并 指定 user 的 table1 

2.删除同义词：

    drop public synonym table_name; 

3.查看所有同义词：

    select * from dba_synonyms 

    同义词拥有如下好处：节省大量的数据库空间，对不同用户的操作同一张表没有多少差别;扩展的数据库的使用范围，能够在不同的数据库用户之间实现无缝交互;同义词可以创建在不同一个数据库服务器上，通过网络实现连接。

 

知识扩展：数据库对象

   表、视图、序列、过程、函数、程序包，甚至其它同义词都可以创建同义词。

  

   1、同义词：私有同义词、公有同义词。

   私有同义词只能被当前模式的用户访问。私有同义词名称不可与当前模式的对象名称相同。要在自身的模式创建私有同义词，

   用户必须拥有Create Synonym系统权限。要在其它用户模式创建私有同义词，用户必须拥有Create Any Synonym系统权限。

   公有同义词可被所有的数据库用户访问。要创建公有同义词，用户必须拥有Create Public Synonym系统权限。

 

   创建私有同义词语法：

   Create [OR REPLACE] SYNONYM [schema.]synonym_name FOR [schema.]object_name;

   其中：OR REPLACE表示在同义词存在的情况下替换该同义词。

         synonym_name表示要创建的同义词的名称。

         object_name指定要为之创建同义词的对象的名称。

   示例1：create synonym s_emp for scott.emp;

 

   创建公有同义词语法：

   Create PUBLIC SYNONYM synonym_name FOR [schema.]object_name;

   示例2：create public synonym emp_syn from scott.emp;

 

   可以查询字典视图User_Synonyms来查看用户所创建的同义词的详细信息

  

   删除同义词语法：Drop Synonym synonym_name; 删除公有同义词加上一个Public

   此命令只删除同义词，不会删除对应的表。

 

   2、序列：是用来生成唯一、连续的整数的数据库对象。序列通常用来自动生成主键或唯一键的值。

   创建序列语法如下：

   Create SEQUENCE sequence_name

     [START WITH integer]

     [INCREMENT BY integer]

     [MAXVALUE integer|NOMAXVALUE]

     [MINVALUE integer|NOMINVALUE]

     [CYCLE|NOCYCLE]

     [CACHE interger|NOCACHE];

   其中：START WITH是指定要生成的第一个序列号。对于升序序列，其默认值为序列的最小值。对于降序序列，其默认值为序列的最大值。

         INCREMENT BY是用于指定序列号之间的间隔。其默认值为1。如果integer为正值，则生成的序列将按升序排列，否则按降序排列。

         MAXVALUE指定序列可以生成的最大值。

         NOMAXVALUE这是默认选项，将升序序列的最大值设为10的27次幂，将降序序列的最大值设为-1。

         MINVALUE指定序列的最小值。MINVALUE必须小于或等于START WITH的值，并且必须小于MAXVALUE。

         NOMINVALUE这是默认选项，将升序序列的最小值设为1，将降序序列的最小值设为-10的26次幂。

         CYCLE指定序列在达到最大值或最小值后，将继续从头开始生成值。

         NOCYCLE这是默认选项。指定序列在达到最大值或最小值后，将不能再继续生成值。

         CACHE使用CACHE选项可以预先分配一组序列号，并将其保留在内存中，这样可以更快的访问序列号。

         NOCACHE此项则不会为加快速度而预先分配序列号。如果在创建序列时忽略了CACHE和NOCACHE选项，Oracle将默认缓存20个序列号。

   示例3：Create SEQUENCE toys_seq

            START WITH 10

            INCREMENT BY 2

            MAXVALUE 2000

            MINVALUE 10

            NOCYCLE

            CACHE 30;

 

   访问序列：可以通过CURRVAL和NEXTVAL伪列来访问该序列的值。

   示例4：演示从序列toys_seq中选择值插入toys表中的toyid列。执行成功将会在该表的toyid列插入值"P10"和"P12"。

          INSERT INTO toys(toyid,toyname,toyprice) values('p'||toys_seq.NEXTVAL,'TWENTY',25);

          INSERT INTO toys(toyid,toyname,toyprice) values('p'||toys_seq.NEXTVAL,'MAGIC PENCIL',75);

   示例5：演示如何查看序列当前值

          Select toys_seq.CURRVAL from dual;

 

   更改序列：ALTER SEQUENCE命令用于设置或删除MINVALUE或MAXVALUE、修改增量值、修改缓存中的序列号的数目。

   修改序列语法如下：注意，不能修改序列的START WITH参数。在修改序列时，应注意升序序列的最小值应小于最大值。

   ALTER SEQUENCE [schema.]sequence_name

     [INCREMENT BY integer]

     [MAXVALUE integer|NOMAXVALUE]

     [MINVALUE integer|NOMINVALUE]

     [CYCLE|NOCYCLE]

     [CACHE interger|NOCACHE];

   示例6：演示如何设置一个新的MAXVALUE，并为toys_seq序列打开了CYCLE。

         ALTER SEQUENCE toys_seq

           MAXVALUE 5000

           CYCLE;

 

   可以查询字典视图User_Sequences来查看用户所创建的序列的详细信息

 

   删除序列语法：Drop SEQUENCE toys_seq;

 

   3、视图

   视图是存储的查询定义。

   创建视图的语法如下：

   Create [OR REPLACE] [FORCE | NOFORCE] VIEW view_name[(alias,alias,...)] AS

   select_statement [WITH CHECK OPTION [CONSTRAINT constraint]] [WITH READ ONLY];

   其中：OR REPLACE表示在该视图存在的话，将重新创建该视图。

         FORCE使用此关键字，则无论基表是否存在，都将创建视图。

         NOFORCE这是默认值。如果使用此关键字，则仅当基表存在时才创建视图。

         view_name表示要创建视图的名称。

         alias指定在视图里面列的名字，名字数目必须与视图所选择的表达式的数目相匹配。

         select_statement表示Select语句。

         WITH CHECK OPTION此选项指定只能插入或更新视图可以访问的行。术语constraint表示为CHECK OPTION约束指定的名称。

         WITH READ ONLY此选项确保不能在此视图上执行任何修改操作。

   示例7：演示创建一个名为ven_view的视图，该视图与vendor_master表具有相同的结构。

         Create VIEW ven_view AS select * from vendor_master;

 

   视图中的ORDER BY子句：以便在查询视图时即使不使用Order By子句，结果集也会按指定的顺序排列行。

   示例8：Create OR REPLACE VIEW ven_view(编号,日期) AS select orderno,odate from vendor_master order by venname;

  

   创建带有错误的视图：使用FORCE选项

   在以下情况下，Oracle也会创建视图：

   视力定义的查询引用了一个不存在的表;视图定义的查询引用了现有表中无效的列;视图的所有者没有所需的权限。

   示例9：下面创建一个基于venmast的视图。但数据库中并不存在名为"venmast"的表。

   Create FORCE VIEW ven AS select * from venmast;

   如果稍后创建名为venmast的表，可以使用ALTER VIEW ven COMPILE;

  

   联接视图：

   示例：演示如何创建联接视图

   Create OR REPLACE VIEW ven_ord_view AS select vm.vencode,venname,orderno,odate,ostatus

   from vendor_master vm,order_master om where vm.vencode=om.vencode;

 

   键保留表：在联接视图中，如果视图包含了一个表的主键，并且也是这个视图的主键，则这个键被保留，则这个表被称为键保留表。

   ven_ord_view视图以vendor_master和order_master这两个表为基表。order_master表被视为键保留表，因为orderno既是order_master表

   的主键，也是视图的主键。而vendor_master表不被视为键保留表，因为vendor_master表的主键vencode是联接字段，不是视图的主键。

   示例10：此示例成功修改了记录，因为odate列属于键保留表order_master。对于非键保留表vendor_master的venname列的更新则是不允许的。

   update wen_ord_view odate=odate+1 where vencode='V003';    注意：运行此示例时，必须确保基表的主键已正确创建。

 

   可以查询字典视图User_Updateble_Columns来查看联接视图中可更新的列。

   可以查询字典视图User_Views来查看用户所创建的视图信息。

 

   视图中的函数：视图可以使用单行函数(由数字、字符、日期组成)、分组函数和表达式。

   示例11：此示例用UPPER函数创建视图，注意使用函数或表达式时，应赋予列一个别名。

   Create VIEW vendor_master_view AS select vencode,UPPER(venname) vendor_name from vendor_master;

 

   删除视图语法：Drop VIEW view_name ;

 

   4、索引：

      查询User_indexes可以获取有关用户已创建的索引的详细信息。

      查询User_ind_partitions可以获取有关用户已创建的分区索引的详细信息。

      查询User_ind_columns可以获取有关列(用户的索引是基于这些列创建的)的详细信息。 

      唯一索引、组合索引、反向键索引、位图索引和基于函数的索引。

      索引在逻辑上物理上都独立于关联表中的数据，在任何时候都可以创建或删除索引，而不会影响基表或其它索引。

      创建普通索引的语法：

      Create INDEX index_name ON table_name(column_list) [TABLESPACE tablespace_name];

      其中：index_name指所创建索引的名称。

            table_name表示为之创建索引的表名。

            column_list是在其上创建索引的列名列表，可以基于多列创建索引。

            tablespace_name为索引指定表空间。

      示例12：演示如何在itemfile表的itemcode列上创建索引

      create index item_index on itemfile(itemcode);

    

      ALTER INDEX语句的REBUILD选项可以用来重建现有的索引。该选项提供的性能要优于使用DROP INDEX和CREATE INDEX语句重新创建索引。

      示例13：重建索引

      ALTER INDEX item_index REBUILD;

 

      删除索引语法：Drop INDEX item_index;

 

      (1)唯一索引：此索引可以确保在定义索引的列中，表的任意两行的值都不相同。Oracle自动为表的主键列创建唯一索引。

         可以使用Create UNIQUE INDEX命令明确地创建唯一索引。

      示例14：在itemfile表的itemcode列上创建了一个名为item_index的唯一索引。

      Create UNIQUE INDEX item_index ON itemfile(itemcode);

 

      (2)组合索引：组合索引是在表中的多个列上创建的索引。组合索引中列的顺序是任意的，不必是表中相邻的列。

         创建组合索引时，应注意定义中使用的列的顺序。通常，最频繁访问的列应放置在列表的最前面。

      示例15：在itemfile表上创建了一个名为comp_index的组合索引，当查询该表的的WHERE子句同时包含这两个列或只包含

              p_category列时，以下示例语句创建的索引将用于检索数据，但如果单独使用itemrate列，则索引不能用于检索数据。

      Create INDEX comp_index ON itemfile(p_category,itemrate);

 

      (3)反向键索引：通常建立在一些值连续增长的列上，例如列中的值是是由序列产生的情况。

      示例16：在itemfile表上创建了一个名为rev_index的反向键索引。注意使用REVERSE关键字。

      Create INDEX rev_index ON itemfile(itemcode) REVERSE; 

      示例17：使用关键字NOREVERSE可以将反向键索引重建为标准索引。

      ALTER INDEX rev_index REBUILD NOREVERSE;

      注意：不能将标准索引重建为反向键索引。

 

      (4)位图索引：如果某个列的值重复超过一百次，则可以考虑在该列上创建位图索引。

      示例18：itemcode是order_detail表中的低基数列，因为货物编码在大多数订单中都是重复的，因此适合在该列上创建位图索引。

      Create BITMAP INDEX bit_ind1 ON order_detail(itemcode);

      位图索引不应当用在频繁发生的INSERT,UPDATE,DELETE操作的表上。位图索引最适合于数据仓库和决策支持系统。

 

      (5)索引组织表：索引组织表与在一个或多个列上建立索引的普通表相似，但它无需为表和索引维护两个单独的存储空间，

                     数据库系统仅维护一个索引，该索引包含相应的已编码键值和与其关联的列值。

      示例19：使用ORGANIZATION INDEX子句来创建索引组织表。

      Create table ind_org_tab

      (

         vencode NUMBER(4) primary key,          注意：primary key是创建索引组织表所必需的。不允许使用分区。

         venname VARCHAR2(20)

      )

      organization index;

      索引组织表适合于通过主键来访问数据。

     

      (6)基于函数的索引：如果在WHERE子句的算术表达式或函数中已经包含了某个列，则不会使用该列上的索引。不能在表达式包含任何

         聚合函数，LOB列、REF列或包含LOB或REF的对象类型上创建基于函数的索引。

      示例20：venname是vendor_master表的一个列，用于存储供应商的姓名，假定所有供应商的姓名都以混合大小写的形式存储

              (如：John Smith、Dave Jones、Tony Greig等等)，同时假定我们经常需要根据供应商的姓名来查询表的数据。由于

              姓名是以混合大小写的形式存储的，因此可能很难给出姓名的正确大小写形式。可以创建如下索引：

      Create INDEX vn_ind ON vendor_master(UPPER(venname));

      示例21：演示如何使用前面创建的基于函数的索引检索数据。

      select * from vendor_master where UPPER(venname)='SMALL';

     

      要创建基于函数或表达式的索引，必须具有QUERY REWRITE系统权限。

 

      (7)索引中的分区：与对表进行分区类似，Oracle也允许对索引分区。牵引分区可以存储在不同的表空间中。

         局部分区索引：Oracle为表的每个分区建立一个独立的索引。

         示例22：先创建分区表

         Create table order_mast

         (

            orderno number(4),

     venname varchar2(20)

         )

         partition by range(orderno)

         (

     partition oe1 values less than(1000),

            partition oe2 values less than(2000),

     partition oe3 values less than(maxvalue)

  );

         接着创建局部索引：

         create INDEX myind ON order_mast(orderno) LOCAL;

        

         全局分区索引：是指在分区表或非分区表上创建的索引。

         示例23：在上面创建的分区表上创建全局索引

         create INDEX glb_ind ON order_mast(orderno) GLOBAL

  partition by range(orderno)

  (

    partition ip1 values less than(1500),

      partition ip2 values less than(maxvalue)

  );

         在有3个分区的表上创建2个分区的索引。注意：不能在散列分区或子分区建立全局索引。

 

         全局非分区索引：全局分区索引是在分区表上创建的全局索引，它类似于非分区表上的索引，索引的结构不会被分割。