Fixing errors found when attempting to view a DeepSee listing

This post provides further detail on the suggestions from the DeepSee Troubleshooting Guide about what to do if a user is not able to view a listing in a DeepSee cube, but instead sees errors like this:

ERROR #5540: SQLCODE: -99 Message: User <username> is not privileged for the operation [zfindStatement+118^%SQL.DynamicStatement.1:<namespace>] (8)

If a user can create a pivot table in Analyzer, but cannot view a detail listing based on that pivot, it may be necessary to grant them additional permissions on the source tables used by the cube.

The DeepSee documentation contains a list of security requirements for various tasks. In order to view a listing, a user must have the SQL SELECT privilege for each source table from which the listing selects data, and must have the SQL EXECUTE privilege for each SQL stored procedure run by the listing query.

In the example that follows, the user lacks the SELECT privilege on the source table. When a user who lacks that privilege attempts to view a listing, an error like the following may be displayed instead:

You can view the SQL Listing Query to see which tables are involved by clicking on the circled button:

Here, the SQL statement selects from the ListingTest.Person table.

To check whether this user has the necessary permissions on that table, log in as a user with administrator privileges and go to System Administration -> Security -> Users.

Select the user who encountered the error, go to the SQL Tables tab, and select the namespace where the source table is stored. Here, the user RoleTest has SELECT and other privileges on the table which stores the listing itself, but not on the source table:

You may wish to create a new user for testing purposes, copying from the user who encountered the error.

To grant a user the SELECT privilege on the source table, use the Add Tables option. You may add this privilege to the user directly, to a role of which the user is a member (which grants the privilege to all members of that role), or to the user _PUBLIC (which grants the privilege to all users), depending on whom you want to allow to query the table.

If data is selected from multiple source tables in the listing query, make sure to grant the SQL SELECT privilege for all of them.

If the SQL Listing Query calls any SQL stored procedures, a user must also have the EXECUTE privilege on those procedures in order to view the listing. You can grant this privilege in the SQL Procedures tab when editing the user definition.

Because of the configuration of the SAMPLES namespace, it is more difficult to create errors of this sort there. If you are interested in creating and resolving this kind of error for testing purposes, you should use a namespace other than SAMPLES.