Transcript Virtual Private Databases

Virtual Private Databases
Dr. Gabriel
Overview of Virtual Private Databases
• A VPD deals with data access
• VPD controls data access at the row or column
level
• SQL Server 2005: use VIEW data object
• Oracle10g:
– Specific functions
2
Overview of Virtual Private Databases
(continued)
3
Overview of Virtual Private Databases
(continued)
• Shared database schema:
– Containing data that belongs to different users
– User view or update only data he or she owns
• Purposes/benefits:
– Security requirements necessitate data access
be restricted at row or column level
– One database schema serves multiple unrelated
groups or entities
4
Implementing Row- and Column-level
Security with SQL Server
• SQL Server 2000 does not support VPDs; you
can mimic their functionality
• Use views and expand security models
5
Implementing a VPD Using Views
• View object limits what users can see and do
with existing data: hides columns or rows from
users
• CREATE VIEW statement: creates data views
6
Hiding Rows Based on the Current
User
• System function USER:
– Returns database user
– Used to implement row-based security
• Implementing row-based security with views:
– Need a column in your tables for the row’s
owner
– Preface it with “CTL”
7
Hiding Rows Based on the Current
User
• Example:
Create table customers (
ID int not null primary key,
LName varchar(50) not null,
…,
CtlUpdUser varchar(200) not null default user)
Create view vcustomer
As
Select id, lname
From customers
Where CtlUpdUser =user
8
Row-based Security Using Access
Levels
• Variation of both:
– Application table-based security model
– Application function-based security model
• Access levels:
–
–
–
–
0 = No access
1 = select
2 = select, insert
3 = select, insert, update
9
Row-based Security Using Access
Levels (continued)
• Access levels (continued):
– 4 = select, insert, update, delete
– 5 = administrator access
• Steps:
– Create the APPLICATION USERS table
– Alter the CUSTOMER table to include the
ACCESS CONTROL column
– With the security structure in place use a view to
retrieve data
10
Row-based Security Using Application
Functions
• Steps (continued): apply privileges
• Drawbacks: it allows insertion, update, and
deletion of records
• Alternatives:
– Use stored procedures
– Use application functions: access table list a
function instead of a level
11
Row-based Security Using Application
Functions
create table tappusersaccess (
username varchar(200) not null primary key,
AccessLevel int not null default 0 )
create table tcustomers (
ID int not null primary key,
LName varchar(200) not null,
...,
AccessLevel int not null default 0 )
create view vcustomer
as
select id, lname
from tcustomers
where accesslevel>0 and accesslevel <=(select isnull(accesslevel,0) from
tappusersaccess where username=user)
12
Row-based Security Using Application
Functions
create procedure pcustomerselect
as
select id,lname
from tcustomers
where accesslevel>0 and accesslevel <=(select isnull(accesslevel,0) from
tappusersaccess where username=user)
create procedure pcustomerdelete
@id int
as
declare @level int
select @level=select isnull(accesslevel,0) from tappusersaccess where username=user
if @level>=4
begin
delete from tcustomers where id=@id and accesslevel>=@level
end
13
Column-based Security (continued)
• Access-level control with SQL Server steps:
– Create the APP_TABLES table
– Create the APP_COLUMNS columns
– All access to the tables must be performed with
stored procedures
14
Column-based Security (continued)
create table tapptables (
tableid int not null primary key,
tablename varchar(200) not null
)
create table tapptablecolumns (
columnid int not null primary key,
tableid int not null ,
columnname varchar(200) not null,
AccessLevel int not null default 0
)
create table tappuseraccess
(
username varchar(200),
accesslevel int
)
15
Column-based Security (continued)
alter proc pcustomerselect
as
declare @qry varchar(max), @level int, @col varchar(128),@ct int;
select @level=(select isnull(accesslevel,0) from tappuseraccess where username=user);
declare cur cursor for
select columnname
from tapptablecolumns a inner join tapptables b on a.tableid=b.tableid
where b.tablename='tcustomers' and a.accesslevel<=@level
select @qry='select '
select @ct=0
open cur
fetch next from cur into @col
while @@fetch_status=0
begin
if @ct=0
begin
select @qry=@qry+@col
end
else
begin
select @qry=@qry+', '+@col
end
select @ct=@ct+1
fetch next from cur into @col
end
close cur
deallocate cur
select @qry=@qry + ' from tcustomers'
print @qry
execute (@qry)
16
Column-based Security (continued)
• Column Privileges with SQL Server
– set update permissions for a user/role on a
particular column in a particular table
• Ex. grant update on customer(phone) to abc
17
Questions?
18