Transcript CS 579 Database Systems

Theory, Practice & Methodology
of Relational Database
Design and Programming
Copyright © Ellis Cohen 2002-2008
Security
and
Data-Tier
Implementations
These slides are licensed under a Creative Commons
Attribution-NonCommercial-ShareAlike 2.5 License.
For more information on how you may use them,
please see http://www.openlineconsult.com/db
1
Overview of Lecture
Application-Based Security
Authentication & Encryption
Data-Tier Implementations
Data-Tier Implementation of Query
Operations
Parameterized Queries
Using Package Variables
Parameterized Queries Using Cursors
Parameterized Queries Using REF Cursors
Secure Data-Tier Implementations
© Ellis Cohen 2001-2008
2
Application-Based
Security
© Ellis Cohen 2001-2008
3
Application Implementation
Users typically access the operations
of a DB application through
– User Interfaces (often but not always
web-based)
– Service Interfaces (often, but not
always web services)
The application code then accesses
the database (directly or through
views or stored procedures), not the
user directly.
© Ellis Cohen 2001-2008
4
UI's and Web Services
Web
Service
Client
Distributed
Application
Environment
User
(Interactive
Client)
Web
Browser
Presentation
Tier
SOAP
Web
Service
Interface
DB
Application
A
P
I
Database
Server
UI
Management
Web or
Application
Server
Middle Tier
© Ellis Cohen 2001-2008
Data Tier
5
Circumventing Access Control
Operations Implement
Access Control by:
– Access-limited
queries/views/modifications
– Explicit or trigger-based
access checks in operations
GREAT! But what prevents an
arbitrary user or client from
simply accessing the Emps
table directly or even from
disabling a trigger?
© Ellis Cohen 2001-2008
6
Application-Based Security
A new schema is created for each new database
application (e.g. EmpDB for the Employee DB
application)
When the application starts up (i.e. the middle-tier
code), it connects to the database using the
application schema name (e.g. EmpDB) and a
private password or security credentials.
Only the application (and not an arbitrary user inside
the firewall) can access the application's tables and
other data, because arbitrary users don't know the
password or can’t obtain the necessary credentials.
(Although the DBA does, and that can still cause
problems!)
It is solely up to the application to enforce security
• By authenticating users (perhaps with help of DB
code like AuthPkg)
• By performing the correct access checks
© Ellis Cohen 2001-2008
7
Limitations of
Application-Based Security
Too easy for application-level errors or
password-breaches to compromise all
the application data
If an intruder can hijack the middle-tier
application, they have complete access
to EmpDB. They can
– Delete all the data
– Subtly sabotage the data
– Make the data inconsistent
– (Possibly) Change the data tier code
– (Possibly) Add back-door access
© Ellis Cohen 2001-2008
8
Securing Application-Based Systems
Some ways of securing applicationbased systems:
• Mutual Authentication
• Message Encryption
• Data Encryption
• Carefully review code for
SQL injection exploits
(http://www.spidynamics.com/papers/
SQLInjectionWhitePaper.pdf)
• Secure Data-Tier Implementation
© Ellis Cohen 2001-2008
9
Authentication
& Encryption
© Ellis Cohen 2001-2008
10
Principal
Who wants to access a database?
• A user
• An application
• etc …
Principal:
The entity in a computer system whose
identity can be authenticated and to
which privileges are granted
© Ellis Cohen 2001-2008
11
Authentication in a Single
Machine Environment
Authentication
Verifying the identity of a principal making a
request
How does the database know who is
making a request?
– Determined at connection time
– Remains associated with the connection
How does the database know who is
establishing a connection
– Provide a userid and password to the DB when
making a connection
– Ask the operating system to identify the
(userid of the) principal making the connection
[Single signon]
© Ellis Cohen 2001-2008
12
Corporate Network Security
Suppose
user is on one machine
database on another
Problem
Secure (mutual) authentication
Secure identification of user to database
Secure identification of database to user
Secure communication between two
machines: Use encryption
With potentially malicious individuals
Eavesdrop on communication line
Remove/modify/insert/replay packets
© Ellis Cohen 2001-2008
13
Primitive Network Attacks
From Stallings, Network
& Internetwork
© Ellis Cohen
2001-2008 Security
14
Security Attacks
Passive Attacks (sniffing/snooping/
eavesdropping/monitoring):
– Obtain message contents (esp sensitive data,
particularly passwords)
– Monitor traffic flows
– Deduce secrets (especially "keys")
Active Attacks
– Masquerade of one entity as some other
(spoofing)
– Replay previous messages (once or many
times)
– Modify messages in transit
– Denial of service (flooding)
– Viruses/Worms
© Ellis Cohen 2001-2008
15
Symmetric/Private Key Encryption
Plain text
This is
extremely
secret
information
This is
extremely
secret
information
kdj%$$KU:78v2n]=kva
w@b^vfeyu8Tj;p].\~4v
$&9ikmunp;p,
decrypt
encrypt
Security
depends
on the
secrecy
of the
key
Plain text
Encrypted text
k
Same key
© Ellis Cohen 2001-2008
k
not the
secrecy of
the
algorithm
16
Symmetric Key-Based Confidentiality
For A to communicate with a service B
• They both share a private key kAB that can
be used both to encrypt and decrypt data
• A uses the key to encrypt data -- [data]kAB
• A sends [data]kAB to B
Eavesdroppers who don't know the key see
"random" bits; packets they insert will (once
decryption is attempted) be identified as fake
• B uses kAB to decrypt [data]kAB and gets
back the data
If a key is used too heavily,
it can be compromised
• If A and B continue to use kAB, it can be
deduced by an eavesdropper
© Ellis Cohen 2001-2008
17
Session Keys
If a key is used too heavily,
it can be compromised
An eavesdropper can deduce a secret key if
it sees a long enough sequence of
encrypted messages
For A to communicate with a service B
• They both share a private key kAB
• When A starts a session with B, B creates a
one-time private session key, kS, and returns
[kS]kAB to A
• A uses kAB to decrypt the message and get
kS, and A & B subsequently use kS to encypt
their messages for the rest of the session
© Ellis Cohen 2001-2008
18
Simple Password-Based Authentication
Assumption: Database's key, kDB, is securely
embedded on each user's local host
Sample Simplified Protocol:
• User process
– creates a one-time private session key, kS
– Sends [userid, passwd, kS]kDB
to database server
• Database server
– uses kDB to obtain userid, passwd, kS
– checks userid and passwd
• Database server and user process use kS
for secure encrypted communication
– Problem: kDB can be too easily compromised
which then completely compromises system
© Ellis Cohen 2001-2008
19
Simple Key-Based Security
Assumption: Every user has registered their
private key with the database server
• User process
– Send the user's name to the database server
• Database service
– Gets the name and looks up the user's registered
key, kUser
– Creates a one-time private session key, kS
– Sends [name, kS]kUser back to user process
• User process
– Extracts name & checks if it is its own name
(Guarantees response came from DB server &
that decryption was successful)
– Extracts kS
• Database server and user process use kS
for secure encrypted communication
© Ellis Cohen 2001-2008
20
Corporate Network
Authentication Approaches
• Database Password Approach:
maintains set of userids & passwords
• Operating System Approach:
uses network login identity
(single signon)
• Authentication Service Approach
(e.g. Kerberos):
Each user and server register a single private key
with Kerberos
Kerberos mutually authenticates users and services
using their registered private keys, and creates a
session key for their communication
Avoids having each service handle key registration,
means that a user only needs one key, rather
than one key for each service
© Ellis Cohen 2001-2008
21
Data & Code Encryption
Databases can encrypt data
(tables or columns) and code
(stored DB operations) using
private keys to further protect
data
© Ellis Cohen 2001-2008
22
Public Networks & Public Keys
Registering passwords or private
keys across public network is not
scalable or secure
In a public key system,
a party has a pair of keys,
K (public) and K' (private):
– You can't decrypt [data]K just by
knowing K. You need to know K'
[[data]K]K' => data
– Also goes the other way!
[[data]K']K => data
© Ellis Cohen 2001-2008
23
Public Key Pairs
Public and private keys are always
generated as a matched pair
K
K' (Private)
K (Public)
K'
Keys are mathematically related but it is
computationally infeasible to deduce a
private key from its public key
Private keys are kept secret - often by being
stored in a tamper-resistant chip
© Ellis Cohen 2001-2008
24
Secure Mutual Authentication
Database Server advertises its public key, DK
A user's public key is UK.
User sends the following to the DB server
– [UK, nonce]DK (a nonce is a random value,
different for every initial request)
Database server
– Uses DK' to extract UK & nonce
– Uses UK to identify user (or user could have also
sent a name on first contact w server)
– creates private session key, kS
– sends [nonce, kS]UK to user process
User process
– uses UK' to extract nonce and kS
– checks nonce (prevents masquerading)
Database service and user process use kS for
secure encrypted communication
© Ellis Cohen 2001-2008
25
Data-Tier
Implementations
© Ellis Cohen 2001-2008
26
Data-Tier Implementation
• The code that actually accesses a
DB application's tables is ALL
implemented in the data tier as
– views & cursors
– stored DB operations (procedures,
functions & packages)
• The actual user operation code in
the middle tier is largely limited to
– figuring out which views, cursors
& DB operations to use
– formatting results
– controlling UI navigation
© Ellis Cohen 2001-2008
27
Goals & Advantages of
Data-Tier Implementation
Information Hiding
Hides the details of the database implementation
• the actual tables & attributes used
• what constraints are enforced & how
And just makes available a collection of views,
and procedures, functions, & cursors
(typically organized into packages)
which enable user operations to be implemented
easily and remain unchanged even if the
underlying DB representation and the business
rules changes
Security
In conjunction with database privileges & roles,
enables implementation to be highly secure
© Ellis Cohen 2001-2008
28
Separation of Responsibility
Data-tier
All access to actual tables
All decisions about what
constraints to enforce and how
Middle-tier
All display of
ordinary & error output
NOTE: Output should NEVER be displayed
from code stored in the data-tier
© Ellis Cohen 2001-2008
29
User Action Code Using
Middle-Tier Implementation
ChangeSal( :empno, :sal )
In a middle-tier implementation,
decisions about what access constraints
to enforce and how to enforce them are
exposed in the middle-tier code
BEGIN
CheckManage( :empno );
UPDATE Emps SET sal = :sal
WHERE empno = :empno;
pl( 'Salary Changed' );
EXCEPTION WHEN OTHERS THEN doerr();
END;
In a middle-tier implementation, the middle-tier
code directly accesses underlying tables
© Ellis Cohen 2001-2008
30
User Action Code Using
Data-Tier Implementation
ChangeSal( :empno, :sal )
Middle-Tier
Code
BEGIN
EmpPkg.ChangeSal( :empno, :sal );
pl( 'Salary Changed' );
EXCEPTION WHEN OTHERS THEN doerr();
END;
Middle-tier does not enforce constraints
or access underlying tables
Just displays ordinary & error output
PROCEDURE ChangeSal (
anEmpno int, aSal number ) IS
BEGIN
CheckManage( anEmpno );
Data-Tier code in
UPDATE Emps SET sal = aSal
Package EmpPkg
WHERE empno = anEmpno;
END;
Stored procedure in the data-tier handles all constraint
enforcement and access to underlying tables
© Ellis Cohen 2001-2008
31
Alternate Data-Tier Implementation
ChangeSal( :empno, :sal )
BEGIN
EmpPkg.ChangeSal( :empno, :sal );
pl( 'Salary Changed' );
EXCEPTION WHEN OTHERS THEN doerr();
END;
PROCEDURE ChangeSal (
anEmpno int, aSal number ) IS
BEGIN
UPDATE Emps SET sal = :sal
WHERE empno = :empno
AND mgr = AuthPkg.GetUser;
Middle-Tier
Code
Data-Tier code in
Package EmpPkg
CheckResults(
'You are not the employee's manager' );
END;
Exact implementation of ChangeSal is hidden
© Ellis Cohen 2001-2008
32
Data-Tier Login Implementation
Login( :empno, :pwd )
Just calls EmpPkg.Login,
hiding the fact that
EmpPkg uses AuthPkg to
authenticate users
DECLARE
theRole: varchar(30);
BEGIN
EmpPkg.Login( :userid, :pwd, theRole );
pl( 'Logged in with role ' || theRole );
END;
Data-Tier code in Package EmpPkg
PROCEDURE Login ( aUserid int, aPwd varchar,
theRole OUT varchar ) IS
BEGIN
AuthPkg.Login( aUserid, aPwd, theRole );
END;
© Ellis Cohen 2001-2008
33
Data-Tier Subsystem Design
Middle-Tier
Login
Data-Tier
EmpPkg
Login
AuthPkg (private)
Login
© Ellis Cohen 2001-2008
34
Data-Tier
Implementation
of
User Query Operations
© Ellis Cohen 2001-2008
35
User Query Code Using
Middle-Tier Implementation
ShowSals
In a middle-tier implementation, the middle-tier
code directly accesses underlying tables
SELECT empno, ename, sal
FROM Emps
WHERE empno = AuthPkg.GetUser
OR mgr = AuthPkg.GetUser;
In a middle-tier implementation,
decisions about what access constraints
to enforce and how to enforce them are
exposed in the middle-tier code
© Ellis Cohen 2001-2008
36
User Query Code Using
Data-Tier Implementation
ShowSals
Middle-Tier
Code
SELECT empno, ename, sal
FROM EmpMgrView
Middle-tier does not enforce constraints or access
underlying tables. Just displays output
Data-Tier
CREATE VIEW EmpMgrView IS
code
SELECT * FROM Emps
WHERE empno = AuthPkg.GetUser
OR mgr = AuthPkg.GetUser;
Use access-limiting views. Views defined in the data-tier handle
all constraint enforcement and access to underlying tables
© Ellis Cohen 2001-2008
37
Parameterized
Queries
Using
Package Variables
© Ellis Cohen 2001-2008
38
Data-Tier View for Query
ShowJobsByDept( :dname )
SELECT job FROM JobDeptView
WHERE dname = :dname
CREATE VIEW JobDeptView AS
SELECT DISTINCT dname, job
FROM Emps NATURAL JOIN Depts
WHERE AuthPkg.GetUser IS NOT NULL;
Typical data-tier implementation using access-limiting view of an
operation which shows the distinct jobs within a specified
department, but only works for users who are logged in
Suppose we want to do more information hiding, and
hide the way that departments are mapped to jobs
© Ellis Cohen 2001-2008
39
Use Parameterized Views
ShowJobsByDept( :dname )
SELECT job FROM JobsByDeptView( :dname )
Great idea.
Not
implemented!
CREATE VIEW JobsByDeptView
PARAMETERS( aDname varchar )
SELECT DISTINCT dname, job
FROM Emps NATURAL JOIN Depts
WHERE dname = aDname
AND AuthPkg.GetUser IS NOT NULL;
Cool idea, but doesn't exist in SQL.
© Ellis Cohen 2001-2008
40
Define a View Parameter Package
CREATE OR REPLACE PACKAGE BODY ViewPkg AS
-- holds the view parameter
theDName Emps.dname%TYPE;
-- sets the view parameter
PROCEDURE SetDName( aDName varchar ) IS
BEGIN
theDName := aDName;
END;
-- gets the view parameter
FUNCTION GetDName RETURN varchar IS
BEGIN
RETURN theDName;
Could include this in
END;
EmpPkg instead
END ViewPkg;
© Ellis Cohen 2001-2008
41
Use Parameter Package
ShowJobsByDept( :dname )
EXECUTE ViewPkg.SetDName( :dname );
SELECT job FROM JobsByDeptView;
CREATE VIEW JobsByDeptView
SELECT DISTINCT dname, job
FROM Emps NATURAL JOIN Depts
WHERE dname = ViewPkg.getDName
AND AuthPkg.GetUser IS NOT NULL;
This approach can also be used to pass parameter values to triggers.
Use this technique very cautiously, since it can negate the advantage
of having triggers be operation-independent
© Ellis Cohen 2001-2008
42
With Explicit Display
ShowJobsByDept( :dname )
DECLARE
knt int := 0;
BEGIN
ViewPkg.SetDName( :dname );
FOR rec IN (SELECT * FROM JobsByDeptView)
LOOP
pl( rec.job );
knt := knt + 1;
END LOOP;
condraise( knt = 0, -20063,
'No jobs to be displayed' );
EXCEPTION WHEN OTHERS THEN plerr();
END;
© Ellis Cohen 2001-2008
43
Parameterized
Queries
Using Cursors
© Ellis Cohen 2001-2008
44
Oracle Packaged Cursors
A cursor is a mechanism for
stepping through the result
set of a SELECT statement
Query For Loops can be used to
iterate through cursors
Oracle allows cursors to be
– defined in packages
– parameterized
© Ellis Cohen 2001-2008
45
Using Packaged Cursors
ShowJobsByDept( :dname )
BEGIN
FOR rec IN EmpPkg.JobsByDeptCursor( :dname )
LOOP
pl( rec.job );
END LOOP;
EXCEPTION WHEN OTHERS THEN plerr();
END;
CURSOR JobsByDeptCursor( aDname varchar ) IS
SELECT DISTINCT job
FROM Emps NATURAL JOIN Depts
Data-Tier
WHERE dname = aDname
code in
AND AuthPkg.GetUser IS NOT NULL;
Package
EmpPkg
© Ellis Cohen 2001-2008
46
Cursors and Data-Tier
Implementations
Packaged cursors are very useful
in data-tier implementations.
They allow constraint
enforcement and
parameterized access to tables
to be placed in the data-tier
with all display of result sets
and errors to be done in the
middle tier
© Ellis Cohen 2001-2008
47
Parameterized
Queries
Using REF Cursors
© Ellis Cohen 2001-2008
48
Oracle Ref Cursors
An Oracle Ref Cursor is a reference
to a cursor that can be returned
from a function
Even better, SQL*Plus supports
REFCURSOR variables
Even better, printing a refcursor
displays the result set in exactly
the same way as SQL*Plus
displays the result of a SELECT
© Ellis Cohen 2001-2008
49
Using Ref Cursors
ShowJobsByDept( :dname )
Assumes rc was defined as
variable rc REFCURSOR
EXECUTE :rc := EmpPkg.GetJobsByDeptCursor( :dname )
print :rc
This has the same effect as
simply issuing the SELECT command
FUNCTION GetJobsByDeptCursor( aDname varchar )
RETURN SYS_REFCURSOR IS
rc SYS_REFCURSOR;
BEGIN
Data-Tier
code in
OPEN rc FOR SELECT DISTINCT dname, job
Package
FROM Emps NATURAL JOIN Depts
EmpPkg
WHERE dname = aDname
AND AuthPkg.GetUser IS NOT NULL;
RETURN rc;
END;
Suppose we wanted an error message
if the current user is not logged in?
© Ellis Cohen 2001-2008
50
Raising Exceptions
ShowJobsByDept( :dname )
BEGIN
:rc := EmpPkg.GetJobsByDeptCursor( :dname );
EXCEPTION WHEN OTHERS THEN plerr();
END;
print :rc
FUNCTION GetJobsByDeptCursor( aDname varchar )
RETURN SYS_REFCURSOR IS
rc SYS_REFCURSOR;
BEGIN
AuthPkg.CheckUser();
OPEN rc FOR SELECT DISTINCT dname, job
FROM Emps NATURAL JOIN Depts
WHERE dname = aDname;
RETURN rc;
Almost. If an exception is raised,
END;
rc will be left undefined, and trying to
print it will result in an error message
© Ellis Cohen 2001-2008
Data-Tier
code in
Package
EmpPkg
51
Exceptional Ref Cursor Display
BEGIN
:rc := EmpPkg.GetJobsByDeptCursor( :dname );
EXCEPTION WHEN OTHERS THEN
:rc := emptycursor();
plerr();
END;
/
print :rc
FUNCTION emptycursor RETURN SYS_REFCURSOR IS
rc SYS_REFCURSOR;
BEGIN
OPEN rc FOR SELECT '' AS " " FROM DUAL
WHERE 1 = 2;
RETURN rc;
Already defined
END;
In UtilSys
© Ellis Cohen 2001-2008
52
Secure Data-Tier
Implementations
© Ellis Cohen 2001-2008
53
Application Implementation Approaches
Middle-Tier Implementation
– Operation code is primarily in the
middle-tier
Data-Tier Implementation
– All enforcement & table access
moved to data-tier.
Display-related code left in the
middle-tier.
Secure Data-Tier Implementation
– Data-tier implementation based on
the notion of a security domain
© Ellis Cohen 2001-2008
54
Database Users & Schemas
Each database user (e.g. SCOTT or EmpDB)
has their own schema: the set of database
objects, e.g. tables, views, packages, etc.
that they own
If you have connected to the database as a
different user OtherUser, then you can try
to access EmpDB's Emps table by writing
EmpDB.Emps
EmpDB> SELECT * FROM Emps
OtherUser> SELECT * FROM EmpDB.Emps
Indicates that we are in SQL*Plus
connected as OtherUser
© Ellis Cohen 2001-2008
55
Schema as a Security Domain
The owner of a schema can control
• Which objects (tables, views,
procedures, packages, etc.) another
schema can see
• What other schemas can do with
them
by explicitly GRANT-ing them to
other schemas (details described
later)
A schema acts as a security domain –
a locus for controlling access to a
group of objects
© Ellis Cohen 2001-2008
56
Gateway Security Domains
In a gateway security domain, other users
never have unconstrained access to the
objects managed by the domain (i.e. those
created in the schema)
In particular, the owner of the schema can
ensure that the objects holding application
data and code cannot be accessed directly,
but only through a special collection of
a) gateway views & cursors
b) gateway operations
(procedures & functions)
defined in the domain with limited access
explicitly granted to other users/clients
© Ellis Cohen 2001-2008
57
Gateway Views Limit Access
A gateway view can provide limited access to the
contents of a table (or even another internal view)
Security Domain
(EmpDB)
Gateway View
or Cursor
User/Client
User cannot access
the table directly
© Ellis Cohen 2001-2008
58
Gateway Operations Limit Access
A gateway stored DB operations (i.e. procedure or
function) straddles security domains.
They can be called from
outside the domain,
but then execute
inside the domain
Security Domain
(EmpDB)
Gateway
Operation
User/Client
User cannot access
the table directly
© Ellis Cohen 2001-2008
59
Secure Data-Tier Application
In a secure data-tier implementation
• The application's data and stored
procedures are defined in the data-tier
implementation schema (e.g. EmpDB)
• The middle-tier connects to the database
through a different middle-tier
implementation schema (e.g. EmpApp)
• The middle-tier schema (e.g. EmpApp) is
not granted direct access to the tables
defined by the data-tier schema (e.g.
EmpDB), but only granted limited access
to gateway operations, views and cursors
EmpDB> GRANT EXECUTE ON EmpPkg TO EmpApp
EmpDB> GRANT SELECT ON EmpMgrView TO EmpApp
…
© Ellis Cohen 2001-2008
60
Secure Data-Tier Identity
EmpDB
curusr
7782
App User
7782
Web
Browser
DB App on
Web/App
Server
Application
authenticates the
user logging in as
7782
(with the help of
EmpDB's
AuthPkg, which
then "knows" that
the user is 7782!)
EmpApp
Database authenticates
the application
connecting as EmpApp
The application can only
access the EmpDB
views/ops visible to
EmpApp
These views/ops use the
user's application identity
(7782) to control access
© Ellis Cohen 2001-2008
61
Secure Data-Tier Querying
ShowSals
SELECT empno, ename, sal
FROM EmpDB.EmpMgrView
Middle-Tier Code executed when
connected as EmpApp
View defined
CREATE VIEW EmpMgrView IS
in EmpDB
SELECT * FROM Emps
WHERE empno = AuthPkg.GetUser
OR mgr = AuthPkg.GetUser
GRANT SELECT ON EmpMgrView TO EmpApp
© Ellis Cohen 2001-2008
62
Secure Data-Tier Implementation
ChangeSal( :empno, :sal )
BEGIN
EmpDB.EmpPkg.ChangeSal( :empno, :sal );
pl( 'Salary Changed' );
EXCEPTION WHEN OTHERS THEN doerr();
END;
Middle-Tier Code executed when
connected as EmpApp
PROCEDURE ChangeSal (
anEmpno int, aSal number ) IS
BEGIN
Data-Tier code in
CheckManage( anEmpno );
Package EmpPkg
UPDATE Emps SET sal = aSal
in EmpDB, to which
WHERE empno = anEmpno;
EmpApp has been
END;
granted access
© Ellis Cohen 2001-2008
63
Alternate Secure Data-Tier Implementation
ChangeSal( :empno, :sal )
BEGIN
EmpDB.EmpPkg.ChangeSal( :empno, :sal );
pl( 'Salary Changed' );
EXCEPTION WHEN OTHERS THEN doerr();
END;
Middle-Tier Code executed when
connected as EmpAppc
PROCEDURE ChangeSal (
anEmpno int, aSal number ) IS
BEGIN
UPDATE Emps SET sal = :sal
WHERE empno = :empno
AND mgr = AuthPkg.GetUser;
Data-Tier code in
Package EmpPkg
in EmpDB
CheckResults(
'You are not the employee's manager' );
END;
© Ellis Cohen 2001-2008
64
Secure Data-Tier Login
Login( :empno, :pwd )
Middle-Tier Code
executed when
connected as
EmpApp
DECLARE
theRole: varchar(30);
BEGIN
EmpDB.EmpPkg.Login( :userid, :pwd, theRole );
pl( 'Logged in with role ' || theRole );
END;
Data-Tier code in Package EmpPkg in EmpDB
PROCEDURE Login ( aUserid int, aPwd varchar,
theRole OUT varchar ) IS
BEGIN
AuthPkg.Login( aUserid, aPwd, theRole );
END;
© Ellis Cohen 2001-2008
65
Data-Tier Subsystem Design
Middle-Tier
Login
Connected as
Schema EmpApp
In Schema EmpDB
Data-Tier
EmpPkg
Login
AuthPkg (private)
Login
© Ellis Cohen 2001-2008
66
Insecure vs Secure
Data-Tier Identity
App User
7782
Web
Browser
DB App on
Web/App
Server
EmpDB
curusr
7782
EmpDB
curusr
7782
App User
7782
Web
Browser
DB App on
Web/App
Server
Presentation
Tier
Middle Tier
© Ellis Cohen 2001-2008
EmpApp
Data Tier
67
Comparing Security
A Secure Data-Tier Implementation
provides a higher degree of security.
Consider the impact if the application is
hijacked or the application's connection to the
database is compromised
• Non-Secure Implementation
The intruder has complete access to the entire
EmpDB schema
• Secure Data-Tier Implementation
The intruder can only masquerade as the users
who are currently connected to the application
(or for whom the hijacker can steal userids and
passwords), and can only compromise EmpDB
to the extent those users would be able to.
Compromises cannot damage internal
consistency of the database, or modify stored
procedures, views or triggers.
© Ellis Cohen 2001-2008
68