《用户管理权限与测试》由会员分享，可在线阅读，更多相关《用户管理权限与测试（12页珍藏版）》请在金锄头文库上搜索。
1、实验题目：用户权限管理与测试 实验时间：2011年4月6 日 实验地点：B322实验二：用户管理权限与测试实验环境：硬件环境: 处理器 AMD Turion 64 X2 Mobile Technology TL-64内存 2 GB DDR2软件环境：Microsoft Windows xp Oracle 9i实验内容：用户管理权限与测试（1） 各个用户与其权限的关系以及对应默认的表空间如下表权限用户系统权限对象权限默认的表空间用户缺省状态（锁定/未锁定）syssecur_userFLASHBACKBACKUPUNLIMIT TABLESPACECREATE USERDROP USERALTER
2、 USERCREAT PROCEDURECREATE TRIGGERINSERTDELETEUPDATESELECTUSERS未DBrestore_userBACKUPUNLIMIT TABLESPACEUSERS未DBbkp_userUNLIMIT TABLESPACECREAT PROCEDURECREATE TRIGGERUSERS未TeamuserUNLIMIT TABLESPACEINSERTDELETEUPDATESELECTTBS_TEAM_DATA未AudituserUNLIMIT TABLESPACETBS_PRETREAT未InneruserUNLIMIT TABLESPA
3、CEINSERTDELETUPDATETBS_POSTDATA_DATA1未（2）创建用户与授权1.数据库安全管理员（syssecur_user）SQLCREATE USER “SYSSECUR_USER” PROFILE “DEFAULT”2 IDENTIFIED BY “123” DEFAULT TABLESPACE “USERS”3 ACCOUNT UNLOCK;授权：GRANT ALTER USER TO SYSSECUR_USERGRANT BACKUP ANY TABLE TO SYSSECUR_USER WITH ADMIN OPTIONGRANT CREATE USER TO
4、SYSSECUR_USERGRANT DELETE ANY TABLE TO SYSSECUR_USER WITH ADMIN OPTIONGRANT DROP USER TO SYSSECUR_USERGRANT FLASHBACK ANY TABLE TO SYSSECUR_USER WITH ADMIN OPTIONGRANT INSERT ANY TABLE TO SYSSECUR_USER WITH ADMIN OPTIONGRANT SELECT ANY TABLE TO SYSSECUR_USER WITH ADMIN OPTIONGRANT UPDATE ANY TABLE T
5、O SYSSECUR_USER WITH ADMIN OPTIONGRANT CONNECT TO SYSSECUR_USER WITH ADMIN OPTION;GRANT RESOURCE TO SYSSECUR_USER WITH ADMIN OPTION;ALTER USER SYSSECUR_USER DEFAULT ROLE ALL创建完成后切换到syssecur_user登录2.数据库恢复管理员（DBrestore_user）SQLCREATE USER “DBRSETORE_USER” PROFILE “DEFAULT”2 IDENTIFIED BY “123” DEFAULT
6、 TABLESPACE “USERS”3 ACCOUNT UNLOCK;授权：GRANT BACKUP ANY TABLE TO DBRESTORE_USER WITH ADMIN OPTIONGRANT FLASHBACK ANY TABLE TO DBRESTORE_USER WITH ADMIN OPTIONGRANT CONNECT TO DBRESTORE_USER;GRANT RESOURCE TO DBRESTORE_USER;3.数据库备份管理员（DBbkp_user）SQLCREATE USER “DBBKP_USER” PROFILE “DEFAULT”2 IDENTIFI
7、ED BY “123” DEFAULT TABLESPACE “USERS”3 ACCOUNT UNLOCK;授权：GRANT BACKUP ANY TABLE TO DBBKP_USERGRANT CONNECT TO DBBKP_USER;GRANT RESOURCE TO DBBKP_USER;4.作业小队（teamuser）CREATE USER TEAMUSER PROFILE DEFAULT IDENTIFIED BY teamuser123 DEFAULT TABLESPACE TBS_TEAM_DATA TEMPORARY TABLESPACE TBS_PRETREAT_TEM
8、P ACCOUNT UNLOCK;授权：GRANT DELETE ON TEAMUSER.OPERATION_INFOR TO TEAMUSER;GRANT INSERT ON TEAMUSER.OPERATION_INFOR TO TEAMUSER;GRANT SELECT ON TEAMUSER.OPERATION_INFOR TO TEAMUSER;GRANT UPDATE ON TEAMUSER.OPERATION_INFOR TO TEAMUSER;GRANT CONNECT TO TEAMUSER;GRANT RESOURCE TO TEAMUSER;5. 数据审核部门（audit
9、user）CREATE USER AUDITUSER PROFILE DEFAULT IDENTIFIED BY audituser123 DEFAULT TABLESPACE TBS_PRETREAT_DATA TEMPORARY TABLESPACE TBS_POSTDATA_TEMP ACCOUNT UNLOCK;授权：GRANT DELETE ON AUDITUSER.TAB_PRETREATDATA TO AUDITUSER;GRANT DELETE ON TEAMUSER.OPERATION_INFOR TO AUDITUSER;GRANT INSERT ON AUDITUSER.
10、TAB_PRETREATDATA TO AUDITUSER;GRANT SELECT ON AUDITUSER.TAB_PRETREATDATA TO AUDITUSER;GRANT SELECT ON TEAMUSER.OPERATION_INFOR TO AUDITUSER;GRANT UPDATE ON AUDITUSER.TAB_PRETREATDATA TO AUDITUSER;GRANT INSERT ON INNERUSER.TAB_POSTDATA TO AUDITUSER;GRANT SELECT ON INNERUSER.TAB_POSTDATA TO AUDITUSER;
11、GRANT CONNECT TO AUDITUSER;GRANT RESOURCE TO AUDITUSER;6.公司内部用户（inneruser）CREATE USER INNERUSER PROFILE DEFAULT IDENTIFIED BY inneruser 123 DEFAULT TABLESPACE TBS_POSTDATA_DATA1 TEMPORARY TABLESPACE TBS_POSTDATA_TEMP ACCOUNT UNLOCK;授权：GRANT CONNECT TO INNERUSER;GRANT RESOURCE TO INNERUSER;GRANT SELE
12、CT ON INNERUSER.TAB_POSTDATA TO INNERUSER;7.外部用户（webuser）CREATE USER WEBUSER PROFILE DEFAULT IDENTIFIED BY webuser123 DEFAULT TABLESPACE TBS_POSTDATA_DATA1 TEMPORARY TABLESPACE TBS_POSTDATA_TEMP ACCOUNT UNLOCK;授权：GRANT SELECT ON INNERUSER.TAB_POSTDATA TO WEBUSER;GRANT CONNECT TO WEBUSER;GRANT RESOUR
13、CE TO WEBUSER;（3）由于表约束无法更改，所以删除又建立三个表：Teamuser:Audituser：Inneruser：（4）设计测试案例分别向三个表中输入测试数据：向TEAMUSER中输入数据：INSERT INTO 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-yyyy HH:MI:SS AM) ,TO_DATE(25-4月 -2011, dd-Mon-yyyy HH:MI:SS AM) ,23.4 ,23 ,11 ,2 ,32 ,12.5 ,44 ,1 )查询：select TEAMUSER.OPERATION_INFOR.PLACEID, TEAMUSER.OPERATION_INFOR.MACHINEID, TEAMUSER.OPERATION_INFOR.TEAMID,
