用户管理权限与测试

实验题目：用户权限管理与测试实验时间：2011年4月6日实验地点：B322PAGE1-班级：信计081学号：09实验二：用户管理权限与测试实验环境：硬件环境:处理器AMDTurion64X2MobileTechnologyTL-64内存2GBDDR2软件环境：MicrosoftWindowsxpOracle9i实验内容：用户管理权限与测试各个用户与其权限的关系以及对应默认的表空间如下表权限用户系统权限对象权限默认的表空间用户缺省状态（锁定/未锁定）syssecur_userFLASHBACKBACKUPUNLIMITTABLESPACECREATEUSERDROPUSERALTERUSERCREATPROCEDURECREATETRIGGERINSERTDELETEUPDATESELECTUSERS未DBrestore_userBACKUPUNLIMITTABLESPACEUSERS未DBbkp_userUNLIMITTABLESPACECREATPROCEDURECREATETRIGGERUSERS未TeamuserUNLIMITTABLESPACEINSERTDELETEUPDATESELECTTBS_TEAM_DATA未AudituserUNLIMITTABLESPACETBS_PRETREAT未InneruserUNLIMITTABLESPACEINSERTDELETUPDATETBS_POSTDATA_DATA1未（2）创建用户与授权1.数据库安全管理员（syssecur_user）SQL>CREATEUSER“SYSSECUR_USER”PROFILE“DEFAULT”IDENTIFIEDBY“123”DEFAULTTABLESPACE“USERSACCOUNTUNLOCK;授权：GRANTALTERUSERTO"SYSSECUR_USER"GRANTBACKUPANYTABLETO"SYSSECUR_USER"WITHADMINOPTIONGRANTCREATEUSERTO"SYSSECUR_USER"GRANTDELETEANYTABLETO"SYSSECUR_USER"WITHADMINOPTIONGRANTDROPUSERTO"SYSSECUR_USER"GRANTFLASHBACKANYTABLETO"SYSSECUR_USER"WITHADMINOPTIONGRANTINSERTANYTABLETO"SYSSECUR_USER"WITHADMINOPTIONGRANTSELECTANYTABLETO"SYSSECUR_USER"WITHADMINOPTIONGRANTUPDATEANYTABLETO"SYSSECUR_USER"WITHADMINOPTIONGRANT"CONNECT"TO"SYSSECUR_USER"WITHADMINOPTION;GRANT"RESOURCE"TO"SYSSECUR_USER"WITHADMINOPTION;ALTERUSER"SYSSECUR_USER"DEFAULTROLEALL创建完成后切换到syssecur_user登录授权：GRANTDELETEON"AUDITUSER"."TAB_PRETREATDATA"TO"AUDITUSER";GRANTDELETEON"TEAMUSER"."OPERATION_INFOR"TO"AUDITUSER";GRANTINSERTON"AUDITUSER"."TAB_PRETREATDATA"TO"AUDITUSER";GRANTSELECTON"AUDITUSER"."TAB_PRETREATDATA"TO"AUDITUSER";GRANTSELECTON"TEAMUSER"."OPERATION_INFOR"TO"AUDITUSER";GRANTUPDATEON"AUDITUSER"."TAB_PRETREATDATA"TO"AUDITUSER";GRANTINSERTON"INNERUSER"."TAB_POSTDATA"TO"AUDITUSER";GRANTSELECTON"INNERUSER"."TAB_POSTDATA"TO"AUDITUSER";GRANT"CONNECT"TO"AUDITUSER";GRANT"RESOURCE"TO"AUDITUSER";6.公司内部用户（inneruser）CREATEUSER"INNERUSER"PROFILE"DEFAULT"IDENTIFIEDBY"inneruser123"DEFAULTTABLESPACE"TBS_POSTDATA_DATA1"TEMPORARYTABLESPACE"TBS_POSTDATA_TEMP"ACCOUNTUNLOCK; 授权：GRANT"CONNECT"TO"INNERUSER";GRANT"RESOURCE"TO"INNERUSER";GRANTSELECTON"INNERUSER"."TAB_POSTDATA"TO"INNERUSER";7.外部用户（webuser）CREATEUSER"WEBUSER"PROFILE"DEFAULT"IDENTIFIEDBY"webuser123"DEFAULTTABLESPACE"TBS_POSTDATA_DATA1"TEMPORARYTABLESPACE"TBS_POSTDATA_TEMP"ACCOUNTUNLOCK;授权：GRANTSELECTON"INNERUSER"."TAB_POSTDATA"TO"WEBUSER";GRANT"CONNECT"TO"WEBUSER";GRANT"RESOURCE"TO"WEBUSER";（3）由于表约束无法更改，所以删除又建立三个表：Teamuser:Audituser：Inneruser：（4）设计测试案例分别向三个表中输入测试数据：向TEAMUSER中输入数据：INSERTINTO"TEAMUSER"."OPERATION_INFOR"("PLACEID","MACHINEID","TEAMID","CLIENT_ID","WORKDATE","WORKTIME","DEEEPTH_PARA","AVERAGE_FLOW","AVERAGE_DENSITY","AVERAGE_PRESSURE","PRESSURE_MAX","AVERAGE_SPEED","SPEED_MAX","ESTIMATE_TIME")VALUES('Dalian','Mac1','T-1','C-1',TO_DATE('25-4月-2011','dd-Mon-yyyyHH:MI:SSAM'),TO_DATE('25-4月-2011','dd-Mon-yyyyHH:MI:SSAM'),23.4,23,11,2,32,12.5,44,1)查询：select"TEAMUSER"."OPERATION_INFOR"."PLACEID","TEAMUSER"."OPERATION_INFOR"."MACHINEID","TEAMUSER"."OPERATION_INFOR"."TEAMID","TEAMUSER"."OPERATION_INFOR"."CLIENT_ID","TEAMUSER"."OPERATION_INFOR"."WORKDATE","TEAMUSER"."OPERATION_INFOR"."WORKTIME","TEAMUSER"."OPERATION_INFOR"."DEEEPTH_PARA","TEAMUSER"."OPERATION_INFOR"."AVERAGE_FLOW","TEAMUSER"."OPERATION_INFOR"."AVERAGE_DENSITY","TEAMUSER"."OPERATION_INFOR"."AVERAGE_PRESSURE","TEAMUSER"."OPERATION_INFOR"."PRESSURE_MAX","TEAMUSER"."OPERATION_INFOR"."AVERAGE_SPEED","TEAMUSER"."OPERATION_INFOR"."SPEED_MAX","TEAMUSER"."OPERATION_INFOR"."ESTIMATE_TIME"from"TEAMUSER"."OPERATION_INFOR"SELECT"TEAMUSER"."OPERATION_INFOR"."PLACEID"as"PLACEID","TEAMUSER"."OPERATION_INFOR"."MACHINEID"as"MACHINEID","TEAMUSER"."OPERATION_INFOR"."TEAMID"as"TEAMID","TEAMUSER"."OPERATION_INFOR"."CLIENT_ID"as"CLIENT_ID","TEAMUSER"."OPERATION_INFOR"."WORKDATE"as"WORKDATE","TEAMUSER"."OPERATION_INFOR"."WORKTIME"as"WORKTIME","TEAMUSER"."OPERATION_INFOR"."DEEEPTH_PARA"as"DEEEPTH_PARA","TEAMUSER"."OPERATION_INFOR"."AVERAGE_FLOW"as"AVERAGE_FLOW","TEAMUSER"."OPERATION_INFOR"."AVERAGE_DENSITY"as"AVERAGE_DENSITY","TEAMUSER"."OPERATION_INFOR"."AVERAGE_PRESSURE"as"AVERAGE_PRESSURE","TEAMUSER"."OPERATION_INFOR"."PRESSURE_MAX"as"PRESSURE_MAX","TEAMUSER"."OPERATION_INFOR"."AVERAGE_SPEED"as"AVERAGE_SPEED","TEAMUSER"."OPERATION_INFOR"."SPEED_MAX"as"SPEED_MAX","TEAMUSER"."OPERATION_INFOR"."ESTIMATE_TIME"as"ESTIMATE_TIME"FROM"TEAMUSER"."OPERATION_INFOR"插入：UPDATE"TEAMUSER"."OPERATION_INFOR"SET"WORKDATE"=TO_DATE('25-4月-201112:00:00AM','dd-Mon-yyyyHH:MI:SSAM'),"WORKTIME"=TO_DATE('25-4月-201112:00:00AM','dd-Mon-yyyyHH:MI:SSAM')WHERErowid='AAAHZlAALAAAAAPAAA'INSERTINTO"TEAMUSER"."OPERATION_INFOR"("PLACEID","MACHINEID","TEAMID","CLIENT_ID","WORKDATE","WORKTIME","DEEEPTH_PARA","AVERAGE_FLOW","AVERAGE_DENSITY","AVERAGE_PRESSURE","PRESSURE_MAX","AVERAGE_SPEED","SPEED_MAX","ESTIMATE_TIME")VALUES('WFD','Mac2','T-2','C-2',TO_DATE('25-4月-201112:00:00AM','dd-Mon-yyyyHH:MI:SSAM'),TO_DATE('25-4月-201112:00:00AM','dd-Mon-yyyyHH:MI:SSAM'),22,11,43,5,34.3,3,12,3)修改：UPDATE"TEAMUSER"."OPERATION_INFOR"SET"MACHINEID"='Ma'WHERErowid='AAAHZlAALAAAAAPAAB'删除：DELETEFROM"TEAMUSER"."OPERATION_INFOR";向AUDITUSER中输入数据：select"AUDITUSER"."TAB_PRETREATDATA"."PLACEID","AUDITUSER"."TAB_PRETREATDATA"."MACHINEID","AUDITUSER"."TAB_PRETREATDATA"."TEAMID","AUDITUSER"."TAB_PRETREATDATA"."CLIENT_ID","AUDITUSER"."TAB_PRETREATDATA"."WORKDATE","AUDITUSER"."TAB_PRETREATDATA"."WORKTIME","AUDITUSER"."TAB_PRETREATDATA"."DEEEPTH_PARA","AUDITUSER"."TAB_PRETREATDATA"."AVERAGE_FLOW","AUDITUSER"."TAB_PRETREATDATA"."AVERAGE_DENSITY","AUDITUSER"."TAB_PRETREATDATA"."AVERAGE_PRESSURE","AUDITUSER"."TAB_PRETREATDATA"."PRESSURE_MAX","AUDITUSER"."TAB_PRETREATDATA"."AVERAGE_SPEED","AUDITUSER"."TAB_PRETREATDATA"."SPEED_MAX","AUDITUSER"."TAB_PRETREATDATA"."ESTIMATE_TIME"from"AUDITUSE