Link to Data File: http://castle.eiu.edu/pingliu/tec5363/Project/planning/vpd/db
ID: 3847749 • Letter: L
Question
Link to Data File:
http://castle.eiu.edu/pingliu/tec5363/Project/planning/vpd/dbsec_vpd_script_06.sql
Connect to user SYSTEM. (5 points) When you need to connect to SYSTEM schema, please use a syntax so that it will ask the user to type the password. A typical example will be: "connect system;"
Grant "DBA" role to user DBSEC. This is the same user created on a previous project. If for some reason, the user was dropped, you need to recreate the user before proceeding. (5 points)
Grant "CREATE SESSION" and "CREATE TABLE" privileges to user VPD_CLERK1. (5 points)
Connect to user DBSEC/secc$1new. Please note that the password was from our previous project. If for some reason, the user was dropped or changed, you need to change it back to the above user name and password. (5 points)
Create "CUSTOMER" table with the following columns. Please note slight changes in the table from previous project. (10 points)
Column Name
Data Type
CUSTOMER_ID
Populate the CUSTOMER table using the data in the file provided. Click here to download the data file. (If your web browser is set at auto mode, you may have to right click the word here and select "Save Link Target As" in order to download the script file.)
You may either put all "insert" statements within this file, or provide a link to another file. Refer to the dbsec_ch4_setup.sql file in your downloaded file if you like to see how it was done. If you use the link, please make sure your link works on your computer. (10 points)
Query the table "CUSTOMER" to show the total number of rows from each user.(5 points)
Grant SELECT, DELETE, INSERT and UPDATE privileges on CUSTOMER table to user VPD_CLERK1.(5 points)
Create a policy function, named "DBSEC_ROW_OWNER_FUNCTION" so that only the data that belong to the current user will be selected. In other words, you need to generate a predicate "CTL_UPD_USER=USER." You may refer to the supplement material on this week for a sample code. Please note the owner of the table (DBSEC) has to be able to access its own table. (20 points) (Please note the double quotation mark (") is not a part of the specification.)
Add the policy using DBMS_RLS.ADD_POLICY function. The policy will be named as "MY_OWNER_POLICY." (20 points)
Connect to VPD_CLERK1/Jessie#22. (5 points)
Query the "CUSTOMER" table to show the total number of rows from each user. If everything is successful, you will see only one row, similar to what is on page 231 in your textbook. (5 points)
Tips:
It is important to make sure that you are connected to user DBSEC/sec$1new, before creating the policy function and running DBMS_RLS.ADD_POLICY. Otherwise, you will see ORA-28110. It took me 3 days of research to find out. The textbook is either not clear or confusing on the issue.
The sequence above was tested. Please make sure to do it right on every step. If any step is missed, it may cause a lot of headache on your part.
Make sure to use the exact spelling as specified. Misspelling is considered wrong by computer system. My grading script will not like it either.
Column Name
Data Type
SALES_REP_ID NUMBER(4)CUSTOMER_ID
NUMBER(8) NOT NULL CUSTOMER_SSN VARCHAR2(9) FIRST_NAME VARCHAR2(20) LAST_NAME VARCHAR2(20) ADDR_LINE VARCHAR2(80) CITY VARCHAR2(30) STATE VARCHAR2(30) ZIP_CODE VARCHAR2(9) PHONE VARCHAR2(15) EMAIL VARCHAR2(80) CC_NUMBER VARCHAR2(20) CREDIT_LIMIT NUMBER GENDER CHAR(1) STATUS CHAR(1) COMMENTS VARCHAR2(1024) CTL_UPD_DTTM DATE CTL_UPD_USER VARCHAR2(30) CTL_REC_STAT CHAR(1)Explanation / Answer
Connect system;
----------------------------------Creating the user-----------------------------------
Drop user DBSEC_ADMIN cascade;
create user DBSEC_ADMIN identified by "tec5363admin"
Default tablespace USERS
Temporary tablespace TEMP;
grant create session, create table to DBSEC_ADMIN;
grant create session, create table to DBSEC_CLERK;
grant create session, create table to DBSEC_DEV;
grant create session, create view, create table to DBSEC;
grant create session, create table to VPD_CLERK1;
grant create session, create table to VPD_CLERK2;
grant all privileges to DBSEC;
---------------------------------------connecting to the specific user and creating the customer table------------------------------------
connect DBSEC/tec5363#1;
--------------droping the table--------------
drop table CUSTOMER;
---------------creating the table------------
CREATE TABLE CUSTOMER (
SALES_REP_ID NUMBER(4) null,
CUSTOMER_ID NUMBER(8) NOT NULL,
CUSTOMER_SSN VARCHAR2(9) null,
FIRST_NAME VARCHAR2(20) null,
LAST_NAME VARCHAR2(20) null,
ADDR_LINE VARCHAR2(80) null,
CITY VARCHAR2(30) null,
STATE VARCHAR2(30) null,
ZIP_CODE VARCHAR2(9) null,
PHONE VARCHAR2(15) null,
EMAIL VARCHAR2(80) null,
CC_NUMBER VARCHAR2(20) null,
CREDIT_LIMIT NUMBER null,
GENDER CHAR(1) null,
STATUS CHAR(1) null,
COMMENTS VARCHAR2(1024) null,
CTL_UPD_DTTM VARCHAR2(9) null,
CTL_UPD_USER VARCHAR2(30) null,
CTL_REC_STAT CHAR(1) null
);
-----------------------------------------------------populating the given table using the given data----------------------------------
INSERT INTO CUSTOMER ( SALES_REP_ID, CUSTOMER_ID, CUSTOMER_SSN, FIRST_NAME, LAST_NAME, ADDR_LINE,
CITY, STATE, ZIP_CODE, PHONE, EMAIL, CC_NUMBER, CREDIT_LIMIT, GENDER, STATUS, COMMENTS,
CTL_UPD_DTTM, CTL_UPD_USER, CTL_REC_STAT ) VALUES (
6415, 201340, '969996970', 'Jeffrey', 'Antoine', '9938 Moreno St.', 'Champagne', 'SD'
, ' 43172', '4319071613', 'JAntoine@iodmgpvjdzprccx.gov', '231146832737266', 200000
, 'M', 'A', '/T if=<iWZhEUBC3)/M~6wa#DgR) 129_J68$rJ#f^j$"''y/g~,Iot3yhSr', TO_Date( '11/10/2003 02:22:07 PM', 'MM/DD/YYYY HH:MI:SS AM')
, 'VPD_CLERK1', '2');
INSERT INTO CUSTOMER ( SALES_REP_ID, CUSTOMER_ID, CUSTOMER_SSN, FIRST_NAME, LAST_NAME, ADDR_LINE,
CITY, STATE, ZIP_CODE, PHONE, EMAIL, CC_NUMBER, CREDIT_LIMIT, GENDER, STATUS, COMMENTS,
CTL_UPD_DTTM, CTL_UPD_USER, CTL_REC_STAT ) VALUES (
2336, 403963, '210609613', 'Karie', 'Ha', '759 Chaplin Ct.', 'Southridge Village'
, 'NH', ' 96288', '1232662963', 'KHa@tukqgvyvyevsyoh.ad', '973266583943724', 200000
, 'F', 'A', ']cRHp%hE}g"VVLjjZ"+$!()GnVH?dd~VrQ|sCS:}ru~=xU=}t2{W+.HM:{hV', TO_Date( '11/30/1999 11:56:15 AM', 'MM/DD/YYYY HH:MI:SS AM')
, 'VPD_CLERK2', '2');
INSERT INTO CUSTOMER ( SALES_REP_ID, CUSTOMER_ID, CUSTOMER_SSN, FIRST_NAME, LAST_NAME, ADDR_LINE,
CITY, STATE, ZIP_CODE, PHONE, EMAIL, CC_NUMBER, CREDIT_LIMIT, GENDER, STATUS, COMMENTS,
CTL_UPD_DTTM, CTL_UPD_USER, CTL_REC_STAT ) VALUES (
7719, 103968, '825791045', 'Jona', 'Coffin', '41 Klaus Avenue', 'Apollo', 'NE', ' 77767'
, '5627177805', 'JCoffin@vdypsygzyfuednn.org', '724295527895485', 500000, 'F', 'I'
, 'jh{p:~Jy hNw*Bz]GFciDwpsH$.MfK.<7o.tI8x''Mm=?RiVn1$@`H^1! g', TO_Date( '01/04/2000 11:56:30 AM', 'MM/DD/YYYY HH:MI:SS AM')
, 'VPD_CLERK2', '3');
INSERT INTO CUSTOMER ( SALES_REP_ID, CUSTOMER_ID, CUSTOMER_SSN, FIRST_NAME, LAST_NAME, ADDR_LINE,
CITY, STATE, ZIP_CODE, PHONE, EMAIL, CC_NUMBER, CREDIT_LIMIT, GENDER, STATUS, COMMENTS,
CTL_UPD_DTTM, CTL_UPD_USER, CTL_REC_STAT ) VALUES (
5605, 403972, '573698676', 'Fernando', 'Breen', '15 Wooldridge Blvd.', 'Alta Loma'
, 'GA', ' 30358', '8652295741', 'FBreen@mpomoutekbtsfqr.mil', '369823482138347', 400000
, 'M', 'X', 'eu)=>YmT[s;OsR(cJ[J763nGslN8ov[@B7{a+ zG%bd,%,$)I73W.jvkW', TO_Date( '03/31/2002 11:56:09 AM', 'MM/DD/YYYY HH:MI:SS AM')
, 'VPD_CLERK1', '3');
INSERT INTO CUSTOMER ( SALES_REP_ID, CUSTOMER_ID, CUSTOMER_SSN, FIRST_NAME, LAST_NAME, ADDR_LINE,
CITY, STATE, ZIP_CODE, PHONE, EMAIL, CC_NUMBER, CREDIT_LIMIT, GENDER, STATUS, COMMENTS,
CTL_UPD_DTTM, CTL_UPD_USER, CTL_REC_STAT ) VALUES (
6415, 803979, '607031683', 'Shandra', 'Mathison', '9948 Greathouse Avenue', 'Black Meadow Landing'
, 'IN', ' 18124', '1343508141', 'SMathison@tcngoytrzevkiml.mil', '826040392371009'
, 30000, 'M', 'I', 'ISJu~,71~/z_qaQzz@sSxvwP$Nj+:#S+_''hzS=8bs(d}f[D2TKbcnc(N#5!9'
, TO_Date( '12/02/1999 11:56:01 AM', 'MM/DD/YYYY HH:MI:SS AM'), 'VPD_CLERK1', '4');
INSERT INTO CUSTOMER ( SALES_REP_ID, CUSTOMER_ID, CUSTOMER_SSN, FIRST_NAME, LAST_NAME, ADDR_LINE,
CITY, STATE, ZIP_CODE, PHONE, EMAIL, CC_NUMBER, CREDIT_LIMIT, GENDER, STATUS, COMMENTS,
CTL_UPD_DTTM, CTL_UPD_USER, CTL_REC_STAT ) VALUES (
7719, 803991, '560732498', 'Sherman', 'Gough', '483 Goins Road', 'Declezville', 'GA'
, ' 78809', '3319632530', 'SGough@ukemjamgfrixoxn.gov', '560451640869870', 400000
, 'F', 'I', 'On*)@ {6yvFx>eW!d5KiHa`"WQNc`=0Nof"~5!-k<@Q^ H''napZ#V-Gf[n', TO_Date( '07/13/2002 11:56:02 AM', 'MM/DD/YYYY HH:MI:SS AM')
, 'VPD_CLERK1', '4');
INSERT INTO CUSTOMER ( SALES_REP_ID, CUSTOMER_ID, CUSTOMER_SSN, FIRST_NAME, LAST_NAME, ADDR_LINE,
CITY, STATE, ZIP_CODE, PHONE, EMAIL, CC_NUMBER, CREDIT_LIMIT, GENDER, STATUS, COMMENTS,
CTL_UPD_DTTM, CTL_UPD_USER, CTL_REC_STAT ) VALUES (
4710, 102788, '321411434', 'Jonathan', 'Redden', '394 Bonnell Ct.', 'Cushenbury', 'WV'
, ' 28224', '3524473472', 'JRedden@myfpqduytwedwhi.net', '600380425339391', 500000
, 'M', 'A', '75/^|WyxNA4bBGv?''qG;K^5W}k0pa4u!rUo^cs6?7F"bl`VUDo7B/;5"?O^', TO_Date( '04/26/1999 11:56:03 AM', 'MM/DD/YYYY HH:MI:SS AM')
, 'VPD_CLERK2', '4');
INSERT INTO CUSTOMER ( SALES_REP_ID, CUSTOMER_ID, CUSTOMER_SSN, FIRST_NAME, LAST_NAME, ADDR_LINE,
CITY, STATE, ZIP_CODE, PHONE, EMAIL, CC_NUMBER, CREDIT_LIMIT, GENDER, STATUS, COMMENTS,
CTL_UPD_DTTM, CTL_UPD_USER, CTL_REC_STAT ) VALUES (
6415, 202795, '710650230', 'Javier', 'Borowski', '81 Propst Blvd.', 'Sunset Ridge'
, 'NM', ' 34549', '9672235086', 'JBorowski@ngcuusxoubjqebl.org', '872102055651744'
, 900000, 'F', 'I', 'kpzBv%A''<.^?8I~9?k.BHw$D27uu@=Pe1a/KCi*n5t]97@b{IX^hHb1#28l'
, TO_Date( '09/12/2000 11:56:15 AM', 'MM/DD/YYYY HH:MI:SS AM'), 'VPD_CLERK2', '3');
INSERT INTO CUSTOMER ( SALES_REP_ID, CUSTOMER_ID, CUSTOMER_SSN, FIRST_NAME, LAST_NAME, ADDR_LINE,
CITY, STATE, ZIP_CODE, PHONE, EMAIL, CC_NUMBER, CREDIT_LIMIT, GENDER, STATUS, COMMENTS,
CTL_UPD_DTTM, CTL_UPD_USER, CTL_REC_STAT ) VALUES (
9644, 202800, '178482684', 'Chung', 'Kroll', '4735 Villa Ct.', 'Patton', 'WI', ' 51046'
, '6677435381', 'CKroll@pnmfbipbfpvippt.ws', '460106949879964', 200000, 'M', 'A', 'E_W"@@NdmiC/"# u_8f@[pT^h!aiDy{p$B,/VCB''!cq~MYUH`JQ_#lL.`:kd'
, TO_Date( '04/03/2002 11:56:14 AM', 'MM/DD/YYYY HH:MI:SS AM'), 'VPD_CLERK1', '2');
INSERT INTO CUSTOMER ( SALES_REP_ID, CUSTOMER_ID, CUSTOMER_SSN, FIRST_NAME, LAST_NAME, ADDR_LINE,
CITY, STATE, ZIP_CODE, PHONE, EMAIL, CC_NUMBER, CREDIT_LIMIT, GENDER, STATUS, COMMENTS,
CTL_UPD_DTTM, CTL_UPD_USER, CTL_REC_STAT ) VALUES (
2336, 402808, '317332588', 'Luciana', 'Suttles', '677 Link Boulevard', 'Devil Canyon'
, 'WV', ' 43595', '3801991750', 'LSuttles@qyrbwnthzpfvfgp.org', '305555222334614'
, 400000, 'M', 'A', ';KVq,O>YGC}a{Y?*J+(IL4IHgH dwtroxy.30Xu|T+]$qjNwTX-N6-#tVWi'
, TO_Date( '10/13/2002 11:56:25 AM', 'MM/DD/YYYY HH:MI:SS AM'), 'VPD_CLERK3', '1');
INSERT INTO CUSTOMER ( SALES_REP_ID, CUSTOMER_ID, CUSTOMER_SSN, FIRST_NAME, LAST_NAME, ADDR_LINE,
CITY, STATE, ZIP_CODE, PHONE, EMAIL, CC_NUMBER, CREDIT_LIMIT, GENDER, STATUS, COMMENTS,
CTL_UPD_DTTM, CTL_UPD_USER, CTL_REC_STAT ) VALUES (
5605, 802809, '947888467', 'Alison', 'Rapp', '51 Elkins Ave', 'Barnwell', 'PA', ' 53434'
, '4476591267', 'ARapp@kriypyiswxcjhrw.org', '522320077393120', 3000, 'M', 'I', 'H3L7=xo0ix G_3(Q4v`}Xr,."udPQw[D-:MzmnjjuXg`^UJ$cr/JuV6C{m'
, TO_Date( '06/20/1998 11:56:30 AM', 'MM/DD/YYYY HH:MI:SS AM'), 'VPD_CLERK3', '1');
INSERT INTO CUSTOMER ( SALES_REP_ID, CUSTOMER_ID, CUSTOMER_SSN, FIRST_NAME, LAST_NAME, ADDR_LINE,
CITY, STATE, ZIP_CODE, PHONE, EMAIL, CC_NUMBER, CREDIT_LIMIT, GENDER, STATUS, COMMENTS,
CTL_UPD_DTTM, CTL_UPD_USER, CTL_REC_STAT ) VALUES (
2200, 402814, '419953925', 'Amie', 'Lauer', '857 Fike St.', 'Needles', 'NH', ' 17032'
, '5544913393', 'ALauer@zasbofypdsbhofk.gov', '857462366816120', 700000, 'M', 'I'
, 'Jhl/~-b-/u@q l9e9RaY''ZW_|n3ZI*(wd[}jxXfvW HKq}YH}X1=}TNGX!iI', TO_Date( '08/07/2001 11:56:04 AM', 'MM/DD/YYYY HH:MI:SS AM')
, 'VPD_CLERK2', '1');
INSERT INTO CUSTOMER ( SALES_REP_ID, CUSTOMER_ID, CUSTOMER_SSN, FIRST_NAME, LAST_NAME, ADDR_LINE,
CITY, STATE, ZIP_CODE, PHONE, EMAIL, CC_NUMBER, CREDIT_LIMIT, GENDER, STATUS, COMMENTS,
CTL_UPD_DTTM, CTL_UPD_USER, CTL_REC_STAT ) VALUES (
7719, 202825, '656992131', 'Zelda', 'Graybill', '92628 Valentino Rd', 'Johnstons Corner'
, 'MT', ' 05892', '8932117375', 'ZGraybill@qyxuloynkkfdeti.edu', '819367329153135'
, 500000, 'M', 'I', ')r*kmJzs1I3WICL-~E/c@#L)h4}:IxO%ma5Eu`J//{o0#sp<S+R3Vy4T#'
, TO_Date( '12/08/1997 11:56:20 AM', 'MM/DD/YYYY HH:MI:SS AM'), 'VPD_CLERK3', '2');
INSERT INTO CUSTOMER ( SALES_REP_ID, CUSTOMER_ID, CUSTOMER_SSN, FIRST_NAME, LAST_NAME, ADDR_LINE,
CITY, STATE, ZIP_CODE, PHONE, EMAIL, CC_NUMBER, CREDIT_LIMIT, GENDER, STATUS, COMMENTS,
CTL_UPD_DTTM, CTL_UPD_USER, CTL_REC_STAT ) VALUES (
4710, 802827, '373998304', 'Madelaine', 'Bunker', '44 Atkins Road', 'Warner', 'VT'
, ' 23341', '1121877120', 'MBunker@bjnwuyyvucfuvqo.net', '568326579816854', 400000
, 'F', 'A', 'H5=+x8E,Yr$=[/T,TPAwF<?QM rcZp+Kc"ZcU<Vc5AP!q__$9wqZpCyc=~]', TO_Date( '06/22/2004 11:17:36 AM', 'MM/DD/YYYY HH:MI:SS AM')
, 'VPD_CLERK3', '3');
INSERT INTO CUSTOMER ( SALES_REP_ID, CUSTOMER_ID, CUSTOMER_SSN, FIRST_NAME, LAST_NAME, ADDR_LINE,
CITY, STATE, ZIP_CODE, PHONE, EMAIL, CC_NUMBER, CREDIT_LIMIT, GENDER, STATUS, COMMENTS,
CTL_UPD_DTTM, CTL_UPD_USER, CTL_REC_STAT ) VALUES (
4587, 802833, '982846219', 'Trey', 'Patton', '19383 Gadson St.', 'Duncan Corners'
, 'SC', ' 88594', '8315941276', 'TPatton@rybatjcromiswjq.ws', '183311530884120', 900000
, 'F', 'X', 's77W''x@eqc8eGry9| Ugxe?L50""9WmS]<<:pgcDxjakltmCPK7e|@M>7Y', TO_Date( '07/02/2002 11:56:13 AM', 'MM/DD/YYYY HH:MI:SS AM')
, 'VPD_CLERK1', '5');
INSERT INTO CUSTOMER ( SALES_REP_ID, CUSTOMER_ID, CUSTOMER_SSN, FIRST_NAME, LAST_NAME, ADDR_LINE,
CITY, STATE, ZIP_CODE, PHONE, EMAIL, CC_NUMBER, CREDIT_LIMIT, GENDER, STATUS, COMMENTS,
CTL_UPD_DTTM, CTL_UPD_USER, CTL_REC_STAT ) VALUES (
6415, 802843, '557626409', 'Thomasine', 'Verduzco', '3393 Slusher Ct.', 'Greensport'
, 'AR', ' 27457', '7033656036', 'TVerduzco@cybzorukacohfmg.edu', '804862258256262'
, 200000, 'M', 'I', '6KH1N~)''G/o5#''KvIR6!HSBhhi7wZ-C|y6V~O{7e!e2<9''}@G*rqE3" 6`)*'
, TO_Date( '01/06/2001 11:56:12 AM', 'MM/DD/YYYY HH:MI:SS AM'), 'VPD_CLERK2', '5');
INSERT INTO CUSTOMER ( SALES_REP_ID, CUSTOMER_ID, CUSTOMER_SSN, FIRST_NAME, LAST_NAME, ADDR_LINE,
CITY, STATE, ZIP_CODE, PHONE, EMAIL, CC_NUMBER, CREDIT_LIMIT, GENDER, STATUS, COMMENTS,
CTL_UPD_DTTM, CTL_UPD_USER, CTL_REC_STAT ) VALUES (
7719, 902854, '312544520', 'Janay', 'Durham', '17 Rhoads Ave', 'Serrano Village', 'PA'
, ' 87676', '2895348626', 'JDurham@jhexclvpvwpqhps.com', '544965052631970', 800000
, 'F', 'I', '@/M esGp-~F|urG=="wR0<dR5,>@ 4h~kjAa)D?RHS"%S*1.GF3p#YU<l@', TO_Date( '07/26/1999 11:56:39 AM', 'MM/DD/YYYY HH:MI:SS AM')
, 'VPD_CLERK3', '5');
INSERT INTO CUSTOMER ( SALES_REP_ID, CUSTOMER_ID, CUSTOMER_SSN, FIRST_NAME, LAST_NAME, ADDR_LINE,
CITY, STATE, ZIP_CODE, PHONE, EMAIL, CC_NUMBER, CREDIT_LIMIT, GENDER, STATUS, COMMENTS,
CTL_UPD_DTTM, CTL_UPD_USER, CTL_REC_STAT ) VALUES (
2200, 802861, '599056078', 'Rosaline', 'Register', '5413 Lemus Street', 'Glen Martin'
, 'VA', ' 69415', '1124044395', 'RRegister@bgtkijdsbqiywjt.edu', '406202025549469'
, 700000, 'F', 'A', 'C[[Y-j2?W{l]pJKU5ondDTdSNjm?;X,d=d~M>ZrZ!@g#Ys{Har?zpQtqFJyI'
, TO_Date( '08/11/1999 11:56:15 AM', 'MM/DD/YYYY HH:MI:SS AM'), 'VPD_CLERK2', '5');
INSERT INTO CUSTOMER ( SALES_REP_ID, CUSTOMER_ID, CUSTOMER_SSN, FIRST_NAME, LAST_NAME, ADDR_LINE,
CITY, STATE, ZIP_CODE, PHONE, EMAIL, CC_NUMBER, CREDIT_LIMIT, GENDER, STATUS, COMMENTS,
CTL_UPD_DTTM, CTL_UPD_USER, CTL_REC_STAT ) VALUES (
5605, 402871, '829945460', 'Elvin', 'Bierman', '32 Holston Rd', 'Sperry', 'AL', ' 37001'
, '8776676239', 'EBierman@tmqzummyuqqvhnh.edu', '430795530735748', 10000, 'M', 'A'
, 'C?%uak+0?160Wg}Ha{0]-:-*!Wd/xvbyxQb-r3_3-_rJ''x:4q#%X4WNA]Zp', TO_Date( '06/02/1999 11:56:11 AM', 'MM/DD/YYYY HH:MI:SS AM')
, 'VPD_CLERK1', '4');
INSERT INTO CUSTOMER ( SALES_REP_ID, CUSTOMER_ID, CUSTOMER_SSN, FIRST_NAME, LAST_NAME, ADDR_LINE,
CITY, STATE, ZIP_CODE, PHONE, EMAIL, CC_NUMBER, CREDIT_LIMIT, GENDER, STATUS, COMMENTS,
CTL_UPD_DTTM, CTL_UPD_USER, CTL_REC_STAT ) VALUES (
6415, 102908, '340253142', 'Yasmine', 'Campbell', '8997 Carlyle Ct.', 'Calzona', 'OR'
, ' 16695', '4876052902', 'YCampbell@jgqccevwxkgpwlu.mil', '427708011949280', 900000
, 'M', 'I', '@FBAX5=bC*)!'']ip4)nr?2G2n|$g<~38I(''uCDvd=`~Fm4q:xO6|+/@0DG)t', TO_Date( '03/08/2004 11:17:36 AM', 'MM/DD/YYYY HH:MI:SS AM')
, 'VPD_CLERK3', '3');
INSERT INTO CUSTOMER ( SALES_REP_ID, CUSTOMER_ID, CUSTOMER_SSN, FIRST_NAME, LAST_NAME, ADDR_LINE,
CITY, STATE, ZIP_CODE, PHONE, EMAIL, CC_NUMBER, CREDIT_LIMIT, GENDER, STATUS, COMMENTS,
CTL_UPD_DTTM, CTL_UPD_USER, CTL_REC_STAT ) VALUES (
2336, 802923, '128593131', 'Synthia', 'Jesse', '48 Hubbs Rd', 'Bethune', 'IA', ' 92351'
, '9477997077', 'SJesse@yzmstmiqynypazn.gov', '716968476790229', 700000, 'M', 'A'
, 'Ac#xT)#e,}=a:KjkpzWCA`3 ){{9.54[uiJRp@$)p+*D]N_8{Ig<vBl tm', TO_Date( '07/20/1998 11:56:59 AM', 'MM/DD/YYYY HH:MI:SS AM')
, 'VPD_CLERK2', '2');
INSERT INTO CUSTOMER ( SALES_REP_ID, CUSTOMER_ID, CUSTOMER_SSN, FIRST_NAME, LAST_NAME, ADDR_LINE,
CITY, STATE, ZIP_CODE, PHONE, EMAIL, CC_NUMBER, CREDIT_LIMIT, GENDER, STATUS, COMMENTS,
CTL_UPD_DTTM, CTL_UPD_USER, CTL_REC_STAT ) VALUES (
4710, 802927, '772222419', 'Jefferson', 'Sturgis', '61 Cheatham Boulevard', 'Cedar Glen'
, 'ME', ' 97376', '5243729989', 'JSturgis@wgyjsdyhsxdswnw.net', '831933118287641'
, 1000000, 'M', 'I', '88pB"gg5,loHzkcMf?b`AwF!K,VHu_9j*1G^qY@61Nw[%_wO[Y$/x<ocEHkw'
, TO_Date( '06/13/2000 11:56:02 AM', 'MM/DD/YYYY HH:MI:SS AM'), 'VPD_CLERK2', '2');
INSERT INTO CUSTOMER ( SALES_REP_ID, CUSTOMER_ID, CUSTOMER_SSN, FIRST_NAME, LAST_NAME, ADDR_LINE,
CITY, STATE, ZIP_CODE, PHONE, EMAIL, CC_NUMBER, CREDIT_LIMIT, GENDER, STATUS, COMMENTS,
CTL_UPD_DTTM, CTL_UPD_USER, CTL_REC_STAT ) VALUES (
2200, 202930, '124224152', 'Moses', 'Roderick', '77 Sartin St.', 'Halloran Springs'
, 'IA', ' 62148', '1418016194', 'MRoderick@mirobvfitorceja.ad', '926954470390221'
, 600000, 'M', 'I', 'H" 6}8(mE%cxmZ<GX>P(f[Xn{)OOY3W;[AL0f!IKd[HJ5AWQZAl-$DgD+OeX'
, TO_Date( '12/30/1999 11:56:06 AM', 'MM/DD/YYYY HH:MI:SS AM'), 'VPD_CLERK3', '3');
INSERT INTO CUSTOMER ( SALES_REP_ID, CUSTOMER_ID, CUSTOMER_SSN, FIRST_NAME, LAST_NAME, ADDR_LINE,
CITY, STATE, ZIP_CODE, PHONE, EMAIL, CC_NUMBER, CREDIT_LIMIT, GENDER, STATUS, COMMENTS,
CTL_UPD_DTTM, CTL_UPD_USER, CTL_REC_STAT ) VALUES (
2336, 402940, '797204201', 'Ervin', 'Rutledge', '991 Leflore St.', 'Trona', 'NJ', ' 09646'
, '6677571235', 'ERutledge@vpdcvsmpvpmwmyb.gov', '107054051372265', 30000, 'M', 'I'
, 'Zozu<"HKMNPOch?ei''d{K-e@;/>j3zI,;_/|PiZnxpbR02d}arL/>z,+4''', TO_Date( '01/04/1998 11:56:11 AM', 'MM/DD/YYYY HH:MI:SS AM')
, 'VPD_CLERK3', '4');
INSERT INTO CUSTOMER ( SALES_REP_ID, CUSTOMER_ID, CUSTOMER_SSN, FIRST_NAME, LAST_NAME, ADDR_LINE,
CITY, STATE, ZIP_CODE, PHONE, EMAIL, CC_NUMBER, CREDIT_LIMIT, GENDER, STATUS, COMMENTS,
CTL_UPD_DTTM, CTL_UPD_USER, CTL_REC_STAT ) VALUES (
5605, 402943, '426127885', 'Shelton', 'Poole', '26 Eddins Ct.', 'Thorn', 'NV', ' 28962'
, '8151627399', 'SPoole@cbhlgpmjxbebart.org', '878012749323524', 700000, 'F', 'I'
, '6DtZi0_`9|PDK!V^}>x%:p17B:E8Fs73tRDE YR{OaW0 9QKM8!G|F>X^;r1', TO_Date( '10/14/2000 11:56:20 AM', 'MM/DD/YYYY HH:MI:SS AM')
, 'VPD_CLERK2', '3');
INSERT INTO CUSTOMER ( SALES_REP_ID, CUSTOMER_ID, CUSTOMER_SSN, FIRST_NAME, LAST_NAME, ADDR_LINE,
CITY, STATE, ZIP_CODE, PHONE, EMAIL, CC_NUMBER, CREDIT_LIMIT, GENDER, STATUS, COMMENTS,
CTL_UPD_DTTM, CTL_UPD_USER, CTL_REC_STAT ) VALUES (
2336, 302954, '467565745', 'Lanny', 'Beaudry', '7433 Norwood Avenue', 'Big Bear City'
, 'MD', ' 97454', '8683192016', 'LBeaudry@dveabmkltklcsuz.mil', '279243298723194'
, 600000, 'M', NULL, '.AQ.RfNWssvNp>=_CLDX7gPl-F!+n?ed/iusH%^,ZVz_hPBx"e(yKo{Nx/]'
, TO_Date( '07/21/1999 11:56:30 AM', 'MM/DD/YYYY HH:MI:SS AM'), 'VPD_CLERK1', '2');
INSERT INTO CUSTOMER ( SALES_REP_ID, CUSTOMER_ID, CUSTOMER_SSN, FIRST_NAME, LAST_NAME, ADDR_LINE,
CITY, STATE, ZIP_CODE, PHONE, EMAIL, CC_NUMBER, CREDIT_LIMIT, GENDER, STATUS, COMMENTS,
CTL_UPD_DTTM, CTL_UPD_USER, CTL_REC_STAT ) VALUES (
4587, 802959, '240819976', 'Tisha', 'Lum', '93789 Carroll Street', 'The Mesa', 'OH'
, ' 80823', '3064206093', 'TLum@rrbhtyvtridwjff.com', '509227564746648', 700000, 'M'
, 'I', 'Vm>X#aDeD{k~#?9 9sf/-)%;/b4(ySK7D8/={`e-M.wAzdcb^8bq}"m+', TO_Date( '01/13/2000 11:56:01 AM', 'MM/DD/YYYY HH:MI:SS AM')
, 'VPD_CLERK3', '1');
INSERT INTO CUSTOMER ( SALES_REP_ID, CUSTOMER_ID, CUSTOMER_SSN, FIRST_NAME, LAST_NAME, ADDR_LINE,
CITY, STATE, ZIP_CODE, PHONE, EMAIL, CC_NUMBER, CREDIT_LIMIT, GENDER, STATUS, COMMENTS,
CTL_UPD_DTTM, CTL_UPD_USER, CTL_REC_STAT ) VALUES (
4710, 202965, '545763811', 'Winfred', 'Mccutcheon', '3552 Sternberg Ave', 'Desert Heights'
, 'RI', ' 42501', '1284803313', 'WMccutcheon@lrxolluwmsozjlk.edu', '894899252811112'
, 20000, 'F', 'I', '[x1>h4z< ! -f"^63J sFs.37=6F;ytq%ub*4''A)LfBu9ccrrHa:o0NT/', TO_Date( '10/29/2000 11:56:13 AM', 'MM/DD/YYYY HH:MI:SS AM')
, 'VPD_CLERK2', '2');
INSERT INTO CUSTOMER ( SALES_REP_ID, CUSTOMER_ID, CUSTOMER_SSN, FIRST_NAME, LAST_NAME, ADDR_LINE,
CITY, STATE, ZIP_CODE, PHONE, EMAIL, CC_NUMBER, CREDIT_LIMIT, GENDER, STATUS, COMMENTS,
CTL_UPD_DTTM, CTL_UPD_USER, CTL_REC_STAT ) VALUES (
2336, 802969, '227055552', 'Darron', 'Lemay', '24 Monk Ave', 'Amboy', 'WI', ' 12750'
, '5387027962', 'DLemay@tgjupoiveixlrpk.mil', '716620972173061', 200000, 'F', 'I'
, ';76tr%$J-s<;l*MZhiHv{1)ngUi%AY%}AJ{zG-vs62TQm@M2Kbf,`^}#l z5', TO_Date( '08/30/1998 11:56:06 AM', 'MM/DD/YYYY HH:MI:SS AM')
, 'VPD_CLERK3', '2');
INSERT INTO CUSTOMER ( SALES_REP_ID, CUSTOMER_ID, CUSTOMER_SSN, FIRST_NAME, LAST_NAME, ADDR_LINE,
CITY, STATE, ZIP_CODE, PHONE, EMAIL, CC_NUMBER, CREDIT_LIMIT, GENDER, STATUS, COMMENTS,
CTL_UPD_DTTM, CTL_UPD_USER, CTL_REC_STAT ) VALUES (
6415, 202975, '689508211', 'Michel', 'Mascarenas', '4851 Lugo Street', 'Black Meadow Landing'
, 'MA', ' 37360', '9477722947', 'MMascarenas@pnrztoupzuzdafb.gov', '311733458507042'
, 900000, 'M', 'A', '0G;OfsVHi/p8U5[ya*5O%85|%H Fl#A[3"UMripK@hLl6q!48MI4csh5o<vS'
, TO_Date( '06/19/2002 11:56:31 AM', 'MM/DD/YYYY HH:MI:SS AM'), 'VPD_CLERK3', '2');
INSERT INTO CUSTOMER ( SALES_REP_ID, CUSTOMER_ID, CUSTOMER_SSN, FIRST_NAME, LAST_NAME, ADDR_LINE,
CITY, STATE, ZIP_CODE, PHONE, EMAIL, CC_NUMBER, CREDIT_LIMIT, GENDER, STATUS, COMMENTS,
CTL_UPD_DTTM, CTL_UPD_USER, CTL_REC_STAT ) VALUES (
7719, 902986, '203541528', 'Porter', 'Mccallum', '11928 Beckley Rd', 'Daggett', 'ND'
, ' 65285', '7071471958', 'PMccallum@cfcpfgsexqyztrg.mil', '625621933294250', 200000
, 'M', 'I', 'ww%}2GfI~MF#sa=<+61=vJ[Pw*q$?L!javJR1366@G7,0fQo+zRggR%o"w#Q', TO_Date( '07/18/1999 11:56:32 AM', 'MM/DD/YYYY HH:MI:SS AM')
, 'VPD_CLERK2', '3');
INSERT INTO CUSTOMER ( SALES_REP_ID, CUSTOMER_ID, CUSTOMER_SSN, FIRST_NAME, LAST_NAME, ADDR_LINE,
CITY, STATE, ZIP_CODE, PHONE, EMAIL, CC_NUMBER, CREDIT_LIMIT, GENDER, STATUS, COMMENTS,
CTL_UPD_DTTM, CTL_UPD_USER, CTL_REC_STAT ) VALUES (
4587, 402991, '282582582', 'Hipolito', 'Ocasio', '69 Heil Rd', 'Grand Terrace', 'MI'
, ' 02007', '5163037593', 'HOcasio@wokldeubdqggzxs.ad', '531275176517319', 800000
, 'M', NULL, 'tg4aDpVuTRK^IJ''2-@$%Ek'' [dnM%''zKwd9n3;$;jD}ny`Es(=nYunoG4', TO_Date( '03/03/2001 11:56:07 AM', 'MM/DD/YYYY HH:MI:SS AM')
, 'VPD_CLERK3', '5');
INSERT INTO CUSTOMER ( SALES_REP_ID, CUSTOMER_ID, CUSTOMER_SSN, FIRST_NAME, LAST_NAME, ADDR_LINE,
CITY, STATE, ZIP_CODE, PHONE, EMAIL, CC_NUMBER, CREDIT_LIMIT, GENDER, STATUS, COMMENTS,
CTL_UPD_DTTM, CTL_UPD_USER, CTL_REC_STAT ) VALUES (
9644, 102992, '476134125', 'Chadwick', 'Perri', '45848 Granger Ave', 'Sunset Hills'
, 'CT', ' 53446', '2803046578', 'CPerri@vjsplbpmdpynfaj.gov', '722877041706541', 500000
, 'M', NULL, '>-q J1w8].M2 JC[!aLOm,=_|j<?yXK@w<OMb][}Hr#bS`K[W;@5@<*<', TO_Date( '01/26/2002 11:56:03 AM', 'MM/DD/YYYY HH:MI:SS AM')
, 'VPD_CLERK1', '4');
INSERT INTO CUSTOMER ( SALES_REP_ID, CUSTOMER_ID, CUSTOMER_SSN, FIRST_NAME, LAST_NAME, ADDR_LINE,
CITY, STATE, ZIP_CODE, PHONE, EMAIL, CC_NUMBER, CREDIT_LIMIT, GENDER, STATUS, COMMENTS,
CTL_UPD_DTTM, CTL_UPD_USER, CTL_REC_STAT ) VALUES (
6415, 903002, '220441842', 'Paris', 'Mckinzie', '93 Heffernan Ct.', 'Skytop', 'AK'
, ' 64214', '9181868814', 'PMckinzie@jkoekwqgvjwfdlr.ad', '302568813431846', 300000
, 'M', 'I', 'D|cfK[`]+]H}OoBaq0-J[A+8R[ zo%bWSq{(=gc8I|V^6zBU*NpBnTuvYda', TO_Date( '02/22/2000 11:56:06 AM', 'MM/DD/YYYY HH:MI:SS AM')
, 'VPD_CLERK2', '1');
INSERT INTO CUSTOMER ( SALES_REP_ID, CUSTOMER_ID, CUSTOMER_SSN, FIRST_NAME, LAST_NAME, ADDR_LINE,
CITY, STATE, ZIP_CODE, PHONE, EMAIL, CC_NUMBER, CREDIT_LIMIT, GENDER, STATUS, COMMENTS,
CTL_UPD_DTTM, CTL_UPD_USER, CTL_REC_STAT ) VALUES (
6415, 103008, '190078003', 'Alleen', 'Benally', '82 Trinh Rd', 'Halloran Springs'
, 'AR', ' 08822', '7977206825', 'ABenally@zqpesvgzdjyifex.ws', '810572747789968', 5000
, 'F', 'I', '($Jke!s6PBVa>kau?mRj]?%r/^>:+ae"G)9U$2)hVRwKnFK%FSey>k`7ch''q', TO_Date( '07/12/2001 11:56:18 AM', 'MM/DD/YYYY HH:MI:SS AM')
, 'VPD_CLERK3', '3');
INSERT INTO CUSTOMER ( SALES_REP_ID, CUSTOMER_ID, CUSTOMER_SSN, FIRST_NAME, LAST_NAME, ADDR_LINE,
CITY, STATE, ZIP_CODE, PHONE, EMAIL, CC_NUMBER, CREDIT_LIMIT, GENDER, STATUS, COMMENTS,
CTL_UPD_DTTM, CTL_UPD_USER, CTL_REC_STAT ) VALUES (
2200, 303031, '347699693', 'Domingo', 'Nicholson', '81384 Eady Ave', 'Cosy Dell', 'NV'
, ' 25971', '8796155309', 'DNicholson@gorxwqvmunmpaos.com', '127168312007226', 900000
, 'F', NULL, '6^bwZr#!hjg2!?nnZKHnh*yp~TiNf~twg/Vgs;''N>7n3dV!##JJjw'']8a*', TO_Date( '12/20/1998 11:56:01 AM', 'MM/DD/YYYY HH:MI:SS AM')
, 'VPD_CLERK3', '4');
INSERT INTO CUSTOMER ( SALES_REP_ID, CUSTOMER_ID, CUSTOMER_SSN, FIRST_NAME, LAST_NAME, ADDR_LINE,
CITY, STATE, ZIP_CODE, PHONE, EMAIL, CC_NUMBER, CREDIT_LIMIT, GENDER, STATUS, COMMENTS,
CTL_UPD_DTTM, CTL_UPD_USER, CTL_REC_STAT ) VALUES (
6415, 803041, '447585037', 'Lynn', 'Pierce', '6233 Perry Blvd.', 'Vanderbilt', 'OR'
, ' 05862', '3065231986', 'LPierce@ehoxvkjrgjtfclc.ad', '460063929844722', 500000
, 'F', 'I', 'Y"uaSwhi4+v*jO>]{oPr"W4yQv|V q`Av2h#7o!#>[`kRA(GZHz`ov$+ys8S', TO_Date( '11/19/1999 11:56:06 AM', 'MM/DD/YYYY HH:MI:SS AM')
, 'VPD_CLERK2', '3');
INSERT INTO CUSTOMER ( SALES_REP_ID, CUSTOMER_ID, CUSTOMER_SSN, FIRST_NAME, LAST_NAME, ADDR_LINE,
CITY, STATE, ZIP_CODE, PHONE, EMAIL, CC_NUMBER, CREDIT_LIMIT, GENDER, STATUS, COMMENTS,
CTL_UPD_DTTM, CTL_UPD_USER, CTL_REC_STAT ) VALUES (
5605, 903046, '940555073', 'Maggie', 'Zimmerman', '9735 Moats Ct.', 'Big Bear Lake'
, 'RI', ' 31451', '8238738308', 'MZimmerman@xqgcutzuiyzuylm.gov', '943182075197205'
, 20000, 'M', NULL, 'jJ,,=_-"?@a:L2_Ux=A,@w8phdMq"W":-0[<mnraja''[Z7i4E7;lPES0Y}C'
, TO_Date( '08/14/1998 11:56:28 AM', 'MM/DD/YYYY HH:MI:SS AM'), 'VPD_CLERK1', '2');
INSERT INTO CUSTOMER ( SALES_REP_ID, CUSTOMER_ID, CUSTOMER_SSN, FIRST_NAME, LAST_NAME, ADDR_LINE,
CITY, STATE, ZIP_CODE, PHONE, EMAIL, CC_NUMBER, CREDIT_LIMIT, GENDER, STATUS, COMMENTS,
CTL_UPD_DTTM, CTL_UPD_USER, CTL_REC_STAT ) VALUES (
2200, 203050, '639577053', 'Palmer', 'Jorge', '75 Funderburk Ct.', 'South Adelanto'
, 'RI', ' 87090', '8361495135', 'PJorge@xjmhroumxldzbxw.edu', '722687405979720', 900000
, 'M', 'A', 'ad$YNqvh NI[($4{SOZ}+e-rR|UJg/ ~SrM0]Xp)`F=JL7@!!Q;?,75aZ%6', TO_Date( '09/20/2000 11:56:08 AM', 'MM/DD/YYYY HH:MI:SS AM')
, 'VPD_CLERK3', '2');
INSERT INTO CUSTOMER ( SALES_REP_ID, CUSTOMER_ID, CUSTOMER_SSN, FIRST_NAME, LAST_NAME, ADDR_LINE,
CITY, STATE, ZIP_CODE, PHONE, EMAIL, CC_NUMBER, CREDIT_LIMIT, GENDER, STATUS, COMMENTS,
CTL_UPD_DTTM, CTL_UPD_USER, CTL_REC_STAT ) VALUES (
2200, 402445, '847284825', 'Bonnie', 'Rivera', '64 Seaton Blvd.', 'Mentone', 'OR'
, ' 48387', '4906202978', 'BRivera@tgmuzxwvbjbwrwm.net', '423617613518501', 500000
, 'F', NULL, 'J3F._i)j4?^d62g!uYYmc~u1?e}_W{;$FK62[''}7-Q7h]Q`XEx,r{`!jyDm7', TO_Date( '07/23/2001 11:56:43 AM', 'MM/DD/YYYY HH:MI:SS AM')
, 'VPD_CLERK3', '2');
INSERT INTO CUSTOMER ( SALES_REP_ID, CUSTOMER_ID, CUSTOMER_SSN, FIRST_NAME, LAST_NAME, ADDR_LINE,
CITY, STATE, ZIP_CODE, PHONE, EMAIL, CC_NUMBER, CREDIT_LIMIT, GENDER, STATUS, COMMENTS,
CTL_UPD_DTTM, CTL_UPD_USER, CTL_REC_STAT ) VALUES (
4710, 202450, '249653159', 'Grady', 'Rooks', '364 Field Ct.', 'Smiley Park', 'NV'
, ' 31333', '3403364242', 'GRooks@rrqfedkcxfanjjp.gov', '911793470591378', 800000
, 'M', 'I', 'eDo[ktvr9e8$+qVAPWje^*RS<nAo$JBKmsxF/[w8y]Qjb5M8kPYOAkCa_}[', TO_Date( '11/06/1998 11:56:44 AM', 'MM/DD/YYYY HH:MI:SS AM')
, 'VPD_CLERK2', '3');
INSERT INTO CUSTOMER ( SALES_REP_ID, CUSTOMER_ID, CUSTOMER_SSN, FIRST_NAME, LAST_NAME, ADDR_LINE,
CITY, STATE, ZIP_CODE, PHONE, EMAIL, CC_NUMBER, CREDIT_LIMIT, GENDER, STATUS, COMMENTS,
CTL_UPD_DTTM, CTL_UPD_USER, CTL_REC_STAT ) VALUES (
2200, 802453, '589325629', 'Catrina', 'Peachey', '1437 Gallant Rd', 'Chubbuck', 'AL'
, ' 33575', '4784691914', 'CPeachey@sqtbvtnfctrzodz.ws', '554825998993864', 200000
, 'F', 'I', 'yB5eT-}R!3:iRH7Jmg"u(@z@F6e.MqUAQp]m''[$luO7j4h$=)m:j x|@X{s#', TO_Date( '04/02/2001 11:56:23 AM', 'MM/DD/YYYY HH:MI:SS AM')
, 'VPD_CLERK1', '2');
INSERT INTO CUSTOMER ( SALES_REP_ID, CUSTOMER_ID, CUSTOMER_SSN, FIRST_NAME, LAST_NAME, ADDR_LINE,
CITY, STATE, ZIP_CODE, PHONE, EMAIL, CC_NUMBER, CREDIT_LIMIT, GENDER, STATUS, COMMENTS,
CTL_UPD_DTTM, CTL_UPD_USER, CTL_REC_STAT ) VALUES (
7719, 202455, '920532308', 'Jeannetta', 'Litton', '98 Conklin Rd', 'Hodge', 'CT', ' 26262'
, '3777862067', 'JLitton@eiyazzgqlnhjhmm.edu', '433573008023612', 900000, 'M', 'I'
, '~TsR$wWY%;7d89a=)~]P% xNkSb+lQp''1w?zGnE9%;"B({(*x[p^|lQj(V]q', TO_Date( '03/27/2001 11:56:02 AM', 'MM/DD/YYYY HH:MI:SS AM')
, 'VPD_CLERK3', '4');
INSERT INTO CUSTOMER ( SALES_REP_ID, CUSTOMER_ID, CUSTOMER_SSN, FIRST_NAME, LAST_NAME, ADDR_LINE,
CITY, STATE, ZIP_CODE, PHONE, EMAIL, CC_NUMBER, CREDIT_LIMIT, GENDER, STATUS, COMMENTS,
CTL_UPD_DTTM, CTL_UPD_USER, CTL_REC_STAT ) VALUES (
2200, 902458, '276127582', 'Dudley', 'Eversole', '99 Vogel Ct.', 'Havasu Palms', 'AK'
, ' 54310', '5125877761', 'DEversole@zvymparukhethka.ad', '362040183624436', 400000
, 'F', 'I', 'JHa64W__)(W.;`r.|!Xd/Sii?Cr9O}mr8(P9sb#2%_94<dlyn+P7+HilUPc', TO_Date( '03/01/1998 11:56:27 AM', 'MM/DD/YYYY HH:MI:SS AM')
, 'VPD_CLERK2', '5');
INSERT INTO CUSTOMER ( SALES_REP_ID, CUSTOMER_ID, CUSTOMER_SSN, FIRST_NAME, LAST_NAME, ADDR_LINE,
CITY, STATE, ZIP_CODE, PHONE, EMAIL, CC_NUMBER, CREDIT_LIMIT, GENDER, STATUS, COMMENTS,
CTL_UPD_DTTM, CTL_UPD_USER, CTL_REC_STAT ) VALUES (
4587, 202470, '507854433', 'Monique', 'Viola', '322 Eckert Ave', 'Chambless', 'IN'
, ' 32757', '7447439503', 'MViola@oqlddqrpqdtgjgs.com', '774964339772242', 600000
, 'M', 'X', 'r=@^?C{iw^ Y''9-r2*@>]mtOfK$o$w<vS=2*45O|:iA@XR[ztfNDqVdZiDNC', TO_Date( '04/19/2001 11:56:28 AM', 'MM/DD/YYYY HH:MI:SS AM')
, 'VPD_CLERK3', '3');
INSERT INTO CUSTOMER ( SALES_REP_ID, CUSTOMER_ID, CUSTOMER_SSN, FIRST_NAME, LAST_NAME, ADDR_LINE,
CITY, STATE, ZIP_CODE, PHONE, EMAIL, CC_NUMBER, CREDIT_LIMIT, GENDER, STATUS, COMMENTS,
CTL_UPD_DTTM, CTL_UPD_USER, CTL_REC_STAT ) VALUES (
9644, 802477, '504610570', 'Candra', 'Gosselin', '989 Bryson Ct.', 'Kramer Junction'
, 'NC', ' 80628', '7114408551', 'CGosselin@nwtqiuxdwjouaqw.edu', '659033752190479'
, 200000, 'M', NULL, '<9aL|3c;N]pLtb_7@QK[3R4uO"Q=baNPN1I7|(0MHiI.[3(06|vc:k;3i_'
, TO_Date( '07/09/1999 11:56:27 AM', 'MM/DD/YYYY HH:MI:SS AM'), 'VPD_CLERK3', '2');
INSERT INTO CUSTOMER ( SALES_REP_ID, CUSTOMER_ID, CUSTOMER_SSN, FIRST_NAME, LAST_NAME, ADDR_LINE,
CITY, STATE, ZIP_CODE, PHONE, EMAIL, CC_NUMBER, CREDIT_LIMIT, GENDER, STATUS, COMMENTS,
CTL_UPD_DTTM, CTL_UPD_USER, CTL_REC_STAT ) VALUES (
4587, 102488, '415454213', 'Raleigh', 'Hinman', '29 Seward Avenue', 'Basin', 'AR'
, ' 19478', '1799876688', 'RHinman@yqxkarrddiqtcad.edu', '743789284180288', 800000
, 'M', NULL, 'P[d#fGRi!"p-mn`7Fv4z-a9C;2.6So6;hA62D8q_LfB3eC|O6*k"Ykns::', TO_Date( '10/25/1998 11:56:09 AM', 'MM/DD/YYYY HH:MI:SS AM')
, 'VPD_CLERK2', '1');
INSERT INTO CUSTOMER ( SALES_REP_ID, CUSTOMER_ID, CUSTOMER_SSN, FIRST_NAME, LAST_NAME, ADDR_LINE,
CITY, STATE, ZIP_CODE, PHONE, EMAIL, CC_NUMBER, CREDIT_LIMIT, GENDER, STATUS, COMMENTS,
CTL_UPD_DTTM, CTL_UPD_USER, CTL_REC_STAT ) VALUES (
2336, 802489, '560773902', 'Marion', 'Benitez', '63 Haworth Ave', 'North Star Ranch'
, 'NM', ' 37341', '4681806596', 'MBenitez@xetnvwflzayegoo.ws', '400941310074656', 400000
, 'M', NULL, '7i%jD(@2''S>''Dz>Ag g9aEA?rp''X5PM"wp$U''U:Y%JBJULS"A%Q9[Y+Z-CY', TO_Date( '08/25/2001 11:56:27 AM', 'MM/DD/YYYY HH:MI:SS AM')
, 'VPD_CLERK3', '1');
INSERT INTO CUSTOMER ( SALES_REP_ID, CUSTOMER_ID, CUSTOMER_SSN, FIRST_NAME, LAST_NAME, ADDR_LINE,
CITY, STATE, ZIP_CODE, PHONE, EMAIL, CC_NUMBER, CREDIT_LIMIT, GENDER, STATUS, COMMENTS,
CTL_UPD_DTTM, CTL_UPD_USER, CTL_REC_STAT ) VALUES (
4710, 902494, '151751181', 'Otto', 'Huff', '28 Sachs Blvd.', 'Desert Knolls', 'UT'
, ' 58438', '6423582104', 'OHuff@urrkxkcrejkkvau.mil', '727139259426118', 700000, 'F'
, NULL, 'Ymn*K/+y#s[A%3[-zmsu2-*O-''g]%4^0tDE%4V<NI(u''Ta^5JN!m;M*guVgP', TO_Date( '05/15/2000 11:56:49 AM', 'MM/DD/YYYY HH:MI:SS AM')
, 'VPD_CLERK1', '5');
commit;
-----------------------------------------------------End of populating process--------------------------------------------------------
--------------------------------creating a password complexity function-------------------------------
connect sys as sysdba;
CREATE OR REPLACE FUNCTION complexity_function
(username varchar2,
password varchar2,
old_password varchar2)
RETURN boolean IS
n boolean;
m integer;
differ integer;
isdigit boolean;
ischar boolean;
ispunct boolean;
digitarray varchar2(25);
punctarray varchar2(25);
chararray varchar2(52);
BEGIN
digitarray:= '0123456789';
chararray:= 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ';
punctarray:='!"#$%&()``*+,-/:;<=>?_';
-- Check if the password is same as the username
IF NLS_LOWER(password) = NLS_LOWER(username) THEN
raise_application_error(-20001, 'Password same as or similar to user');
END IF;
-- Check for the minimum length of the password
IF length(password) < 6 THEN
raise_application_error(-20002, 'Password length less than 6');
END IF;
-- Check if the password is too simple. A dictionary of words may be
-- maintained and a check may be made so as not to allow the words
-- that are too simple for the password.
IF NLS_LOWER(password) IN ('welcome', 'database', 'account', 'user', 'oracle') THEN
raise_application_error(-20002, 'Password too simple');
END IF;
-- Check if the password contains at least one letter, one digit and one
-- punctuation mark.
-- 1. Check for the digit
isdigit:=FALSE;
m := length(password);
FOR i IN 1..10 LOOP
FOR j IN 1..m LOOP
IF substr(password,j,1) = substr(digitarray,i,1) THEN
isdigit:=TRUE;
GOTO findchar;
END IF;
END LOOP;
END LOOP;
IF isdigit = FALSE THEN
raise_application_error(-20003, 'Password should contain at least one digit, one character and one punctuation');
END IF;
-- 2. Check for the character
<<findchar>>
ischar:=FALSE;
FOR i IN 1..length(chararray) LOOP
FOR j IN 1..m LOOP
IF substr(password,j,1) = substr(chararray,i,1) THEN
ischar:=TRUE;
GOTO findpunct;
END IF;
END LOOP;
END LOOP;
IF ischar = FALSE THEN
raise_application_error(-20003, 'Password should contain at least one
digit, one character and one punctuation');
END IF;
-- 3. Check for the punctuation
<<findpunct>>
ispunct:=FALSE;
FOR i IN 1..length(punctarray) LOOP
FOR j IN 1..m LOOP
IF substr(password,j,1) = substr(punctarray,i,1) THEN
ispunct:=TRUE;
GOTO endsearch;
END IF;
END LOOP;
END LOOP;
IF ispunct = FALSE THEN
raise_application_error(-20003, 'Password should contain at least one
digit, one character and one punctuation');
END IF;
<<endsearch>>
-- Check if the password differs from the previous password by at least
-- 3 characters
IF old_password IS NOT NULL THEN
differ := length(old_password) - length(password);
IF abs(differ) < 3 THEN
IF length(password) < length(old_password) THEN
m := length(password);
ELSE
m := length(old_password);
END IF;
differ := abs(differ);
FOR i IN 1..m LOOP
IF substr(password,i,1) != substr(old_password,i,1) THEN
differ := differ + 1;
END IF;
END LOOP;
IF differ < 3 THEN
raise_application_error(-20004, 'Password should differ by at
least 3 characters');
END IF;
END IF;
END IF;
-- Everything is fine; return TRUE ;
RETURN(TRUE);
END;
/
--------------------------------------------------------creating the profiles------------------------------------------------------
connect DBSEC/tec5363#1;
drop profile DBSEC_ADMIN_PROF;
create profile DBSEC_ADMIN_PROF
limit
SESSIONS_PER_USER 5
CONNECT_TIME 480
IDLE_TIME 60
PASSWORD_LIFE_TIME 30
PASSWORD_GRACE_TIME 7
PASSWORD_VERIFY_FUNCTION complexity_function;
drop profile DBSEC_DEV_PROF;
create profile DBSEC_DEV_PROF
limit
CONNECT_TIME 720
IDLE_TIME 120
CPU_PER_CALL 60
PASSWORD_LIFE_TIME 30
PASSWORD_GRACE_TIME 14
PASSWORD_VERIFY_FUNCTION complexity_function;
drop profile DBSEC_CLERK_PROF;
create profile DBSEC_CLERK_PROF
limit
SESSIONS_PER_USER 1
CPU_PER_CALL 500
CONNECT_TIME 480
IDLE_TIME 30
LOGICAL_READS_PER_CALL 10000
FAILED_LOGIN_ATTEMPTS 3
PASSWORD_LIFE_TIME 30
PASSWORD_LOCK_TIME 3
PASSWORD_GRACE_TIME 14
PASSWORD_VERIFY_FUNCTION complexity_function;
commit;
---------------------------------------creating the roles------------------------------
drop role DBSEC_ADMIN_ROLE;
create role DBSEC_ADMIN_ROLE;
grant select, alter on DBSEC.CUSTOMER to DBSEC_ADMIN_ROLE;
drop role DBSEC_CLERK_ROLE;
create role DBSEC_CLERK_ROLE;
grant select, insert, update on DBSEC.CUSTOMER to DBSEC_CLERK_ROLE;
drop role DBSEC_SUPERVISOR_ROLE;
create role DBSEC_SUPERVISOR_ROLE;
grant select, insert, update, delete on DBSEC.CUSTOMER to DBSEC_SUPERVISOR_ROLE;
drop role DBSEC_QUERY_ROLE;
create role DBSEC_QUERY_ROLE;
grant select on DBSEC.CUSTOMER to DBSEC_QUERY_ROLE;
--------------------------------------assigning roles and profiles to the user--------------------------
grant DBSEC_ADMIN_ROLE to DBSEC_ADMIN;
alter user DBSEC_ADMIN profile DBSEC_ADMIN_PROF;
grant DBSEC_CLERK_ROLE to DBSEC_CLERK;
alter user DBSEC_CLERK profile DBSEC_CLERK_PROF;
grant DBSEC_CLERK_ROLE to VPD_CLERK1;
alter user VPD_CLERK1 profile DBSEC_CLERK_PROF;
grant DBSEC_CLERK_ROLE to VPD_CLERK2;
alter user VPD_CLERK2 profile DBSEC_CLERK_PROF;
grant DBSEC_ADMIN_ROLE to DBSEC_DEV;
alter user DBSEC_DEV profile DBSEC_DEV_PROF;
grant DBSEC_SUPERVISOR_ROLE to DBSEC_DEV;
--------------------------------------------creating a view------------------
create view CUSTOMER_F_VIEW as
SELECT SALES_REP_ID, CUSTOMER_ID, CUSTOMER_SSN, FIRST_NAME, LAST_NAME, ADDR_LINE, CITY, STATE, ZIP_CODE, PHONE, EMAIL, CC_NUMBER, CREDIT_LIMIT, GENDER, STATUS, COMMENTS, CTL_UPD_DTTM, CTL_UPD_USER, CTL_REC_STAT FROM CUSTOMER WHERE CTL_UPD_USER= USER;
grant select, insert, delete, update on CUSTOMER_F_VIEW to VPD_CLERK1;
grant create session to VPD_CLERK1;
--------------------------------------------Implementing virtual private databse--------------
create or replace function DBSEC_ROW_OWNER(p_schema in varchar2, p_object in varchar2)
return varchar2
as
begin
if ( user = 'DBSEC' ) then return '';
else return 'CTL_UPD_USER = USER';
end if;
end;
/
--------------------------------Adding the policy function to the created row function-------------------
begin
DBMS_RLS.ADD_POLICY(
object_schema => 'DBSEC',
object_name => 'CUSTOMER',
policy_name => 'MY_OWNER_POLICY',
function_schema => 'DBSEC',
policy_function => 'DBSEC_ROW_OWNER',
statement_types => 'select, insert, update, delete' ,
update_check => TRUE
);
end;
/
------------------------------------------------------performing the query by connecting to the different user---------------------
connect VPD_CLERK1/jessie$22;
select * from DBSEC.CUSTOMER;
-----------------------------------------------------Auditing the customer table by creating the following auditing functions--------------------
connect DBSEC/tec5363#1;
connect sys as sysdba;
---------------------------droping the select policy-------------------
EXEC DBMS_FGA.DROP_POLICY (OBJECT_SCHEMA => 'DBSEC', -
OBJECT_NAME => 'customer', -
POLICY_NAME => 'AUDIT_POLICY_1_SELECT');
--------------------------creating the first policy_select----------------
EXEC DBMS_FGA.ADD_POLICY (object_schema => 'DBSEC', -
object_name => 'CUSTOMER', -
policy_name => 'AUDIT_POLICY_1_SELECT', -
audit_condition => NULL, -
audit_column => NULL, -
handler_schema => NULL, -
handler_module => NULL, -
enable => TRUE, -
statement_types => 'SELECT');
---------------------------droping the credit policy------------------
EXEC DBMS_FGA.DROP_POLICY (OBJECT_SCHEMA => 'DBSEC', -
OBJECT_NAME => 'customer', -
POLICY_NAME => 'AUDIT_POLICY_2_CREDIT');
---------------------------creating the second policy_credit-----------
EXEC DBMS_FGA.ADD_POLICY (object_schema => 'DBSEC', -
object_name => 'CUSTOMER', -
policy_name => 'AUDIT_POLICY_2_CREDIT', -
audit_condition => 'CREDIT_LIMIT > 50000', -
audit_column => NULL, -
handler_schema => NULL, -
handler_module => NULL, -
enable => TRUE, -
statement_types => 'UPDATE');
---------------------------droping the delete policy----------------------
EXEC DBMS_FGA.DROP_POLICY (OBJECT_SCHEMA => 'DBSEC', -
OBJECT_NAME => 'customer', -
POLICY_NAME => 'AUDIT_POLICY_3_DELETE');
---------------------------creating the third policy_delete-------------
EXEC DBMS_FGA.ADD_POLICY (object_schema => 'DBSEC', -
object_name => 'CUSTOMER', -
policy_name => 'AUDIT_POLICY_3_DELETE', -
audit_condition => NULL, -
audit_column => NULL, -
handler_schema => NULL, -
handler_module => NULL, -
enable => TRUE, -
statement_types => 'DELETE');
audit SELECT, UPDATE, DELETE on sys.aud$ by access;
connect DBSEC/tec5363#1;
-----------------------------giving permissions to the specific user-----------------------------------
grant SELECT, INSERT, UPDATE, DELETE on CUSTOMER to VPD_CLERK1;
grant SELECT, INSERT, UPDATE, DELETE on CUSTOMER to VPD_CLERK2;
------------------------------------connecting to the vpd user to perform audit function--------------------
connect VPD_CLERK1/jessie$22;
select first_name, last_name from DBSEC.CUSTOMER where CUSTOMER_ID = 103244;
update DBSEC.CUSTOMER set credit_limit=50000 where CUSTOMER_ID=103244;
delete from DBSEC.CUSTOMER where CUSTOMER_ID=103244;
rollback;
commit;
------------------------------------connecting to VPD2 to perform the audit function-------------------------------
connect VPD_CLERK2/lassie$46;
select first_name, last_name from DBSEC.CUSTOMER where CUSTOMER_ID = 103244;
update DBSEC.CUSTOMER set credit_limit=50000 where CUSTOMER_ID=103244;
delete from DBSEC.CUSTOMER where CUSTOMER_ID=103244;
rollback;
commit;
Related Questions
drjack9650@gmail.com
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.