Focal Point Banner


As of December 1, 2020, Focal Point is retired and repurposed as a reference repository. We value the wealth of knowledge that's been shared here over the years. You'll continue to have access to this treasure trove of knowledge, for search purposes only.

Join the TIBCO Community
TIBCO Community is a collaborative space for users to share knowledge and support one another in making the best use of TIBCO products and services. There are several TIBCO WebFOCUS resources in the community.

  • From the Home page, select Predict: WebFOCUS to view articles, questions, and trending articles.
  • Select Products from the top navigation bar, scroll, and then select the TIBCO WebFOCUS product page to view product overview, articles, and discussions.
  • Request access to the private WebFOCUS User Group (login required) to network with fellow members.

Former myibi community members should have received an email on 8/3/22 to activate their user accounts to join the community. Check your Spam folder for the email. Please get in touch with us at community@tibco.com for further assistance. Reference the community FAQ to learn more about the community.


Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     SQL Script causing DBA file error.

Read-Only Read-Only Topic
Go
Search
Notify
Tools
SQL Script causing DBA file error.
 Login/Join
 
Gold member
posted
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,


7.6.11
Windows
HTML, PDF, Excel etc
DevStudio/Webfocus/Focus IBM
SQL Server 2000 / 2008
DB2
 
Posts: 78 | Location: UK | Registered: February 07, 2008Report This Post
Expert
posted Hide Post
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


Tom Flynn
WebFOCUS 8.1.05 - PROD/QA
DB2 - AS400 - Mainframe
 
Posts: 1972 | Location: Centennial, CO | Registered: January 31, 2006Report This Post
Gold member
posted Hide Post
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!


7.6.11
Windows
HTML, PDF, Excel etc
DevStudio/Webfocus/Focus IBM
SQL Server 2000 / 2008
DB2
 
Posts: 78 | Location: UK | Registered: February 07, 2008Report This Post
Virtuoso
posted Hide Post
Jinx,
Could you show an example of SQL code?


Daniel
In Focus since 1982
wf 8.202M/Win10/IIS/SSA - WrapApp Front End for WF

 
Posts: 1980 | Location: Tel Aviv, Israel | Registered: March 23, 2006Report This Post
Gold member
posted Hide Post
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,


7.6.11
Windows
HTML, PDF, Excel etc
DevStudio/Webfocus/Focus IBM
SQL Server 2000 / 2008
DB2
 
Posts: 78 | Location: UK | Registered: February 07, 2008Report This Post
Virtuoso
posted Hide Post
Jinx,

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


Daniel
In Focus since 1982
wf 8.202M/Win10/IIS/SSA - WrapApp Front End for WF

 
Posts: 1980 | Location: Tel Aviv, Israel | Registered: March 23, 2006Report This Post
Gold member
posted Hide Post
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,


7.6.11
Windows
HTML, PDF, Excel etc
DevStudio/Webfocus/Focus IBM
SQL Server 2000 / 2008
DB2
 
Posts: 78 | Location: UK | Registered: February 07, 2008Report This Post
Virtuoso
posted Hide Post
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.


Daniel
In Focus since 1982
wf 8.202M/Win10/IIS/SSA - WrapApp Front End for WF

 
Posts: 1980 | Location: Tel Aviv, Israel | Registered: March 23, 2006Report This Post
Platinum Member
posted Hide Post
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.
 
Posts: 140 | Registered: May 02, 2007Report This Post
Gold member
posted Hide Post
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.


7.6.11
Windows
HTML, PDF, Excel etc
DevStudio/Webfocus/Focus IBM
SQL Server 2000 / 2008
DB2
 
Posts: 78 | Location: UK | Registered: February 07, 2008Report This Post
Virtuoso
posted Hide Post
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.


Daniel
In Focus since 1982
wf 8.202M/Win10/IIS/SSA - WrapApp Front End for WF

 
Posts: 1980 | Location: Tel Aviv, Israel | Registered: March 23, 2006Report This Post
Expert
posted Hide Post
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.


Ginny
---------------------------------
Prod: WF 7.7.01 Dev: WF 7.6.9-11
Admin, MRE,self-service; adapters: Teradata, DB2, Oracle, SQL Server, Essbase, ESRI, FlexEnable, Google
 
Posts: 2723 | Location: Ann Arbor, MI | Registered: April 05, 2006Report This Post
Gold member
posted Hide Post
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.


7.6.11
Windows
HTML, PDF, Excel etc
DevStudio/Webfocus/Focus IBM
SQL Server 2000 / 2008
DB2
 
Posts: 78 | Location: UK | Registered: February 07, 2008Report This Post
Expert
posted Hide Post
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.


Ginny
---------------------------------
Prod: WF 7.7.01 Dev: WF 7.6.9-11
Admin, MRE,self-service; adapters: Teradata, DB2, Oracle, SQL Server, Essbase, ESRI, FlexEnable, Google
 
Posts: 2723 | Location: Ann Arbor, MI | Registered: April 05, 2006Report This Post
Virtuoso
posted Hide Post
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
In Focus since 1982
wf 8.202M/Win10/IIS/SSA - WrapApp Front End for WF

 
Posts: 1980 | Location: Tel Aviv, Israel | Registered: March 23, 2006Report This Post
Gold member
posted Hide Post
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.


7.6.11
Windows
HTML, PDF, Excel etc
DevStudio/Webfocus/Focus IBM
SQL Server 2000 / 2008
DB2
 
Posts: 78 | Location: UK | Registered: February 07, 2008Report This Post
Virtuoso
posted Hide Post
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
In Focus since 1982
wf 8.202M/Win10/IIS/SSA - WrapApp Front End for WF

 
Posts: 1980 | Location: Tel Aviv, Israel | Registered: March 23, 2006Report This Post
Gold member
posted Hide Post
Daniel,

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

Jinx.


7.6.11
Windows
HTML, PDF, Excel etc
DevStudio/Webfocus/Focus IBM
SQL Server 2000 / 2008
DB2
 
Posts: 78 | Location: UK | Registered: February 07, 2008Report This Post
  Powered by Social Strata  

Read-Only Read-Only Topic

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     SQL Script causing DBA file error.

Copyright © 1996-2020 Information Builders