oracle用户权限管理.docx

1、oracle用户权限管理实验二：用户权限管理与测试实验环境：操作系统： windows XP 软件版本：oracle 9i实验内容：用户权限管理与测试实验要求：（1）先用二维表的形式画出各个用户与其权限的关系以及对应默认的表空间（默认表空间参看实验一的内容需求）（2）根据上述要求创建以上所有用户、授予其相应权限并指定相应默认的表空间以及用户的缺省状态。请写出完整、具体的SQL语句。（3）设计测试案例。创建完成后，进行实际测试，以测试其有效性。以实际环境的截图为准。实验步骤：一、 各个用户与其权限的关系以及对应默认的表空间如下表：权限用户系统权限对象权限默认的表空间用户缺省状态（锁定/未锁定）s

2、yssecur_userUNLIMIT TABLESPACECREATE USER ALTERUSER DORP USER-User未锁定DBrestore_userBACKUP UNLIMIT TABLESPACE-User未锁定DBbkp_userFALSH BACKUNLIMIT TABLESPACE-User未锁定TeamuserUNLIMIT TABLESPACEIDUSTbs_team_data未锁定audituserUNLIMIT TABLESPACEIDUSTbs_pretreat_data未锁定inneruserUNLIMIT TABLESPACESTbs_postdata_

3、data1未锁定说明：IINSERT; D-Delete; U-Update; S-Select二、用户创建、授权并指定相应默认的表空间以及用户的缺省状态：（1）作业小队（teamuser）：创建：CREATE USER TEAMUSER PROFILE DEFAULT IDENTIFIED BY teamuser123 DEFAULT TABLESPACE TBS_TEAM_DATA TEMPORARY TABLESPACE TBS_TEAM_TEMP ACCOUNT UNLOCK;GRANT CONNECT TO TEAMUSER WITH ADMIN OPTION;GRANT RESOU

4、RCE TO TEAMUSER WITH ADMIN OPTION; 授权：GRANT DELETE ON TEAMUSER.OPERATION_INFOR TO TEAMUSERGRANT INSERT ON TEAMUSER.OPERATION_INFOR TO TEAMUSERGRANT SELECT ON TEAMUSER.OPERATION_INFOR TO TEAMUSERGRANT UPDATE ON TEAMUSER.OPERATION_INFOR TO TEAMUSER（2）数据审核部门（audituser）：创建：CREATE USER AUDITUSER PROFILE

5、DEFAULT IDENTIFIED BY audituser DEFAULT TABLESPACE TBS_PRETREAT_DATA TEMPORARY TABLESPACE TBS_PRETREAT_TEMP ACCOUNT UNLOCK;GRANT CONNECT TO AUDITUSER WITH ADMIN OPTION;GRANT RESOURCE TO AUDITUSER WITH ADMIN OPTION;授权：GRANT DELETE ON AUDITUSER.TAB_PRETREATDATA TO AUDITUSERGRANT DELETE ON TEAMUSER.OPE

6、RATION_INFOR TO AUDITUSERGRANT INSERT ON AUDITUSER.TAB_POSTDATA TO AUDITUSERGRANT INSERT ON AUDITUSER.TAB_PRETREATDATA TO AUDITUSERGRANT SELECT ON AUDITUSER.TAB_POSTDATA TO AUDITUSERGRANT SELECT ON AUDITUSER.TAB_PRETREATDATA TO AUDITUSERGRANT SELECT ON TEAMUSER.OPERATION_INFOR TO AUDITUSERGRANT UPDA

7、TE ON AUDITUSER.TAB_PRETREATDATA TO AUDITUSER（3）公司内部用户（inneruser）：创建：CREATE USER INNERUSER PROFILE DEFAULT IDENTIFIED BY inneruser123 DEFAULT TABLESPACE TBS_POSTDATA_DATA1 TEMPORARY TABLESPACE TBS_POSTDATA_TEMP ACCOUNT UNLOCK;GRANT CONNECT TO INNERUSER WITH ADMIN OPTION;GRANT RESOURCE TO INNERUSER W

8、ITH ADMIN OPTION;授权：GRANT SELECT ON AUDITUSER.TAB_POSTDATA TO INNERUSER（4）外部用户（webuser）：创建：CREATE USER WEBUSER PROFILE DEFAULT IDENTIFIED BY webuser123 DEFAULT TABLESPACE TBS_POSTDATA_DATA1 TEMPORARY TABLESPACE TBS_POSTDATA_TEMP ACCOUNT UNLOCK;GRANT CONNECT TO WEBUSER WITH ADMIN OPTION;GRANT RESOURC

9、E TO WEBUSER WITH ADMIN OPTION;授权：GRANT SELECT ON AUDITUSER.TAB_POSTDATA TO WEBUSER（5）数据库备份管理员（DBbkp_user）创建及授权：CREATE USER DBBKP_USER PROFILE DEFAULT IDENTIFIED BY DBbkp_user123 DEFAULT TABLESPACE USERS ACCOUNT UNLOCK;GRANT BACKUP ANY TABLE TO DBBKP_USER WITH ADMIN OPTIONGRANT UNLIMITED TABLESPACE

10、TO DBBKP_USER WITH ADMIN OPTION;GRANT CONNECT TO DBBKP_USER WITH ADMIN OPTION;GRANT RESOURCE TO DBBKP_USER WITH ADMIN OPTION; （6）数据库恢复管理员（DBrestore_user）创建及授权：CREATE USER DBRESTORE_USER PROFILE DEFAULT IDENTIFIED BY DBrestore_user123 DEFAULT TABLESPACE USERS ACCOUNT UNLOCK;GRANT FLASHBACK ANY TABLE

11、TO DBRESTORE_USER WITH ADMIN OPTIONGRANT UNLIMITED TABLESPACE TO DBRESTORE_USER WITH ADMIN OPTIONGRANT CONNECT TO DBRESTORE_USER WITH ADMIN OPTION;GRANT RESOURCE TO DBRESTORE_USER WITH ADMIN OPTION;（7）数据库安全管理员（syssecur_user）创建及授权：CREATE USER SYSSECUR_USER PROFILE DEFAULT IDENTIFIED BY syssecur_user1

12、23 DEFAULT TABLESPACE USERS ACCOUNT UNLOCK;GRANT ALTER USER TO SYSSECUR_USER WITH ADMIN OPTIONGRANT CREATE USER TO SYSSECUR_USER WITH ADMIN OPTIONGRANT DROP USER TO SYSSECUR_USER WITH ADMIN OPTIONGRANT CONNECT TO SYSSECUR_USER WITH ADMIN OPTION;GRANT RESOURCE TO SYSSECUR_USER WITH ADMIN OPTION; 二、 测

13、试案例：作业小队（teamuser）测试：连接到teamuser用户：Connect TEAMUSER / teamuser123（1）向OPERATION_INFOR表中插入测试数据：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_TI

14、ME)VALUES(CSJCT01,M01,Z01,WRC01,TO_DATE(24-04-2011,dd-MM-yyyy HH:MI:SSAM),TO_DATE(10:10:10,dd-MM-yyyy HH:MI:SSAM) ,10 ,10 ,10 ,10 ,10 ,10 ,10 ,10 );（2）查询表中数据：Connect TEAMUSER / teamuser123SELECT * FROM OPERATION_INFOR;（3）更新表中数据：UPDATE TEAMUSER.OPERATION_INFORSET TEAMID= M02;（4）删除表中数据：DELETE FROM TEA

15、MUSER.OPERATION_INFOR;数据审核部门（audituser）测试：连接到audituser用户：Connect AUDITUSER / audituser（1）向TAB_PRETREATDATA表中插入数据：INSERT INTO AUDITUSER.TAB_PRETREATDATA (PLACEID,MACHINEID, TEAMID ,CLIENT_ID , WORKDATE ,WORKTIME ,DEEEPTH_PARA,AVERAGE_FLOW , AVERAGE_DENSITY,AVERAGE_PRESSURE,PRESSURE_ MAX,AVERAGE_SPEED

16、,SPEED_MAX,ESTIMATE_TIME)VALUES(CSJCT01,M01,Z01,WRC01,TO_DATE(24-04-2011,dd-MM-yyyyHH:MI:SSAM),TO_DATE(10:10:10,dd-MM-yyyyHH:MI:SSAM) ,10 ,10 ,10 ,10 ,10 ,10 ,10 ,10 );（2）向TAB_POSTDATA表中插入数据：INSERT INTO AUDITUSER.TAB_POSTDATA (PLACEID,MACHINEID, TEAMID ,CLIENT_ID , WORKDATE ,WORKTIME ,DEEEPTH_PARA,A

17、VERAGE_FLOW , AVERAGE_DENSITY,AVERAGE_PRESSURE,PRESSURE_ MAX,AVERAGE_SPEED,SPEED_MAX,ESTIMATE_TIME)VALUES(CSJCT01,M01,Z01,WRC01,TO_DATE(24-04-2011,dd-MM-yyyyHH:MI:SSAM),TO_DATE(10:10:10,dd-MM-yyyyHH:MI:SSAM) ,10 ,10 ,10 ,10 ,10 ,10 ,10 ,10 );（3）查询表中数据：Connect AUDITUSER / audituserSELECT * FROM TAB_P

18、RETREATDATA;（4）更新表中数据：UPDATE AUDITUSER.TAB_PRETREATDATASET TEAMID= M02;（5）删除表中数据：DELETE FROM AUDITUSER.TAB_PRETREATDATA;数据库安全管理员（syssecur_user）测试：连接到syssecur_user管理员：Connect SYSSECUR_USER / syssecur_user123 AS SYSDBA（1） 删除数据库备份管理员（DBbkp_user）：drop user DBBKP_USER cascade;（2） 重新创建数据库备份管理员（DBbkp_user）

19、及授权：CREATE USER DBBKP_USER PROFILE DEFAULT IDENTIFIED BY DBbkp_user123 DEFAULT TABLESPACE USERS ACCOUNT UNLOCK;GRANT BACKUP ANY TABLE TO DBBKP_USER WITH ADMIN OPTION;GRANT UNLIMITED TABLESPACE TO DBBKP_USER WITH ADMIN OPTION;GRANT CONNECT TO DBBKP_USER WITH ADMIN OPTION;GRANT RESOURCE TO DBBKP_USER WITH ADMIN OPTION;（3） 查询所有信息：DESC ALL_USERS;