Re: Crystal reports, synonyms, views and Security

Simon Dobner wrote:
>
> I have an application which has a security layer of which a part is made of
> roles and synonyms.
>
> Synonyms are created in each users schema, these synonyms point to views
> which sit on top of the real table.
>
> When a user selects from table tab_1 he is really selecting from synonym tab_1
> which points to view tab_1_nn which sits on top of table tab_1.
>
> A user can only select from 'tab_1', not 'owner.tab_1'
>
> Some persons here wish to use Crystal Reports to query the database.
> Crystal Reports appears to insist on prefixing all tables with the table
> owner in it's generated SQL- ie all reports select from owner.tab_1
>
> This means that unless the user who actually wrote the report is the one
> running it, it will fail with an ORA-00942.
>
> I know that you can manually edit the SQL in the 'view Database SQL'
> function, but any changes are overwritten every time the report changes,
> and my users are not real happy about having to do it anyway.
>
> The currently (user) proposed solution is to give every user the owner
> password (they are all very trustworthy they tell me)
>
> I'm hoping that someone out there has encountered this problem and solved
> it in a DBA approved way ?
>
> We are currently on Crystal v7, but could upgrade to 8 or 8.5

It not a Crystal reports problem. In fact, from all of the SQL generating tools ( and Crystal reports is one ) I have seen, they all generate SQL in a similar way. As it turns out, <owner>.<table> is the proper full syntax. Now we understand that, where is the problem?

The problem is the synonyms. Get rid of the synonyms. Take a look at how Oracle structures its data dictionary. Oracle creates a set of views in the data dictionary catalog ( V$_PARAMETER, DBA_TABLES, USER_ROLES, ALL_INDEXES, etc ). Upon these views are created PUBLIC synonyms. Oracle grants select on the views to the SELECT_CATALOG role. What you need to do is grant SELECT on <owner>.<view> to a role such as SELECT_REPORT_DATA, then grant that role to the appropriate users. You could even leave the private synonyms in tact if needed, but you have to grant select on the underlying tables/views upon which the queries are executed. It seems that the designer of your database went overboard in the number of layers of 'insulation' he placed between the users and the data.

-- 
Andrew Allen
Livonia, MI
E- Mailto:ajalle_at_ameritech.net