COMP102: Introduction to Databases, 21 & 22
Dr Muhammad Sulaiman Khan
Department of Computer Science
University of Liverpool
U.K.
01 April, 2011
Dr Muhammad Sulaiman Khan COMP102: Introduction to Databases, 21 & 22
SQL, part 8
Dr Muhammad Sulaiman Khan COMP102: Introduction to Databases, 21 & 22
Specific topics for today:
Triggers, contd
More about constraints:
Assertions
Security Issues
Authentication
Authorisation
Dr Muhammad Sulaiman Khan COMP102: Introduction to Databases, 21 & 22
INSTEAD-OF triggers
Allows the word BEFORE or AFTER (the event statement) to be
replaced by INSTEAD OF.
The meaning is that when an event awakens a trigger, the
action of the trigger is done instead of the event itself.
This type of trigger is available in some commercial RDBMSs
(not in MySQL).
This construct is not very useful when the trigger is on a
stored (base) table, but it is very useful when used on a view.
The reason is that we cannot really modify views (they do not
physically exist).
An instead-of trigger intercepts attempts to modify the view
and in its place performs whatever action the database
designer deems appropriate.
Dr Muhammad Sulaiman Khan COMP102: Introduction to Databases, 21 & 22
Example: INSTEAD-OF trigger
Consider the schema:
Movie(title, year, length, studioName, producerCertNo)
and the following view:
CREATE VIEW ParamountMovie AS
SELECT title, year FROM Movie
WHERE studioName = ’Paramount’;
When attempting to update this view by, e.g., inserting the title and year
of a new movie the system is not able to properly figure out the
studioName and will insert studioName = NULL in the Movie tuple.
We’ll define an instead-of trigger to prevent this situation (note that
WHEN not present):
CREATE TRIGGER ParamountInsert
INSTEAD OF INSERT ON ParamountMovie
REFERENCING NEW ROW AS NewRow
FOR EACH ROW
INSERT INTO Movie (title, year, studioName)
VALUES ([Link], [Link], ’Paramount’);
Dr Muhammad Sulaiman Khan COMP102: Introduction to Databases, 21 & 22
Remark: Triggers in MySQL
The REFERENCING ... clause is not explicitly present in
MySQL.
Instead, the old and new (modified) table can be referred to
by using reserved words OLD and NEW.
When specifying the event UPDATE MySQL does not allow for
UPDATE OF (columns) ON table, but only UPDATE ON
table.
Example: The trigger from the previous slide in MySQL (note
that INSTEAD OF is not available in MySQL):
CREATE TRIGGER ParamountInsert
INSTEAD OF INSERT ON ParamountMovie
FOR EACH ROW
INSERT INTO Movie (title, year, studioName)
VALUES ([Link], [Link], ’Paramount’);
Dr Muhammad Sulaiman Khan COMP102: Introduction to Databases, 21 & 22
Assertions in SQL
It is a boolean-valued SQL expression that must be true at all
times.
Any database modification that would change the assertion to
false will be rejected.
General form of assertion:
CREATE ASSERTION <name> CHECK (<condition>)
where name is the name of assertion we may use to refer to it,
and condition is any predicate, like those used after WHERE.
We may drop the assertion by executing:
DROP ASSERTION <name>
Assertions are present in the SQL standard, not in MySQL.
Dr Muhammad Sulaiman Khan COMP102: Introduction to Databases, 21 & 22
Example: Assertion-1
Consider the following schema:
MovieProducer(name, address, certNo, netWorth)
Studio(name, address, presidentCertNo)
We’ll define an assertion that prevents a movie producer to become the
president of a studio unless her netWorth is at least $10, 000, 000:
CREATE ASSERTION RichPresident
( NOT EXISTS
( SELECT *
FROM Studio, MovieProducer
WHERE presidentCertNo = certNo
AND netWorth < 10000000 )
);
Note the difference between table/column CHECK constraints and assertions: the
assertion above operates on two tables, whereas a table/column CHECK
constraint can only be defined for a single table.
Incidentally, the above constraint can also be expressed in terms of
table/column CHECK constraints.
Dr Muhammad Sulaiman Khan COMP102: Introduction to Databases, 21 & 22
Example: Assertion from previous slide as table constraints
Consider the previous schema:
MovieProducer(name, address, certNo, netWorth)
Studio(name, address, presidentCertNo)
The assertion from the previous slide can be expressed by the table
CHECK constraints when defining the table(s):
CREATE TABLE Studio (
name CHAR(30) PRIMARY KEY,
address VARCHAR(255),
presidentCertNo INT REFERENCES MovieProducer (certNo),
CHECK ( presidentCertNo NOT IN
( SELECT certNo FROM MovieProducer
WHERE netWorth < 10000000 ) ) );
Note, however, that the constraint above will only be checked when a change to
its table, Studio, occurs.
Unlike the previous assertion, it will not catch the situation where the net worth
of some studio president from MovieProducer dropped below $10, 000, 000.
To get the full effect of the previous assertion, we need to add another
constraint to the declaration of table MovieProducer, saying that netWorth be
at least $10, 000, 000 if that producer is also the president of a studio.
Dr Muhammad Sulaiman Khan COMP102: Introduction to Databases, 21 & 22
Example: Assertion-2
Consider the schema:
Movie(title, year, length, studioName, producerCertNo)
We’ll define an assertion that says that the total length of all movies by a
given studion shall not exceed 10, 000 minutes:
CREATE ASSERTION SumLength
CHECK ( 10000 >= ALL
( SELECT SUM(length) FROM Movie GROUP BY studioName )
);
This constraints operates on single table Movie; it can be defined as
a table CHECK constraint in the declaration of the Movie table.
Important: If CHECK ( 10000 >= ALL ( SELECT SUM(length)
FROM Movie GROUP BY studioName )) is present as a table
constraint, then it is NOT checked on deletion of a row from Movie
(incidentally, this is OK here), but checked if defined as assertion!
Note: Table/column CHECK constraints work as we want if they
involve attributes of the table explicitly, but not if they involve
subqueries.
Dr Muhammad Sulaiman Khan COMP102: Introduction to Databases, 21 & 22
Comparison of constraints
The principal differences among the column CHECK constraints,
table CHECK constraints and assertions:
Dr Muhammad Sulaiman Khan COMP102: Introduction to Databases, 21 & 22
Security
As a RDBMS is a server that can accept many different
clients, security of the data is very important to consider.
Who should be allowed to view your data? Who should be
allowed to create, modify or delete it?
The important questions are:
How can you tell one user from another at the database level ?
How can you define what they can do ?
Dr Muhammad Sulaiman Khan COMP102: Introduction to Databases, 21 & 22
Motivating Example
In a hospital there might be many different types of database
user:
Hospital Administrator
Database Administrator
Doctor
Patient
Secretary
Each type might have many instances (people). (e.g., 3
DBAs, 20 secretaries, 100 doctors,...)
Dr Muhammad Sulaiman Khan COMP102: Introduction to Databases, 21 & 22
Motivating Example
Hospital Administrator: Needs access to aggregate data.
Database Administrator: Needs access to all data.
Doctor: Needs access to individual patients (at least).
Patient: Needs access to own data.
Secretary: Needs access to subset of all patients’ data.
Dr Muhammad Sulaiman Khan COMP102: Introduction to Databases, 21 & 22
Motivating Example
So we need to determine if a user is a secretary, and if so
allow them to change some of the patients’ data (e.g.,
address, telephone number) but not all of it, and not even
view all of it (e.g., private medical details).
First we need to establish an identity for the current user of
the database.
Secondly we need to find which type of user they are.
Then we need to ensure that they can only perform the
actions they should be allowed to do.
Dr Muhammad Sulaiman Khan COMP102: Introduction to Databases, 21 & 22
Authentication vs authorisation
We make the distinction between:
Authentication: Determining the identify of a user.
Authorisation: Whether the user is allowed to perform an
action.
First we need to authenticate the user.
This is done via a user name and some means of verifying that the
person should be using that name.
Dr Muhammad Sulaiman Khan COMP102: Introduction to Databases, 21 & 22
Authentication
Clients will send the username that they want to authenticate as to
the database. Much like when logging into any system.
However the verification can happen in several different ways (see
MySQL Manual):
md5: Client sends an md5 encrypted password.
crypt: Client sends a crypt() encoded password.
password: Client sends an unencrypted password.
krb5: Use Kerberos 5 to authenticate.
ident: Use the OS to authenticate.
pam: Use the Pluggable Authentication Module service to
authenticate.
Dr Muhammad Sulaiman Khan COMP102: Introduction to Databases, 21 & 22
Typical multi-user computer environment
Dr Muhammad Sulaiman Khan COMP102: Introduction to Databases, 21 & 22
Authentication: Encryption
Encryption: Encoding the data by a special algorithm that renders
the data unreadable by any program without the decryption key.
One of the most commonly used encryption/decryption
algorithms is RSA (Rivest, Shamir, Adleman).
E.g., MySQL can use the OpenSSL interface to protect the
communication between the cliens and the SQL Server by
using RSA algorithm.
We will first explain the idea behind the Public Key
Cryptography.
Dr Muhammad Sulaiman Khan COMP102: Introduction to Databases, 21 & 22
Public Key Cryptography
Dr Muhammad Sulaiman Khan COMP102: Introduction to Databases, 21 & 22
Authentication: RSA Encryption/Decryption
Dr Muhammad Sulaiman Khan COMP102: Introduction to Databases, 21 & 22
RSA Algorithm Security
Its security comes from the (believed/experienced) computational
difficulty of factoring large numbers.
To be secure, very large numbers must be used for p and q – 100
decimal digits at the very least.
Factoring large numbers and quantum computing.
Dr Muhammad Sulaiman Khan COMP102: Introduction to Databases, 21 & 22
Example: RSA Key generation
Dr Muhammad Sulaiman Khan COMP102: Introduction to Databases, 21 & 22
Example: RSA Encryption/Decryption
Dr Muhammad Sulaiman Khan COMP102: Introduction to Databases, 21 & 22
Authentication
Different programming interfaces will have different ways to
pass this information through to the DBMS.
If the authentication fails, then the server will deny access to
the client.
Typically this will be logged, so that an administrator can
check up on failed or successful but unexpected attempts to
access the data.
But how do we talk about these users within the DBMS ?
Dr Muhammad Sulaiman Khan COMP102: Introduction to Databases, 21 & 22
Users in PostgreSQL
RDBMS PostgreSQL uses a system of roles, which model
both users and groups of users.
To create a new role (or user):
CREATE ROLE name [options];
To delete a role (or user):
DROP ROLE name;
To view current roles:
SELECT * FROM pg roles;
... where name is the name of the user or group you want to
create or delete.
Dr Muhammad Sulaiman Khan COMP102: Introduction to Databases, 21 & 22
Roles (PostgreSQL)
CREATE ROLE name [options];
Possible options:
CREATEDB: If included, role can create databases.
CREATEROLE: Role can create new roles.
INHERIT: Role will inherit options from other roles.
LOGIN: Role is allowed to login to the database.
PASSWORD ’pw’: Password for logging in.
IN ROLE name: Role should be a member of the named role.
Dr Muhammad Sulaiman Khan COMP102: Introduction to Databases, 21 & 22
Roles (PostgreSQL)
So for our hospital example, we might want:
CREATE ROLE secretary;
CREATE ROLE Judith LOGIN PASSWORD ’...’ IN ROLE
secretary;
Now we can have Judith log in, and she’s known to be a
secretary. Excellent! So our authentication part (in
PostgreSQL) is done.
Dr Muhammad Sulaiman Khan COMP102: Introduction to Databases, 21 & 22
Authentication and users (MySQL)
This feature is modest in MySQL 5.0: it only supports users,
but not groups of users (roles).
To create a user in MySQL:
CREATE USER <user> [IDENTIFIED BY [PASSWORD]
<password>]
To drop a user in MySQL:
DROP USER <user>
Dr Muhammad Sulaiman Khan COMP102: Introduction to Databases, 21 & 22
Authorisation
We know which user is performing the operation (and in
PostgreSQL we know which group of users they’re in).
Now we need to be able to assign various abilities to those
users for different tables within the database, so that
secretaries only get a limited view but doctors can see the
larger picture.
This is done with a system of privileges.
Dr Muhammad Sulaiman Khan COMP102: Introduction to Databases, 21 & 22
Authorisation in PostgreSQL
There is a privilege for each SQL command.
These can be assigned or revoked from any user/role, for any
table.
Add privileges:
GRANT privs ON table TO role;
Remove privileges:
REVOKE privs ON table FROM role;
Dr Muhammad Sulaiman Khan COMP102: Introduction to Databases, 21 & 22
Privileges in PostgreSQL
SELECT: Able to select rows from table.
INSERT: Able to insert rows into table.
UPDATE: Able to update existing rows in table.
DELETE: Able to delete rows from table.
RULE: Able to create a new rule for the table.
REFERENCES: Able to create foreign keys.
TRIGGER: Able to create triggers for table.
CREATE: Able to create new tables in database.
EXECUTE: Able to execute functions.
ALL PRIVILEGES: Able to do anything.
Dr Muhammad Sulaiman Khan COMP102: Introduction to Databases, 21 & 22
Example: Privileges in PostgreSQL
Assume that we have the following (simplistic) tables:
Patient(id, name, address, phone, doctorId, diagnosis)
Doctor(id, name, office, extension)
Diagnosis(id, patientId, doctorId, name, severity)
We would want to allow a secretary to change the details in
the patient table, view the doctor table, and not be able to do
anything with the diagnosis table:
GRANT ALL PRIVILEGES ON Patient TO secretary;
GRANT SELECT ON Doctor TO secretary;
REVOKE ALL PRIVILEGES ON Diagnosis FROM
secretary;
Many of the privileges can be specified down to the column, rather
than the table.
For example, if some sensitive data was stored in table Patient in
the diagnosis column, we could do:
REVOKE SELECT (diagnosis) ON Patient FROM secretary;
Dr Muhammad Sulaiman Khan COMP102: Introduction to Databases, 21 & 22
Authorisation in MySQL
There is a privilege for each SQL command.
These can be assigned or revoked from any user, for any table.
Dr Muhammad Sulaiman Khan COMP102: Introduction to Databases, 21 & 22
Authorisation in MySQL
To grant privileges in MySQL:
GRANT priv type [(column list)]
ON { tbl name | *.* | db name.* }
TO user [IDENTIFIED BY [PASSWORD] ’password’]
[WITH with option [with option] ...]
with option =
GRANT OPTION
| MAX-QUERIES-PER-HOUR count
| MAX-UPDATES-PER-HOUR count
| MAX-CONNECTIONS-PER-HOUR count
| MAX-USER-CONNECTIONS count
Dr Muhammad Sulaiman Khan COMP102: Introduction to Databases, 21 & 22
Privileges in MySQL
Privilege types priv type in MySQL:
ALTER: Enables use of ALTER TABLE.
SELECT: Able to select rows from table.
INSERT: Able to insert rows into table.
UPDATE: Able to update existing rows in table.
DELETE: Able to delete rows from table.
DROP: Able to use DROP TABLE.
CREATE: Able to create new tables in database.
EXECUTE: Able to execute functions.
ALL [PRIVILEGES]: Able to do anything, except GRANT
OPTION.
GRANT OPTION: Able to grant privileges.
Dr Muhammad Sulaiman Khan COMP102: Introduction to Databases, 21 & 22
Example: Grant in MySQL
ON tbl name grants privileges to only table tbl name
ON db [Link] name grants privileges to only table
tbl name within database db name
GRANT ALL ON *.* TO ’user’@’somehost’; grants all
privileges to all databases on the SQL server to user ’user’
connecting from ’somehost’.
GRANT SELECT, INSERT ON [Link] TO
’someuser’@’somehost’; grants select and insert privileges
to user ’someuser’ on table mytbl within database mydb.
User ’user’ has to connect from machine ’somehost’.
GRANT SELECT (col1), INSERT (col1,col2) ON
[Link] TO ’someuser’@’localhost’; means that
user ’someuser’ when connecting from the local machine
can perform select on column col1 and insert into columns
col1 and col2, all within only table [Link].
Dr Muhammad Sulaiman Khan COMP102: Introduction to Databases, 21 & 22
Revoking privileges in MySQL
To grant privileges in MySQL:
REVOKE priv type [(column list)]
ON { tbl name | *.* | db name.* }
FROM user
The meaning of the respetive elements of REVOKE is the same as
for the GRANT statement.
Dr Muhammad Sulaiman Khan COMP102: Introduction to Databases, 21 & 22
Network Security
Relational database systems often have to listen for requests
from other machines over a network.
We can use network security to improve our database security,
for example with a firewall.
Limit the connections to known machines.
Does the database support encrypted traffic ?
Change the listening port from the default.
Dr Muhammad Sulaiman Khan COMP102: Introduction to Databases, 21 & 22
System Security
Relational databases run as any other application, and many of the
same security issues apply:
Run the RDBMS as a non privileged OS user (e.g., ’mysql’
not ’root’).
Change the name of the roles/users with privileges in the
database from ’root’, ’system’, ’administrator’ to something
less guessable.
Use strong passwords on machine and in database.
Dr Muhammad Sulaiman Khan COMP102: Introduction to Databases, 21 & 22