Virtual Private Databases

1. Virtual Private Databases Dr. Gabriel

2. 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

3. Overview of Virtual Private Databases (continued)

4. 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

5. 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

6. 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

7. 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”

8. 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

9. 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

10. 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

11. 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

12. 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)

13. 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

14. 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

15. 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 )

16. 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)

17. 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

18. Questions?