SYSADM Authority Role

Mohamed Esmael

SYSADM Authority Role

Dear All 

i am trying to make role to manage SYSADM user as below according to https://www.ibm.com/support/knowledgecenter/en/SSEPEK_11.0.0/seca/src/tpc/db2z_migratesysadm.html  

Cutomized SYSADM to tstsod


Create Role ADMTESTSOD;

GRANT DBADM WITHOUT ACCESSCTRL WITH DATAACCESS ON SYSTEM to ADMTESTSOD;
GRANT SQLADM on SYSTEM to ROLE ADMTESTSOD;
GRANT EXplain on SYSTEM to ROLE ADMTESTSOD;
GRANT SYSOPR on SYSTEM to ROLE ADMTESTSOD;
GRANT ARCHIVE on SYSTEM to ROLE ADMTESTSOD;
GRANT BSDS on SYSTEM to ROLE ADMTESTSOD;
GRANT ARCHIVE on SYSTEM to ROLE ADMTESTSOD;
GRANT CREATESG on SYSTEM to ROLE ADMTESTSOD;
GRANT ARCHIVE on SYSTEM to ROLE ADMTESTSOD;
GRANT STOSPACE on SYSTEM to ROLE ADMTESTSOD;


CREATE TRUSTED CONTEXT TESTSOD_ADM
BASED UPON CONNECTION USING SYSTEM AUTHID TESTSOD
DEFAULT ROLE ADMTESTSOD WITHOUT ROLE AS OBJECT OWNER
ATTRIBUTES (JOBNAME 'TESTSOD')
Enable;

 

So after i run it , it success and then try by user TESTSOD to create DB it gives me error  -552 

 

 

Jørn Thyssen

RE: SYSADM Authority Role
(in response to Mohamed Esmael)

Hi Mohamed, 

You are missing a TO ROLE in your first GRANT:

 

GRANT DBADM WITHOUT ACCESSCTRL WITH DATAACCESS ON SYSTEM to ROLE ADMTESTSOD;

 

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 Authority Role
(in response to Jørn Thyssen)

Hello Jorn

I do that and still the error appears to me   SQLcode = -552

Jørn Thyssen

RE: SYSADM Authority Role
(in response to Mohamed Esmael)

Hi Mohamed,

Can you post the DDL statement you are trying to execute as well as the full sqlcode/sql error message text, please?

 

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 Authority Role
(in response to Jørn Thyssen)

The SQL Statement :- Create Database DSNT0125 

The Result:- 

DB2 SQL Error: SQLCODE=-552, SQLSTATE=42502, SQLERRMC=TESTSOD;CREATE DATABASE, DRIVER=4.17.30

Jørn Thyssen

RE: SYSADM Authority Role
(in response to Mohamed Esmael)

Hi Mohamed,

Your trusted context is not being picked up: the error message contains "TESTSOD" which is your authorization ID.

How are you connecting to Db2?

 

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 Authority Role
(in response to Jørn Thyssen)

Using Data studio 

Jørn Thyssen

RE: SYSADM Authority Role
(in response to Mohamed Esmael)

If you are using Data Studio the ATTRIBUTE in the TRUSTED CONTEXT is incorrect.

The attribute JOBNAME refers to a job or task running under z/OS, e.g., a batch job running DSNTIAD or DSNTEP2.

For remote connections you need to set different attributes such as IP address, workstation name, etc.

 

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 Authority Role
(in response to Jørn Thyssen)

if i use spufi , the attribute of jobname will be correct to refer to userID ? 

Jørn Thyssen

RE: SYSADM Authority Role
(in response to Mohamed Esmael)

Correct, if you run SPUFI ISPF under TSO then ATTRIBUTE JOBNAME 'userid' will work. You may have to logoff/logon after ALTER'ing the TRUSTED CONTEXT.

 

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 Authority Role
(in response to Jørn Thyssen)

When i use spufi the below error appears 

DSNT408I SQLCODE = -552, ERROR: TESTSOD DOES NOT HAVE THE PRIVILEGE TO PERFORM OPERATION CREATE DATABASE
DSNT418I SQLSTATE = 42502 SQLSTATE RETURN CODE
DSNT415I SQLERRP = DSNXODD2 SQL PROCEDURE DETECTING ERROR
DSNT416I SQLERRD = 40 0 0 -1 0 0 SQL DIAGNOSTIC INFORMATION
DSNT416I SQLERRD = X'00000028' X'00000000' X'00000000' X'FFFFFFFF'
X'00000000' X'00000000' SQL DIAGNOSTIC INFORMATION

Jørn Thyssen

RE: SYSADM Authority Role
(in response to Mohamed Esmael)

Hi Mohamed,

Try adding a WITH USE clause to the CREATE TRUSTED CONTEXT:

 

CREATE TRUSTED CONTEXT TESTSOD_ADM
BASED UPON CONNECTION USING SYSTEM AUTHID TESTSOD
DEFAULT ROLE ADMTESTSOD WITHOUT ROLE AS OBJECT OWNER
ATTRIBUTES (JOBNAME 'TESTSOD')
Enable
WITH USE FOR TESTSOD ROLE ADMTESTSOD WITHOUT AUTHENTICATION;

 

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 Authority Role
(in response to Jørn Thyssen)

Thanks alot jorn for replying and sorry for being late to you as i was on vacation and i test that and working fine with me but i change DEFAULT ROLE ADMTESTSOD WITHOUT ROLE AS OBJECT OWNER to  DEFAULT ROLE ADMTESTSOD WITH ROLE AS OBJECT OWNER and qualifier 

and also the the user can grant or revoke objects he created