Applicable Versions: 3.05.0031 Onwards
Users can only create subjects available. As such, user data access can be controlled by limiting the subjects. This means it is common to want different subjects available for different users.
DataPA reads the schema from the connected databases, and builds a series of temp tables for the schema, which are made available to the user. If any of these records are deleted from the temp tables then the fields/tables will not be available to the end user. DataPA provides a procedure ( PAFilterSchema ) that enables the schema to be limited for different users.
The PAFilterSchema procedure must be called PAFilterSchema.p and reside in the PROPATH of the AppServer. The procedure receives the username as an input parameter, a ttDB, ttdbTable, ttdbIndex, dbIdxField and dbRelations temp tables as input-output parameters. The definitions for this procedure should be as follows:
DEFINE TEMP-TABLE DB
FIELD DBID AS INTEGER
FIELD LogName AS CHARACTER
FIELD PhName AS CHARACTER
INDEX idx IS PRIMARY IS UNIQUE DBID.
DEFINE TEMP-TABLE dbTABLE
FIELD DBID AS INTEGER
FIELD TableID AS INTEGER
FIELD NAME AS CHARACTER
INDEX idx IS PRIMARY IS UNIQUE DBID TableID.
DEFINE TEMP-TABLE dbFIELD
FIELD DBID AS INTEGER
FIELD TableID AS INTEGER
FIELD Col-label AS CHARACTER
FIELD Data-Type AS CHARACTER
FIELD fDecimals AS INTEGER
FIELD fDesc AS CHARACTER
FIELD Extent AS INTEGER
FIELD Field-Name AS CHARACTER
FIELD Fld-case AS LOGICAL
FIELD fFormat AS CHARACTER
FIELD fHelp AS CHARACTER
FIELD Initial AS CHARACTER
FIELD fLabel AS CHARACTER
FIELD Mandatory AS LOGICAL
FIELD Order AS INTEGER
FIELD Valexp AS CHARACTER
FIELD Valmsg AS CHARACTER
FIELD fView-As AS CHARACTER
FIELD Width AS INTEGER
INDEX idx IS PRIMARY IS UNIQUE DBID TableID Order.
DEFINE TEMP-TABLE dbINDEX
FIELD DBID AS INTEGER
FIELD TableID AS INTEGER
FIELD Active AS LOGICAL
FIELD Index-Name AS CHARACTER
FIELD idxDesc AS CHARACTER
FIELD PRIMARY AS LOGICAL
FIELD idxUnique AS LOGICAL
FIELD Wordidx AS LOGICAL
INDEX idx IS PRIMARY IS UNIQUE DBID TableID Index-Name.
DEFINE TEMP-TABLE dbIDXFIELD
FIELD DBID AS INTEGER
FIELD TableID AS INTEGER
FIELD Index-Name AS CHARACTER
FIELD Field-Name AS CHARACTER
FIELD Abbreviate AS LOGICAL
FIELD idxAscending AS LOGICAL
FIELD Index-Seq AS INTEGER
INDEX idx IS PRIMARY IS UNIQUE DBID TableID Index-Name Index-Seq.
DEFINE TEMP-TABLE dbRelations
FIELD DBID AS INTEGER
FIELD PARENT AS CHARACTER
FIELD CHILD AS CHARACTER
INDEX idx IS PRIMARY IS UNIQUE DBID PARENT Child.
DEFINE INPUT PARAMETER ip-cUserName AS CHARACTER NO-UNDO.
DEFINE INPUT-OUTPUT PARAMETER TABLE FOR DB.
DEFINE INPUT-OUTPUT PARAMETER TABLE FOR dbTable.
DEFINE INPUT-OUTPUT PARAMETER TABLE FOR dbField.
DEFINE INPUT-OUTPUT PARAMETER TABLE FOR dbIndex.
DEFINE INPUT-OUTPUT PARAMETER TABLE FOR dbIdxField.
DEFINE INPUT-OUTPUT PARAMETER TABLE FOR dbRelations.
The username received by PAFilterSchema is the username entered by the user when DataPA connects to the AppServer, if available, otherwise the users Windows username.
The temp tables contain the schema tables, fields and indexs that will be available for the users to build subjects. If you delete any temp table records, the corresponding table, field or index will not be available to the user.
An example of the body code for PAFilterSchema.p is as follows:
DEFINE VARIABLE lFound AS LOGICAL NO-UNDO.
DEFINE VARIABLE cGroups AS CHARACTER NO-UNDO.
/* Only Michael are allowed to see the admin table*/
FOR EACH dbTable
WHERE dbTable.NAME = ""Admin"" :
/* No DataPA Uses are allowed to see the email and Mobile number fields*/
FOR EACH dbField
WHERE dbField.DBID = dbTable.DBID
AND dbField.Field-Name = ""MobileNumber""
OR dbField.Field-Name = ""Email"":
DELETE dbField.
END.
IF ip-cUserName <> ""Michael"" THEN
DELETE dbTable.
END.