Subject Re: [firebird-support] Account security for running reports
Author Helen Borrie
At 02:30 AM 11/02/2010, Myles Wakeham wrote:
>I posted to the Firebird-tools list a more detailed question
>specifically regarding setting up a 'reporting user' account on Firebird
>for Crystal Reports, only to later work out that the problem I am
>encountering is more of a general security/grant issue with FB, so I
>think the more appropriate place to post the question is here.
>I need to create a user account with FB 1.5 super server that can run
>reports in a 'read/only' state. I'm using IBExpert to administer
>privileges however I can also do this through generic SQL.
>I have created a 'reports' user account, but no matter what I seem to do
>with privileges, I'm getting an error with 3rd party reporting tools
>stating that they cannot see the tables. However they run the reports
>created for this user just fine, and show the data correctly. It would
>seem that the errors I am getting has something to do with the inability
>of any 3rd party reporting program through the Firebird ODBC driver v2
>(the open source one) to read the schema of the database for the
>reporting user. Yet if I run the report as SYSDBA on the database, it
>works without issue.

SYSDBA can see everything. No other user can see *anything* unless you have granted the required privileges to that user.

>What is the statement I should be using to grant privileges to an
>account for the database, so that the account can fully see the metadata
>of the database, yet has read/only access to the tables in the database?

Create a role, e.g., REPORTS_ROLE and provide SELECT and REFERENCES privileges to all the objects you want users of that role to see.


The application must then log in providing both the user name and the role. It should then also be able to see the associated metadata tables.

> Is there a particular trick required to set this up so that ODBC users
>can run reports with 3rd party reporting tools without a bunch of error
>dialogs popping up all the time?

"Error dialogs popping up all the time" suggests that the application code isn't handling exceptions appropriatelly. Firebird can't fix that for you.

The Firebird ODBC driver would (or should) have some structure available in its parameters that resolves the generic ODBC assumptions about metadata access to fit the Firebird use case. That's the question you need to ask in the firebird-odbc-devel list.

That list is also where you are likely to find other Crystal users who might have some ingenious magic trick to offer for particular Crystal annoyances.

^ heLen ^