SYSADM Sub authorities & Catalog Tables

Mohamed Esmael

SYSADM Sub authorities & Catalog Tables

Hello 

1- can i remove sub authorities from sysadm authority 

for example we know that SYSOPR is sub authority for SYSADM so can i revoke SYSOPR from SYSADM 

2-  if DB2 (Databases, Tables or subsystem ) are dropped what happens to Catalog tables  

3- can i backup catalog tables and restore it 

Jørn Thyssen

RE: SYSADM Sub authorities & Catalog Tables
(in response to Mohamed Esmael)

Hi Mohamed,

Re 1): Why would you want do that? If you have a user that needs SYSADM sans SYSOPR, why not grant the user the individual authorities needed?

 

Re 2): When you drop an object (database, table, tablespace, index, and so on) the corresponding entries are removed from the catalog tables. You cannot drop a subsystem. 

 Re 3): Yes, it is indeed recommended to backup the catalog and directory. You will either use your utility automation tool or installation job DSNTIJIC.Recovery requires special care: https://www.ibm.com/support/knowledgecenter/SSEPEK_12.0.0/ugref/src/tpc/db2z_recovercatalogdirectoryobjects.html

Best regards,

Jørn Thyssen

Rocket Software
77 Fourth Avenue • Waltham, MA • 02451 • USA
E: [login to unmask email] • W: www.rocketsoftware.com 

Views are personal. 

Mohamed Esmael

RE: SYSADM Sub authorities & Catalog Tables
(in response to Jørn Thyssen)

hi john 

Thanks for your reply 

1- i want to limit Sysadm authority 

Jørn Thyssen

RE: SYSADM Sub authorities & Catalog Tables
(in response to Mohamed Esmael)

Why? 

Why not limit the number of users with SYSADM access or audit them appropriately?

 

Best regards,

Jørn Thyssen

Rocket Software
77 Fourth Avenue • Waltham, MA • 02451 • USA
E: [login to unmask email] • W: www.rocketsoftware.com 

Views are personal. 

Mohamed Esmael

RE: SYSADM Sub authorities & Catalog Tables
(in response to Jørn Thyssen)

i need to be more secure  also i think about that 

Lizette Koehler

SYSADM Sub authorities & Catalog Tables
(in response to Mohamed Esmael)
If you could explain what is not secure in your environment?



SYSADM should only be set up for limited functions/groups/individuals. It is very powerful.



The people that have SYSADM have to be trusted to use it properly. If not, then put in auditing functions to make sure it is not being used incorrectly. Daily, Weekly reports to management to review could work for auditing



If you remove SYSADM – will you create a condition where you need it and no longer have access to it.



Lizette





From: Mohamed zaki [mailto:[login to unmask email]
Sent: Sunday, July 30, 2017 5:00 AM
To: [login to unmask email]
Subject: [DB2-L] - RE: SYSADM Sub authorities & Catalog Tables



i need to be more secure also i think about that



Lizette Koehler

SYSADM Sub authorities & Catalog Tables
(in response to Lizette Koehler)
There are changes to SYSADM in DB2 10



This presentation could be helpful. Paste the whole title into your favorite browser and you should find it.





DB2 10 for z/OS Security Overview





SECADM authority Separates security duties from SYSADM and SYSCTRL

Prevents SYSADM and SYSCTRL from granting or revoking privileges

Cannot access or change the data



What can SECADM do? GRANT Role privileges

CREATE, COMMENT, DROP ROLE

CREATE, ALTER, COMMENT, DROP TRUSTED CONTEXT

New DB2 10 Audit privileges SELECT, INSERT, UPDATE, DELETE on new SYSIBM.SYSAUDITPOLICIES table



New DB2 10 row and column access controls CREATE, ALTER, COMMENT, DROP row permissions and column masks

ALTER TABLE to activate row and column level access control

CREATE_SECURE_OBJECT privilege



SELECT, INSERT,UPDATE, DELETE on catalog tables





Hope this helps



Lizette





From: Lizette Koehler [mailto:[login to unmask email]
Sent: Sunday, July 30, 2017 7:48 AM
To: [login to unmask email]
Subject: [DB2-L] - RE: SYSADM Sub authorities & Catalog Tables



If you could explain what is not secure in your environment?



SYSADM should only be set up for limited functions/groups/individuals. It is very powerful.



The people that have SYSADM have to be trusted to use it properly. If not, then put in auditing functions to make sure it is not being used incorrectly. Daily, Weekly reports to management to review could work for auditing



If you remove SYSADM – will you create a condition where you need it and no longer have access to it.



Lizette





From: Mohamed zaki [mailto:[login to unmask email]
Sent: Sunday, July 30, 2017 5:00 AM
To: [login to unmask email] <mailto:[login to unmask email]>
Subject: [DB2-L] - RE: SYSADM Sub authorities & Catalog Tables



i need to be more secure also i think about that



Mohamed Esmael

RE: SYSADM Sub authorities & Catalog Tables
(in response to Lizette Koehler)

Lizette

First of all 

Thanks  for your kindly response and that presentation

I know that SYSADM authority should be within trusted people and to be limited and what i mean by secure is to be under control so i think about to use trusted context and roles to build  SYSADM Authority , also i think about audit policy to audit action taken by them  

Avram Friedman

RE: SYSADM Sub authorities & Catalog Tables
(in response to Mohamed Esmael)

I suggest giving SYSADM including install SYSADM to no one.
Have a security process, linked to change control to check out the SYSADM id, much like checking out an emergency ID.

In addition allowing even read access to catalog tables is a major security exposure.  It allows members of one department/agency/customer to find out sensitive information about another.
Table names allow the construction of inappropriate SQL
Knowledge for column orders and or names allow for retrieval of personal identification data.

i suggest catalog vies for dbadms

 

Avram Friedman
DB2-L hall of fame contributer
DB2-L acting administrator

[login to unmask email]

Mohamed Esmael

RE: SYSADM Sub authorities & Catalog Tables
(in response to Avram Friedman)

Thanks Avram 

 i think about to use trusted context and roles to build  SYSADM Authority , also i think about audit policy to audit action taken by them  is that good 

J&#248;rn Thyssen

RE: SYSADM Sub authorities & Catalog Tables
(in response to Mohamed Esmael)

I have seen that in use at some customers, but unfortunately I don't have any technical insight into their implementation

Best regards,

Jørn Thyssen

Rocket Software
77 Fourth Avenue • Waltham, MA • 02451 • USA
E: [login to unmask email] • W: www.rocketsoftware.com 

Views are personal.