Db2

Db2

Connect with Db2, Informix, Netezza, open source, and other data experts to gain value from your data, share insights, and solve problems.

 View Only
Back to discussions
Expand all | Collapse all

DB2 for ZOS - Issue using DB2 Roles and GRANT ROLE statement

  • 1.  DB2 for ZOS - Issue using DB2 Roles and GRANT ROLE statement

    Posted Thu January 16, 2025 11:51 AM

    We are using DB2 V12

    Attempting to Create a Role and assign the Role to a user using SQL statements.

    Following the IBM documentation.

    This specific SQL statement:  GRANT ROLE "80MI4400PG" TO USER PROTTA  always fails.  Errors are similar to:

    I always get:       SQLCODE : -104                           DSNTIAR CODE :  0                   .
                                                                                      .
      DSNT408I SQLCODE = -104, ERROR:  ILLEGAL SYMBOL ""80MI4400PG"". SOME SYMBOLS    .
               THAT MIGHT BE LEGAL ARE: TO WITH WITHOUT IN ,                          .
      DSNT418I SQLSTATE   = 42601 SQLSTATE RETURN CODE                                .
      DSNT415I SQLERRP    = DSNHPARS SQL PROCEDURE DETECTING ERROR                    .
      DSNT416I SQLERRD    = 3  0  0  -1  12  502 SQL DIAGNOSTIC INFORMATION           .
      DSNT416I SQLERRD    = X'00000003'  X'00000000'  X'00000000'  X'FFFFFFFF'        .
               X'0000000C'  X'000001F6' SQL DIAGNOSTIC INFORMATION                 

    I have SYSADM rights.  I've tried about a dozen variations of GRANT ROLE ....

    Cannot get past the issue and don't know what next steps should be.  I have opened case with IBM support as well....

    Does anyone have guidance to solve this issue?



    ------------------------------
    David Perrin
    ------------------------------


  • 2.  RE: DB2 for ZOS - Issue using DB2 Roles and GRANT ROLE statement

    Posted Thu January 16, 2025 04:32 PM

    Hi David,

    I am not a z/OS person, but SQLCODE -104 is indicating invalid symbol;

    - can you verify that Db2 z/OS allows an identifier starting with digit? Shouldn't it be following the rules of an SQL identifier?
    - can you verify that role you are going ti assign to an user has been created and exists?

    For Db2 V11.5 for LUW this is how I would create and assign role to an user:



    ------------------------------
    Jan Nelken
    ------------------------------

    Original Message


  • 3.  RE: DB2 for ZOS - Issue using DB2 Roles and GRANT ROLE statement

    Posted Thu January 16, 2025 05:10 PM

    We heard back from IBM support that Assigning Users to Roles   isn't supported under DB2 for ZOS .

    I see that your example did work.  We've tried Role names without numbers and same results.

    Are you using DB2 v12 under ZOS operating system?



    ------------------------------
    David Perrin
    ------------------------------

    Original Message


  • 4.  RE: DB2 for ZOS - Issue using DB2 Roles and GRANT ROLE statement

    Posted Thu January 16, 2025 05:34 PM

    Ok - as I stated before - I am not a z/OS person.

    Long time ago I was told that Db2 is a Db2 is a Db2.  Since then I was treating this rather as a warning than as a compatibility statement :-)

    Sorry if I confused you - I was using Db2 for LUW (Linux, Unix and Windows) _ I have no clue about Db2 flavours which belong to Smithsonian Institute ...



    ------------------------------
    Jan Nelken
    ------------------------------

    Original Message


  • 5.  RE: DB2 for ZOS - Issue using DB2 Roles and GRANT ROLE statement

    Posted Fri January 17, 2025 09:48 AM

    Thanks for you feedback!

     

    ------------- Next Scheduled PTO -------------

    TBD

     

    Mobile:  231-245-6535

     




    Original Message


  • 6.  RE: DB2 for ZOS - Issue using DB2 Roles and GRANT ROLE statement

    Posted Fri January 17, 2025 11:11 AM
    Edited by Jan Nelken Fri January 17, 2025 11:12 AM

    Perhaps you can check whether assigning role to a group will work for Db2 z/OS; if it works then you could create an unique group for each user and thus go around this limitation ...



    ------------------------------
    Jan Nelken
    ------------------------------

    Original Message


  • 7.  RE: DB2 for ZOS - Issue using DB2 Roles and GRANT ROLE statement

    Posted Sat January 18, 2025 04:25 AM
    Edited by Jan Nelken Sat January 18, 2025 04:29 AM

    Sorry - stupid idea.

    Look into Db2 z/OS documentation:

    https://www.ibm.com/docs/en/db2-for-zos/12?topic=roles-in-trusted-context

    specially read this:
    A role is available only in a trusted context.
    ...

    I also found this:
    A role is a database object that is created in Db2. It is defined through the SQL CREATE ROLE statement and a trusted connection. A role cannot be used outside of a trusted context unless the user in a role grants privileges to an ID. 



    ------------------------------
    Jan Nelken
    ------------------------------

    Original Message


  • 8.  RE: DB2 for ZOS - Issue using DB2 Roles and GRANT ROLE statement

    Posted Sun January 19, 2025 05:30 AM

    Hello David,

    Please try removing the quotation marks.

    They are implicit so I think that is the reason for the error message showing 'ILLEGAL SYMBOL ""80MI4400PG"".', with double quotati9n marks.

     I mean this:

    GRANT ROLE 80MI4400PG TO USER PROTTA

    Besides this, I have the same doubt that Jan about roles starting by a digit instead of a character.

    Thanks.



    ------------------------------
    Soledad Martinez
    Volkswagen Group Services
    Salinas - Asturias
    ------------------------------

    Original Message


  • 9.  RE: DB2 for ZOS - Issue using DB2 Roles and GRANT ROLE statement

    Posted Sun January 19, 2025 11:14 PM

    Roles like other database objects have naming conventions. most state that a object must start with a Alpha..  A-Z, they can't start with a number.



    ------------------------------
    Douglas Partch
    ------------------------------

    Original Message


  • 10.  RE: DB2 for ZOS - Issue using DB2 Roles and GRANT ROLE statement

    Posted Sun January 19, 2025 11:43 PM

    I forgot to mention that on Db2 Zoz you use Trust Context to assign user that can access a role.  This must different then Db2 LUW.

    here is an example of each statement needed.

    Create role CTXROLE;

    grant select on myschema.mytable to Role CTXROLE;

    CREATE TRUSTED CONTEXT CTX1 

    BASED UPON CONNECTION USING SYSTEM AUTHID ADMF001

    ATTRIBUTES (ADDRESS '9.30.131.203', ENCRYPTION 'LOW')

    DEFAULT ROLE CTXROLE

    ENABLE 

    WITH USE FOR SAM, JOE

    ROLE ROLE1 WITH AUTHENTICATION;



    ------------------------------
    Douglas Partch
    ------------------------------

    Original Message