Previous Topic: Securing Data Access for DQL UseNext Topic: Authorizing Data Access Using the Security Maintenance Menu

DQ Internal Security › Securing Data Access for DQL Use › Limiting Access to Database Tables

Limiting Access to Database Tables

The Database Administrator can list all CA Datacom/DB tables, rows, and columns that the users are going to query. Determine which users need to access which tables and list each user and the tables to which they need access. If your site security is controlled by an external security package, make sure the Security Administrator who manages that package has this information. CA Dataquery uses the external CA Datacom/DB security for table and database level access.

Table assignments control what each user sees on the Directory of Tables panel. When a list of tables is requested, the current authorization ID of the user is used to read the CA Datacom Datadictionary table for all tables, views, and synonyms for that authorization ID. Items on the list are checked for read(SELECT) authorization by a call to CA Datacom/DB. Although your site might have specified multiple CA Datacom Datadictionary DBIDs, all of the data entered under the SECURITY CONTROL administrative function is stored in the CA Datacom Datadictionary DBID named in the DQOPTLST macro DDDBID= parameter.

When you add a user to CA Dataquery using the User Table Maintenance panel under the USERS administrative function, CA Dataquery automatically creates a CA Datacom Datadictionary PERSON entity-occurrence. This entity-occurrence is added to the dictionary named in the DDDBID= parameter in the DQOPTLST macro. This entity-occurrence name is the same as the CA Dataquery user ID.

For CA Dataquery Security to work there can be no LOCK or password on the following CA Datacom Datadictionary tables:

Personal tables created in DQL Mode cannot be accessed by another user unless the user knows the full name of the table including its AUTHID.

Note: CA Datacom/DB database IDs can range from 1 to 5000. However, CA Dataquery internal security only allows three digits for a database ID. Therefore, if you have tables in databases with IDs greater than 999 that need to be secured, you must use external security.

In SQL Mode, access to personal tables can only be given by the table creator, using the GRANT command or through CA Datacom/DB security. The REVOKE command removes the authorization.

Note: For more information about GRANT and REVOKE, see the CA Datacom/DB SQL User Guide.