Re: 2 calls to same program using different parameter lengths

I strongly recommending making this a subprocedure in a service program.
Have the subprocedure have the one input variable, which would be the raw
account number. And have it return the scrambled account number.
AccountNumberScrambled = ScrambleAcct(AsFromFile);

And this is why...
Starting with 7.2, if you load the free
Resource
ID Option Description
5770SS1 47 IBM Advanced Data Security for i

Then you can use "row and column access control" aka RCAC (pronounced
r-cak)

First, you would create a function which present your new subprocedure to
sql.
CREATE FUNCTION MYLIB.SCRAMBLEACCT(VARCHAR(17))
RETURNS VARCHAR(17)
LANGUAGE RPGLE
SPECIFIC MYLIB.SCRAMBLEACCT
DETERMINISTIC
NO SQL
RETURNS NULL ON NULL INPUT
NO EXTERNAL ACTION
EXTERNAL NAME 'MYLIB/MYSRVPGM(MYSUBPROCEDURE)'
PARAMETER STYLE GENERAL;


You can test your subprocedure with this:
select ColA, ColB, ColC, scrambleacct(myacct) as myacct
from mytable

Your subprocedure could be enhanced to determine if the user is a
'superuser' and should not have this scrambled but should instead see the
raw data.

Then, when you have it working as desired,

CREATE MASK ACCT_MASK ON MYTABLE
FOR COLUMN MYACCT RETURN
SCRAMBLEACCT(MYACCT)
END
ENABLE;

ALTER TABLE MYTABLE
ACTIVATE COLUMN ACCESS CONTROL;

This will be highly effective. Regular queries, DSPPFM, RPG chain's, etc,
will all respect this. Even QSECOFR.

There is something called "separation of duties" in security. You would
have to add those who can do CREATE MASK into such things. See
CHGFCNUSG FCNID(QIBM_DB_SECADM) USER(myuserid) USAGE(*ALLOWED)
If you are in a HA environment ensure you do this on your backup machine
as well, and put your HA vendor user into CHGFCNUSG also. Sample
CHGFCNUSG FCNID(QIBM_DB_SECADM) USER(MIMIXOWN) USAGE(*ALLOWED)
Separation of duties allows someone to have *ALLOBJ, *SAVRST and *SECADM
to do backups and security functions and yet still not see social security
numbers.

If you're not ready to fully dive into RCAC, or if you're running a
primitive like 7.1, you can create views which use this same subprocedure
and function like this:
CREATE VIEW MytblMasked as (
select ColA, ColB, ColC, scrambleacct(myacct) as myacct
from mytable)
Then you lock them out of the table and only allow them to use the view.
This involves getting into the details of EDTOBJAUT.

Rob Berendt