Transcript slides

Theory, Practice & Methodology
of Relational Database
Design and Programming
Copyright © Ellis Cohen 2002-2008
Enforcing
Access Constraints
with Privileges
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
Discretionary Access Control
Mechanisms (DAC)
Secure Updates with Views and Procedures
Dynamic Access Control using
Database Identity
Database Applications using
Database Identity
Grant-Based Enforcement
Database vs Data-Tier Identity for
Secure Gateways
Role-Based Data-Tier Implementation
Dynamic Role Assignment
Role Grant Based Enforcement Using RBAC (RoleBased Access Control)
Mapping Between Database &
Application Identities
© Ellis Cohen 2001-2008
2
Discretionary
Access Control
Mechanisms
(DAC)
© Ellis Cohen 2001-2008
3
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
TOMMY, then you can try to access SCOTT's
ScottInfo table by writing SCOTT.ScottInfo
SCOTT> SELECT * FROM ScottInfo
TOMMY> SELECT * FROM SCOTT.ScottInfo
Indicates that we are in SQL*Plus
connected as TOMMY
© Ellis Cohen 2001-2008
4
Privileges
Privilege
Right of a database user (e.g. TOMMY) to use a
database object (e.g. table, view, procedure,
function, package, etc.) in a particular way
Discretionary Access Control Model
A database user has all the privileges for any object
it owns (i.e. are in its schema)
Database users can (at their discretion)
• grant privileges to other database users
• revoke privileges they have granted
TOMMY> SELECT * FROM SCOTT.ScottInfo
will not be allowed unless TOMMY has been granted
the privilege to access ScottInfo (by SCOTT, or
some other user who has the power to do so)
© Ellis Cohen 2001-2008
5
Table Privileges
Privileges on tables include
SELECT - query table
UPDATE - update (specific columns in) table (also
requires SELECT)
INSERT - insert rows in table (can limit columns w
explicitly set values)
DELETE - delete rows from table
Examples
SCOTT> GRANT SELECT ON ScottInfo TO JOE, MARY
JOE> SELECT * FROM SCOTT.ScottInfo
SCOTT> GRANT SELECT, UPDATE(nam) ON ScottInfo
TO TOMMY
Be very careful about granting anything
but SELECT access to tables and views
© Ellis Cohen 2001-2008
6
Revocation
Whenever a database user grants another database
user a privilege, the user can later revoke that
privilege
After SCOTT grants TOMMY access to ScottInfo
SCOTT> GRANT SELECT, UPDATE(nam,sal), INSERT
ON ScottInfo TO TOMMY
SCOTT can later revoke any or all of those privileges
from TOMMY
SCOTT> REVOKE SELECT, UPDATE(sal)
ON ScottInfo FROM TOMMY
© Ellis Cohen 2001-2008
7
Selective READ Access Problem
Suppose EmpDB is the DBA (and owner) of a
company's employee information table,
Emps( empno, ename, deptno, addr, mgr,
job, sal )
EmpDB wants to allow SMITH & ADAMS to be
able to see name and address information
in Emps, but no other information.
What SQL commands does EmpDB execute to
ensure that SMITH or ADAMS have the
appropriate access to the Emps table.
© Ellis Cohen 2001-2008
8
Granting Access to Views
EmpDB> GRANT SELECT(ename,addr) ON Emps
TO SMITH, ADAMS
Access can
doesn't work in most databases.
be granted to
Here's what does work:
a view instead
of to the
EmpDB> CREATE VIEW NameAddrView AS
underlying
SELECT ename, addr
table
FROM Emps
EmpDB> GRANT SELECT ON NameAddrView
TO SMITH, ADAMS
Now SMITH can execute (for example)
SMITH> SELECT addr FROM EmpDB.NameAddrView
WHERE ename = 'BLAKE'
Suppose EmpDB only wants to let SMITH and ADAMS see
names & addresses of tuples of CLERKs in Emps?
© Ellis Cohen 2001-2008
9
Granting Access to Restricted Views
EmpDB> CREATE VIEW NameAddrView AS
SELECT ename, addr
FROM Emps
WHERE job = 'CLERK'
EmpDB> GRANT SELECT ON NameAddrView
TO SMITH, ADAMS
Now SMITH can still execute
SMITH> SELECT addr FROM EmpDB.NameAddrView
WHERE ename = 'BLAKE'
© Ellis Cohen 2001-2008
10
Secure Updates
with
Views & Procedures
© Ellis Cohen 2001-2008
11
Individual Update Problem
Suppose BLAKE needs to be able to
change his own address in Emps?
What SQL commands (views & grants
only) does EmpDB execute to
ensure that BLAKE has the
appropriate access to Emps to
change his own address, but can't
make other changes
© Ellis Cohen 2001-2008
12
Individual Update Solution
EmpDB> CREATE VIEW ChangeBlakeView AS
SELECT addr FROM Emps
WHERE ename = 'BLAKE'
EmpDB> GRANT SELECT, UPDATE
ON ChangeBlakeView TO BLAKE
Now BLAKE can execute
BLAKE> UPDATE EmpDB.ChangeBlakeView
SET addr = '31 Upham Way, …'
© Ellis Cohen 2001-2008
13
Secure Modification Problem
Supose that EmpDB wants to allow
SMITH to change anyone's address
in the Emps table (given their
name)
What SQL commands (views & grants
only) does EmpDB execute to
ensure that SMITH has the
appropriate capabilities?
© Ellis Cohen 2001-2008
14
Secure Modification Solution
EmpDB> CREATE VIEW NameAddrView AS
SELECT ename, addr FROM Emps
EmpDB> GRANT SELECT, UPDATE(addr)
ON NameAddrView TO SMITH
Now SMITH can execute
SMITH> UPDATE EmpDB.NameAddrView
SET addr = '31 Upham Way, …'
WHERE ename = 'BLAKE'
© Ellis Cohen 2001-2008
15
Granting Access to Operations
Suppose EmpDB has defined a stored DB
DoSomething, which does something
If TOMMY is connected, TOMMY can try to
use that procedure by doing
TOMMY> EXECUTE DoSomething()
This will only be allowed if EmpDB has
granted TOMMY the privilege to EXECUTE
DoSomething
EmpDB> GRANT EXECUTE ON DoSomething
TO TOMMY
Based on this, is there another way to solve the
Secure Modification Problem?
© Ellis Cohen 2001-2008
16
Secure Operation Approach
EmpDB> CREATE PROCEDURE UpdateAddr
( aNam varchar, anAddr varchar ) IS
BEGIN
Uses Emps,
UPDATE Emps SET addr = anAddr
not EmpDB.Emps
WHERE ename = aNam
END;
Domain Switching:
Because this procedure is
EmpDB> GRANT EXECUTE defined by EmpDB, it executes
ON UpdateAddr
in EmpDB's schema, with
TO SMITH
EmpDB's privileges, even
when called by SMITH
Now SMITH can invoke
SMITH> EXECUTE EmpDB.UpdateAddr(
'BLAKE', '31 Upham Way, …' );
© Ellis Cohen 2001-2008
17
Granting Access to
Packaged Operations
Suppose EmpDB has defined a package EmpPkg,
with procedure DoSomething
If TOMMY is connected,
TOMMY can try to execute that function by doing
TOMMY> EXECUTE
EmpDB.EmpPkg.DoSomething()
This will only be allowed if
EmpDB has granted TOMMY the privilege to
EXECUTE the operations in EmpPkg
EmpDB> GRANT EXECUTE ON EmpPkg TO TOMMY
© Ellis Cohen 2001-2008
18
Dynamic
Access Control
using
Database Identity
© Ellis Cohen 2001-2008
19
Using USER
USER (in an Oracle SQL query) refers to the
database identity of the user who is connected
(e.g. SCOTT, EmpDB)
USER can be very useful in conjunction with
discretionary access control
Suppose every employee connects to the database
and their database identity is the same as their
employee name (e.g. KING, SMITH, etc.).
What does the following query return:
SELECT job FROM Emps
WHERE ename = USER
© Ellis Cohen 2001-2008
20
USER-Based Queries
SELECT job FROM Emps
WHERE ename = USER
returns the job of the (employee
whose name is the same as the)
current user
© Ellis Cohen 2001-2008
21
Dynamic Access View Problem
Suppose BLAKE & ADAMS need to be
able to change their own address in
Emps?
What SQL commands (views & grants
only) does EmpDB execute to
ensure that BLAKE and ADAMS have
the appropriate access to Emps to
change their own address, but can't
make other changes
Solve this using a single dynamic
access view – a view which shows
different information to different
users!
© Ellis Cohen 2001-2008
22
Dynamic Access Views
EmpDB> CREATE VIEW ChangeMyAddrView AS
SELECT addr FROM Emps
WHERE ename = USER
EmpDB> GRANT SELECT, UPDATE
ON ChangeMyAddrView
TO BLAKE, ADAMS
Now BLAKE can execute
BLAKE> UPDATE EmpDB.ChangeMyAddrView
SET addr = '31 Upham Way, …'
Can this also be done by defining a procedure?
© Ellis Cohen 2001-2008
23
Dynamic Access Procedures
EmpDB>
CREATE PROCEDURE ChangeAddr(
newaddr varchar ) IS
BEGIN
UPDATE Emps SET addr = newaddr
WHERE ename = USER
END;
Even though this procedure runs in EmpDB's
domain, USER still refers to the database identity
of the user who initially connected to the database
EmpDB> GRANT EXECUTE ON ChangeAddr
TO BLAKE, ADAMS
Now BLAKE can execute
BLAKE> EXECUTE EmpDB.ChangeAddr(
'31 Upham Way, …' )
© Ellis Cohen 2001-2008
24
Using PUBLIC Grants
EmpDB> GRANT SELECT, UPDATE
ON ChangeMyAddrView
TO PUBLIC
This allows any user to use
ChangeMyAddrView
If we still wanted to only allow
BLAKE & ADAMS to change their
address, how could
ChangeMyAddrView to be written?
© Ellis Cohen 2001-2008
25
Use Stronger Access-Limiting View
EmpDB> CREATE VIEW ChangeMyAddrView AS
SELECT addr FROM Emps
WHERE ename = USER
AND USER IN ( 'BLAKE', 'ADAMS' )
EmpDB> GRANT SELECT, UPDATE
ON ChangeMyAddrView
TO PUBLIC
Now BLAKE can execute
BLAKE> UPDATE EmpDB.ChangeMyAddrView
SET addr = '31 Upham Way, …'
© Ellis Cohen 2001-2008
26
Complex View Problem
EmpDB would like to let every
employee view salaries.
• Every user can see their own salary
• Dept managers can additionally see
the salaries of all the employees in
their depts.
• The president can see everyone's
salary
What SQL commands (views & grants
only) does EmpDB execute?
Can this be done using a single view?
© Ellis Cohen 2001-2008
27
Complex Views
EmpDB> CREATE VIEW SalView AS
SELECT empno, ename, sal FROM Emps
WHERE ename = USER
OR deptno = (SELECT deptno FROM Emps
WHERE ename = USER
AND job = 'DEPTMGR')
OR 'PRESIDENT' = (SELECT job FROM Emps
WHERE ename = USER)
EmpDB> GRANT SELECT ON SalView
TO PUBLIC
Now SCOTT can execute
SCOTT> SELECT * FROM EmpDB.SalView
© Ellis Cohen 2001-2008
28
Database Applications
Using
Database Identity
© Ellis Cohen 2001-2008
29
Using Database Application
The application data is still stored in its
own schema (e.g. EmpDB)
Each user connects directly to the database
using their own identity (e.g. SCOTT,
CLARK, KING)
Each user executes database application
operations. These are allowed based on
• USER identity and/or
• The privileges granted to the user
© Ellis Cohen 2001-2008
30
Authentication with
Database-Identity
EmpDB
App User
CLARK
Web
Browser
DB App on
Web/App
Server
Application
authenticates the
user CLARK by
connecting to the
database as that
user
(or using a
connection that
CLARK provides)
CLARK
Database authenticates
the connection made
by/for CLARK
The application can
only access the
EmpDB views/ops
granted to CLARK
These views/ops use
the USER identity to
control access
© Ellis Cohen 2001-2008
31
Views using Database Identity
ShowSals
SELECT empno, ename, sal
FROM EmpDB.EmpMgrView
Middle-Tier
Code
run by
connected
user –
e.g. CLARK
+ An employee's salary can only be seen by the
employee or by their direct manager
CREATE VIEW EmpMgrView IS
SELECT * FROM Emps
WHERE ename = USER
OR mgr =
(SELECT empno FROM Emps
WHERE ename = USER)
EmpDB
Data-Tier
code
EmpDB> GRANT EmpMgrView TO PUBLIC
© Ellis Cohen 2001-2008
32
Procedures Using Database Identity
MoveDept( :deptno, :loc )
Executed by a
USER from the
middle-tier
BEGIN
EmpDB.EmpPkg.MoveDept( :deptno, :loc );
pl( 'Dept Moved' );
EXCEPTION WHEN OTHERS THEN doerr();
END;
Only the President can move a department
PROCEDURE MoveDept( aDeptno int, aLoc varchar ) IS
BEGIN
In EmpDB.EmpPkg
UPDATE Depts
SET loc = aLoc
WHERE deptno = aDeptno AND
(SELECT job FROM Emps
WHERE ename = USER) = 'PRESIDENT';
CheckResults( 'Could not move department' );
END;
EmpDB> GRANT EXECUTE ON EmpPkg TO PUBLIC
© Ellis Cohen 2001-2008
33
Reusable Check Procedure
Only the President can move a department
PROCEDURE MoveDept( aDeptno int, aLoc varchar ) IS
BEGIN
CheckPrez();
UPDATE Depts
SET loc = aLoc
WHERE deptno = aDeptno;
CheckResults( 'Could not move department' );
END;
PROCEDURE CheckPrez IS
aJob Emps.job%TYPE;
BEGIN
SELECT job INTO aJob FROM Emps
WHERE ename = USER;
IF job != 'PRESIDENT' THEN
RAISE_APPLICATION_ERROR( -20078,
'You must be the president to do that' );
END IF;
END;
© Ellis Cohen 2001-2008
34
Using a Trigger
Only the President can move a department
PROCEDURE MoveDept( aDeptno int, aLoc varchar ) IS
BEGIN
UPDATE Depts
SET loc = aLoc
WHERE deptno = aDeptno;
CheckResults( 'Could not move department' );
END;
CREATE OR REPLACE TRIGGER PrezMove
BEFORE UPDATE OF loc ON Depts
FOR EACH ROW
CALL CheckPrez
-- Note: requires CheckPrez use
-- autonomous transactions
© Ellis Cohen 2001-2008
35
Grant-Based
Enforcement
© Ellis Cohen 2001-2008
36
Grants & Gateways
Access constraints can be enforced
using database identity by
– Gateways with Dynamic Access: Gateway
views/packages are granted to PUBLIC,
with access controlled dynamically based
on USER
– Differential Grants: Tables (and other
objects) are differentially granted to
users with appropriate privilege
These can also be combined
© Ellis Cohen 2001-2008
37
Grant-Based Enforcement
MoveDept( :deptno, :loc )
Executed by a
USER from the
middle-tier
BEGIN
UPDATE EmpDB.Depts
SET loc = :loc
WHERE deptno = :deptno;
CheckResults( 'Could not move department' );
pl( 'Dept Moved' );
EXCEPTION WHEN OTHERS THEN doerr();
END;
Only the President (i.e. KING) can move a department
EmpDB> GRANT SELECT, INSERT,
UPDATE, DELETE ON Depts TO KING
© Ellis Cohen 2001-2008
38
Grant-Based Advantages
Grant-based implementations allow
different sets of privileges to be
granted to different users
This limits the amount of damage a
user can potentially do if
access-checking is incorrect
But: grant-based control can be
hard to administer
© Ellis Cohen 2001-2008
39
Gateway Advantages
Gateways
(Secure Data-Tier
Implementations)
• support information hiding,
allowing representations to be
changed without changing
middle-tier code
• limit damage that can be done
to tables if middle-tier is
compromised
© Ellis Cohen 2001-2008
40
Database vs
Data-Tier Identity
for Secure Gateways
© Ellis Cohen 2001-2008
41
Database Connections
In a real middle-tier application, how
could database connections be arranged
on a per-user basis?
1. The user first connects to the database, and
then "hands" the DB connection to the
application when it connects to the
application.
2. The user passes the database login id and
password to the application, which opens a
connection to the database on behalf of the
user (requires lots of trust)
3. The user passes single-use time-limited
encrypted credentials to the application
(which can't read them), which passes them
to the database (which can) which then opens
a connection on the user's behalf.
© Ellis Cohen 2001-2008
42
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
UI
Management
Web or
Application
Server
Middle Tier
© Ellis Cohen 2001-2008
Database
Server
The DB
application
maintains a
connection on
behalf of each
interactive or webservice user
(e.g. SCOTT,
CLARK)
Data Tier
43
Virtual Connections
Database connections are expensive
Web/App servers typically use a small
pool of connections, which are used
for requests, independent of which
user made a user operation request
This could make it impossible to
enforce access constraints based on
database user identity.
Some databases (e.g. Oracle) allow
each user to have a separate virtual
connection over an actual database
connection.
© Ellis Cohen 2001-2008
44
Limitations of DB User Identity
Virtual connections make it feasible
to identity users by their DB
identity and gain the added security
of database-managed access
control.
However, there is administrative DB
overhead for every new DB user.
This may be acceptable inside a
medium-sized organization.
It may not be reasonable for a large
organization, and is definitely not
reasonable for e-commerce
applications, where users are part
of the general population!
© Ellis Cohen 2001-2008
45
Secure Database Identity vs
Secure Data-Tier Identity
EmpDB
App User
CLARK
Web
Browser
DB App on
Web/App
Server
CLARK
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
46
Comparing Secure Data-Tier vs
Database Identity
Secure Data-Tier Identity provides almost the same
level of security as Database Identity without
requiring a separate DB identity for each application
user.
Consider the impact if the application is hijacked or the
application's connection to the database is compromised
In both cases
The intruder can only masquerade as the users who are
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.
Why use Database Identity?
• Oracle Security Policies are designed to work
with Database Identity
• Database Identity supports Role-Based
enforcement!
© Ellis Cohen 2001-2008
47
Role-Based
Data Tier
Implementation
© Ellis Cohen 2001-2008
48
Simple User Role Diagram
KnownUser
Admin
KnownUser
Query: ShowAddr( userid ) -- show a user's address if public
Action: UpdateMyAddr ( … ) -- update current user's address
Admin
Query: ShowAllAddrs -- show all public user addresses
Action: UpdateAddr ( userid, … ) -- update user's address
Implemented in AddrDB
© Ellis Cohen 2001-2008
49
Role-Based Implementation
• Create a separate package for each role
• Users who have a particular role are
granted
– EXECUTE access for packages for their role
(and super-roles)
– SELECT access for views for their role (and
super-roles)
• Can completely enforce role-based access
constraints
• Can simplify the enforcement of identitybased access constraints
© Ellis Cohen 2001-2008
50
Organizing for Roles
KnownUser
Admin
KnownUser
ShowAddr( userid ) -- show a user's address if public
-- create a view granted to all known users
UpdateMyAddr ( … ) -- update current user's address
-- put a procedure in a package granted to all known users
Admin
ShowAllAddrs -- show all public user addresses
-- put a cursor in a package granted to all known users
Action: UpdateAddr ( userid, … ) -- update user's address
-- put a procedure in a package granted to all known users
© Ellis Cohen 2001-2008
51
Admin View/Package
VIEW ShowAllAddrView IS
SELECT userid, street, city, state, zip
FROM Users
WHERE isPublic = 'T'
PROCEDURE UpdateAddr( usrid: varchar, … ) IS
BEGIN
UPDATE Users SET …
In AdminPkg
WHERE userid = usrid;
END;
-- Neither needs to check that USER is an Admin, since
-- the view & package will only be granted to an Admin
AddrDB> GRANT SELECT ON ShowAllAddrView TO
BIGWIG, KAHUNA, …
AddrDB> GRANT EXECUTE ON AdminPkg TO
BIGWIG, KAHUNA, …
© Ellis Cohen 2001-2008
The Admin
users
52
KnownUser Package
CURSOR AddressById( usrid: varchar ) IS
SELECT street, city, state, zip
FROM Users
In KnownPkg
WHERE userid = usrid
AND isPublic = 'T'
PROCEDURE UpdateMyAddr(… ) IS
UPDATE Users SET …
WHERE userid = USER
In KnownPkg
-- Neither needs to check that USER is a KnownUser
-- since the package will only be granted to known users
AddrDB> GRANT EXECUTE ON KnownPkg TO
BIGWIG, KAHUNA, …,
TOM, DICK, HARRY, …
© Ellis Cohen 2001-2008
The Admin
users
The other
known users
53
EmpDB
DeptMgr
Employee
Executive
Define views and packages
(containing procedures, functions & cursors)
specific to each role
President
EmpPkg, granted to all employees
ExecPkg, granted just to executives
(either dept mgrs or the president)
DeptMgrPkg, granted just to dept mgrs
PrezPkg, granted just to the President
© Ellis Cohen 2001-2008
54
Grants for the President
CREATE PACKAGE PrezPkg AS
PROCEDURE MoveDept( … );
PROCEDURE CreateDept( … );
PROCEDURE DestroyDept( … );
END PrezPkg;
-- only KING is granted access to PrezPkg
GRANT
GRANT
GRANT
GRANT
EXECUTE ON PrezPkg TO KING;
EXECUTE ON DeptMgrPkg TO KING;
EXECUTE ON ExecPkg TO KING;
EXECUTE ON EmpPkg TO KING;
© Ellis Cohen 2001-2008
MoveDept,
CreateDept &
DestroyDept
are assigned to
the President
role
KING, the
President, needs
to be able to
invoke actions in
all these packages
55
MoveDept
PROCEDURE MoveDept( aDeptno int, aLoc varchar ) IS
BEGIN
UPDATE Depts SET loc = aLoc
WHERE deptno = aDeptno;
CheckResults( 'No such department' );
END;
Note: There is no need to check the constraint that
"Only the president can move depts"
either by
• adding checking code to MoveDept, or
• using a trigger of deleting a Dept
BECAUSE: PrezPkg, which includes MoveDept,
has only been granted to KING!
(although the EmpDB DBA can delete a dept directly!)
© Ellis Cohen 2001-2008
56
Access Constraint Enforcement
Role-based access constraints
– e.g. only an Admin can update any address
– e.g. only a president can move a dept
can be implemented wholly by grants
– there is no need for dynamic access checking
Identity-based access constraints
– require dynamic access checks
– but may be simplified due to grants
Consider:
An employee's salary can only be changed
by the employee's dept mgr or the president
© Ellis Cohen 2001-2008
57
Using Gateways
PROCEDURE ChangeSal ( anEmpno int, aSal number ) IS
BEGIN
CheckDeptMgrOrPrez( anEmpno );
UPDATE Emps SET sal = aSal
WHERE empno = anEmpno;
CheckResults( 'You can't make that salary change' );
END;
Alternatives
1) ChangeSal is in a package that is granted to PUBLIC
2) ChangeSal is in a package (e.g. ExecPkg) that is only
granted to DeptMgrs or the President
© Ellis Cohen 2001-2008
58
Effect of Grants on Checking
PROCEDURE CheckDeptMgrOrPrez ( anEmpno int ) IS
knt int;
BEGIN
CheckPrez(); -- checks if the user is the President
EXCEPTION WHEN OTHERS THEN
SELECT count(*) INTO knt FROM Emps e1, Emps e2
WHERE e1.ename = USER
AND e1.job = 'DEPTMGR'
AND e1.deptno = e2.deptno
AND e2.empno = anEmpno;
IF (knt = 0) THEN
RAISE_APPLICATION_ERROR( -20045,
'You are not the employee's dept manager' );
END IF;
END;
This line is not needed if ChangeSal's package has
only been granted to DeptMgrs & the President
(but better to keep it there for redundancy)
© Ellis Cohen 2001-2008
59
Dynamic
Role
Assignment
© Ellis Cohen 2001-2008
60
Dynamic Roles
• Role-Based Implementations makes
most sense when users do not
change roles, or change roles
rarely.
• If users do change roles, then their
privileges must change to
correspond with their change in
roles (e.g. if their job changes)
• This can be facilitating by defining
procedures to add or remove the
privileges
© Ellis Cohen 2001-2008
61
Add Privileges Dynamically
PROCEDURE AddPrivs( ename varchar, job varchar ) IS
BEGIN
IF job = 'PRESIDENT' THEN
GrantTo( 'EXECUTE ON PrezPkg', ename );
END IF;
IF job IN ('PRESIDENT, 'DEPTMGR') THEN
GrantTo( 'EXECUTE ON ExecPkg', ename );
END IF;
GrantTo( 'EXECUTE ON EmpPkg', ename );
END;
In EmpDB.RolePkg
PROCEDURE GrantTo( what varchar, gto varchar ) IS
PRAGMA AUTONOMOUS_TRANSACTION;
sqlstr varchar(100);
BEGIN
sqlstr := 'GRANT ' || what || ' TO ' || gto;
EXECUTE IMMEDIATE sqlstr;
COMMIT;
END;
© Ellis Cohen 2001-2008
62
Remove Privileges Dynamically
PROCEDURE RemovePrivs( ename varchar, job varchar )
IS BEGIN
IF job = 'PRESIDENT' THEN
RemoveFrom( 'EXECUTE ON PrezPkg', ename );
END IF;
IF job IN ('PRESIDENT, 'DEPTMGR') THEN
RemoveFrom( 'EXECUTE ON ExecPkg', ename );
END IF;
RemoveFrom( 'EXECUTE ON EmpPkg', ename );
END;
In EmpDB.RolePkg
PROCEDURE RemoveFrom( what varchar, gfrom varchar ) IS
PRAGMA AUTONOMOUS_TRANSACTION;
sqlstr varchar(100);
BEGIN
sqlstr := 'REVOKE ' || what || ' FROM ' || gfrom;
EXECUTE IMMEDIATE sqlstr;
COMMIT;
END;
© Ellis Cohen 2001-2008
63
Dynamically Assigning Privileges
In EmpDB.ExecPkg
PROCEDURE AddEmp(
anEmpno int, anEname varchar, aSal number,
aJob varchar, aMgr int, aDeptno int ) IS
BEGIN
INSERT INTO
Emps( empno, ename, sal, job, mgr, deptno )
VALUES
( anEmpno, anEname, aSal, aJob, aMgr, aDeptno );
-- give them the appropriate privileges
RolePkg.AddPrivs( anEname, aJob );
END;
Similarly, TerminateEmp calls RemovePrivs
ChangeJob and ChangePosition calls both
RemovePrivs (for their old job) and
AddPrivs (for their new job)
© Ellis Cohen 2001-2008
64
Role Grant Based
Enforcement
Using RBAC
(Role-Based
Access Control)
© Ellis Cohen 2001-2008
65
Granting Privileges to Users
Privileges
Users
select on
PersonalView
HARRY
select on
ManagedView
ADAMS
execute on
EmpPkg
© Ellis Cohen 2001-2008
66
Roles & Privileges
Users are granted a bundle or privileges
specific to their roles.
So far, we've seen how to do that explicitly
(in UserPkg.AddPrivs)
SQL has a built-in role mechanism, which
associates a bundle of privileges with a role.
Associating a role with a user
automatically gives that user access
to all of the role's privileges
© Ellis Cohen 2001-2008
67
Using Roles
Privileges
Role
select on
PersonalView
select on
ManagedView
Users
HARRY
Employee
ADAMS
execute on
EmpPkg
…
GRANT EXECUTE ON EmpPkg TO Employee;
GRANT Employee TO HARRY, ADAMS;
© Ellis Cohen 2001-2008
68
SQL Roles
Any sufficiently privileged user can
create SQL roles
To allow EmpDB to create roles:
(you'll need to be connected to the DB as a
privileged user – e.g. SYSTEM -- to do this)
SYSTEM> GRANT CREATE ROLE TO EmpDB
Now, EmpDB can create roles
EmpDB> CREATE ROLE President
EmpDB> CREATE ROLE DeptMgr
EmpDB> CREATE ROLE Employee
© Ellis Cohen 2001-2008
Define a SQL role
for each
concrete role
69
Granting Privileges to Roles
Instead of granting privileges directly to
users
• Grant the privileges to roles
• Grant the roles to users
As part of application setup, grant the
privileges to the roles
EmpDB> GRANT EXECUTE ON EmpPkg
TO Employee, DeptMgr, President
EmpDB> GRANT EXECUTE ON ExecPkg
TO DeptMgr, President
EmpDB> GRANT EXECUTE ON PrezPkg
TO President
…
© Ellis Cohen 2001-2008
70
Granting Roles to Users
So, since KING is the President,
we grant KING all the privileges
associated with the President role.
GRANT President TO KING;
© Ellis Cohen 2001-2008
71
Using Roles for EmpDB
Privileges
Roles
execute on
PrezPkg
Users
KING
President
JONES
execute on
ExecPkg
DeptMgr
…
ADAMS
execute on
EmpPkg
Employee
© Ellis Cohen 2001-2008
72
Add Privileges Using Roles
PROCEDURE AddPrivs( ename varchar, job varchar ) IS
BEGIN
CASE job
WHEN 'PRESIDENT' THEN
GrantTo( 'President', ename );
WHEN 'DEPTMGR' THEN
GrantTo( 'DeptMgr', ename );
ELSE
GrantTo( 'Employee', ename );
END CASE;
END;
When a new user is added, or a user's job changes,
they need to be granted the role corresponding to their job.
Similarly for RemovePrivs
© Ellis Cohen 2001-2008
73
Granting Roles to Roles
A role can be granted
to a parent role
DeptMgr
Employee
Executive
Define a role for each concrete or abstract role.
Grant privileges to the corresponding role
President
GRANT EXECUTE ON EmpPkg TO Employee
GRANT EXECUTE ON ExecPkg TO Executive
GRANT EXECUTE ON PrezPkg TO President
GRANT Employee TO Executive
GRANT Executive TO DeptMgr
GRANT Executive TO President
Grant roles
based on the
role hierarchy
© Ellis Cohen 2001-2008
74
Using Hierarchical Roles
Privileges
Roles
Users
KING
execute on
PrezPkg
President
JONES
execute on
ExecPkg
Executive
DeptMgr
Employee
…
ADAMS
execute on
EmpPkg
© Ellis Cohen 2001-2008
75
Mapping Between
Database and
Application Identities
© Ellis Cohen 2001-2008
76
User Identification Problem
Just because applications
connect to the database
with the user's DB login id
does not necessarily mean
those identities are directly
useful to application code
If an employee's ename does NOT correspond to
a user's DB login id (so WHERE ename = USER
is not effective) how can we figure out which
tuples in Emps should be accessed for a user
doing particular operations?
© Ellis Cohen 2001-2008
77
Authentication with
Database-Identity & Mapping
EmpDB
curusr
7782
App User
CLARK
Web
Browser
DB App on
Web/App
Server
Application
authenticates the
user CLARK by
connecting to the
database as that
user
(or using a
connection that
CLARK provides)
CLARK
Database authenticates the
connection made by/for CLARK
The application can only access
the EmpDB views/ops granted
to CLARK
EmpDB maps CLARK to 7782,
so EmpDB code can be based
on application identity
© Ellis Cohen 2001-2008
78
User Mapping Table
Maintain a table that maps between a
user's DB identity and their
application identity.
Use a package to provide mapping
functions (which use the table)
Have data-tier code call mapping
functions, and then make decisions
based on employee number
WHERE ename = USER

WHERE empno = UserPkg.GetUser
© Ellis Cohen 2001-2008
79
UserPkg Mapping Support
CREATE TABLE DBUsers(
userid int PRIMARY KEY, dbid varchar(60) UNIQUE );
CREATE PACKAGE BODY UserPkg AS
curuser int;
PROCEDURE Register( anId int, aDBid varchar ) IS
BEGIN
INSERT INTO DBUsers VALUES( anId, aDBid );
END;
FUNCTION GetUser RETURNS int IS
BEGIN
IF curuser IS NULL THEN
SELECT userid INTO curuser FROM DBUsers
WHERE dbid = USER;
RETURN curuser;
END;
…
END UserPkg;
No need for a Login procedure (or a password), since we rely on
the authentication done when the user connects to the Database.
© Ellis Cohen 2001-2008
80
ChangeSal Implementation w Mapping
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 CLARK
PROCEDURE ChangeSal ( anEmpno int, aSal number ) IS
BEGIN
UPDATE Emps SET sal = :sal
WHERE empno = :empno
AND mgr = UserPkg.GetUser;
CheckResults( 'You are not the employee's manager' );
END;
Data-Tier code in Package EmpPkg in EmpDB
© Ellis Cohen 2001-2008
81
Register the DB & Application ID
Mapping
In EmpDB.ExecPkg
PROCEDURE AddEmp(
anEmpno int, anEname varchar, aSal number,
aJob varchar, aMgr int, aDeptno int ) IS
BEGIN
INSERT INTO
Emps( empno, ename, sal, job, mgr, deptno )
VALUES
( anEmpno, anEname, aSal, aJob, aMgr, aDeptno );
-- Register their employee name with their dbid
UserPkg.Register( anEmpno, anEname );
-- give them the appropriate privileges
RolePkg.AddPrivs( anEname, aJob );
END;
If an employee's ename was NOT their
database identity, that would have to
be supplied as an added parameter
© Ellis Cohen 2001-2008
82