MS Access and Query Analyzer access DB2 database running on OS/39 0

Grace Chen

MS Access and Query Analyzer access DB2 database running on OS/39 0
Hi List,
Our user want to use MS Access and Query Analyzer to access databases in DB2
running on OS/390.
They all have read access on their tables. However, they also need read
access on SYSTEM CATALOG TABLES like:
SYSCOLUMNS, SYSTABLES, SYSINDEXES, SYSKEYS and SYSSYNONYMS
Is there any risk to allow end user to read access system catalog tables?
Do I need create a view on the system catalog tables for them to use?
Do they need to access all SYSTEM CATALOG TABLES or only some of them?

Grace Chen
<mailto:[login to unmask email]>




**************************************************************************************************
The contents of this email and any attachments are confidential.
It is intended for the named recipient(s) only.
If you have received this email in error please notify the system manager or the
sender immediately and do not disclose the contents to any one or make copies.

** eSafe scanned this email for viruses, vandals and malicious content **
**************************************************************************************************

Grace Chen

MS Access and Query Analyzer access DB2 database running on OS/39 0
(in response to Eric Pearson)
Hi List,
Our user want to use MS Access and Query Analyzer to access databases in DB2
running on OS/390.
They all have read access on their tables. However, they also need read
access on SYSTEM CATALOG TABLES like:
SYSCOLUMNS, SYSTABLES, SYSINDEXES, SYSKEYS and SYSSYNONYMS
Is there any risk to allow end user to read access system catalog tables?
Do I need create a view on the system catalog tables for them to use?
Do they need to access all SYSTEM CATALOG TABLES or only some of them?

Grace Chen
<mailto:[login to unmask email]>




**************************************************************************************************
The contents of this email and any attachments are confidential.
It is intended for the named recipient(s) only.
If you have received this email in error please notify the system manager or the
sender immediately and do not disclose the contents to any one or make copies.

** eSafe scanned this email for viruses, vandals and malicious content **
**************************************************************************************************

Eric Pearson

Re: MS Access and Query Analyzer access DB2 database running on O S/39 0
(in response to Grace Chen)
Grace,
A concern would be interference with DDL.
If the users have long running queries and what they
run does not have COMMITs, they could cause the DBA staff
to get SQLCODE -911 trying to do DDL.


Regards,
Eric Pearson


-----Original Message-----
From: Grace Chen [mailto:[login to unmask email]
Sent: Thursday, January 16, 2003 12:06 PM
To: [login to unmask email]
Subject: MS Access and Query Analyzer access DB2 database running on OS/39 0


Hi List,
Our user want to use MS Access and Query Analyzer to access databases in DB2
running on OS/390.
They all have read access on their tables. However, they also need read
access on SYSTEM CATALOG TABLES like:
SYSCOLUMNS, SYSTABLES, SYSINDEXES, SYSKEYS and SYSSYNONYMS
Is there any risk to allow end user to read access system catalog tables?
Do I need create a view on the system catalog tables for them to use?
Do they need to access all SYSTEM CATALOG TABLES or only some of them?

Grace Chen
<mailto:[login to unmask email]>




****************************************************************************
**********************

The contents of this email and any attachments are confidential.

It is intended for the named recipient(s) only.

If you have received this email in error please notify the system manager or
the

sender immediately and do not disclose the contents to any one or make
copies.



** eSafe scanned this email for viruses, vandals and malicious content **

****************************************************************************
**********************



Eric Pearson

Re: MS Access and Query Analyzer access DB2 database running on O S/39 0
(in response to Sibimon Philip)
Grace,
A concern would be interference with DDL.
If the users have long running queries and what they
run does not have COMMITs, they could cause the DBA staff
to get SQLCODE -911 trying to do DDL.


Regards,
Eric Pearson


-----Original Message-----
From: Grace Chen [mailto:[login to unmask email]
Sent: Thursday, January 16, 2003 12:06 PM
To: [login to unmask email]
Subject: MS Access and Query Analyzer access DB2 database running on OS/39 0


Hi List,
Our user want to use MS Access and Query Analyzer to access databases in DB2
running on OS/390.
They all have read access on their tables. However, they also need read
access on SYSTEM CATALOG TABLES like:
SYSCOLUMNS, SYSTABLES, SYSINDEXES, SYSKEYS and SYSSYNONYMS
Is there any risk to allow end user to read access system catalog tables?
Do I need create a view on the system catalog tables for them to use?
Do they need to access all SYSTEM CATALOG TABLES or only some of them?

Grace Chen
<mailto:[login to unmask email]>




****************************************************************************
**********************

The contents of this email and any attachments are confidential.

It is intended for the named recipient(s) only.

If you have received this email in error please notify the system manager or
the

sender immediately and do not disclose the contents to any one or make
copies.



** eSafe scanned this email for viruses, vandals and malicious content **

****************************************************************************
**********************



Leon Katsnelson

Re: MS Access and Query Analyzer access DB2 database running on OS/39 0
(in response to Eric Pearson)
If you don't allow access to the system catalog MS Access will be pretty
much useless.

Leon Katsnelson
Manager, DB2 Product Planning
Administration Assistant: Robin Stockfish (905) 413-3632


|---------+---------------------------->
| | Grace Chen |
| | <[login to unmask email]|
| | O.CA> |
| | Sent by: DB2 Data|
| | Base Discussion |
| | List |
| | <[login to unmask email]|
| | LASSOC.COM> |
| | |
| | |
| | 01/16/2003 06:05 |
| | PM |
| | Please respond to|
| | DB2 Data Base |
| | Discussion List |
| | |
|---------+---------------------------->
>--------------------------------------------------------------------------------------------------------------------------------------------------|
| |
| To: [login to unmask email] |
| cc: |
| Subject: MS Access and Query Analyzer access DB2 database running on OS/39 0 |
| |
| |
>--------------------------------------------------------------------------------------------------------------------------------------------------|



Hi List,
Our user want to use MS Access and Query Analyzer to access databases in
DB2 running on OS/390.
They all have read access on their tables. However, they also need read
access on SYSTEM CATALOG TABLES like:
SYSCOLUMNS, SYSTABLES, SYSINDEXES, SYSKEYS and SYSSYNONYMS
Is there any risk to allow end user to read access system catalog tables?
Do I need create a view on the system catalog tables for them to use?
Do they need to access all SYSTEM CATALOG TABLES or only some of them?

Grace Chen




**************************************************************************************************

The contents of this email and any attachments are confidential.
It is intended for the named recipient(s) only.
If you have received this email in error please notify the system manager
or the
sender immediately and do not disclose the contents to any one or make
copies.

** eSafe scanned this email for viruses, vandals and malicious content **
**************************************************************************************************



Sibimon Philip

Re: MS Access and Query Analyzer access DB2 database running on O S/39 0
(in response to Leon Katsnelson)
How Query analyzer can be used to run the DB2 OS/390 query? Can it use a
ODBC DSN? We are using remote server to do this. Not with IBM DB2 connect.

Thanks..sibi

-----Original Message-----
From: Pearson, Eric L, [mailto:[login to unmask email]
Sent: Thursday, January 16, 2003 11:27 AM
To: [login to unmask email]
Subject: Re: MS Access and Query Analyzer access DB2 database running on O
S/39 0


Grace,
A concern would be interference with DDL.
If the users have long running queries and what they
run does not have COMMITs, they could cause the DBA staff
to get SQLCODE -911 trying to do DDL.


Regards,
Eric Pearson


-----Original Message-----
From: Grace Chen [mailto:[login to unmask email]
Sent: Thursday, January 16, 2003 12:06 PM
To: [login to unmask email]
Subject: MS Access and Query Analyzer access DB2 database running on OS/39 0


Hi List,
Our user want to use MS Access and Query Analyzer to access databases in DB2
running on OS/390.
They all have read access on their tables. However, they also need read
access on SYSTEM CATALOG TABLES like:
SYSCOLUMNS, SYSTABLES, SYSINDEXES, SYSKEYS and SYSSYNONYMS
Is there any risk to allow end user to read access system catalog tables?
Do I need create a view on the system catalog tables for them to use?
Do they need to access all SYSTEM CATALOG TABLES or only some of them?

Grace Chen
<mailto:[login to unmask email]>


****************************************************************************
**********************

The contents of this email and any attachments are confidential.

It is intended for the named recipient(s) only.

If you have received this email in error please notify the system manager or
the

sender immediately and do not disclose the contents to any one or make
copies.



** eSafe scanned this email for viruses, vandals and malicious content **

****************************************************************************
**********************



Michael Ebert

Re: MS Access and Query Analyzer access DB2 database running on O S/39 0
(in response to Grace Chen)
Hi Grace,

in a high security environment you might want to restrict access to some
columns that allow the user to see the contents of user tables (high2key,
low2key, and the column distribution values come to mind). Apart from
that, I see no reason to keep users from reading the Catalog tables. If
you can teach them to e.g. select the proper CARDF column instead of
running SELECT COUNT(*) on giant tables, it will even help your system.
Also I've never ever seen DDL problems resulting from catalog table
queries. These always occur because users have long-running queries (or
unattended displays) that lock the DBD. To prevent that, you'd have to
disallow tools that use dynamic SQL altogether which is not practical.

Dr. Michael Ebert
DB2 Database Administrator
aMaDEUS Data Processing
Erding / Munich, Germany




Grace,
A concern would be interference with DDL.
If the users have long running queries and what they
run does not have COMMITs, they could cause the DBA staff
to get SQLCODE -911 trying to do DDL.

Regards,
Eric Pearson
-----Original Message-----
From: Grace Chen [mailto:[login to unmask email]
Sent: Thursday, January 16, 2003 12:06 PM
To: [login to unmask email]
Subject: MS Access and Query Analyzer access DB2 database running on OS/39 0

Hi List,
Our user want to use MS Access and Query Analyzer to access databases in
DB2 running on OS/390.
They all have read access on their tables. However, they also need read
access on SYSTEM CATALOG TABLES like:
SYSCOLUMNS, SYSTABLES, SYSINDEXES, SYSKEYS and SYSSYNONYMS
Is there any risk to allow end user to read access system catalog tables?
Do I need create a view on the system catalog tables for them to use?
Do they need to access all SYSTEM CATALOG TABLES or only some of them?

Grace Chen

David S Waugh

Re: MS Access and Query Analyzer access DB2 database running on O S/39 0
(in response to Michael Ebert)
I HAVE seen users and tools cause problems interfering with DDL -- used to happen with PeopleSoft from time to time (I think they've cleaned up their act somewhat since then though).

One solution would be to create & maintain "Shadow" copies of the DB2 Catalog tables the tools would be going against, then point the tools to the Shadow tables instead of the real thing. Never tried it with MS Access or Query Analyzer though, so I don't know if you can make them point to a Shadow Catalog table or not.

HTH

David S. Waugh, NCW
DSW Consulting & Services
Former DB2 Sysprog, now useless & clueless Applications DBA


---------- "Dr. Michael Ebert" <[login to unmask email]> writes:

Hi Grace,

in a high security environment you might want to restrict access to some
columns that allow the user to see the contents of user tables (high2key,
low2key, and the column distribution values come to mind). Apart from
that, I see no reason to keep users from reading the Catalog tables. If
you can teach them to e.g. select the proper CARDF column instead of
running SELECT COUNT(*) on giant tables, it will even help your system.
Also I've never ever seen DDL problems resulting from catalog table
queries. These always occur because users have long-running queries (or
unattended displays) that lock the DBD. To prevent that, you'd have to
disallow tools that use dynamic SQL altogether which is not practical.

Dr. Michael Ebert
DB2 Database Administrator
aMaDEUS Data Processing
Erding / Munich, Germany




Grace,
A concern would be interference with DDL.
If the users have long running queries and what they
run does not have COMMITs, they could cause the DBA staff
to get SQLCODE -911 trying to do DDL.

Regards,
Eric Pearson
-----Original Message-----
From: Grace Chen [mailto:[login to unmask email]
Sent: Thursday, January 16, 2003 12:06 PM
To: [login to unmask email]
Subject: MS Access and Query Analyzer access DB2 database running on OS/39 0

Hi List,
Our user want to use MS Access and Query Analyzer to access databases in
DB2 running on OS/390.
They all have read access on their tables. However, they also need read
access on SYSTEM CATALOG TABLES like:
SYSCOLUMNS, SYSTABLES, SYSINDEXES, SYSKEYS and SYSSYNONYMS
Is there any risk to allow end user to read access system catalog tables?
Do I need create a view on the system catalog tables for them to use?
Do they need to access all SYSTEM CATALOG TABLES or only some of them?

Grace Chen