**SQL** Design and implement a Virtual Private Database that limits database acc
ID: 670448 • Letter: #
Question
**SQL**
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.
In most cases you will need to create a separate policy and function for each of the two tables you select.
*** Please inculde a .sql script file containing all SQL statements you used for this effort. All SQL statements should be in one file with appropriate connect statements to differentiate users who run the script. The script file should contain comments describe each major set of SQL statements.
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
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.