Catalog tables

Mohamed Esmael

Catalog tables

Dear all 

what the meaning about privileges on updatable catalog tables

as when i read the administrative authorities and privileges , i read that privileges under many administrative authorities 

Also who can delete catalog tables 

Another thing if i drop database so every privileges on that DB will dropped also and delete from catalog tables ??

Note:- My little knowledge on that catalog table will be updated for example when i grant privileges to Authorization id , so i t will insert row on sysibm.sysusersauth   

Roy Boxwell

Catalog tables
(in response to Mohamed Esmael)
No-one can delete the catalog tables (Unless you have flipped the catalog update bit)
No-one can delete from the catalog/directory tables (unless you have flipped the catalog update bit)
If you drop a DB *everything* apart from ALIAS’s is automatically dropped for you
If you GRANT any AUTH to any user it will get put into one of the relevant AUTH tables

Roy Boxwell

SOFTWARE ENGINEERING GMBH and SEGUS Inc.
-Product Development-

Heinrichstrasse 83-85
40239 Duesseldorf/Germany
Tel. +49 (0)211 96149-675
Fax +49 (0)211 96149-32
Email: [login to unmask email]<mailto:[login to unmask email]>
http://www.seg.de http://www.seg.de

Software Engineering GmbH
Amtsgericht Düsseldorf, HRB 37894
Geschäftsführung: Gerhard Schubert, Bettina Schubert

From: Mohamed zaki [mailto:[login to unmask email]
Sent: Tuesday, July 25, 2017 11:08 AM
To: [login to unmask email]
Subject: [DB2-L] - Catalog tables


Dear all

what the meaning about privileges on updatable catalog tables

as when i read the administrative authorities and privileges , i read that privileges under many administrative authorities

Also who can delete catalog tables

Another thing if i drop database so every privileges on that DB will dropped also and delete from catalog tables ??

Note:- My little knowledge on that catalog table will be updated for example when i grant privileges to Authorization id , so i t will insert row on sysibm.sysusersauth

-----End Original Message-----

Steen Rasmussen

Catalog tables
(in response to Mohamed Esmael)
You need some kind of authorization to select from the SYSIBM tables – either implicit via ag. SYSADM or explicitly via someone granting SELECT.
You cant DROP catalog tables – from some tables you can INSERT/UPDATE/DELETE rows – but schema management is typically done via CATMAINT provided by IBM.

You are correct that GRANT, BIND, CREATE user defined objects will result in DB2 doing “INSERT, UPDATE, DELETE” from the respective catalog tables – like your example a GRANT will result in INSERT into one or more SYSIBM tables.

Steen Rasmussen

From: Mohamed zaki [mailto:[login to unmask email]
Sent: Tuesday, July 25, 2017 4:08 AM
To: [login to unmask email]
Subject: [DB2-L] - Catalog tables

CAUTION: This email originated from outside of CA. Do not click links or open attachments unless you recognize the sender and know the content is safe.

Dear all

what the meaning about privileges on updatable catalog tables

as when i read the administrative authorities and privileges , i read that privileges under many administrative authorities

Also who can delete catalog tables

Another thing if i drop database so every privileges on that DB will dropped also and delete from catalog tables ??

Note:- My little knowledge on that catalog table will be updated for example when i grant privileges to Authorization id , so i t will insert row on sysibm.sysusersauth

-----End Original Message-----

Mohamed Esmael

RE: Catalog tables
(in response to Roy Boxwell)

Thanks alot for your reply 

how can I flipped the catalog update bit?

Steen Rasmussen

Catalog tables
(in response to Mohamed Esmael)
You really don’t want to flip that bit since you might end up corrupting the entire DB2 syste,

Steen

From: Mohamed zaki [mailto:[login to unmask email]
Sent: Tuesday, July 25, 2017 8:59 AM
To: [login to unmask email]
Subject: [DB2-L] - RE: Catalog tables

CAUTION: This email originated from outside of CA. Do not click links or open attachments unless you recognize the sender and know the content is safe.

Thanks alot for your reply

how can I flipped the catalog update bit?

-----End Original Message-----

Mohamed Esmael

RE: Catalog tables
(in response to Steen Rasmussen)

i  know i will not need that

i just want to know for my information 

Chris Tee

Catalog tables
(in response to Mohamed Esmael)
Mohamed


Given your relative inexperience with DB2, it is not something I would recommend you do.


regards


Chris


________________________________
From: Mohamed zaki <[login to unmask email]>
Sent: 25 July 2017 14:59
To: [login to unmask email]
Subject: [DB2-L] - RE: Catalog tables


Thanks alot for your reply

how can I flipped the catalog update bit?

-----End Original Message-----

Raymond Bell

Catalog tables
(in response to Mohamed Esmael)
You could look it up on t’interweb. It’s next to the entry on how to make a thermonuclear device from tissue paper and bits of chewing gum… ;o)

Raymond Bell
DB2 Database Administrator | IT Operations | Technology | RBS

From: Mohamed zaki [mailto:[login to unmask email]
Sent: 25 July 2017 15:02
To: [login to unmask email]
Subject: [DB2-L] - RE: Catalog tables


*********************************************
" This message originates from outside our organisation. Consider carefully whether you should click on any links, open any attachments or reply. If in doubt, forward to ~ Phishing"
*********************************************


i know i will not need that

i just want to know for my information

-----End Original Message-----
The Royal Bank of Scotland plc. Registered in Scotland No 90312. Registered Office: 36 St Andrew Square, Edinburgh EH2 2YB. The Royal Bank of Scotland is authorised by the Prudential Regulation Authority, and regulated by the Financial Conduct Authority and Prudential Regulation Authority. The Royal Bank of Scotland N.V. is authorised and regulated by the De Nederlandsche Bank and has its seat at Amsterdam, the Netherlands, and is registered in the Commercial Register under number 33002587. Registered Office: Gustav Mahlerlaan 350, Amsterdam, The Netherlands. The Royal Bank of Scotland N.V. and The Royal Bank of Scotland plc are authorised to act as agent for each other in certain jurisdictions.

National Westminster Bank Plc. Registered in England No. 929027. Registered Office: 135 Bishopsgate, London EC2M 3UR. National Westminster Bank Plc is authorised by the Prudential Regulation Authority, and regulated by the Financial Conduct Authority and the Prudential Regulation Authority.

The Royal Bank of Scotland plc and National Westminster Bank Plc are authorised to act as agent for each other.

This e-mail message is confidential and for use by the addressee only. If the message is received by anyone other than the addressee, please return the message to the sender by replying to it and then delete the message from your computer. Internet e-mails are not necessarily secure. The Royal Bank of Scotland plc, The Royal Bank of Scotland N.V., National Westminster Bank Plc or any affiliated entity (“RBS” or “us”) does not accept responsibility for changes made to this message after it was sent. RBS may monitor e-mails for business and operational purposes. By replying to this message you give your consent to the monitoring of your e-mail communications with us.

Whilst all reasonable care has been taken to avoid the transmission of viruses, it is the responsibility of the recipient to ensure that the onward transmission, opening or use of this message and any attachments will not adversely affect its systems or data. No responsibility is accepted by RBS in this regard and the recipient should carry out such virus and other checks as it considers appropriate.

Visit our website at www.rbs.com http://www.rbs.com

Roy Boxwell

Catalog tables
(in response to Mohamed Esmael)
Cross memory APF authorised assembler.
Extremely dangerous to do...

Roy Boxwell
SOFTWARE ENGINEERING GmbH and SEGUS Inc.
-Product Development-
Heinrichstrasse 83-85
40239 Düsseldorf/Germany
Tel. +49 (0)211 96149-675
Fax +49 (0)211 96149-32
Email: [login to unmask email]<mailto:[login to unmask email]>
http://www.seg.de

Software Engineering GmbH
Amtsgericht Düsseldorf, HRB 37894
Geschäftsführung: Gerhard Schubert, Bettina Schubert

On 25 Jul 2017, at 16:01, Mohamed zaki <[login to unmask email]<mailto:[login to unmask email]>> wrote:


i know i will not need that

i just want to know for my information

-----End Original Message-----

Peter Vanroose

RE: Catalog tables
(in response to Mohamed Esmael)

Mohamed,

If I understand your question correctly:

what the meaning about privileges on updatable catalog tables

Well... most catalog tables have a few columns which are marked with "This column can
be updated" in the SQL Reference Guide (SC19-4066-08), in the Appendix "DB2 Catalog Tables".
Indicating that (1) these columns contain statistics on the particular object, (2) are filled by RUNSTATS, (3) some of them are (or were) used by the DB2 optimizer, and most importantly: (4) they are manually updatable.
Moreover, (5) some catalog tables consist of only "Use S" columns.
So your question refers to (4) and (5).

Given sufficient privileges, those catalog columns can be filled with the SQL UPDATE statement or (for (5)) even manipulated with INSERT and/or DELETE.

An example of table type (5) is SYSIBM.SYSTABSTATS. Examples of updatable columns in the catalog are CARDF, NPAGESF and PCTPAGES in SYSIBM.SYSTABLES.

As to your question: privileges to issue UPDATE (or INSERT or DELETE) statements against those columns or tables in the catalog follow exactly the same rules as privileges (and authorization checks) for "normal" tables:
either an explicit UPDATE authority for one table (or even one column) to either a user ID, a group ID, a role, or even to PUBLIC. Or alternatively, an UPDATE authority on a view on one of the catalog tables.

--      Peter Vanroose
        ABIS Training & Consulting,
        Leuven, Belgium.
        http://www.abis.be/

Mohamed Esmael

RE: Catalog tables
(in response to Peter Vanroose)

Thanks a lot peter for your kindly reply 

I got the idea about updatable catalog table that can be updated either automatic for example when i create table or through run RUNSTATS utility or manually like sysibm.sysauditpolicy

I have another question 

1-  can i know the name of column that indicates that i can update on it 

2- can i make backup for catalog tables and restore on system for maintenance issue  (i know i must do that on migration)   

 

Thanks in advance 

Steen Rasmussen

Catalog tables
(in response to Mohamed Esmael)
The IBM Db2 SQL Reference Guide and particularly the Catalog Tables section is the place to go. Columns which can be updated has a note at the end of the column description : This is an updatable column

Steen Rasmussen

From: Mohamed Esmael [mailto:[login to unmask email]
Sent: Monday, August 07, 2017 6:14 AM
To: [login to unmask email]
Subject: [DB2-L] - RE: Catalog tables

CAUTION: This email originated from outside of CA. Do not click links or open attachments unless you recognize the sender and know the content is safe.

Thanks a lot peter for your kindly reply

I got the idea about updatable catalog table that can be updated either automatic for example when i create table or through run RUNSTATS utility or manually like sysibm.sysauditpolicy

I have another question

1- can i know the name of column that indicates that i can update on it

2- can i make backup for catalog tables and restore on system for maintenance issue (i know i must do that on migration)



Thanks in advance

-----End Original Message-----

Steen Rasmussen

Catalog tables
(in response to Mohamed Esmael)
Restoring the catalog tables is pretty much a DR event since the catalog has to be in sync with the objects and the DSNDB01 objects, log etc. You need to read the Disaster recovery redbook.

Steen

From: Mohamed Esmael [mailto:[login to unmask email]
Sent: Monday, August 07, 2017 6:14 AM
To: [login to unmask email]
Subject: [DB2-L] - RE: Catalog tables

CAUTION: This email originated from outside of CA. Do not click links or open attachments unless you recognize the sender and know the content is safe.

Thanks a lot peter for your kindly reply

I got the idea about updatable catalog table that can be updated either automatic for example when i create table or through run RUNSTATS utility or manually like sysibm.sysauditpolicy

I have another question

1- can i know the name of column that indicates that i can update on it

2- can i make backup for catalog tables and restore on system for maintenance issue (i know i must do that on migration)



Thanks in advance

-----End Original Message-----

Mohamed Esmael

RE: Catalog tables
(in response to Steen Rasmussen)

Thanks for co-operation