× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.


  1.  Home
  2. MIDRANGE-L
  3. August 2018

RE: Another FieldProc question

I can't test it yet, but here is my theory:

I think the book just isn't doing a good job of explaining what they mean by "access". Access is granted on a row by row basis. Either you can access the row, or you can't. Columns don't come into play. When you extract data from the table, you get all the rows that meet your query specs. At that point, if there are any column masks, they are applied before presenting you the data. You still accessed the column. You just don't see the actual valid data.

Take a table with name, state, ssn. Same column mask, but a row access restriction that says that DUMMY1 doesn't have access to the state of LA.
KEVIN LA 123-45-6789
ROB IN 987-65-4321
FRED CA 555-44-3333

Rob's select see's everything and can insert anything. DUMMY1's select sees ROB and FREE with masked SSN. He can update those rows. He can insert new rows for any state but LA. If he trys to insert a row with LA in the state, it fails with permission denied.

Create another table with the same fields and restraints. Have DUMMY1 insert into table2 select * from table 1. As ROB a select * from table2 and I suspect you will see 2 rows with masked SSN's.

This conversation has gotten me interested in all of this. I'm downloading B_GROUPx_4 now to install this on my development LPAR.




Kevin Bucknum
Senior Programmer Analyst
MEDDATA/MEDTRON
Tel: 985-893-2550

-----Original Message-----
From: MIDRANGE-L [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf
Of Rob Berendt
Sent: Friday, August 31, 2018 6:53 AM
To: Midrange Systems Technical Discussion
Subject: RE: Another FieldProc question

You're right. I think section 3.5 is full of excrement. From Run SQL scripts

create table rob.employee (
Name char(30),
ssn char(11))
rcdfmt employeer
;
CREATE MASK SSN_MASK ON rob.EMPLOYEE
FOR COLUMN SSN RETURN
CASE
WHEN (current user = 'ROB')
THEN SSN
WHEN (current user = 'DUMMY1')
THEN 'XXX-XX-' || SUBSTR(SSN,8,4)
ELSE NULL
END
ENABLE;

ALTER TABLE ROB.EMPLOYEE
ACTIVATE COLUMN ACCESS CONTROL;

INSERT INTO ROB.EMPLOYEE
VALUES ('ROB', '123-45-6789');

SELECT * FROM ROB.EMPLOYEE;

CL: CRTUSRPRF DUMMY1 PASSWORD(DUMMY1);
COMMIT;

Now sign on to a 5250 session as DUMMY1

SELECT * FROM ROB.EMPLOYEE
....+....1....+....2....+....3....+....4...
NAME SSN
ROB XXX-XX-6789
******** End of data ********

UPDATE ROB.EMPLOYEE
SET SSN='234-56-7891'
WHERE NAME='ROB'
1 rows updated in EMPLOYEE in ROB.

SELECT * FROM ROB.EMPLOYEE
....+....1....+....2....+....3....+....4...
NAME SSN
ROB XXX-XX-7891
******** End of data ********



Rob Berendt
--
IBM Certified System Administrator - IBM i 6.1 Group Dekko Dept 1600 Mail
to: 2505 Dekko Drive
Garrett, IN 46738
Ship to: Dock 108
6928N 400E
Kendallville, IN 46755
http://www.dekko.com





From: "Rob Berendt" <rob@xxxxxxxxx>
To: "Midrange Systems Technical Discussion" <midrange-l@xxxxxxxxxxxx>
Date: 08/31/2018 07:10 AM
Subject: RE: Another FieldProc question
Sent by: "MIDRANGE-L" <midrange-l-bounces@xxxxxxxxxxxx>



Again, doesn't that totally fly in the face of the line from section 3.5:
<snip>
Furthermore, DB2 prevents an INSERT statement from inserting a row or an
UPDATE statement from modifying a row such that the current user cannot
be

permitted to access it. You cannot create a situation in which the data
you inserted or changed is no longer accessible to you.
</snip>

Rob Berendt
--
IBM Certified System Administrator - IBM i 6.1
Group Dekko
Dept 1600
Mail to: 2505 Dekko Drive
Garrett, IN 46738
Ship to: Dock 108
6928N 400E
Kendallville, IN 46755
http://www.dekko.com





From: "Birgitta Hauser" <Hauser@xxxxxxxxxxxxxxx>
To: "'Midrange Systems Technical Discussion'"
<midrange-l@xxxxxxxxxxxx>
Date: 08/31/2018 01:03 AM
Subject: RE: Another FieldProc question
Sent by: "MIDRANGE-L" <midrange-l-bounces@xxxxxxxxxxxx>



If you are working with RCAC, you need either a CHECK Constraint with an
ON
INSERT VIOLATION and ON UPDATE VIOLATION, or a SECURED Before
Insert/Update
Trigger for avoiding the "real" data gets overridden:

Example Check Constraint:
Alter Table YourSchema.YourTable
Add Check (Birthday <> '0001-01-01'
On Insert Violation Set BirthDay = Default
On Update Violation Preserve BirthDay;

Example Before Trigger
Create or Replace Trigger YourScchema.Replace_Mask_Birthday
Before Insert or Update On YourSchema.YourTable
Referencing New Row as N Old Row as O
For Each Row
Mode Db2Row
Secured
When (n.Birthday = '0001-01-01')
Begin
If Inserting Then Set N.Birthday = Default;
ElseIf Updating Tnen Set N.BirthDay = o.Birthday;
End If;
End;

In this way you will avoid that data gets inadverted overridden.

Mit freundlichen Grüßen / Best regards

Birgitta Hauser

"Shoot for the moon, even if you miss, you'll land among the stars." (Les
Brown)
"If you think education is expensive, try ignorance." (Derek Bok)
"What is worse than training your staff and losing them? Not training them
and keeping them!"
„Train people well enough so they can leave, treat them well enough so
they
don't want to.“ (Richard Branson)


-----Original Message-----
From: MIDRANGE-L <midrange-l-bounces@xxxxxxxxxxxx> On Behalf Of
James H.
H.
Lampert
Sent: Donnerstag, 30. August 2018 19:08
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxx>
Subject: Another FieldProc question

Ladies and Gentlemen:

Suppose a file has a FieldProc (we'll assume, for now, that it's from
Crypto
Complete) on a field. And suppose some users are authorized to see the
complete field contents, others are authorized to see partially redacted
field contents, and others are not authorized to see the field at all.

Let's also assume that the FieldProc is not some total botch job.

Now suppose user JSMITH, who is not authorized to see the field at all,
changes a record in the file, and user JDOE, who is authorized to see
partially redacted contents, each change a record. In neither case do they
enter a new value for the field that has the FieldProc on it.

What happens to the value of that field?

And what happens to it if they DO enter new values for it?

--
JHHL
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx To subscribe,
unsubscribe,
or change list options,
visit: https://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx Before posting, please take
a
moment to review the archives at https://archive.midrange.com/midrange-l.

Please contact support@xxxxxxxxxxxx for any subscription related
questions.

Help support midrange.com by shopping at amazon.com with our affiliate
link:
http://amzn.to/2dEadiD

--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at https://archive.midrange.com/midrange-l.

Please contact support@xxxxxxxxxxxx for any subscription related
questions.

Help support midrange.com by shopping at amazon.com with our affiliate
link: http://amzn.to/2dEadiD



--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at https://archive.midrange.com/midrange-l.

Please contact support@xxxxxxxxxxxx for any subscription related
questions.

Help support midrange.com by shopping at amazon.com with our affiliate
link: http://amzn.to/2dEadiD



--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at https://archive.midrange.com/midrange-l.

Please contact support@xxxxxxxxxxxx for any subscription related
questions.

Help support midrange.com by shopping at amazon.com with our affiliate
link: http://amzn.to/2dEadiD

As an Amazon Associate we earn from qualifying purchases.

This thread ...
Replies:
Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.