实验二：用户权限和基本SQL语言.doc

实验二：用户权限和基本SQL语言1. 实验目的：（1） 熟悉用户管理、权限管理等数据库安全管理机制；（2） 熟悉用户外部表、普通表、视图创建方式，基本SQL语句如简单SELECT查询和统计功能的实用。2. 实验内容（1） 创建faculty, phd, postgraduate, undergraduate四个用户，并相应的创建四个角色facultyRole, phdRole, postgraduateRole, undergraduateRole，并给各用户赋予相应的角色和权限，使得各数据库用户至少具有连接数据库、创建表、创建目录等权限。创建用户：- Create the user create user FACULTY identified externally default tablespace USERS temporary tablespace TEMP profile DEFAULT;- Grant/Revoke role privileges grant facultyrole to FACULTY with admin option;- Grant/Revoke system privileges grant alter any cluster to FACULTY with admin option;grant alter any table to FACULTY with admin option;grant alter database to FACULTY with admin option;- Create the user create user PHD identified externally default tablespace USERS temporary tablespace TEMP profile DEFAULT;- Grant/Revoke role privileges grant phdrole to PHD with admin option;- Grant/Revoke system privileges grant alter any cluster to PHD;grant alter any table to PHD with admin option;grant alter database to PHD with admin option;- Create the user create user POSTGRADUATE identified externally default tablespace USERS temporary tablespace TEMP profile DEFAULT;- Grant/Revoke role privileges grant postgraduaterole to POSTGRADUATE with admin option;- Grant/Revoke system privileges grant alter any table to POSTGRADUATE with admin option;grant alter database to POSTGRADUATE with admin option;- Create the user create user UNDERGRADUATE identified externally default tablespace USERS temporary tablespace TEMP profile DEFAULT;- Grant/Revoke role privileges grant undergraduaterole to UNDERGRADUATE with admin option;- Grant/Revoke system privileges grant alter any cluster to UNDERGRADUATE with admin option;grant alter any table to UNDERGRADUATE with admin option;grant alter database to UNDERGRADUATE with admin option;创建角色：- Create the role create role FACULTYROLE identified externally;- Grant/Revoke system privileges grant alter any table to FACULTYROLE;grant alter database to FACULTYROLE;- Create the role create role PHDROLE identified externally;- Grant/Revoke system privileges grant alter any procedure to PHDROLE with admin option;grant alter any table to PHDROLE with admin option;grant alter profile to PHDROLE with admin option;- Create the role create role POSTGRADUATEROLE;identified externally;- Grant/Revoke system privileges grant alter any index to POSTGRADUATEROLE with admin option;grant alter any table to POSTGRADUATEROLE with admin option;grant comment any table to POSTGRADUATEROLE with admin option;- Create the role create role UNDERGRADUATEROLE identified externally;- Grant/Revoke system privileges grant alter any cluster to UNDERGRADUATEROLE with admin option;grant alter any table to UNDERGRADUATEROLE with admin option;grant alter database to UNDERGRADUATEROLE with admin option;（2） 查看、修改和设置各用户的概要文件。- Create profile create profile PROFILE1 limit sessions_per_user 2 idle_time 15 failed_login_attempts 3 password_life_time 30;- Add users to profile alter user faculty profile PROFILE1;alter user phd profile PROFILE1;alter user postgraduate profile PROFILE1;alter user undergraduate profile PROFILE1;（3） 在faculty用户对应的模式下创建paper_ext、author_ext、write_ext和cite_ext四个外部表，然后基于这四个外部表，创建对应的四个普通表，不要带主键和外键约束。创建外部表的过程中，可能需要增加faculty某些权限才能操作成功。以paper表为例，创建外部表及其对应的普通表的Oracle10g的SQL语句如下：-创建外部表对应的物理文件所在的文件目录，下载来的实验数据集请放在d:dblp_data，也可以是其他目录名，但是要一致。CREATE DIRECTORY DBLP_DATA_FILES AS D:DBLP_DATA; -创建paper_ext外部表CREATE TABLE faculty.paper_ext(paperid VARCHAR2(150),title VARCHAR2(700),year NUMBER(4),type VARCHAR2(20)ORGANIZATION EXTERNAL(TYPE ORACLE_LOADERDEFAULT DIRECTORY DBLP_DATA_FILES ACCESS PARAMETERS(FIELDS TERMINATED BY )LOCATION(paper.txt)REJECT LIMIT UNLIMITED;-根据paper_ext外部表，创建普通表paper.CREATE TABLE faculty.paper AS SELECT TRIM(paperid) AS paperid, TRIM(title) AS title, TRIM(year) AS year , TRIM(type) AS type FROM faculty.paper_ext; 其他的表的相应SQL语句请自己写出。请统计并记录下来各表的记录数。 -创建write_ext外部表CREATE TABLE faculty.write_ext(id VARCHAR2(700),paperid VARCHAR2(700)ORGANIZATION EXTERNAL(TYPE ORACLE_LOADERDEFAULT DIRECTORY DBLP_DATA_FILES ACCESS PARAMETERS(FIELDS TERMINATED BY )LOCATION(write.txt)REJECT LIMIT UNLIMITED;-根据write_ext外部表，创建普通表write.CREATE TABLE faculty.write AS SELECT TRIM(id) AS id, TRIM(paperid) AS papered FROM faculty.write_ext;CREATE TABLE faculty.author_ext(authorid VARCHAR2(700),name VARCHAR2(700)ORGANIZATION EXTERNAL(TYPE ORACLE_LOADERDEFAULT DIRECTORY DBLP_DATA_FILES ACCESS PARAMETERS(FIELDS TERMINATED BY )LOCATION(author.txt)REJECT LIMIT UNLIMITED;-根据author_ext 外部表，创建普通表author.CREATE TABLE faculty.author AS SELECT TRIM(authorid) AS authorid, TRIM(name) AS name FROM faculty.author_ext;CREATE TABLE faculty.cite_ext (paperid VARCHAR2(700),cited VARCHAR2(700)ORGANIZATION EXTERNAL(TYPE ORACLE_LOADERDEFAULT DIRECTORY DBLP_DATA_FILES ACCESS PARAMETERS(FIELDS TERMINATED BY )LOCATION(cite.txt)REJECT LIMIT UNLIMITED;-根据cite_ext 外部表，创建普通表cite.CREATE TABLE faculty.Cite AS SELECT TRIM(paperid) AS paperid, TRIM(cited) AS cited FROM faculty.cite._ext;（4） 创建三个外模式：博士论文外模式、硕士论文外模式和其他论文外模式，每个外模式实际上对应若干表或者是视图，以博士论文外模式为例，给出相应的SQL语句：CREATE VIEW vPhdPaper AS SELEC * FROM paper WHERE type = phdthesis;CREATE TABLE vPhdCite AS SELECT * FROM cite WHERE paperid IN ( SELECT paperid FROM vPhdPaper) AND cited IN (SELECT paperid FROM vPhdPaper);CREATE TABLE vPhdWrite AS SELECT * FROM write WHERE paperid IN ( SELECT paperid FROM vPhdPaper); CREATE TABLE vPhdAuthor AS SELECT * FROM author WHERE authorid IN ( SELECT authorid FROM vPhdWrite);该模式的含义是：限定paper表中论文类型为phdthesis的论文，以及这些论文对应的作者信息author、论文引用信息cite和论文写作信息write。同样的道理，可以定义硕士论文外模式为论文类型为masterthesis，其他论文外模式为论文类型不等于phdthesis和masterthesis的论文及相应信息。create or replace view vwrite asselect ID,PAPERID from WRITEwhere id = 1;（5） 设计权限管理模式，使得undergraduate用户具有其他论文外模式的所有权限，postgraduate具有硕士论文外模式的所有权限，只具有其他论文外模式的SELECT权限，phd具有博士论文外模式所有权限，但只具有硕士论文外模式和其他论文外模式的SELECT权限。利用直接给用户赋予系统权限或者对象权限，或者利用角色role机制给各用户赋予权限。 （6） 设计权限管理模式，测试具有with admin option和with grant