Design and implement a Virtual Private Database that limits database access. You
ID: 3873762 • Letter: D
Question
Design and implement a Virtual Private Database that limits database access. Your design should be based on two of your columns and two of your tables. (Hint: where col1 = value1 AND col2 = value2). Which columns and tables you choose is up to you and will be based on your initial design. You may alter your tables if it makes sense to do so.
Develop and conduct a test plan that clearly shows how access has been restricted by using the Virtual Private Database.
In most cases you will need to create a separate policy and function for each of the two tables you select.
Explanation / Answer
There are 2 tables:
AssetsTable and MaintainAssetsTable
ID
Item
Description
Category
Condition
Acquired Date
Purchase Price
Current Value
Location
Manufacturer
Model
Comments
Attachments
Retired Date
1
Desk top Computer Systems
ImMovable Asset
(4) Bad
$200.00
$120.00
0
2
LapTops
Movable Asset
(2) Good
$5,421.00
$4,053.00
0
3
LAN Cables
Immovable
(5) Poor
$45.00
$42.00
0
4
Pheripherals (Mouse, Keyboards)
Immovable
(2) Good
$54.00
$51.00
0
5
EthernetCards
Immovable
(2) Good
$14.00
$10.00
0
6
Housing
Fixed Asset
(1) Great
$200,000.00
$225,000.00
0
7
Pen Tablet
Movable Asset
(4) Bad
$74.00
$65.00
0
8
(1) Category
(2) Good
$0.00
0
Maintain Assets Table:
ID
Title
Priority
Status
% Complete
Description
Start Date
Due Date
Attachments
1
Fix LAN Cables
(1) High
In Progress
50%
9/27/2015
0
2
Upgrade RAM on old desktops
(3) Low
Completed
100%
9/27/2015
0
3
Meet Customer
(2) Normal
Deferred
0%
9/27/2015
0
4
Follow up Incident Reports
(1) High
Waiting on someone else
20%
9/27/2015
9/30/2015
0
5
Prepare Management Reports
(1) High
In Progress
52%
9/27/2015
0
6
Conduct Programmers Meeting
(1) High
In Progress
56%
9/27/2015
10/15/2015
0
7
Install TeamViewwer Software
(1) High
Completed
100%
9/27/2015
0
8
Upgrade Virus Scanners in all 20 Systems
(3) Low
Deferred
0%
9/27/2015
10/30/2015
0
9
Recruit New Testers
(1) High
Not Started
1%
9/27/2015
11/20/2015
0
10
Advertise
(3) Low
In Progress
45%
9/27/2015
11/10/2015
0
Policy:
CREATE CONTEXT CategoryContext USING setCategoryContextPackage;
CREATE OR REPLACE PACKAGE RestrictCategoryContextPackage IS
PROCEDURE RestrictCategoryToImmovableAssetsOnly;
EBD;
/
CREATE OR REPLACE PACKAGE BODY RestrictCategoryContextPackage IS
PROCEDURE RestrictCategoryToImmovableAssetsOnly
IS
LocalVariableCategoryId NUMBER;
BEGIN
SELECT CategoryID INTO LocalVariableCategoryID FROM CheggDataBase.AssetsTable
WHERE Category = “ImmovaleAssets”;
DBMS_SESSION.SET_CONTEXT(‘CategoryContext’,’CategoryId’, LocalVariableCategoryId);
END;
END;
/
ID
Item
Description
Category
Condition
Acquired Date
Purchase Price
Current Value
Location
Manufacturer
Model
Comments
Attachments
Retired Date
1
Desk top Computer Systems
ImMovable Asset
(4) Bad
$200.00
$120.00
0
2
LapTops
Movable Asset
(2) Good
$5,421.00
$4,053.00
0
3
LAN Cables
Immovable
(5) Poor
$45.00
$42.00
0
4
Pheripherals (Mouse, Keyboards)
Immovable
(2) Good
$54.00
$51.00
0
5
EthernetCards
Immovable
(2) Good
$14.00
$10.00
0
6
Housing
Fixed Asset
(1) Great
$200,000.00
$225,000.00
0
7
Pen Tablet
Movable Asset
(4) Bad
$74.00
$65.00
0
8
(1) Category
(2) Good
$0.00
0
Related Questions
drjack9650@gmail.com
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.