Transcript slides

Theory, Practice & Methodology
of Relational Database
Design and Programming
Copyright © Ellis Cohen 2002-2008
Maintaining
Session State
in the Data Tier
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
Temporary Session Tables
Package Variables
Maintaining Session State
in the Data Tier
Multi-User & Virtual Connections
Shared Multi-User Connections
Adding Security to Multi-User
Connection
© Ellis Cohen 2001-2008
2
Temporary
Session Tables
© Ellis Cohen 2001-2008
3
Session-Specific Information
It can be useful to maintain a
collection of information separately
for each user session
We could store this information in
tables:
– Use a single table, and use the user's
session id as part of the primary key
– Use a separate table for each user
session
© Ellis Cohen 2001-2008
4
Temporary Tables
A temporary table
• Has a single CREATE TABLE definition
• A separate instance of the table is created for
each separate database connection that
accesses the table
• The instance of the table is deleted whether
the database connected is closed.
Assuming that every user session results
in a separate connection to the DB,
temporary session tables can be used to store
user session-specific information
© Ellis Cohen 2001-2008
5
Defining Temporary Tables
CREATE GLOBAL TEMPORARY TABLE SomeTable (
… ) ON COMMIT PRESERVE ROWS;
Session Tables: Use ON COMMIT PRESERVE ROWS, which
retains the data in each table instance from transaction to
transaction (just like permanent tables)
Transaction Tables: Use ON COMMIT DELETE ROWS which
automatically empties the data in each table instance at the end
of every transaction (this is the default, and is useful for holding
temporary data during a transaction)
© Ellis Cohen 2001-2008
6
Using Session Tables
Suppose
• our application has a number of
user query operations which
display information about products
• we want to keep track of every
product we viewed in a session
• we want to be able to implement a
query that shows the ids & names
of all products we have viewed in
the session so far
© Ellis Cohen 2001-2008
7
Remembering Seen Products
CREATE GLOBAL TEMPORARY TABLE SeenProducts (
prodid int primary key references Products
) ON COMMIT PRESERVE ROWS;
PROCEDURE RememberProduct( aProdid int )
IS
BEGIN
INSERT INTO SeenProducts VALUES( aProdid );
-- insert the product into the table to remember it
-- if it's already there, ignore the exception!
EXCEPTION WHEN OTHERS THEN NULL;
END;
© Ellis Cohen 2001-2008
8
Remembering Displayed Products
ShowProduct( :prodid )
DECLARE
productRec Products%ROWTYPE;
BEGIN
SELECT * INTO productRec
FROM Products
WHERE prodid = :prodid;
pl( prodRec.prodnam );
pl( prodRec.description );
-- remember it!
RememberProduct( :prodid );
EXCEPTION WHEN OTHERS THEN doerr();
END;
© Ellis Cohen 2001-2008
9
List Remembered Products
ListRememberedProducts
SELECT prodid, prodnam
FROM Products NATURAL JOIN
SeenProducts;
If multiple users are connected to the database,
each user will have their own copy of SeenProducts
© Ellis Cohen 2001-2008
10
Package
Variables
© Ellis Cohen 2001-2008
11
Package Variables
PL/SQL Packages can declare
variables
A separate instance of each
package variable is created for
each separate database
connection that accesses the
package.
These can hold typed data in the
data tier specific to a database
connection.
© Ellis Cohen 2001-2008
12
Package Variable Visibility
Variables can be declared in
• the package body: only
accessible to procedures and
functions also in the package
body (i.e. private)
• the package description: also
visible outside of the package.
Package variables persistently
hold data values for the
lifetime of the connection
© Ellis Cohen 2001-2008
13
Package Variable Example
SQL> CREATE OR REPLACE PACKAGE BODY MyUtil AS
startdate date; -- package body variable
FUNCTION getdate RETURN date IS
BEGIN
RETURN startdate;
-- always returns the initial date
-- dangerous if session lasts too long
END;
FUNCTION getNumColumns( tblnam varchar )
...
BEGIN -- package initialization
SELECT sysdate INTO startdate FROM dual;
END MyUtil;
/
© Ellis Cohen 2001-2008
14
Package Variables & Sessions
Suppose MyUtil is was created by a
user connected to the database as
EmpDB.
While that user is logged in, another
user also connects as EmpDB.
What happens to MyUtil.startdate?
Is it reinitialized?
© Ellis Cohen 2001-2008
15
Package Variables are Per-Connection
Actually, each different connection to
EmpDB will get its own private copy
of MyUtil.startdate.
And that private copy of
MyUtil.startdate will be initialized
the first time that MyUtil is
accessed by code executed via that
connection!
© Ellis Cohen 2001-2008
16
Package Body Exercise
PACKAGE RememberedEmployee AS
PROCEDURE Remember( IN anEmpno number );
-- remembers an employee's number
FUNCTION GetEmp return number;
-- returns the remembered employee's number
FUNCTION GetJob RETURN varchar;
-- returns the job of the remembered employee
FUNCTION HasHigherSal( IN aSal number )
RETURN boolean;
--- indicates whether the remembered
employee has a salary higher than aSal
END RememberedEmployee;
Write the body!
© Ellis Cohen 2001-2008
17
Package Body Exercise Answer #1
PACKAGE BODY RememberedEmployee AS
remEmpno emp.empno%TYPE;
PROCEDURE Remember( IN anEmpno number ) IS
BEGIN remEmpno := anEmpno; END;
FUNCTION GetEmp RETURN number IS
BEGIN RETURN remEmpno; END;
FUNCTION GetJob RETURN varchar IS
remJob emp.job%TYPE;
BEGIN
SELECT job INTO remJob FROM Emps
WHERE empno = remEmpno;
RETURN remJob;
END;
FUNCTION HasHigherSal( IN aSal number ) RETURN boolean IS
remSal emp.sal%TYPE;
BEGIN
SELECT sal INTO remSal FROM Emps
WHERE empno = remEmpno;
RETURN (remSal > aSal);
END;
END RememberedEmployee;
© Ellis Cohen 2001-2008
18
Package Body Exercise Answer #2
PACKAGE BODY RememberedEmployee AS
remEmpno emp.empno%TYPE;
remJob emp.job%TYPE;
remSal emp.sal%TYPE;
Dangerous if
job or sal can be
changed in Emps
PROCEDURE Remember( IN anEmpno number ) IS
BEGIN
remEmpno := anEmpno;
SELECT job, sal INTO remJob, remSal FROM Emps
WHERE empno = anEmpno;
END;
FUNCTION GetEmp RETURN number IS
BEGIN RETURN remEmpno; END;
FUNCTION GetJob RETURN varchar IS
BEGIN RETURN remJob; END;
FUNCTION HasHigherSal( IN aSal number )
RETURN boolean IS
BEGIN RETURN (remSal > aSal); END;
END RememberedEmployee;
© Ellis Cohen 2001-2008
19
Maintaining
Session State
in the Data Tier
© Ellis Cohen 2001-2008
20
Middle-Tier Session Variables
App User
7782
Middle-Tier
:curuser
7782
EmpDB
App User
6419
Middle-Tier
:curuser
6419
© Ellis Cohen 2001-2008
21
Session Variable Placement
We've been assuming that
middle-tier session variables
are the best place to maintain
some per-session information
such as :curuser
Are there any reasons to instead
(or also) maintain this
information in the data-tier
(i.e. in the database?)
© Ellis Cohen 2001-2008
22
Data Tier Placement
Data tier placement enables
– Session recovery on middle-tier
crash
– Session migration for load
balancing
– Simpler reusable views and
procedures
© Ellis Cohen 2001-2008
23
Package Variables & Session Data
Assuming
that every user session uses a
separate connection to the DB
Then
package variables can be used to
store user session data
© Ellis Cohen 2001-2008
24
Data-Tier Session Variables
With curuser
as a package
variable
App User
7782
Middle-Tier
:curuser
7782
curuser
7782
EmpDB
App User
6419
Middle-Tier
:curuser
6419
curuser
6419
Data-tier session variables are implemented as package
variables, which are maintained separately for each session!
© Ellis Cohen 2001-2008
25
AuthPkg Example
Suppose our application defines
AuthPkg to register and login
users, and to keep track of the
current user in each session, with
operations:
PROCEDURE Register( aUser, aPwd, aRole )
PROCEDURE Login( usr, pwd, theRole )
PROCEDURE Logout
FUNCTION GetUser RETURN int
-- returns logged in user (or NULL)
© Ellis Cohen 2001-2008
OUT
parameter
26
AuthPkg Body
CREATE TABLE AuthUsers(
userid int PRIMARY KEY, passwd varchar(128), role varchar(30) );
CREATE PACKAGE BODY AuthPkg AS
curuser int;
PROCEDURE Register( aUser int, aPwd varchar, aRole varchar ) IS
BEGIN
INSERT INTO AuthUsers VALUES( aUser, aPwd, aRole );
END;
PROCEDURE Login( usr int, pwd varchar, theRole OUT char ) IS
BEGIN
SELECT role INTO theRole FROM AuthUsers
WHERE userid = usr AND passwd = pwd;
curuser := usr;
END;
PROCEDURE Logout IS
BEGIN
curuser := NULL;
END;
FUNCTION GetUser RETURNS int IS
BEGIN
RETURN curuser;
END;
…
END AuthPkg;
Each DB session (i.e.
connection) has its
own instance of
package variables.
So curuser has a
value specific to the
connected DB session
© Ellis Cohen 2001-2008
27
Using AuthPkg in Login
Login( :userid, :pwd )
OUT parameter
Returns the
role
DECLARE
theRole varchar(30);
BEGIN
AuthPkg.Login( :userid, :pwd, theRole );
pl( 'Logged in with role ' || theRole );
END;
© Ellis Cohen 2001-2008
28
Using Script Variables
Login( :userid, :pwd )
&1 -- :userid
&2 -- :pwd
DECLARE
theRole varchar(30);
BEGIN
AuthPkg.Login( &1, &2, theRole );
pl( 'Logged in with role ' || theRole );
END;
© Ellis Cohen 2001-2008
29
ShowSals using :curuser
ShowSals
SELECT empno, ename, sal
FROM Emps
WHERE empno = :curuser
OR mgr = :curuser
Shows employee #, name & salary
of the current user and of all employees the
current user directly manages
(using the middle tier variable :curuser)
Assuming that the identity of :curuser was only
maintained in the data-tier package AuthPkg,
rewrite ShowSals
© Ellis Cohen 2001-2008
30
ShowSals using AuthPkg
Shows employee #, name & salary
of current user and
all employees the current user
directly manages!
ShowSals
SELECT empno, ename, sal
FROM Emps
WHERE empno = AuthPkg.GetUser
OR mgr = AuthPkg.GetUser
Keeping track of the identity of the current user in the
data-tier is known as Data-Tier Identity
© Ellis Cohen 2001-2008
31
Caching Session Variables
in the Middle Tier
Login( :userid, :pwd )
DECLARE
theRole varchar(30);
BEGIN
AuthPkg.Login( :userid, :pwd, :currole );
:curuser := :userid;
pl( 'Logged in with role ' || :currole );
END;
ShowSals
SELECT empno, ename, sal
FROM Emps
WHERE empno = :curuser
OR mgr = :curuser
© Ellis Cohen 2001-2008
Write a view
showing the
employee #s &
names of all
employee managed
by the current user
32
Session-Based Views
CREATE CurManageView AS
SELECT empno, ename
FROM Emps
WHERE mgr = :curuser
Middle-tier session variables can't be
used in views.
There's no way for the database to
access them!
CREATE CurManageView AS
SELECT empno, ename
FROM Emps
WHERE mgr = AuthPkg.GetUser
© Ellis Cohen 2001-2008
33
Multi-User & Virtual
Connections
© Ellis Cohen 2001-2008
34
Per-User DB Connection
App User
7782
Middle-Tier
:curuser
7782
curuser
7782
EmpDB
App User
6419
Middle-Tier
:curuser
6419
© Ellis Cohen 2001-2008
curuser
6419
35
User Sessions & DB Connections
In real systems
Does the middle tier maintain a
separate connection to the DB
for each separate application
user session?
© Ellis Cohen 2001-2008
36
Database Connections
Database connections are expensive
Web/App servers typically use a small
pool of connections, which are used
for requests.
Connections
App
Server
© Ellis Cohen 2001-2008
Database
Server
37
Virtual Connections
Some databases (e.g. Oracle)
support virtual connections
• There are still only a small # of
"physical" connections between the
Web/App server and the database
• It's possible to code as if there
were a separate connection for
each application user
• Separate temporary tables and
package variable instances are
created for each virtual connection
© Ellis Cohen 2001-2008
38
Shared Multi-User
Connections
© Ellis Cohen 2001-2008
39
Using Shared Connections
If
– a middle tier server uses connection pooling
– virtual connections are not used
then
– it is not possible to associate a single user id
with a connection
The middle-tier server
– gets requests from different application users
– ships them over a shared connection to the
database server
– Although this requires each request to be a
separate transaction
© Ellis Cohen 2001-2008
40
Switching Users
The middle tier processes requests on behalf of the
various users. Suppose that :requser is the identity
of the user whose request is being processed.
Before executing the operation code to implement
the request, the middle tier must switch the current
user (for that connection) to be :requser
• If :curuser is maintained in the middle tier, the
middle tier executes
:curuser := :requser
• If curuser is mantained in AuthPkg, the middle
tier sets the curuser package variable to hold
the identity of :requser by calling
AuthPkg.SwitchUser( :requser )
© Ellis Cohen 2001-2008
41
AuthPkg with User Switching
CREATE PACKAGE BODY AuthPkg AS
curuser int;
…
PROCEDURE Login( usr int, pwd varchar, theRole OUT char ) IS
BEGIN
SELECT role INTO theRole FROM AuthUsers
WHERE userid = usr AND passwd = pwd;
curuser := usr;
END;
PROCEDURE Logout IS
BEGIN
curuser := NULL;
END;
PROCEDURE SwitchUser( requsr int ) IS
curuser := requsr;
…
END AuthPkg;
© Ellis Cohen 2001-2008
42
ShowSals w User Switching
ShowSals
AuthPkg.SwitchUser( :requser )
SELECT ename, sal
FROM Emps
WHERE empno = AuthPkg.GetUser
OR mgr = AuthPkg.GetUser
© Ellis Cohen 2001-2008
43
Adding Security
to Multi-User
Connections
© Ellis Cohen 2001-2008
44
Adding Security
Requests should only be able to be
made on behalf of users who have
logged in and not logged out.
If we can ensure this, it can help
protect against
– Application Server Errors
– Hackers who might tap into the
application server or the database
connection
© Ellis Cohen 2001-2008
45
AuthPkg Body
CREATE TABLE CurUsers( userid int primary key );
CREATE PACKAGE BODY AuthPkg AS
curuser int;
PROCEDURE Login( usr int, pwd varchar, theRole OUT char ) IS
BEGIN
SELECT role INTO theRole FROM AuthUsers
This version of the
WHERE userid = usr AND passwd = pwd;
code ensures that a
curuser := usr;
user can only have
one
session. Why?
INSERT INTO CurUsers VALUES( usr );
How could you
END;
allow multiple
sessions per user?
PROCEDURE Logout IS
BEGIN
DELETE FROM CurUsers WHERE userid = curuser;
curuser := NULL;
END;
PROCEDURE SwitchUser( requsr int ) IS
SELECT userid INTO curuser FROM CurUsers
WHERE userid = requsr;
…
END AuthPkg;
Checks that user is
actually logged in
© Ellis Cohen 2001-2008
46
User Assignment
The middle tier could assign a request from
any user to any connection
Suppose when a user logged in, the middle
tier assigned that user to a specific
connection, and then directs all requests
by that user to the same connection.
Then, a request to SwitchUser should only
work if it was on the same connection on
which the user logged in.
Change AuthPkg accordingly
© Ellis Cohen 2001-2008
47
AuthPkg with User Assignment (1)
CREATE GLOBAL TEMPORARY TABLE CurUsers( userid int primary key )
ON COMMIT PRESERVE ROWS;
CREATE PACKAGE BODY AuthPkg AS
Maintains separate table
curuser int;
each connection
…
PROCEDURE Login( usr int, pwd varchar, theRole OUT char ) IS
BEGIN
SELECT role INTO theRole FROM AuthUsers
WHERE userid = usr AND passwd = pwd;
curuser := usr;
INSERT INTO CurUsers VALUES( usr );
END;
PROCEDURE Logout IS
BEGIN
DELETE FROM CurUsers WHERE userid = curuser;
curuser := NULL;
END;
PROCEDURE SwitchUser( requsr int ) IS
SELECT userid INTO curuser FROM CurUsers
WHERE userid = requsr;
…
END AuthPkg;
Checks that
the user is
actually
logged in on
that
connection
This depends upon trusting that the middle tier will
not login the same user on multiple connections
© Ellis Cohen 2001-2008
for
48
AuthPkg with User Assignment (2)
CREATE TABLE CurUsers( userid int primary key, sessid int );
CREATE PACKAGE BODY AuthPkg AS
curuser int;
…
PROCEDURE Login( usr int, pwd varchar, theRole OUT char ) IS
BEGIN
INSERT INTO CurUsers VALUES( usr,
SYS_CONTEXT( 'USERENV', 'SESSIONID') );
SELECT role INTO theRole FROM AuthUsers
Remembers the
WHERE userid = usr AND passwd= pwd;
connection to
curusr := usr;
which the Login
END;
request was sent.
Raises exception
PROCEDURE Logout IS
if user logged in
BEGIN
more than once
DELETE FROM CurUsers WHERE userid = curuser;
curuser := NULL;
END;
PROCEDURE SwitchUser( requsr int ) IS
SELECT userid INTO curuser FROM CurUsers
WHERE userid = requsr AND sessid =
SYS_CONTEXT( 'USERENV', 'SESSIONID');
…
END AuthPkg;
© Ellis Cohen 2001-2008
Checks that the
request is in the
same connection
where the user
was logged in
49