Transcript Chapter 5 - Integrity And Security

Chapter 5 : Integrity And Security
Domain Constraints
 Referential Integrity
 Security
 Triggers
 Authorization
 Authorization in SQL
 Views
 Assertions

Triggers

A trigger is a statement that is executed
automatically by the system as a side effect
of a modification to the database. Triggers
are fired implicitly and not called by user like
procedure and function
To design a trigger mechanism, we must:


Specify the conditions under which the trigger is
to be executed
Specify the actions to be taken when the trigger
executes
Use of Database Triggers




To access table during regular business
hours or on predetermined weekdays
To keep track of modification of data along
with the user name, the operation
performed and the time when the
operation was performed
To prevent invalid transaction
Enforces complex security authorization
Database Triggers Vs Procedures
Triggers do not accept parameters
whereas procedures can have
parameters
 Triggers are executed (fired)
automatically upon modification of the
table or it’s data whereas to execute a
procedure it has to be explicitly called by
the user

How To apply Database Triggers
A trigger has three parts



A triggering event or statement – An SQL
statement that causes a trigger to be fired. It can be
insert, update or delete statement for a specific
table
A trigger restriction – It specifies a Boolean
expression that must be TRUE for the trigger to fire.
It conditionally controls the execution of trigger.
Specified using WHEN clause
Trigger Action – PL/SQL block to be executed
when triggering statement is encountered and trigger
restriction evaluates to TRUE
Types of Triggers
Row Triggers – A row trigger is fired
each time a row in the table is affected
by triggering statement. If the triggering
statement affects no rows, the trigger is
not executed at all
 Statement Triggers – A statement
trigger is fired once on behalf of the
triggering statement, independent of
number of rows affected by the
triggering statement

Before Vs After Triggers
When defining a trigger it is necessary to specify
the trigger timing i. e. when trigger action is to
be executed in relation to the triggering
Statement. Before and After apply to both row
and statement trigger
 Before Triggers – Trigger action is executed
before triggering statement
 After Triggers – Trigger action is executed
after triggering statement
Creating A Trigger
CREATE OR REPLACE TRIGGER [ schema. ]
< trigger_name >
{ BEFORE, AFTER }
{ DELETE, INSERT, UPDATE [ OF column1, . . . ]
ON [schema.] < table_name >
[ REFERENCING { OLD AS old, NEW AS new} ]
[ FOR EACH ROW [ WHEN condition ] ]
DECLARE
<variable declarations>;
<constant declarations>;
BEGIN
< PL/SQL sub-program body >;
Exception
< exception PL/SQL block >;
End;
Trigger Example
CREATE OR REPLACE TRIGGER t_Audit_trail
BEFORE DELETE OR UPDATE ON Customer
FOR EACH ROW
DECLARE
oper varchar2(8);
BEGIN
If updating then
oper :=‘Update’
end if;
If deleting then
oper :=‘Delete’
end if;
insert into audit_cust values (:OLD.custno, :OLD.fname,
:OLD.lname, :OLD.address, oper, user, sysdate);
End;
Security Management
Granting And Revoking Permissions
The permissions or rights that allow user
to use some of or all of resources on the
server are called Privileges
 Granting of Privileges - Objects that are
created by a user are owned and controlled by
that user. If a user want to access any of the
objects belonging to another user, the owner
of the object will have to give permissions for
such access
 Revoking of Privileges – Privileges once given
can be taken back by the owner of the object
Granting Privileges
GRANT statement provides various types of
access to database objects such as tables,
views, sequences and so on. A user can grant all
or only specific object privileges
GRANT <object_privileges>
ON <object_name>
To <user_name>
[WITH GRANT OPTION];
WITH GRANT OPTION – Allows the grantee to in turn grant
object privileges to other users
Object Privileges






ALTER – Allows grantee to change the table definition
with the ALTER TABLE command
DELETE – Allows grantee to remove records from the
table with DELETE command
INDEX – Allows grantee to create an index on the
table with the CREATE INDEX command
INSERT – Allows grantee to add records to the table
with the INSERT command
SELECT – Allows grantee to query the table with
SELETE command
UPDATE – Allows grantee to modify the records in
the table with the UPDATE command
Granting Privileges



All permissions to secompa user on employee object
GRANT ALL ON employee TO secompa
Give secompb user permission to only view and
modify the records in the table client_master
GRANT SELECT, UPDATE ON client_master TO
secompb
Give secompa user all data manipulation permissions
on table salesman_master along with grant permission
on the same table to other users
GRANT ALL ON salesman_master TO secompa
WITH GRANT OPTION
Revoking Privileges
REVOKE statement is used to deny the
grant given on an object
REVOKE <object_privileges>
ON <object_name>
FROM <user_name>
 REVOKE is used to revoke object privileges
that the user previously granted directly to the
grantee
 REVOKE is not used to revoke the privileges
granted through the operating system
Revoking Privileges


Take back all permissions on employee object from
secompa user
REVOKE ALL ON employee FROM secompa
Take back view and modify permission from secompb
user on table client_master
REVOKE SELECT, UPDATE ON client_master
FROM secompb
VIEWS




Effective way to meet security requirement
Virtual relation / table
A view is mapped to a SELECT statement.
A table on which a view is based is described
in the FROM clause and known as BASE
TABLE / RELATION
SELECT clause consist of sub-set of
columns from BASE table / relation
VIEWS …




DMBS stores definition about a VIEW in the
system catalog, Data Dictionary
VIEW holds no data at all until a call to view
is made
DBMS treats VIEW like a BASE table /
relation
VIEW can be queried same as BASE table
VIEW …

READ ONLY VIEW
VIEW used only for looking at table data i. e retrieval
of data (SELECT) not for manipulation of data
(INSERT, UPDATE, DELETE)

UPDATABLE VIEW
VIEW used for data retrieval as well as INSERT,
UPDATE, DELETE
Why VIEWs Are Created / Benefits


Data Security
To keep data redundancy to the minimum
possible. It reduces redundant data on the
HDD to a very large extent
VIEW - Limitations / drawbacks

VIEWs will run slower than QUERY
Creating VIEW
Syntax :
CREATE VIEW <view_name> AS
SELECT A1, A2, …, An
FROM <table_name>
WHERE P
GROUP BY <group criteria>
HAVING P
Note : ORDER BY clause can not be used
while creating VIEWs
Querying VIEWs
Syntax :
SELECT A1, A2, …, Ak
FROM <view_name>
WHERE P
GROUP BY <group_criteria>
HAVING P
ORDER BY A1, A2, …, Ak
Where ,
A1, A2, …, Ak are attributes of a relation /
table
P predicate
Updatable Views

For a view to be updatable, it should meet
following criteria



Views must be defined from single table
To INSERT records using VIEWs, all the
PRIMARY KEY & NOT NULL columns must be
included in the view definition
UPDATE & DELETE records can be done using
Views even if the all PRIMARY KEY and NOT
NULL columns are excluded from view definition
Restrictions on Updatable Views

VIEW definition must not include






Aggregate functions
DISTINCT, GROUP BY or HAVING Clause
Sub-queries
Constants, String or Value expressions like
SELL_PRICE * 0.15
UNION, INTERSECT or MINUS clause
If a view is defined from another view, the second
view should be updatable
Destroying a VIEW
DROP VIEW command is used to remove a
VIEW from database
Syntax :
DROP VIEW <view_name>
Assertions



An assertion is a predicate expressing a
condition that we wish the database always to
satisfy
An assertion in SQL takes the form
create assertion <assertion-name> check
<predicate>
When an assertion is made, the system tests it
for validity, and tests it again on every update
that may violate the assertion
 This testing may introduce a significant
amount of overhead; hence assertions should
be used with great care
Assertions

The sum of all loan amounts for each branch
must be less than the sum of all account
balances at the branch.
create assertion sum-constraint check
(not exists (select * from branch
where (select sum(amount)
from loan where loan.branch-
name = branch.branch-name)
>= (select sum(amount) from
account where
loan.branch-name =
branch.branch-name)))