Relational Database II
Transact SQL: Security and Authorization
PREPARED BY:
DR. YOUSSEF ROUMIEH
E-MAIL : [email protected]
Reference:
2
Introduction
The privileges are, for one or more users the possibility of using certain objects and
among these objects, certain SQL orders.
Privilege assignment
This is the GRANT SQL statement that allows to assign a privilege to different users on
different objects.
All users must communicate their username in order to access the database.
The user who creates a table is considered the owner of this table.
He/she has all the rights on this table and its contents. On the other hand the other
users have no rights on this table (neither reading nor modification) unless the owner
gives them explicitly these rights with a GRANT order.
3
Syntax SQL
GRANT privileges ON table/view TO user
[WITH GRANT OPTION]
The privileges that can be given are:
SELECT Reading right
INSERT Record insertion right
UPDATE Records modification right
UPDATE(col1, col2,…) Records modification right limited to certain
columns
DELETE Records deletion right
ALL All the above rights
4
GRANT privileges ON table/view TO user
[WITH GRANT OPTION]
The ON clause specifies the application of permissions to a specified
object (table or view).
The TO clause specifies the list of security accounts (users).
The WITH GRANT OPTION command specifies that the user can grant
permissions to other users.
5
Example 1:
Fouad allows to run SQL SELECT statements on Students table.
GRANT SELECT ON Students TO Fouad
Example 2:
Authorizes Mike and Fouad to modify the data by all update SQL commands (INSERT,
UPDATE, DELETE) but not to read them!
GRANT INSERT, UPDATE, DELETE ON Students
TO Mike, Fouad
Example 3:
Authorizes Maria to launch SQL SELECT statements on the table Students but also to
transmit to any other user the rights he has acquired in this order.
GRANT SELECT ON Students TO Maria
WITH GRANT OPTION
6
Example 4:
Allow Remie to run SQL SELECT, INSERT, DELETE statements on the Students table but also
to transmit to any other user the rights he has acquired in this order.
GRANT SELECT, INSERT, DELETE ON Students TO Remie
WITH GRANT OPTION
Example 5:
Allows present and future users to run SQL SELECT and UPDATE statements on the
Students table.
GRANT SELECT, UPDATE ON Students TO PUBLIC
7
Example 6 :
Remie launches the following order:
GRANT ALL PRIVILEGES ON Students TO Fouad
Which authorizes Fouad to launch on the table Students, the same orders SQL, than
those authorized with Remie (SELECT, INSERT, DELETE).
This is called rights inheritance, meaning that the user with these rights can again assign
them to one or more other users.
Example 7 :
Remie launches the following order:
GRANT UPDATE ON Students TO Fouad
This command will cause an error because Remie is not allowed to launch UPDATE
statements on the Students table and cannot transmit a right that it does not have!
8
Fine management of privileges
(Students table)
Is it possible to manage finer privileges than on the entire table or view? In
particular, can we manage privileges at the level of certain columns of a
table?
The answer is YES, but you have to use a little trick...
INSERT and UPDATE column privileges
Example 8:
GRANT UPDATE (Address, Phone)ON Students TO Sami
This order allows Sami to modify only the columns Address and Phone of the Students
table.
9
We can define the usable columns for an insertion order for a user:
Example 9
GRANT INSERT (First_Name, Last_Name, Address, Phone)ON Students TO Sami
This order allows Sami to insert a new row into the table, only by specifying the
listed columns. The problem is that in this list does not appear the key column ... In
other words, Sami can never insert anything at all, unless the key is calculated by a
trigger before insertion.
Note: In the case of inserting column privileges, it is necessary to include all
NOT NULL columns that have no default value clause, or filled with a
trigger before insertion.
10
SELECT column privileges
This type of privilege is not handled directly by an SQL statement. Indeed,
it is not possible to write:
Example 10:
GRANT SELECT (ID, First_Name, Last_Name) ON Students TO Sami
This order is not legal at the SQL level.
But a GRANT order can bring on a view!
You must create a view to manage Sami user selection privileges.
CREATE VIEW V_Students
As SELECT ID, First_Name, Last_Name FROM Students
GRANT SELECT ON V_Students TO Sami
11
Reference privileges (schema)
When linking tables to one another, the referential integrity mechanism is
often used to handle foreign keys.
Given the following relational schema:
Students(ID, First_Name, Last_Name, Address, Phone)
Courses(Code, Name, Credits)
Enroll(StudentID, CourseCode, EnrollDate, Grade)
12
Let’s see what happens on an example:
GRANT SELECT, INSERT, UPDATE, DELETE ON Students TO Malak
GRANT SELECT, INSERT, UPDATE, DELETE ON Enroll TO Malak
Malak will be able to select and delete without problems in all tables (Students,
Enroll).
She will be able to update the data and to insert without any problem in the
Student table.
However, she will sometimes encounter a database refusal to update the Enroll
table. Worse, she will be impossible for her to insert data in this last table...
What is the reason?
13
The Enroll table uses 2 tables in reference: Students and Courses.
But our user Malak has no privilege on the Courses table.
It will therefore be impossible for her to insert, as during the update, to specify a
value for the CourseCode column.
To be able to set a value for the CourseCode column when executing UPDATE
and INSERT statements, our user Malak must have an additional privilege
defined as follows:
GRANT REFERENCES (Code) ON Courses TO Malak
NOTE: The reference privilege does not focus exclusively on integrity constraints,
but on any constraint that references a column of an external table.
14
Revocation of privileges
A user who has granted a privilege can resume it with the REVOKE
command
SQL syntax:
REVOKE privilege ON table/view FROM user
Note: If you remove a privilege from a user, that privilege is automatically
removed from any other user to whom they granted that privilege.
15
Example 11 :
Removes the selection privilege from the Students table assigned to Fouad
REVOKE SELECT ON Students FROM Fouad
Example 12 :
Removes the insert and delete privileges from the Students table assigned to mike and
fouad in example 2, but not the update one (UPDATE).
REVOKE INSERT, DELETE ON Students FROM mike, fouad
Example 13 :
Removes the possibility for maria to transmit the selection privilege on the Students
table.
REVOKE GRANT OPTION FOR SELECT ON Students FROM maria
16
Revocation problematic
There are, however, some pitfalls in the use of the revocation mechanism.
We will show some of them using different examples.
Suppose whoever launches the orders is the user sami.
Unlike “systems” rights, privileges are cumulative. The same privilege can
thus be obtained on the same object several times from different users.
The privilege will be completely removed when all users who have given
this privilege have removed it.
Example 14:
GRANT SELECT ON T_CLIENT TO mike
WITH GRANT OPTION
GRANT SELECT ON T_CLIENT TO maria
17
It is now mike who is the user who will launch the following order:
GRANT SELECT ON T_CLIENT TO maria
Finally, sami takes again the hand to revoke thus:
REVOKE SELECT ON T_CLIENT FROM maria
Can maria select rows from the T_CLIENT table?
The answer is YES, because he still has a selection right coming from mike!
18
Here is another problem. The super user PUBLIC does not target anyone in
particular nor in general. Therefore, a particular privilege cannot be
removed from a given user even if privileges have been assigned to
“PUBLIC”.
GRANT SELECT ON T_CLIENT TO PUBLIC
REVOKE SLEECT ON T_CLIENT FROM nadine
In fact we must understand that the privilege management model in SQL
is based on graph theory and can quickly become complicated when
you want to manage multiple rights finely.
19
No simultaneous privileges on multiple objects
Thus, it is not possible to grant privileges to several objects simultaneously.
Example:
GRANT INSERT, DELETE ON Students, Courses, Enroll
TO mike, fouad
Such an order is syntactically incorrect because of the presence of several
tables...
The consequence is that the fine management of the rights of 5 users on a
database with a hundred tables translates into at least a hundred statements
to pass...
20
Access control model –
Role-based model
21
A role is a collection of rights (Rights are assigned to roles)
Roles are assigned to users (A user can receive multiple roles)
The user chooses a role that the/she wants to play
The privileges of the user are the privileges of the role(s) he/she activated(s)
Roles provide great flexibility in the expression of security regulations
Well adapted to commercial applications
1 professional function => 1 role
The purpose of the role is to collect privileges on objects and then move
users into one role or another.
22
Example: Medical Regulation
Patient
Login LastName FirstName Age Gender
Pfranck Franck Patricia 60 F
Mrobert Robert Martin 35 M
Diagnostic
Login Disease
Pfranck Ulcere
Mrobert Pneumonie
Mrobert Asthme
23
Security regulations
Physicians have permission to view and edit both tables without restriction.
The secretaries are allowed to see the patient table.
Nurses are allowed to see both tables.
Patients are allowed to see the information that concerns them.
Subjects: Physicians, nurses, secretaries and patients
Objects:
The 2 tables
The tuples of each table
Actions: SELECT, INSERT, UPDATE, DELETE
24
Creating Roles
CREATE ROLE Physician;
CREATE ROLE Nurse;
CREATE ROLE Secretary;
CREATE ROLE patient;
25
Rights associated with each role
GRANT ALL ON Patient TO Physician;
GRANT ALL ON Diagnostic TO Physician;
GRANT SELECT ON Patient TO Nurse;
GRANT SELECT ON Diagnostic TO Nurse;
GRANT SELECT ON Patient TO Secretary;
Problem for the last rule because the granularity level of the SQL security
model is the table and not the tuple
26
Creating 2 views
CREATE VIEW Vpatient AS
SELECT * FROM Patient where Login=user;
CREATE VIEW Vdiagnostic AS
SELECT * FROM Diagnostic where Login=user;
Rights associated with each view
GRANT SELECT ON Vpatient to patient;
GRANT SELECT ON Vdiagnostic to patient;
27
Create a login and a user
create login maria with password ='123'
create user maria for login maria with DEFAULT_SCHEMA=Medical
Assign roles to users
GRANT Physician TO …
GRANT Nurse TO …
GRANT Secretary TO …
GRANT Disease TO pfranck;
GRANT Disease TO mrobert;
alter role patient add member pfranck
28
Exercise
Given the Sales database which contains the following 5 tables.
Note that primary keys are underlined and foreign keys are in italics.
Client(ClientID, ClientName, Gender, City, Birthdate, Telephone)
Employee(EmployeeID, EmployeeFName, EmployeeLName, Gender, Salary,
Commission, HireDate)
Item(ItemID, Description, Price)
Invoice(InvoiceID, InvoiceDate, ClientID*, EmployeeID*)
InvoiceDetail(InvoiceID*, ItemID*, Quantity)
Client(ClientID, ClientName, Gender, City, Birthdate, Telephone)
Employee(EmployeeID, EmployeeFName, EmployeeLName,
29
Gender, Salary, Commission, HireDate)
Item(ItemID, Description, Price)
Invoice(InvoiceID, InvoiceDate, ClientID*, EmployeeID*)
InvoiceDetail(InvoiceID*, ItemID*, Quantity)
1. Create the Client role.
2. A client has the right to consult only the information that concerns himself.
How to affect this right to the created role?
3. Create the Detail role and grant all rights on the InvoiceDetail table to the
Detail role, so that Detail can transfer its privileges.
4. Which rights will cause a problem to the Detail role?
5. Solve the problem caused by the rights in Q4.
Client(ClientID, ClientName, Gender, City, Birthdate, Telephone)
Employee(EmployeeID, EmployeeFName, EmployeeLName,
30
Gender, Salary, Commission, HireDate)
Item(ItemID, Description, Price)
Invoice(InvoiceID, InvoiceDate, ClientID*, EmployeeID*)
InvoiceDetail(InvoiceID*, ItemID*, Quantity)
6. Suppose Bob uses SQL Server authentication to access the Sales schema.
Write the SQL statements that:
a. Create a login for Bob.
b. Create a Bob user for the login created with the Sales default schema.
c. Create the R_Emp role.
d. Assign the R_Emp role to the Bob user.
7. An employee is allowed to see only his information. Write the SQL
statements to assign this right to the created Role R_Emp.
Client(ClientID, ClientName, Gender, City, Birthdate, Telephone)
Employee(EmployeeID, EmployeeFName, EmployeeLName,
31
Gender, Salary, Commission, HireDate)
Item(ItemID, Description, Price)
Invoice(InvoiceID, InvoiceDate, ClientID*, EmployeeID*)
InvoiceDetail(InvoiceID*, ItemID*, Quantity)
8. Write the SQL statements to grant the following privileges:
a. Account A can retrieve or modify Clients relation and can grant any of these privileges to other users.
b. Account B can retrieve all employee attributes except for salary.
c. Account C can retrieve or modify invoices.
i. What privileges will be a problem for Account C?
ii. How to solve this problem?
d. Account D can retrieve any invoice attribute and can modify Employees.
e. Account E can retrieve any attribute of employees but only for tuples of female employees.
9. Write the SQL statements to revoke the following privileges:
a. Account B cannot retrieve all employee attributes except for salary.
10. Account C does not allow changes the invoices.
