Hi,

We are using a DBA master file to add security to various other master files.
So basically at the end of each master file we add the following:
DBA=DBAPW,
DBAFILE=DBAFILE,$
This then references a master file that adds security similar to the following:
USER=DEPART1,ACCESS=RW,$
USER=DEPART2,ACCESS=RW,RESTRICT=VALUE,NAME=SYSTEM,VALUE=DNAME NE 'AA' OR 'AB',$

When I use webfocus code for reports, all run fine with no errors and the security works as expected, but any reports written in SQL (SQLMSS) are failing and producing the following error:
(FOC051) DBA ERROR. RESTRICTED FIELDNAME NOT RECOGNIZED: DEPART1
(FOC051) DBA ERROR. RESTRICTED FIELDNAME NOT RECOGNIZED: DEPART2

Any help as to why this is happening would be great.

Thanks.

This message has been edited. Last edited by: Jinx,

Jinx,
Where is the password???

From IBI:

Reference: DBA Guidelines
You can ensure that the security restrictions you place on Master Files are correct by adhering to these guidelines.

Every file with access limits must have a DBA password.

No segment, field, or field value restrictions may be specified at the Database Administrator level. The Database Administrator should have unlimited access to the data source and all cross-referenced data sources.

Once security restrictions have been applied, the Database Administrator should conduct thorough testing of every restriction before the data source is used.

It is particularly important to check field values to make sure they do not contain errors. If they are in error, user access to the field data will be unnecessarily restricted.

All groups of cross-referenced data sources must have the same security restrictions.

You must have a DBA password to encrypt and decrypt or restrict existing data sources.

The Database Administrator can change any type of security restriction.

Access levels effect what fields users can access. The Database Administrator must consider what commands each user will need. If a user does not have access rights, that user will receive an error message.

Hope this helps...

Tom

The DBA password is there - set as DBAPW in the master file called DBAFILE.mas
The fact that it all works 100% fine with standard webfocus script (TABLE FILE..blah blah) and not with SQL (Select *) seems to point to something missing within the SQL 'set up' - but that is just a guess.

Thanks for the suggestion though!

Jinx,
Could you show an example of SQL code?

Hi,

This is some of the code...

-SET &TODAY = &YYMD;
-SET &LAST_MONTH = DATECVT((DATEADD((DATEMOV((DATECVT(&TODAY,'I8YYMD','YYMD')), 'BOM')), 'M', -1)), 'YYMD','I8YYMD');
-SET &THIS_MONTH = DATECVT((DATEMOV((DATECVT(&TODAY,'I8YYMD','YYMD')), 'BOM')), 'YYMD','I8YYMD');

SQL SELECT DISTINCT
T1.STATUS,
T1.CODE,
T2.CLIENT_NUMBER,
T2.CLIENT_NAME,
T3.LCODE,
T4.TERM,
FROM
T1 INNER JOIN T3 ON
T1.CODE = T3.CODE
AND T1.NUMB = T3.NUMB INNER JOIN T2 ON
T1.CODE = T2.CODE
AND T1.NUMB = T2.NUMB LEFT JOIN T4 ON
T1.CODE = T4.CODE
AND T1.NUMB = T4.NUMB
WHERE
PART = 'CA'
AND T1.E_MONTH = '&LAST_MONTH'
AND T1.REP_DATE = '&THIS_MONTH'
AND COMPANY NOT IN ('AS', 'DE', 'MR', 'JN')
AND MARKET <> 'PP'
AND STATUS NOT IN ('TERM', 'RECOVER')
AND RECOVERY_ = 'N'
;
END

TABLE FILE SQLOUT
PRINT *
ON TABLE HOLD AS HFINAL
END

This code has been working well for some time now - but has 'fallen over' since inclusion of DBA security in master file.


Thanks,

Jinx.

This message has been edited. Last edited by: Jinx,

Jinx,

Apparently you are using SQL passthru.
I wonder how come a master file is accessed. Are your tables called T1 T2 T3?

Daniel,

Unfortunately I am quite new to webfocus so had no clue that SQL Passthrough bypassed the master file...thanks for the info (I feel like a total idiot now :-/). Either way, it is besides the point.
I didn't mention that I had changed the code to the following when trying to add the DBA security (I will edit my previous post to reflect this):

SQL
SELECT DISTINCT
T1.STATUS,
T1.CODE,
T2.CLIENT_NUMBER,
T2.CLIENT_NAME,
T3.LCODE,
T4.TERM,
FROM
T1 INNER JOIN T3 ON
T1.CODE = T3.CODE
AND T1.NUMB = T3.NUMB INNER JOIN T2 ON
T1.CODE = T2.CODE
AND T1.NUMB = T2.NUMB LEFT JOIN T4 ON
T1.CODE = T4.CODE
AND T1.NUMB = T4.NUMB
WHERE
etc etc

Same problem with the DBA error.
The table names are not T1, T2 etc, I have changed them for this post.

Thanks,

Jinx.

This message has been edited. Last edited by: Jinx,

Jinx,

If I am not mistaken, in you previous post you had an SQL SQLMSS statement. Now you show just SQL. So you are not using passthru. In fact, Focus is translating your SQL to TABLE and accessing your master files.
So, I would suggest you write something simple:

  
TABLE FILE T1
PRINT F1
END

SQL
SELECT F1 FROM T1
;
END

and see what happens.

Is there more code that you are not providing to us. Could you possibly be generating additional temporary files, followed by a TABLE and WebFOCUS is picking up the wrong Master?

Or simply finding the one that is DBA protected.

Daniel,

Yeah as I mentioned on my previous post, I was going to edit the SQL so it was not using passthru as it confused the issue. What you suggested I have tried before, so a simple:
TABLE FILE T1
PRINT *
END....works, data is returned

but

SQL
SELECT * FROM T1;
END....does not work - get the DBA error.

To reply to FortuneCookie, the code I provided is exactly what I am trying to run, there are no additional temporary files.

Thanks!

Jinx.

Jinx,

I ran a little test and it seems to work. So here is what I did:

A master:

  
FILE=AK, SUFFIX=FIX
 SEGNAME=SEG1, SEGTYPE=S0
 FIELD=DEPT, ALIAS=DP, USAGE=I3, ACTUAL=A3, $
 FIELD=CITIES, ALIAS=CTS, USAGE=A50, ACTUAL=A50, $
END
DBA=DANNY, DBAFILE=DBA, $

The data:

  
001Chicago,Lisle,Naperville
002Detroit,Southfield,Kalamazoo
003New York,Albany
004Washington
005San Francisco,Los Angeles,San Diego,Beverly Hills

The DBA file

  
FILE=DBA, SUFFIX=FIX
 SEGNAME=DUMMY
 FIELD=DUMMY, ALIAS=, USAGE=A1, ACTUAL=A1, $
END
DBA=DANNY, $
PASS= , ACCESS=R, $
PASS=YOU, ACCESS=R, RESTRICT=VALUE, NAME=SYSTEM, VALUE=READLIMIT EQ 100, $
FILENAME=AK,$
PASS= , ACCESS=R, $
PASS=YOU, ACCESS=R, RESTRICT=VALUE, NAME=SYSTEM, VALUE=DEPT NE 4, $

The focexec:

  
-* File AK2.fex
-SET &ECHO=ALL;
SET PASS=YOU
FILEDEF AK DISK C:\ibi\apps\focalpoint\AK.ftm
TABLE FILE AK
PRINT CITIES
BY DEPT
END
SQL
SELECT DEPT, CITIES FROM AK
ORDER BY DEPT
;
END

The output:

  
PAGE     1
  
  
  DEPT  CITIES                                                                  
  ----  ------                                                                  
     1  Chicago,Lisle,Naperville                          
     2  Detroit,Southfield,Kalamazoo                      
     3  New York,Albany                                   
     5  San Francisco,Los Angeles,San Diego,Beverly Hills 


PAGE     1
  
  
  DEPT  CITIES                                                                  
  ----  ------                                                                  
     1  Chicago,Lisle,Naperville                          
     2  Detroit,Southfield,Kalamazoo                      
     3  New York,Albany                                   
     5  San Francisco,Los Angeles,San Diego,Beverly Hills 

Notice the same outputs.

DBA security is not meant to work for passthru. It is dependent on a master file description to either have the DBA info in it or refer to a file. By definition, SQL Passthru does not have a master so there is no where for WF to go to find the DBA information.

Thought the problem was solved, but unfortunately it wasn't, though I have managed to identify exactly what is causing the problem but not why. Decision is to convert all SQL code to webfocus and warn other users not to use SQL on tables with DBA security added.
Thanks All.

Jinx.

How are you passing the DBA password to the fex? In previous postings, I don't see a SET PASS=DBAPW statement. You must either prompt the user for the DBA pass and do a variable substitution, i.e. SET PASS=&DBAPASS; or hard-code it in your program and then encrypt the program.

Jinx,

This really is weird. Tell me, could you use the masters and data I sent you and see if you have the same problem? It works fine with me.

Daniel,

Yours worked perfectly. It did help me figure out where the problem is. The DBA file is from our old version of WFS (and contains a lot of code as it implements security for most of out master files therefore the reason I only posted a portion of it), we have just upgraded, so perhaps this is a legacy problem...
I changed yours slightly to resemble my DBA file more, By adding one more master file - which references an employee table called BA. The problem comes in where I try to restrict an entire FIELD on the AK master file. So..using the data you gave me and adding one master file and some additions to the DBA:

BA Data

BOB
ANNE
DEL

BA Master file:

FILENAME=BA, SUFFIX=SQLMSS , $
SEGMENT=BA, SEGTYPE=S0, $
FIELDNAME=EMP, ALIAS=EMP, USAGE=A10, ACTUAL=A10,
MISSING=ON, $
END
DBA=DANNY, DBAFILE=DBATEST, $

AK Master file:

FILENAME=AK, SUFFIX=SQLMSS , $
SEGMENT=AK, SEGTYPE=S0, $
FIELDNAME=DEPT, ALIAS=DEPT, USAGE=A10, ACTUAL=A10,
MISSING=ON, $
FIELDNAME=CITIES, ALIAS=CITIES, USAGE=A10, ACTUAL=A10,
MISSING=ON, $
END
DBA=DANNY, DBAFILE=DBATEST, $

DBA file:

FILE=DBATEST, SUFFIX=FIX
SEGNAME=DUMMY
FIELD=DUMMY, ALIAS=, USAGE=A1, ACTUAL=A1, $
END
DBA=DANNY, $
PASS= , ACCESS=R, $
PASS=YOU, ACCESS=R, RESTRICT=VALUE, NAME=SYSTEM, VALUE=READLIMIT EQ 100, $
FILENAME=AK,$
PASS= , ACCESS=R, $
PASS=YOU, ACCESS=R, RESTRICT=FIELD, NAME=CITIES, $
FILENAME=BA,$
PASS= , ACCESS=R, $
PASS=YOU, ACCESS=R, RESTRICT=VALUE, NAME=SYSTEM, VALUE=EMP NE 'BOB', $

FEX:

SET USER=YOU

TABLE FILE BA
PRINT EMP
END

SQL
SELECT EMP
FROM BA;
END


Result:

PAGE 1

EMP
ANNE
DEL

PAGE 2

(FOC051) DBA ERROR. RESTRICTED FIELDNAME NOT RECOGNIZED: YOU

This exactly replicates my problem, where the webfocus code works fine but the SQL doesn't. I suppose the question is, can one restrict access to fields in this way, is the way it is done incorrect?


Ginny, each password is set for a specific user group per report, basically hardcoded as a standard at the beginning of each report - if for some reason the report writer forgets this, there is further checking in reportcaster which runs a small script and adds password/user details. For any web based adhoc reports, the user will enter a username and password into the web app that runs the report, thereby given access to only those items she has permission to view (as set out in the DBA and various other security 'methods').


Thanks,
Jinx.

Jinx,

I have reproduced your problem. In my opinion this is a BUG and I would open a case with IBI. I can post my repro, if you wish.

Daniel,

Thanks for all the help, I'll look at bothering IBI then ;-).

Jinx.