Securing Data Access for SQL Use › Creating Personal Tables

Creating Personal Tables

The user can use the STORE command to create tables using the data retrieved by a query for his own use. The STORE command creates a table in the user's personal authorization ID and populates the table with the results of the current query. The user can have the data in his own personal tables and then update the tables once a week or whenever convenient.

The Personal Database Facility adds the necessary authorizations required by DQL Language, automatically to personal tables, except when external security is in use. A user who is allowed to use both DQL Language and SQL can access his personal tables from either query mode. A user who is authorized to use SQL only can access another user's personal tables if access privileges have been granted by the owner. (See the CA Datacom/DB SQL User Guide for information on the GRANT and REVOKE commands for accessing tables.) Allow users access to personal tables that they require to perform their job functions.

Here are some considerations for the use of PDB (personal data base) and the STORE command. With these functions, tables are created (by using SQL) into an area specified in the user's profile (this profile information is still used even when external security is in effect for CA Dataquery). The area must be in a database for which the user has both "create" authority and CA Dataquery authority to do maintenance. The area and database should be built separately for each user (or group of users) that are allowed PDB authority, so that these tables are not put into any arbitrary database. See the CA Datacom/DB Database and System Administration Guide for information about how to set up a database for SQL use.

You can select portions of tables for a user to access, then use the CA Dataquery Editor to create a view of a portion of a table. The use of views allows users access to the portion of data that they require to perform their job and protects the rest of the data from unnecessary access. See the CA Datacom/DB SQL User Guide for more information on the use of views.