Oracle管理组织索引,权限和角色

.1.掌握维护oracle数据完整性的技巧2.理解索引概念，会建立索引3.管理oracle的权限和角色维护数据的完整性数据的完整性用于确保数据库数据遵从一定的商业和逻辑规则。在oracle中，数据完整性感谢阅读可以使用约束、触发器、应用程序（过程、函数）三种方法来实现，在这三种方法中，因为谢谢阅读约束易于维护，并且具有最好的性能，所以作为维护数据完整性的首选。感谢阅读约束约束用于确保数据库数据满足特定的商业规则。在oracle中，约束包括：notnull、unique(唯谢谢阅读一)、primarykey、foreignkey和check(检查)五种。感谢阅读notnull(非空)如果在列上定义了notnull，那么当插入数据时，必须为列提供数据。精品文档放心下载unique(唯一)当定义了唯一约束后，该列值是不能重复的，但是可以为null。精品文档放心下载primarykey(主键)用于唯一的标示表行的数据，当定义主键约束后，该列不但不能重复而且不能为null。谢谢阅读需要说明的是：一张表最多只能有一个主键，但是可以有多个unqiue约束。谢谢阅读foreignkey(外键)用于定义主表和从表之间的关系。外键约束要定义在从表上，主表则必须具有主键约束或是感谢阅读unique约束，当定义外键约束后，要求外键列数据必须在主表的主键列存在或是为null。感谢阅读check.用于强制行数据必须满足的条件，假定在sal列上定义了check约束，并要求sal列值在精品文档放心下载1000~2000之间，如果不在1000~2000之间就会提示出错。谢谢阅读商店售货系统表设计案例---创建SQL>createtablescott.goods(--商品表精品文档放心下载goodsIdchar(8)primarykey,--主键感谢阅读goodsNamevarchar2(30),unitpricenumber(10,2)check(unitprice>0),谢谢阅读categoryvarchar2(8),providervarchar2(30));SQL>createtablecustomer(--客户表精品文档放心下载customerIdchar(8)primarykey,--主键谢谢阅读namevarchar2(50)notnull,--不为空精品文档放心下载addressvarchar2(50),emailvarchar2(50)unique,谢谢阅读sexchar(2)default'男'check(sexin('男','女')),谢谢阅读cardIdchar(18));.SQL>createtablepurchase(--购买表感谢阅读customerIdchar(8)referencescustomer(customerId),感谢阅读goodsIdchar(8)referencesgoods(goodsId),感谢阅读numsnumber(5)check(numsbetween1and30)感谢阅读);商店售货系统表设计案例(2)---维护增加商品名不能为空SQL>altertablegoodsmodifygoodsNamenotnull;感谢阅读增加身份证不能重复SQL>altertablecustomeraddconstraintcarduniqueunique(cardId);精品文档放心下载增加客户的住址限制SQL>altertablecustomeraddconstraintaddresscheckcheck(addressin('东城','西谢谢阅读城'));删除约束当不在需要某个约束时，可以删除。SQL>altertable表名dropconstraint约束名称；谢谢阅读特别说明：在删除主键约束的时候，可能有错误，比如：SQL>altertable表名dropprimarykey;感谢阅读这是因为如果两张表存在主从关系，那么在删除主表的主键约束时，必须带上cascade选感谢阅读项，如下：.SQL>altertable表名dropprimarykeycascade;精品文档放心下载显示约束信息1.显示约束信息通过查询数据字典视图user_constraints，可以显示当前用户所有的约束的信息。精品文档放心下载SQL>selectconstraint_name,constraint_type,status,validatedfromuser_constraints谢谢阅读wheretable_name='表名'；2.显示约束列通过查询数据字典视图user_cons_cloumns，可以显示约束所对应的表列信息。感谢阅读SQL>selectcolumn_name,positionfromuser_cons_cloumnswhere精品文档放心下载constraint_name='约束名';3.当然也有更容易的方法，直接用pl/sqldeveloper查看即可。谢谢阅读表级定义和列级定义列级定义列级定义是在定义列的同时定义约束如在department表定义主键约束SQL>createtabledepartment4(谢谢阅读dept_idnumber(2)constraintpk_departmentprimarykey,谢谢阅读namevarchar2(12),locvarchar2(12));表级定义.表级定义是指在定义了所有列后，再定义约束。这里需要注意的是notnull约束只能在列精品文档放心下载级上定义。以在建立employee2表时定义主键约束和外键约束为例：精品文档放心下载SQL>createtableemployee2(感谢阅读emp_idnumber(4),namevarchar2(15),dept_idnumber(2),感谢阅读constraintpk_employeeprimarykey(emp_id),感谢阅读constraintfk_departmentforeign_key(dept_id)谢谢阅读referencesdepartment4(dept_id));谢谢阅读管理索引 --- 原理介绍索引是用于加速数据存取的数据对象。合理的使用索引可以大大降低i/o次数，从而提高数精品文档放心下载据访问性能。索引有很多种，我们主要介绍常用的几种：为什么添加了索引后，会加快查询速度呢？管理索引---创建索引单列索引单列索引是基于单个列所建立的索引，比如：createindex索引名on表名(列名)感谢阅读复合索引复合索引是基于两列或是多列的索引。在同一张表上可以有多个索引，但是要求列的组合必谢谢阅读须不同，比如：createindexemp_idx1onemp(ename,job);精品文档放心下载.createindexemp_idx2onemp(job,name);精品文档放心下载使用原则(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_typefromuser_indexswheretable_name='表名';谢谢阅读显示索引列通过查询数据字典视图user_ind_columns，可以显示索引对应的列的信息精品文档放心下载selecttable_name,column_namefromuser_ind_columnswhere谢谢阅读index_name='IND_ENAME';你也可以通过pl/sqldeveloper工具查看索引信息。感谢阅读掌管权限和角色这一部分我们主要看oracle中如何管理权限和角色，权限和角色的区别在哪里。精品文档放心下载当刚刚建立用户时，用户没有任何权限，也不能执行任何操作。如果要执行某种特定的数据感谢阅读库操作，则必须为其授予系统的权限；如果用户要访问其他方案的对象，则必须为其授予对谢谢阅读象的权限。为了简化权限的管理，可以使用角色。.权限权限是指执行特定类型的sql命令或是访问其他方案对象的权利，包括系统权限和对象权限感谢阅读两种：系统权限介绍：系统权限是指执行特定类型sql命令的权利。它用于控制用户可以执行的一个或是一组数据感谢阅读库操作。比如当用户具有createtable权限时，可以在其方案中建表，当用户具有createany精品文档放心下载table权限时，可以在任何方案中建表。oracle提供了100多种系统权限。精品文档放心下载常用的有;createsession 连接数据库createview 建视图createprocedure 建过程、函数、包

createtable建表createpublicsynonymcreatetrigger建触发器谢谢阅读

建同义词createcluster 建簇显示系统权限oracle提供了100多种系统权限，而且oracle的版本越高，提供的系统权限就越多，我们谢谢阅读可以查询数据字典视图system_privilege_map，可以显示所有系统权限。感谢阅读select*fromsystem_privilege_maporderbyname;精品文档放心下载授权系统权限一般情况，授予系统权限是由dba完成的，如果用其它用户来授予系统权限，则要求该用精品文档放心下载户必须具有grantanyprivilege的系统权限在授予系统权限时，可以带有withadmin感谢阅读option选项，这样，被授予权限的用户或是角色还可以将该系统权限授予其它的用户或是精品文档放心下载角色。为了让大家快速理解，我们举例说明：1.创建两个用户ken,tom。初始阶段他们没有任何权限，如果登录就会给出错误的信息。精品文档放心下载.createuserkenidentifiedbym123;谢谢阅读2.给用户ken授权①grantcreatesession,createtabletokenwithadminoption;谢谢阅读②grantcreateviewtoken;精品文档放心下载3.给用户tom授权我们可以通过ken给tom授权，因为withadminoption是加上的。当然也可以通过dba精品文档放心下载为tom授权，我们就用ken给tom授权：精品文档放心下载①grantcreatesession,createtabletotom;谢谢阅读②grantcreateviewtotom;→ok吗？【不ok】感谢阅读回收系统权限一般情况下，回收系统权限时dba来完成的，如果其他的用户来回收系统权限，要求该用谢谢阅读户必须具有相应系统权限及转授系统权限的选项(withadminoption)。回收系统权限使用感谢阅读revoke来完成。当回收了系统权限后，用户就不能执行相应的操作了，但是请注意，系统权限级联回收问题？精品文档放心下载【不是级联回收】system→ken→tom用system执行如下操作：revokecreatesessionfromken;请思考tom还能登录吗？---可以登录谢谢阅读对象权限介绍指访问其他方案对象的权利，用户可以直接访问自己的方案的对象，但是如果要访问别的方谢谢阅读案的对象，则必须具有其对象的权限。比如smith用户要访问scott.emp表（scott：方案，emp：表）感谢阅读.则必须在scott.emp表上具有对象的权限。常用的有：alter修改 delete删除 select查询精品文档放心下载update修改 index索引 references引用谢谢阅读显示对象权限

insert添加rxecute执行通过数据字典视图可以显示用户或是角色所具有的对象权限。视图为dba_tab_privs精品文档放心下载SQL>connsystem/manager;SQL>selectdistinctprivilegefromdba_tab_privs;谢谢阅读SQL>selectgrantor,owner,table_name,privilegefromdba_tab_privswhere精品文档放心下载grantee='BLAKE';授予对象权限在oracle9i前，授予对象权限是由对象的所有者来完成的，如果用其它的用户来操作，则谢谢阅读需要用户具有相应的(withgrantoption)权限，从oracle9i开始，dba用户(sys,system)感谢阅读可以将任何对象上的权限授予其它用户。授予对象权限使用grant命令来完成的。精品文档放心下载对象权限可以授予用户，角色，和public。在授予权限时，如果带有withgrantoption精品文档放心下载选项，则可以将该权限转授给其它用户。但是要注意withadminoption选项不能被授予感谢阅读角色。1.monkey用户要操作scott.emp表，则必须授予相应的对象权限感谢阅读①希望monkey可以查询scott.emp的表数据，怎样操作？感谢阅读SQL>grantselectonemptomonkey;感谢阅读②希望monkey可以修改scott.emp的表数据，怎样操作？谢谢阅读SQL>grantupdateonemptomonkey;谢谢阅读.③希望monkey可以删除scott.emp的表数据，怎样操作？谢谢阅读SQL>grantdeleteonemptomonkey;感谢阅读④有没有更加简单的方法，一次把所有权限赋给monkey？精品文档放心下载SQL>grantallonemptomonkey;谢谢阅读2.能否对monkey访问权限更加精细控制（授予列权限）谢谢阅读①希望monkey只可以修改scott.emp的表的sal字段，怎样操作？感谢阅读SQL>grantupdateonemp(sal)tomonkey;感谢阅读②希望monkey只可查询scott.emp的表的ename，sal数据，怎样操作？谢谢阅读SQL>grantselectonemp(ename,sal)tomonkey;感谢阅读3.授予alter权限如果black用户要修改scott.emp表的结构，则必须授予alter对象权限感谢阅读SQL>connscott/root123;SQL>grantalteronemptoblack;精品文档放心下载当然也可以用system，sys来完成这件事4.授予execute权限如果用户想要执行其他方案的包过程/函数，则需要有execute权限。谢谢阅读比如为了让ken可以执行包dbms_transaction，可以授予execute权限。感谢阅读SQL>connsystem/manager;SQL>grantexecuteondbms_transactiontoken;感谢阅读5.授予index权限如果想在别的方案的表上建立索引，则必须具有index对象权限，如为了让black可以在感谢阅读scott.emp上建立索引，就给其index的对象权限。精品文档放心下载.SQL>grantindexonscott.emptoblack;精品文档放心下载6.使用withgrantoption选项该选项用于转授对象权限，但是该选项只能被授予用户，而不能授予角色精品文档放心下载SQL>connscott/root123;SQL>grantselectonemptoblackwithgrantoption;谢谢阅读SQL>connblack/m123;SQL>grantselectonscott.emptojones;感谢阅读回收对象权限在oracle9i中，收回对象的权限可以由对象的所有者来完成，也可以用dba用户(sys,system)谢谢阅读来完成这里要说明的是：收回对象权限后，用户就不能执行相应的sql命令，但是要注意的是对象谢谢阅读的权限是否会被级联回收？【会级联回收】如：scott→black→jones(selectonemp)精品文档放心下载SQL>connscott/root123;SQL>revokeselectonempfromblack;精品文档放心下载这时，jones用户不能再查询scott.emp表。精品文档放心下载角色角色就是相关权限的命令集合，使用角色的主要目的就是为了简化权限的管理。假定有用户谢谢阅读a，b，c，为了让他们都拥有权限①连接数据库②在scott.emp表上select，insert，update。精品文档放心下载如果采用直接授权操作，则需要进行12次授权。我们如果采用角色就可以简化：.首先将createsession,selectonscott.emp,insertonscott.emp,updateonscott.emp感谢阅读授予角色，然后将该角色授予a,b,c用户，这样就可以三次授予搞定。感谢阅读角色分为预定义和自定义角色两类：预定义角色预定义角色时oracle所提供的角色，每种角色都用于执行一些特定的管理任务，下面我们感谢阅读介绍常用的预定义角色connect，resource，dba谢谢阅读（一）connect角色connect角色有一般应用开发人员需要的大部分权限，当建立了一个用户后，多数情况下，谢谢阅读只要给用户授予connect和resource角色就够了，那么connect角色具有哪些系统权限感谢阅读呢？altersession修改回话createcluster创建簇createdatabaselink修改数据库连接谢谢阅读createsession连接数据库createtable创建表createview 创建视图createsequence创建序列（二）resource角色resource角色具有应用开发人员所需要的其他权限，比如建立存储过程、触发器等。这里谢谢阅读需要注意的是resource角色隐含了unlimitedtablespace（无限制表空间）系统权限。谢谢阅读resource角色包含了以下系统权限：createcluster创建簇.createindextype创建索引createtable创建表createsequence创建序列createtypecreateprocedure创建过程createtrigger创建触发器（三）dba角色dba角色具有所有的系统权限，及withadminoption选项，默认的dba用户为sys和感谢阅读system，他们可以将任何系统权限授予其它用户。但是要注意的是dba角色不具备sysdba谢谢阅读和sysoper的特权（启动和关闭数据库）。自定义角色顾名思义就是自己定义的角色，根据自己的需要来定义。一般是由dba建立，如果用别的感谢阅读用户来建立，则需要具有createrole的系统权限。在建立角色时可以指定验证方式（不验感谢阅读证，数据库验证等）。（一）建立角色（不验证）如果角色是公用的角色，可以采用不验证的方式建立角色createrole角色名notidentified;感谢阅读（二）建立角色（数据库验证）采用这样的方式时，角色名、口令存放在数据库中。当激活该角色时，必须提供口令，在建感谢阅读立这种角色时，需要为其提供口令。createrole角色名identifiedbym123;精品文档放心下载角色授权.当建立角色时，角色没有任何权限，为了使得角色完成特定任务，必须为其授予相应的系统感谢阅读权限和对象权限。（一）给角色授权给角色授予权限和给用户授予权限没有太大区别，但是要注意，系统权限的unlimited谢谢阅读tablespace和对象权限的withgrantoption选项是不能授予角色的。感谢阅读SQL>connsystem/manager;SQL>grantcreatesessionto角色名withadminoption;精品文档放心下载SQL>connscott/root123;SQL>grantselectonempto角色名;感谢阅读SQL>grantinsert,updateonempto角色名;谢谢阅读SQL>grantdeleteonempto角色名;精品文档放心下载（二）分配角色给某个用户一般分配角色是由dba来完成的。如果要以其他用户身份分配角色，则要求用户必须具有精品文档放心下载grantanyrole的系统权限。SQL>connsystem/manager;SQL>grant角色名to用户名withadminoption;谢谢阅读因为我给了withadminoption选项，所以，用户可以把system分配给它的角色分配给感谢阅读别的用户。删除角色使用droprole，一般是dba来执行，如果用其它用户则要求改用户具有dropanyrole感谢阅读系统权限。SQL>connsystem/manager;.SQL>droprole角色名;显示角色信息①显示所有角色SQL>select*fromdba_roles;谢谢阅读②显示角色具有的系统权限SQL>selectprivilege,admin_optionfromrole_sys_privswhererole='角色名';感谢阅读③显示角色具有的对象权限通过查询数据字典视图dba_tab_privs可以查看角色拥有的对象权限或是列的权限。精品文档放心下载④显示用户具有的角色及默认角色当以用户的身份连接到数据库时，oracle会自动的激活默认的角色，通过查询数据字典视精品文档放心下载图dba_role_privs可以显示某个用户具有的所有角色以及当前默认的角色谢谢阅读SQL>selectgranted_role,default_rolefromdba_role_privswheregrantee='用户名';谢谢阅读精细访问控制是指用户可以使用函数、策略实现更加细微的安全访问控制。如果使用精细访问控制，则当谢谢阅读在客户端发出sql语句(select、insert、update、delete)等，oracle会自动在sql语句后精品文档放心下载追加谓词(where子句)，并执行新的sql语句。通过这样的控制，可以使得不同的数据库用精品文档放心下载户在访问相同表时，返回不同的数据信息。另附用户管理使用:一、用户管理在oracle中存在三类用户：sysdba、sysoper、普通用户。前两类用户为特权用户，感谢阅读拥有数据库的所有权限，特权用户的相关信息可以从动态性能视图v$pwfile_users中获得。感谢阅读.SQL>select*fromv$pwfile_users;精品文档放心下载3. USERNAME SYSDBA SYSOPER SYSASM感谢阅读-----------------------------------------------------------谢谢阅读5. SYS TRUE TRUE FALSE创建用户只能由sys用户完成，或者拥有CREATEUSER权限的用户。谢谢阅读创建用户的语法格式如下：CREATEUSERuser_nameIDENTIFIEDBYpassword感谢阅读DEFAULTTABLESPACEtbs_name感谢阅读TEMPORARYTABLESPACEtemp_tbs_name谢谢阅读QUOTAtbs_sizeONtbs_name谢谢阅读PASSWORDEXPIREACCOUNTLOCK|UNLOCKQUOTA子句为用户在表空间上指定空间配额，可以KB、MB等为单位，或谢谢阅读UNLIMITED指定为无限制的空间配额，或授予用户UNLIMITEDTABLESPACES系统权限。感谢阅读PASSWORDEXPIRE子句指定用户在第一次登陆时必须修改密码。精品文档放心下载ACCOUNT指定用户账号的状态。用户创建之后可通过ALTERUSER命令修改用户的相关信息。感谢阅读删除用户：SQL>DROPUSERuser_name;但是当用户下存在对象时，需要使用CASCADE选项进行删除：精品文档放心下载SQL>DROPUSERuser_nameCASCADE;精品文档放心下载此时创建了用户并不能进行登录，需要对用户赋予相应权限之后才能进行登录。谢谢阅读SQL>conntest1/test@lsjcom谢谢阅读ERROR:ORA-01045:用户TEST1没有CREATESESSION权限;登录被拒绝感谢阅读.二、用户权限的管理Oracle中用户权限有两类，一类是系统权限，包括对数据库对象的增、删、改，对感谢阅读数据库的创建和备份等；另一类是数据库权限，主要是针对数据库对象的访问权限。感谢阅读表2-1常用的系统权限系统权限说明Createcluster在自己的模式中创建簇的权限Createprocedure在自己的模式中创建存储函数、存储过程、程序包的权限Createsession登录数据库的权限Createtable在自己的模式中创建表的权限Createtablespace在自己的模式中创建表空间的权限Unlimitedtablespace在任意表空间都有无限空间配额的权限Createtrigger在自己的模式中创建触发器的权限Createuser创建用户的权限Createview在自己的模式中创建视图的权限表2-2常用的对象权限对象权限说明ALTER修改表和序列的权限SELECT对数据库对象中的数据的查询权限INSERT对数据库对象中的数据插入的权限.DELETE对数据库对象中的数据删除的权限UPDATE对数据库对象中的数据更新的权限REFERENCES可以与一个表建立关联关系的权限，在授予权限时可以指定列，也可以不指定EXECUTE执行存储函数、存储过程和程序包的权限RENAME重命名数据库对象的权限为用户授予系统权限的语法格式如下：SQL>GRANT权限，…TOuser，…[WITHADMINOPTION];谢谢阅读为用户授予对象权限的语法格式如下：SQL>GRANT权限(列名),…ON对象TOuser,…[WITHADMINOPTION]感谢阅读其中WITHADMINOPTION子句使得权限的获得者可以将权限再分配给其他用户。感谢阅读回收用户系统权限的语法格式如下：SQL>REVOKE权限，…FROMuser,…感谢阅读回收用户对象权限的语法格式如下：SQL>REVOKE权限,…ON对象FROMuser,…;精品文档放心下载当回收所有权限时，可以使用REVOKEALL三、与权限相关的数据字典权限类型数据字典视图 说明系统权限system_privilege_map当前数据库中已经定义的所有系统权限感谢阅读.dba_sys_prvis 任何用户所具有的系统权限(sys用户)谢谢阅读user_sys_privs 用户自己所拥有的系统权限谢谢阅读session_privs 一个用户在当前会话中所具有的权限感谢阅读dba_tab_privs 任何用户所具有的对象权限谢谢阅读dba_col_privs 任何用户在列上所具有的权限感谢阅读user_tab_privs 用户自己所拥有的对象权限感谢阅读对象权限user_col_privs 用户自己所拥有的列的权限精品文档放心下载四、角色管理角色是一组权限的集合，使权限管理更加方便。数据库中有两类角色，一类是oracle预定感谢阅读义角色，一类是用户自定义角色。角色名称 所包含权限CONNECTCREATESESSIONCREATECLUSTER,CREATEINDEXTYPE,CREATEOPERATOR,CREATEPROCEDURE,CREATESEQUENCE,CREATETABLE,RESOURCECREATETRIGGER,.CREATETYPE.DBA 可通过DBA_SYS_PRIVS数据字典查看感谢阅读1、创建角色SQL>select*fromv$pwfile_users;感谢阅读3. USERNAME SYSDBA SYSOPER SYSASM谢谢阅读-----------------------------------------------------------谢谢阅读5. SYS TRUE TRUE FALSESQL>CREATEROLErole_name;精品文档放心下载2、删除角色：SQL>DROPROLErole_name;3、向角色授予权限授予系统权限：SQL>GRANT权限，…TOrole_name[WITHADMINOPTION];感谢阅读授予对象权限：SQL>GRANT权限，…ON对象TOrole_name；精品