数据库（oracle的安全性和完整性控制）

1.

create user s2015214068u1 identified by 123456;
create user s2015214068u2 identified by 123456;
create user s2015214068u3 identified by 123456;
create user s2015214068u4 identified by 123456;
grant connect to s2015214068u1,s2015214068u2,s2015214068u3,s2015214068u4;


2.
grant select on student to s2015214068u1;

切换用户
1）select * from s2015214068.student;
2）select sname,sno,ssex from s2015214068.student where sname like '张%';
3）select sname,sno from s2015214068.student where sname like '.阳';

show user;/*显示用户名*/


3.
grant all on student  to s2015214068u2,s2015214068u3;
grant all on course  to s2015214068u2,s2015214068u3;
update  s2015214068.student  set sage=20 where sname='smith';

4.
grant update(sno) on student to s2015214068u4;
update  s2015214068.student  set sno='201521406' where sname='smith';


5.
先创建u5用户
create user s2015214068u5 identified by 123456;
grant insert on sc to s2015214068u5;
insert into s2015214068.sc values('200215122','1',90);

6.
grant select on sc to public;
1)select sno,grade from s2015214068.sc where cno='3' order by sno desc;
2)select cno,count(sno) from s2015214068.sc group by cno;


二

1.
revoke update on student from s2015214068u2;	

2.
revoke select on sc from public;

3.
revoke insert on sc from s2015214068u5;

4.
1.对于s2015214068u2用户，输入修改学号指令：
update  s2015214068.student  set sno='201521406' where sname='smith';
提示：SQL 错误: ORA-01031: 权限不足

2.提示：表或视图不存在
3.提示：表或视图不存在

三


1.
create role gp;

2.
grant select,update,insert on student to gp;

3.
grant gp to s2015214068u1;

4.
登录s2015214068u1
均具有相应权限

四：

1.
create table Teacher
(Tno varchar(9) primary key,
Tname varchar(9) unique);

2.
create table Student
(Sno varchar(4) check(sno between 9000 and 9999),
sage int constraint a1 check  (sage<29),
ssex varchar(2) check (ssex in('男','女') ),
sname varchar(10) not null
);


3.
 alter table Student drop constraint a1;
 alter table Student add constraint a1 check(sage<40);

4.

4.create table course1(
cno varchar2(4) constraint pk_c primary key,
cname varchar2(10),
tno varchar2(10) constraint fk_c_t references teacher(tno) On Delete Set NULL,
credit number(3) check (credit<=7)
);


5.create table sc1(
cno varchar2(4) REFERENCES course1(cno) On Delete Cascade,
sno char(10)REFERENCES stu(sno) On Delete Cascade,
grade number(3),
primary key(cno,sno)
);