【DBS】Lecture 04. Advanced SQL

发表于 | 分类于 ， | | 阅读次数:

Integrity Constraint

  • not null (主码不可为空)
  • unique
  • check ( \ )
1
2
3
4
CREATE table branch2(branch_name varchar(30) primary key,
                     branch_city varchar(30),
                     assets integer not null,
                     check (assets >= 100) );

Domain Constraint

1
2
create domain hourly-wage numeric(5,2);
constraint value-test check (value >= 4.00);

Referential Integrity

Account (account-number, branch-name, balance) — 参照关系

Branch (branch-name, branch-city, assets) — 被参照关系

cascading action

1
2
3
4
5
6
Create table course
	(...
	foreign key (dept_name) references department
		on delete cascade
		on update cascade,
	...);

Assertion

1
2
CREATE assertion <assertion-name>
CHECK <predicate>;

But SQL does not provide a construct for asserting: for all X, P (X)

So it is achieved in a round -about fashion, using: not exists X, such that not P (X)

1
2
3
4
5
6
7
8
9
CREATE assertion credits_earned_constraint check
  (not exists  (select ID
                from student
                where tot_cred <> (select sum (credits) 
                                   from takes natural join course 
                                   where student. ID = takes. ID
                                   and grade is not null and grade <> 'F')
                )
   );


Trigger

trigger

1
2
3
4
5
6
7
<CREATE/REPLACE> trigger secure_ student before <insert/delete/update> on student
	Begin
		IF(to_char(sysdate, 'DY') in（'星期六'，'星期日'))
			OR(to_char(sysdate, 'HH24') NOT Between 8 and 17) 
		THEN raise_ application_error(-20506, '你只能在上班时间修改数据');
		END IF;
	END;
1
drop trigger <trigger_name>

Authorization

  • Security Specification
1
2
GRANT <privilege list> on <table/view>
TO <user list>

\: user-ids / public / a role

  • Privilege
1
GRANT select,insert ON branch TO U1,U2,U3

select / insert / update / delete / references / all privileges / all

  • Roles

    permit common privileges for a class of users can be specified just once

1
2
3
4
5
6
7
8
Create role teller; 
Create role manager; 
Grant select on branch to teller; 
Grant update (balance) on account to teller; 
Grant all privileges on account to manager; 
Grant teller to manager; 
Grant teller to alice, bob; 
Grant manager to avi;
  • Revoke
1
2
REVOKE <privilege list> ON <table/view>
FROM <user list> [restrict/cascade]

restrict：仅撤回用户本人

cascade：连锁撤回，同时撤回用户对外的授权

Audit Trails

log all changes to the database

1
AUDIT <st-opt>[BY <users>][BY session/access][whenever successful / whenever not seccessful]

\: table, view, role, index

NOAUDIT… 取消审计

by\缺省时对所有用户审计

by session：每次会话期间，相同类型需审计的SQL语句仅记录一次

  • Audit in oracle:
1
AUDIT <obj-opt> ON <obj>/DEFAULT [by session / by access][whenever successful / whenever not successful]

实体审计对所有用户起作用

ON \ 指出审计对象表、视图名。

ON DEFAULT 对其后创建的所有对象起作用。

取消审计：NOAUDIT …