数据库系统课件03-第3章 表空间用户权限和角色

目录01表空间的创建与管理02用户的创建与管理03系统权限和对象权限04角色管理本章重点了解Oracle表空间及用户的概念和作用掌握对表空间的创建及管理掌握对用户的创建及管理掌握对系统权限的作用与使用掌握对对象权限的作用与使用掌握对角色的作用与使用本章任务完成“Q_MicroChat”微聊项目表空间创建、用户管理和权限设置及角色管理。具体任务分解如下：【任务3-1】项目表空间创建【任务3-2】项目用户管理【任务3-3】项目权限设置及角色管理任务驱动本章目标知识点听懂做复习精通表空间的作用★★表空间的创建及管理★★★★★用户的作用★★用户的创建及管理★★★★★系统权限的作用与使用★★★★★对象权限的作用与使用★★★★★角色的作用与使用★★★★★JavaJSP表空间的创建与管理1表空间是Oracle逻辑存储结构中数据的逻辑组织形式，Oracle使用表空间将各种数据库对象组合在一起；当用户对数据库对象进行操作时，面向的是逻辑对象，而非直接操作物理文件，极大的方便了数据库操作。3.1表空间【示例】查询系统中所有表空间的信息3.1表空间SQL>SELECTtablespace_name,status,extent_management,2allocation_type,segment_space_management,contents3FROMdba_tablespaces;TABLESPACE_NAMESTATUSEXTENT_MANAGEMENTALLOCATION_TYPESEGMENT_SPACCONTENTS---------------------------------------------------------------------------------------------------------------------------------SYSTEMONLINELOCALSYSTEMMANUALPERMANENTSYSAUXONLINELOCALSYSTEMAUTOPERMANENTUNDOTBS1 ONLINELOCALSYSTEMMANUALUNDOTEMP ONLINELOCALUNIFORMMANUALTEMPORARYUSERS ONLINELOCALSYSTEMAUTOPERMANENT在数据库应用中对于表空间的使用，Oracle数据库建议每个应用分别对应一个独立的表空间，所有用户对象和数据保存在非系统表空间中，这样不仅能够分离不同应用的数据，而且能够减少读取数据文件时产生的I/O冲突。根据表空间的功能特性，一个表空间的创建，应该需要确定表空间的名称、表空间的管理方式、表空间的类型、表空间的数据文件、区的分配方式、段的管理方式、表空间数据块大小等参数。3.1.1创建表空间【语法】创建表空间3.1.1创建表空间CREATE[TEMPORARY|UNDO]TABLESPACEtablespace_nameDATAFILEdatafile_spacification[BLOCKSIZEnumberK][ONLINE|OFFLINE][LOGGIN|NOLOGGING][FORCELOGGING][COMPRESS|NOCOMPRESS][EXTENTMANAGEMENTDICTIONARY|LOCAL[AUTOALLOCATE|UNIFORMSIZEnumberK|M]][SEGMENTSPACEMANAGEMENTAUTO|MANUAL];其中：TEMPORARY|UNDO表示创建的表空间类型，分为永久表空间（PERMANENT）、临时表空间（TEMPORARY）和撤销表空间（UNDO），默认为永久表空间；DATAFILE用于指定表空间所对应的数据文件的信息；BLOCKSIZE表示表空间所基于的数据块大小，默认为标准块表空间；ONLINE|OFFLINE表示新建表空间处于脱机状态还是联机状态；LOGGING|NOLOGGING表示指定所有保存在该表空间中的默认日志选项，其中LOGGING表示数据库对象的创建以及数据的DML操作信息都写入重做日志文件，NOLOGGING表示数据的加载操作不写入重做日志文件；3.1.1创建表空间FORCELOGGING表示表空间中所有对象发生的变化信息都将被写入重做日志文件，忽略NOLOGGING子句的作用TEMPORARY|UNDO表示创建的表空间类型，分为永久表空间（PERMANENT）、临时表空间（TEMPORARY）和撤销表空间（UNDO），默认为永久表空间；COMPRESS|NOCOMPRESS表示是否将数据块中的数据进行压缩，COMPRESS表示压缩，压缩的结果是消去列中的重复值，当检索数据时，Oracle会自动对数据解压缩；NOCOMPRESS表示不执行压缩；EXTENTMANAGEMENT用于指定表空间的管理方式，包括字典管理方式（DICTIONARY）和本地管理方式（LOCAL），默认为LOCAL；本地管理表空间中区的分配方式默认为自动分配，用参数AUTOALLOCATE表示；参数UNIFORM表示定制分配；SEGMENTSPACEMANAGEMENT表示本地管理表空间中段的管理方式，默认为自动管理方式AUTO。3.1.1创建表空间【语法】数据文件的详细配置3.1.1创建表空间CREATETABLESPACEtablespace_nameDATAFILEpath/file_nameSIZEnumberK|M[REUSE][AUTOEXTENDOFF|ON [NEXTnumberK|MMAXSIZEUNLIMITED|numberK|M]];其中：tablespace_name为创建的表空间名称；path/file_name为所创建的表空间实际存储在磁盘中的数据文件地址；SIZE指定数据文件初始大小；REUSE表示如果该数据文件已经存在，则清除该文件并重新创建；如果未使用该关键字，则当数据文件已经存在时将出错；AUTOEXTEND指定数据文件是否可以自动扩展。如果可以自动扩展，需要设置NEXT值指明每次扩展的大小，设置MAXSIZE值指明文件的最终大小。默认值为OFF，不可扩展。3.1.1创建表空间【示例】创建名为“qstspace”的本地管理的永久表空间【示例】创建名为“qstspace2”的本地管理的永久表空间3.1.1创建表空间SQL>CREATETABLESPACEqstspace2DATAFILE'e:\oracle12c\userspace\tbs.dbf'SIZE30M;表空间已创建。SQL>CREATETABLESPACEqstspace22DATAFILE'e:\oracle12c\userspace\tbs2.dbf'SIZE30M3EXTENTMANAGEMENTLOCALUNIFORMSIZE512K;表空间已创建。【示例】指定数据文件的可扩展性【示例】为一个表空间创建多个数据文件3.1.1创建表空间SQL>CREATETABLESPACEqstspace32DATAFILE'e:\oracle12c\userspace\tbs3.dbf'SIZE30M3AUTOEXTENDONNEXT5MMAXSIZE500M;表空间已创建。SQL>CREATETABLESPACEqstspace42DATAFILE'e:\oracle12c\userspace\data_1.dbf'size20M,3'e:\oracle12c\userspace\data_2.dbf'size5M;表空间已创建。改变表空间大小ALTERTABLESPACE...ADDDATAFILE命令可以为永久表空间添加数据文件【示例】为“qstspace”表空间添加一个大小为10M的新的数据文件3.1.2表空间管理SQL>ALTERTABLESPACEqstspace2ADDDATAFILE'e:\oracle12c\userspace\tbs_1.dbf'SIZE10M;表空间已更改。【示例】将“qstspace”表空间的数据文件“tbs_1.dbf”的大小增加到20M。【示例】将“qstspace”表空间的数据文件“tbs_1.dbf”设置为自动扩展。3.1.2表空间管理SQL>ALTERDATABASEDATAFILE'e:\oracle12c\userspace\tbs_1.dbf'RESIZE20M;数据库已更改。SQL>ALTERDATABASEDATAFILE'e:\oracle12c\userspace\tbs_1.dbf'2AUTOEXTENDONNEXT5MMAXSIZE100M;数据库已更改。设置默认表空间通过查询数据字典DATABASE_PROPERTIES可以获取当前数据库的默认永久表空间【示例】查询数据库默认表空间3.1.2表空间管理SQL>SELECTproperty_name,property_valueFROMdatabase_properties2WHEREproperty_name='DEFAULT_PERMANENT_TABLESPACE';PROPERTY_NAME PROPERTY_VALUE-------------------------------------------------------------DEFAULT_PERMANENT_TABLESPACEUSERS重命名表空间使用ALTERTABLESPACE...RENAMETO命令对表空间的名称进行修改【示例】修改数据库表空间“qstspace”的名称为“new_qstspace”3.1.2表空间管理SQL>ALTERTABLESPACEqstspaceRENAMETOnew_qstspace;表空间已更改。删除表空间仅删除其在数据库中的记录将记录和数据文件一起删除【示例】仅删除表空间在数据库中的记录【示例】删除表空间及其数据文件3.1.2表空间管理SQL>DROPTABLESPACEqstspace4;表空间已删除。SQL>DROPTABLESPACEqstspace3INCLUDINGCONTENTSANDDATAFILES;表空间已删除。JavaJSP用户的创建与管理2系统用户管理员用户示例方案用户内置用户普通用户（由系统用户创建）3.2用户【示例】查看当前数据库所有用户信息3.2用户SQL>SELECTusername,account_status,default_tablespaceFROMdba_users;USERNAME ACCOUNT_STATUSDEFAULT_TABLESPACE--------------------------------------------------------------------------------------SYSTEM OPEN SYSTEMSYS OPEN SYSTEMDBSNMP EXPIRED&LOCKED SYSAUXSCOTT EXPIRED&LOCKED USERSORACLE_OCM EXPIRED&LOCKED USERSOJVMSYS EXPIRED&LOCKED USERSSYSKM EXPIRED&LOCKED USERS...USERNAME列标识用户的登录名ACCOUNT_STATUS列标识账号的当前状态DEFAULT_TABLESPACE列标识用户的默认表空间3.2用户使用CREATEUSER命令创建新用户，执行该语句的用户必须具有CREATEUSER权限。【语法】3.2.1创建用户CREATEUSERuser_nameIDENTIFIEDBYpassword[DEFAULTTABLESPACEdefault_tablespace][TEMPORARYTABLESPACEtemp_tablespace][PASSWORDEXPIRE][ACCOUNTLOCK|UNLOCK];其中：user_name指定要创建的数据库用户名IDENTIFIEDBYpassword指定用户采用数据库身份认证DEFAULTTABLESPACE指定用户的默认表空间TEMPORARYTABLESPACE指定用户的临时表空间PASSWORDEXPIRE指定用户口令的初始状态为过期，用户在首次登录数据库时必须修改口令ACCOUNTLOCK|UNLOCK设置用户的初始状态为锁定或不锁定3.2.1创建用户【示例】创建一个名为“test”的普通用户3.2.1创建用户SQL>CREATEUSERtestIDENTIFIEDBYTESTtest123;用户已创建。SQL>--查询用户的属性信息SQL>SELECTusername,account_status,default_tablespace,temporary_tablespace2FROMdba_usersWHERElower(username)='test';USERNAMEACCOUNT_STATUSDEFAULT_TABLESPACETEMPORARY_TABLESPACE-------------------------------------------------------------------TEST OPEN USERS TEMP【示例】创建用户的同时为其指定默认表空间3.2.1创建用户SQL>CREATEUSERtest02IDENTIFIEDBYTESTtest02123DEFAULTTABLESPACEqstspace;用户已创建。SQL>--查询用户的属性信息SQL>SELECTusername,account_status,default_tablespace,temporary_tablespaceFROMdba_usersWHERElower(username)='test02';USERNAMEACCOUNT_STATUSDEFAULT_TABLESPACETEMPORARY_TABLESPACE---------------------------------------------------------------TEST02 OPEN QSTSPACE TEMP【语法】修改用户密码【示例】修改用户口令3.2.2用户管理ALTERUSER...IDENTIFIEDBYSQL>ALTERUSERtest02IDENTIFIEDBYTESTtest2015;用户已更改。【语法】修改用户默认表空间【示例】修改用户默认表空间3.2.2用户管理ALTERUSER...DEFAULTTABLESPACESQL>ALTERUSERtest02DEFAULTTABLESPACEusers;用户已更改。【语法】用户的锁定与解锁【示例】用户账户的锁定与解锁3.2.2用户管理SQL>ALTERUSERtestACCOUNTLOCK;用户已更改。SQL>ALTERUSERtestACCOUNTUNLOCK;用户已更改。ALTERUSER...ACCOUNTLOCK|UNLOCK【语法】删除用户【示例】删除用户3.2.2用户管理SQL>DROPUSERtest02CASCADE;用户已删除。DROPUSERusername[CASCADE];模式（Schema）是指用户所拥有的所有对象的集合。模式与用户相对应，当在Oracle数据库中创建一个用户时，系统会自动在数据库中创建一个与用户同名的模式。模式作为数据库对象的容器而存在，用于数据库对象的管理，这些数据库对象包括：表、索引、视图、序列、同义词、PL/SQL包、存储函数、存储过程等；表空间、用户、角色等数据库对象不属于任何模式，称为非模式对象。3.2.3用户与模式JavaJSP系统权限和对象权限3Oracle数据库使用权限来控制用户对数据库的访问和用户在数据库中所能执行的操作。所谓权限就是执行特定类型SQL命令或访问其他数据对象的权利。用户在数据库中可以执行什么样的操作，以及可以对哪些对象进行操作，完全取决于该用户所拥有的权限。Oracle权限分为两大类系统权限对象权限3.3权限系统权限：是指在数据库级别执行某种操作的权限，或针对某一类对象执行某种操作的权限，如CREATESESSION权限、CREATETABLE权限。【示例】获取所有系统权限名称3.3.1系统权限3.3.1系统权限SQL>SELECTDISTINCTprivilegeFROMdba_sys_privs;PRIVILEGE-------------------------------CREATEJOBINHERITANYPRIVILEGESDROPANYPROCEDUREDROPANYPROCEDUREDROPANYMATERIALIZEDVIEWALTERANYRULESETALTERANYTABLECREATEANYCUBEDIMENSIONDROPANYDIRECTORYALTERANYOPERATORDROPANYDIMENSIONEMEXPRESSCONNECT系统权限通常分为两大类一类是对数据库某一类对象的操作能力，与具体的数据库对象无关，通常带有ANY关键字另一类系统权限是数据库级别的某种操作能力3.3.1系统权限常用系统权限及其功能3.3.1系统权限系统权限功能系统权限功能CREATETABLE在当前用户模式中创建、修改、删除表CREATEUSER创建用户CREATEANYTABLE在任何模式中创建表ALTERUSER修改用户ALTERANYTABLE修改任何模式中的表或视图DROPUSER删除用户DROPANYTABLE删除任何模式中的表CREATESESSION连接登录数据库CREATEROLE创建角色CREATEANYINDEX在任何模式中创建索引ALTERANYROLE修改任何角色ALTERANYINDEX修改任何模式中的索引DROPANYROLE删除任何角色DROPANYINDEX删除任何模式中的索引【语法】系统权限的授予其中：system_privilege_list表示系统权限列表，多个系统权限以逗号分隔ALLPRIVILEGES表示所有系统权限user_list表示用户列表，多个用户以逗号分隔WITHADMINOPTION表示允许系统权限接收者再把此权限授予其他用户3.3.1系统权限GRANTsystem_privilege_list|[ALLPRIVILEGES]TOuser_list[WITHADMINOPTION];【示例】为用户授予连接登录数据库的系统权限【示例】为用户授予创建表、创建序列的系统权限3.3.1系统权限SQL>CONNSYSTEM/QSTqst2015;已连接。SQL>GRANTCREATESESSIONTOtest;授权成功。SQL>CONNSYSTEM/QSTqst2015;已连接。SQL>GRANTCREATETABLE,CREATESEQUENCETOtest;授权成功。【示例】为用户授予一定的系统权限，且具有传递性3.3.1系统权限SQL>CONNSYSTEM/QSTqst2015;已连接。SQL>GRANTCREATETABLE,CREATESEQUENCETOtestWITHADMINOPTION;授权成功。SQL>CONNtest/QSTqst2015;已连接。SQL>GRANTCREATETABLETOtest02;授权成功。【语法】系统权限的回收【示例】3.3.1系统权限REVOKEsys_privilege_list|[ALLPRIVILEGES]FROMuser_list;SQL>CONNSYSTEM/QSTqst2015;已连接。SQL>REVOKECREATETABLE,CREATESEQUENCEFROMtest;撤销成功。对象权限是指对某个特定模式对象的操作权限在Oracle数据库中，不同类型的模式对象具有不同的对象权限，还有一些对象没有对象权限，只能通过系统权限进行控制，如索引、触发器、数据库链接等对象3.3.2对象权限常用对象权限及其功能3.3.2对象权限对象权限功能SELECT用于查询表、视图和序列UPDATE更新表、视图中的数据INSERT向表、视图中插入新的记录DELETE删除表、视图中的数据ALTER修改表、序列的属性INDEX在表上创建索引REFERENCE为表创建外键EXECUTE函数、存储过程、程序包等的调用或执行【语法】对象权限的授予其中：object_privilege_list表示对象权限列表，多个对象权限以逗号分隔ALL[PRIVILEGES]表示某对象上的所有对象权限[schema.]object表示模式对象，默认为当前模式中的对象user_list表示用户列表，多个用户以逗号分隔WITHGRANTOPTION表示允许对象权限接收者把此对象权限授予其他用户3.3.2对象权限GRANTobject_privilege_list|ALL[PRIVILEGES]ON[schema.]objectTOuser_list[WITHGRANTOPTION];【示例】将SCOTT模式的emp表的部分对象权限授予用户test3.3.2对象权限SQL>CONNSYSTEM/QSTqst2015;已连接。SQL>GRANTSELECT,INSERT,UPDATEONscott.empTOtest;授权成功。SQL>CONNtest/QSTqst2015;已连接。SQL>SELECT*FROMscott.empWHEREempno=7782;EMPNOENAMEJOBMGRHIREDATESALCOMMDEPTNO-------------------------------------------------------------------------------------7782 CLARKMANAGER783909-6月-81245010【示例】对象权限的传递3.3.2对象权限SQL>CONNSYSTEM/QSTqst2015;已连接。SQL>GRANTSELECT,INSERT,UPDATEONscott.empTOtestWITHGRANTOPTION;授权成功。SQL>CONNtest/QSTqst2015;已连接。SQL>GRANTSELECT,INSERTONscott.empTOtest02;授权成功。SQL>CONNtest02/QSTqst2015;已连接。SQL>SELECT*FROMscott.empWHEREempno=7782;EMPNOENAMEJOBMGRHIREDATESALCOMMDEPTNO-------------------------------------------------------------7782CLARKMANAGER783909-6月-81245010【示例】为用户赋予scott.emp表的所有对象权限3.3.2对象权限SQL>CONNSYSTEM/QSTqst2015;已连接。SQL>GRANTALLONscott.empTOtest;授权成功。【语法】对象权限的回收其中：CASCADECONSTRAINTS表示当回收REFERENCE对象权限或回收ALLPRIVILEGES对象权限时，删除利用REFERENCE对象权限创建的外键约束3.3.2对象权限REVOKEobject_privilege_list|ALL[PRIVILEGE]ON[schema.]objectFROMuser_list[CASCADECONSTRAINTS];【示例】回收用户“test02”在scott.emp表上的某些权限【示例】回收用户“test”在scott.emp表上的所有权限3.3.2对象权限SQL>CONNSYSTEM/QSTqst2015;已连接。SQL>REVOKESELECT,INSERTONscott.empFROMtest02撤销成功。SQL>CONNSYSTEM/QSTqst2015;已连接。SQL>REVOKEALLONscott.empFROMtest撤销成功。【示例】所有权限3.3.2对象权限SQL>CONNSYSTEM/QSTqst2015;已连接。SQL>GRANTALLONscott.empTOtestWITHGRANTOPTION;授权成功。SQL>CONNtest/QSTqst2015;已连接。SQL>GRANTSELECTONscott.empTOtest02;授权成功。SQL>CONNSYSTEM/QSTqst2015;已连接。SQL>REVOKESELECTONscott.empFROMtest;撤销成功。SQL>CONNtest02/QSTqst2015;已连接。SQL>SELECT*FROMscott.emp;SELECT*FROMscott.emp*第1行出现错误:ORA-00942:表或视图不存在JavaJSP角色管理4角色是指系统权限或者对象权限的集合。Oracle允许首先创建一个角色，然后将角色信息赋予用户，从而间接地将权限分配给用户。因为角色的可复用性，因此可以将角色再次分配给其他用户，从而减少了重复工作。3.4角色常用系统预定义角色3.4角色角色名称包括的权限DBA包含所有系统权限，且带有WITHADMINOPTION选项，即可以将系统权限授予其他用户CONNECTCREATESESSION、ALTERSESSION、CREATESEQUENCE、CREATESYNONYM、CREATEVIEW、CREATECLUSTER、CREATEDATABASELINKRESOURCECREATESEQUENCE、CREATETABLE、CREATETRIGGER、CREATETYPE、CREATECLUSTER、CREATEPROCEDURE【语法】用户自定义角色其中：role_name表示自定义角色的名称，该名称不能与任何用户名或其他角色名相同NOTIDENTIFIED表示角色采用数据库认证，激活角色时不需要口令IDENTIFIEDBYpassword表示角色采用数据库认证，激活角色时需要输入口令3.4.1角色的创建与授权CREATEROLErole_name[NOTIDENTIFIED]|[IDENTIFIEDBYpassword];【示例】创建一个不需要口令的数据库认证的角色【示例】创建一个需要口令的数据库认证的角色3.4.1角色的创建与授权SQL>CONNsystem/QSTqst2015;已连接。SQL>CREATEROLErole_emp;角色已创建。SQL>CONNsystem/QSTqst2015;已连接。SQL>CREATEROLErole_managerIDENTIFIEDBYrolemanager;角色已创建。【示例】角色的授权和回收3.4.1角色的创建与授权SQL>CONNsystem/QSTqst2015;已连接。SQL>GRANTALLONscott.empTOrole_emp;授权成功。SQL>GRANTCREATESESSION,CREATETABLE,CREATEVIEWTOrole_manager;授权成功。SQL>REVOKEUPDATE,DELETEONscott.empFROMrole_emp;撤销成功。SQL>REVOKECREATETABLE,CREATEVIEWFROMrole_manager;撤销成功。【语法】角色授予其中：WITHADMINOPTION表示被授予用户可以将此角色再授予其他用户，或从任何具有该角色的用户那里回收该角色3.4.2角色的授予与回收GRANTrole_listTOuser_list|role_list[WITHADMINOPTION];【示例】将系统预定义角色授予一个角色【示例】将用户自定义角色授予一个用户3.4.2角色的授予与回收SQL>CONNsystem/QSTqst2015;已连接。SQL>GRANTCONNECT,RESOURCETOrole_emp;授权成功。SQL>CONNsystem/QSTqst2015;已连接。SQL>GRANTrole_empTOtestWITHADMINOPTION;授权成功。【示例】系统权限、对象权限和角色的组合授予3.4.2角色的授予与回收SQL>CONNsystem/QSTqst2015;已连接。SQL>GRANTCONNECT,role_empTOtest;授权成功。SQL>GRANTrole_emp,SELECTONscott.empTOtest;GRANTrole_emp,SELECTONscott.empTOtest*第1行出现错误:ORA-00990:权限缺失或无效【示例】从其他角色或用户回收角色3.4.2角色的授予与回收SQL>CONNsystem/QSTqst2015;已连接。SQL>REVOKERESOURCEFROMrole_emp;撤销成功。SQL>REVOKErole_empFROMtest;撤销成功。【语法】修改角色【示例】为角色role_emp添加认证口令，取消role_manager的认证口令3.4.3角色管理ALTERROLErole[NOTIDENTIFIED]|[IDENTIFIEDBYpassword];SQL>CONNsystem/QSTqst2015;已连接。SQL>ALTERROLErole_empIDENTIFIEDBYroleemp;角色已丢弃。SQL>ALTERROLErole_managerNOTIDENTIFIED;角色已丢弃。【语法】禁用与激活角色其中：user表示设置默认角色的用户名称role_list表示指定的默认角色，多个角色名称以逗号分隔ALL表示将用户的所有角色都设置为默认角色，处于激活状态EXCEPTrole_list表示除了指定的角色被禁用外，其余所有角色都为默认角色NONE表示禁用用户的所有角色，即用户登录时所有角色都处于禁用状态3.4.3角色管理ALTERUSERuserDEFAULTROLErole_list|[ALL[EXCEPTrole_list]]|NONE;【示例】禁用用户“test”的所有角色【示例】将用户“test”的所有角色设置为默认角色3.4.3角色管理SQL>CONNsystem/QSTqst2015;已连接。SQL>ALTERUSERtestDEFAULTROLENONE;用户已更改。SQL>ALTERUSERtestDEFAULTROLEALL;用户已更改。【示例】将用户“test”的部分角色设置为默认角色【示例】将用户“test”除某个角色外其他所有角色设置为默认角色3.4.3角色管理SQL>ALTERUSERtestDEFAULTROLEALLEXCEPTrole_emp;用户已更改。SQL>ALTERUSERtestDEFAULTROLECONNECT,role_emp;用户已更改。删除角色【示例】删除用户自定义角色3.4.3角色管理SQ