用户管理权限与测试.docx
- 文档编号:28578379
- 上传时间:2023-07-19
- 格式:DOCX
- 页数:15
- 大小:319.08KB
用户管理权限与测试.docx
《用户管理权限与测试.docx》由会员分享,可在线阅读,更多相关《用户管理权限与测试.docx(15页珍藏版)》请在冰豆网上搜索。
用户管理权限与测试
用户管理权限与测试
实验二:
用户管理权限与测试
实验环境:
硬件环境:
处理器AMDTurion64X2MobileTechnologyTL-64
内存2GBDDR2
软件环境:
MicrosoftWindowsxpOracle9i
实验内容:
用户管理权限与测试
(1)各个用户与其权限的关系以及对应默认的表空间如下表
权限
用户
系统权限
对象权限
默认的表空间
用户缺省状态
(锁定/未锁定)
syssecur_user
FLASHBACK
BACKUP
UNLIMITTABLESPACE
CREATEUSER
DROPUSER
ALTERUSER
CREATPROCEDURE
CREATETRIGGER
INSERT
DELETE
UPDATE
SELECT
USERS
未
DBrestore_user
BACKUP
UNLIMITTABLESPACE
USERS
未
DBbkp_user
UNLIMITTABLESPACE
CREATPROCEDURE
CREATETRIGGER
USERS
未
Teamuser
UNLIMITTABLESPACE
INSERT
DELETE
UPDATE
SELECT
TBS_TEAM_DATA
未
Audituser
UNLIMITTABLESPACE
TBS_PRETREAT
未
Inneruser
UNLIMITTABLESPACE
INSERT
DELET
UPDATE
TBS_POSTDATA_DATA1
未
(2)创建用户与授权
1.数据库安全管理员(syssecur_user)
SQL>CREATEUSER“SYSSECUR_USER”PROFILE“DEFAULT”
2IDENTIFIEDBY“123”DEFAULTTABLESPACE“USERS”
3ACCOUNTUNLOCK;
授权:
GRANTALTERUSERTO"SYSSECUR_USER"
GRANTBACKUPANYTABLETO"SYSSECUR_USER"WITHADMINOPTION
GRANTCREATEUSERTO"SYSSECUR_USER"
GRANTDELETEANYTABLETO"SYSSECUR_USER"WITHADMINOPTION
GRANTDROPUSERTO"SYSSECUR_USER"
GRANTFLASHBACKANYTABLETO"SYSSECUR_USER"WITHADMINOPTION
GRANTINSERTANYTABLETO"SYSSECUR_USER"WITHADMINOPTION
GRANTSELECTANYTABLETO"SYSSECUR_USER"WITHADMINOPTION
GRANTUPDATEANYTABLETO"SYSSECUR_USER"WITHADMINOPTION
GRANT"CONNECT"TO"SYSSECUR_USER"WITHADMINOPTION;
GRANT"RESOURCE"TO"SYSSECUR_USER"WITHADMINOPTION;
ALTERUSER"SYSSECUR_USER"DEFAULTROLEALL
创建完成后切换到syssecur_user登录
2.数据库恢复管理员(DBrestore_user)
SQL>CREATEUSER“DBRSETORE_USER”PROFILE“DEFAULT”
2IDENTIFIEDBY“123”DEFAULTTABLESPACE“USERS”
3ACCOUNTUNLOCK;
授权:
GRANTBACKUPANY
TABLETO"DBRESTORE_USER"WITHADMINOPTION
GRANTFLASHBACKANYTABLETO"DBRESTORE_USER"WITHADMINOPTION
GRANT"CONNECT"TO"DBRESTORE_USER";
GRANT"RESOURCE"TO"DBRESTORE_USER";
3.数据库备份管理员(DBbkp_user)
SQL>CREATEUSER“DBBKP_USER”PROFILE“DEFAULT”
2IDENTIFIEDBY“123”DEFAULTTABLESPACE“USERS”
3ACCOUNTUNLOCK;
授权:
GRANTBACKUPANY
TABLETO"DBBKP_USER"
GRANT"CONNECT"TO"DBBKP_USER";
GRANT"RESOURCE"TO"DBBKP_USER";
4.作业小队(teamuser)
CREATEUSER"TEAMUSER"PROFILE"DEFAULT"
IDENTIFIEDBY"teamuser123"DEFAULTTABLESPACE
"TBS_TEAM_DATA"
TEMPORARYTABLESPACE"TBS_PRETREAT_TEMP"
ACCOUNTUNLOCK;
授权:
GRANTDELETEON"TEAMUSER"."OPERATION_INFOR"TO"TEAMUSER";
GRANTINSERTON"TEAMUSER"."OPERATION_INFOR"TO"TEAMUSER";
GRANTSELECTON"TEAMUSER"."OPERATION_INFOR"TO"TEAMUSER";
GRANTUPDATEON"TEAMUSER"."OPERATION_INFOR"TO"TEAMUSER";
GRANT"CONNECT"TO"TEAMUSER";
GRANT"RESOURCE"TO"TEAMUSER";
5.数据审核部门(audituser)
CREATEUSER"AUDITUSER"PROFILE"DEFAULT"
IDENTIFIEDBY"audituser123"DEFAULTTABLESPACE
"TBS_PRETREAT_DATA"
TEMPORARYTABLESPACE"TBS_POSTDATA_TEMP"
ACCOUNTUNLOCK;
授权:
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";
GRANTSELECT
ON"INNERUSER"."TAB_POSTDATA"TO"INNERUSER";
7.外部用户(webuser)
CREATEUSER"WEBUSER"PROFILE"DEFAULT"
IDENTIFIEDBY"webuser123"DEFAULTTABLESPACE
"TBS_POSTDATA_DATA1"
TEMPORARYTABLESPACE"TBS_POSTDATA_TEMP"
ACCOUNTUNLOCK;
授权:
GRANTSELECT
ON"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"AUDITUSER"."TAB_PRETREATDATA"
向INNERUSER输入数据:
select"INNERUSER"."TAB_POSTDATA"."PLACEID",
"INNERUSER"."TAB_POSTDATA"."MACHINEID",
"INNERUSER"."TAB_POSTDATA"."TEAMID",
"INNERUSER"."TAB_POSTDATA"."CLIENT_ID",
"INNERUSER"."TAB_POSTDATA"."WORKDATE",
"INNERUSER"."TAB_POSTDATA"."WORKTIME",
"INNERUSER"."TAB_POSTDATA"."DEEEPTH_PARA",
"INNERUSER"."TAB_POSTDATA"."AVERAGE_FLOW",
"INNERUSER"."TAB_POSTDATA"."AVERAGE_DENSITY",
"INNERUSER"."TAB_POSTDATA"."AVERAGE_PRESSURE",
"INNERUSER"."TAB_POSTDATA"."PRESSURE_MAX",
"INNERUSER"."TAB_POSTDATA"."AVERAGE_SPEED",
"INNERUSER"."TAB_POSTDATA"."SPEED_MAX",
"INNERUSER"."TAB_POSTDATA"."ESTIMATE_TIME"
from"INNERUSER"."TAB_POSTDATA"
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- 用户 管理权限 测试
![提示](https://static.bdocx.com/images/bang_tan.gif)