oracle 10g ex---角色，用户，表空间，验证（实验一）

一.创建用户.角色和表

1.用户登录和退出

登录方式：

sqlplus sys/oracle@orcl as sysdba -- sys具有sysdba权限，需要加上 as sysdba
sqlplus scott/tiger@orcl --普通用户登陆

退出：

SQL>disc

2.表空间

参考：

http://www.oracle.com/pls/tahiti/tahiti.tabbed?section=75124

例子：

---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

DEFAULT Storage Example

This statement creates a tablespace namedtabspace_2 with one datafile:

CREATE TABLESPACE tabspace_2    DATAFILE 'diska:tabspace_file2.dat' SIZE 20M    DEFAULT STORAGE (INITIAL 10K NEXT 50K                     MINEXTENTS 1 MAXEXTENTS 999)    ONLINE; 

AUTOEXTEND Example

This statement creates a tablespace namedtabspace_3 with one datafile. When more space is required, 50 kilobyte extents will be added up to a maximum size of 10 megabytes:

CREATE TABLESPACE tabspace_5    DATAFILE 'diskb:tabspace_file3.dat' SIZE 500K REUSE   AUTOEXTEND ON NEXT 500K MAXSIZE 10M;

---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

创建实验使用缺省表空间:

Admin表

CREATE TABLESPACE TAdmin 

DATAFILE 'C:\oraclexe\oradata\XE\admin.dbf' SIZE 20M
DEFAULT STORAGE ( INITIAL 64K NEXT 64K MAXEXTENTS UNLIMITED PCTINCREASE 50 );

Teacher表

CREATE TABLESPACE TTeacher

DATAFILE 'C:\oraclexe\oradata\XE\teacher.dbf' SIZE 2M
DEFAULT STORAGE ( INITIAL 64K NEXT 64K MAXEXTENTS UNLIMITED PCTINCREASE 50 );

Student表

CREATE TABLESPACE TStudnet

DATAFILE 'C:\oraclexe\oradata\XE\student.dbf' SIZE 2M
DEFAULT STORAGE ( INITIAL 64K NEXT 64K MAXEXTENTS UNLIMITED PCTINCREASE 50 );

备注:

在Oracle中，命令和对象名称都是大小写不敏感的，因为Oracle在处理语句时，将所有的名称和命令全部转化为大写。

但是对于字符串中的字符，无论是比较还是排序，都是大小写敏感的。这在Oracle是默认方式，但不是唯一的方式。

3.角色和用户

（1）创建profile:

备注：

http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/statements_6010.htm#i2065930

http://psoug.org/reference/profiles.html

Admin:

CREATE PROFILE admin_profile LIMIT 

 SESSIONS_PER_USER UNLIMITED 

 CPU_PER_SESSION UNLIMITED

 FAILED_LOGIN_ATTEMPTS 3

 PASSWORD_GRACE_TIME 30

 PASSWORD_REUSE_MAX 100;

teacher:

CREATE PROFILE teacher_profile LIMIT

 SESSIONS_PER_USER 2 

 IDLE_TIME 30 

 CONNECT_TIME 60;

student:

CREATE PROFILE student_profile LIMIT

 SESSIONS_PER_USER 1 

 IDLE_TIME 30 

 CONNECT_TIME 60;

（2）创建用户

备注：

http://download.oracle.com/docs/cd/B19306_01/network.102/b14266/admusers.htm

系统管理员（admin）:

CREATE USER admin  IDENTIFIED BY admin 

 DEFAULT TABLESPACE TAdmin 

 QUOTA 500K ON users 

 PROFILE admin_profile;

 GRANT DBA TO admin; 

alter user admin profile admin_profile; 

教师（teacher）:

CREATE USER teacher1 IDENTIFIED BYteacher1 

 DEFAULT TABLESPACE TTeacher 

 QUOTA 500K ON users 

 PROFILE teacher_profile;

 GRANT CONNECT TO teacher1;

alter user teacher1 profile teacher_profile; 

alter user teacher1 default tablespace TTeacher;

学生（student）:

CREATE USER wuxueyi  IDENTIFIED BY wuxueyi 

 DEFAULT TABLESPACE TStudent 

 QUOTA 500K ON users 

 PROFILE student_profile;

 GRANT CONNECT TO wuxueyi;

alter userwuxueyi profilestudent_profile; 

（3）建表

1.

CREATE TABLE TeacherInfo
(
  teacherID Char(18) NOT NULL,
  teacherName Char(20) NOT NULL,
  DepartmentCode Char(5) NOT NULL,
  SEX Char(1) NOT NULL,
  teacherBrief Varchar2(255) NULL,
  Tel Char(20) NULL,
  Fax Char(20) NULL,
  CONSTRAINT pk_TeacherInfo_Id  PRIMARY KEY (teacherID)
);

2.

CREATE TABLE CourseInfo
(
  courseID Varchar2(8) NOT NULL,
  courseName Varchar2(32) NOT NULL,
  DepartmentCode Varchar2(5) NOT NULL,
  credit Number(3,1) NOT NULL,
  Resume Varchar2(255) NULL,
  CONSTRAINT pk_CourseInfo_Id PRIMARY KEY (courseID)

);

3.

CREATE TABLE DepartmentInfo
(
  departmentCode Char(4) NOT NULL,
  departmentName Char(32) NOT NULL,
  CONSTRAINT pk_DepartmentInfo_Id PRIMARY KEY (departmentCode)
);

4.

CREATE TABLE SelectiveInfo
(
  studentID Char(18) NOT NULL,
  classID Number(10,0) NOT NULL,
  grade Number(4,2) NULL,
  selectiveDate Date NOT NULL,
  status Int,
  CONSTRAINT pk_SelectiveInfo_Id PRIMARY KEY (studentID,classID)
);

5.

CREATE TABLE ClassInfo
(
  classID Number(10,0) NOT NULL,
  className Varchar2(32),
  teacherID Char(18) NOT NULL,
  courseID Char(8) NOT NULL,
  classYear Char(6) NULL,
  ClassTimePlace VarChar2(128) NULL,
  CONSTRAINT pk_ClassInfo_Id PRIMARY KEY(classID),

  CONSTRAINT FK_ClassInfo_TeacherInfo FOREIGN KEY (teacherID) REFERENCES TeacherInfo(teacherID),

  CONSTRAINT FK_ClassInfo_ClassInfo FOREIGN KEY (classID) REFERENCES ClassInfo(classID),

  CONSTRAINT FK_ClassInfo_DepartmentInfo FOREIGN KEY (departmentCode) REFERENCES DepartmentInfo(DepartmentCode) 

);

6.

CREATE TABLE StudentInfo
(
  studentID Char(18) NOT NULL,
  studentName Char(40) NOT NULL,
  sex Char(2) NULL,
  departmentCode Char(5) NOT NULL,
  Address Varchar2(200),
  Tel Varchar2(20),
  Fax Varchar2(20),
  CONSTRAINT pk_StudentInfo_Id PRIMARY KEY (courseID),
  CONSTRAINT FK_StudentInfo_DepartmentInfo FOREIGN KEY (departmentCode) REFERENCES   DepartmentInfo(departmentCode) 
);

（4）分配权限

备注：

http://download.oracle.com/docs/cd/B10501_01/server.920/a96521/privs.htm
http://www.adp-gmbh.ch/ora/sql/grant.html

http://www.techonthenet.com/oracle/grant_revoke.php

http://www.orafaq.com/wiki/Oracle_database_Security_FAQ

http://psoug.org/reference/roles.html

并不是所有的DML操作都支持授权到列，只有INSERT、UPDATE和REFERENCES权限支持到列：

教师权限：

grant select on studentinfo to teacher1

grant select on teacherinfo to teacher1

grant update on teacherinfo to teacher1

grant select on courseinfo to teacher1

grant select, update on selectiveInfo to teacher1

grant select on DepartmentInfo to teacher1

学生权限：

grant select on StudentInfo to wuxueyi

grant select on CourseInfo to wuxueyi

grant select on SelectiveInfo to wuxueyi

二.权限管理及安全信息查询

1)系统管理员admin登录选课系统数据库，登录三次失败，第四次输入正确的密码进行登录，会出现什么情况？为什么？

2)使teacher1具有创建数据表系统权限，以用户teacher1登录，创建一个部门代码表DepartmentInfo作为系统管理员创建的选课信息表的备份；

分配权限：

grant create table to teacher1

grant resource to teacher1

建表：

CREATE TABLE DepartmentInfo
(
  departmentCode Char(4) NOT NULL,
  departmentName Char(32) NOT NULL,
  CONSTRAINT pk_DepartmentInfo_Id PRIMARY KEY (departmentCode)
);

3)用户teacher1登录，分配学生用户（你的名字的拼音）查看teacher1. DepartmentInfo记录的权限；学生用户登录，查看teacher1. DepartmentInfo中的记录；

grant select on DepartmentInfo to wuxueyi;

wuxueyi帐号登录：

select * from teacher1.departmentInfo

4)取消用户teacher1创建数据表权限；取消学生用户查看teacher1. DepartmentInfo记录的权限；

revoke select on DepartmentInfo from wuxueyi;

revoke create tablefrom teacher1

revoke resourcefrom teacher1

5)从数据库中删除用户teacher1

drop user teacher1 cascade

6)创建密码验证函数，要求密码至少包含大小写字母、数字和特殊字符（@#$%）中的一个，并且不少于8位。然后将该密码验证函数应用于学生用户并验证效果（可选）

三.数据库审计

备注：

http://www.th7.cn/Article/sj/ora/200910/367566.html

http://rake.itpub.net/post/4038/24963

http://download.oracle.com/docs/cd/B10501_01/server.920/a96521/audit.htm#1108

首先打开审计

Audit_trail：
None：是默认值，不做审计；
DB：将audit trail 记录在数据库的审计相关表中，如aud$，审计的结果只有连接信息；
DB,Extended：这样审计结果里面除了连接信息还包含了当时执行的具体语句；
OS：将audit trail 记录在操作系统文件中，文件名由audit_file_dest参数指定；
XML：10g里新增的。

1) 审计所有查询选课消息表（admin.selectiveInfo）的操作；

   AUDIT SELECT ON admin.selectiveInfo 

2) 审计用ADMIN创建数据表的操作；

  Audit create any table by admin

3) 审计所有数据更新操作；

     Audit update any table by access