Lecture 04.Advanced SQL

Integrity Constraint

• not null (主码不可为空)
• unique
• check ( <predicate> )

CREATE table branch2(branch_name varchar(30) primary key,
                     branch_city varchar(30),
                     assets integer not null,
                     check (assets >= 100) );

Domain Constraint

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

Create table course
	(...
	foreign key (dept_name) references department
		on delete cascade
		on update cascade,
	...);

Assertion

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)

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

<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;

drop trigger <trigger_name>

Authorization

• Security Specification

GRANT <privilege list> on <table/view>
TO <user list>

<user list>: user-ids / public / a role

• Privilege

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

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

REVOKE <privilege list> ON <table/view>
FROM <user list> [restrict/cascade]

restrict：仅撤回用户本人

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

Audit Trails

log all changes to the database

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

<st-opt>: table, view, role, index

NOAUDIT… 取消审计

by<users>缺省时对所有用户审计

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

• Audit in oracle:

AUDIT <obj-opt> ON <obj>/DEFAULT [by session / by access][whenever successful / whenever not successful]

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

ON <obj> 指出审计对象表、视图名。

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

取消审计：NOAUDIT …