1 / 18

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 Oracle10 g: Specific functions. Overview of Virtual Private Databases (continued).

naasir
Télécharger la présentation

Virtual Private Databases

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  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?

More Related