Db2

Db2

Where DBAs and data experts come together to stop operating and start innovating. Connect, share, and shape the AI era with us.


#Data


#Data
#Databases
#Operatingsystems
#Db2
#Databasesolutions
 View Only

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

    Posted 01/16/25 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 01/16/25 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
    ------------------------------



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

    Posted 01/16/25 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 01/16/25 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 01/17/25 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 01/17/25 11:11 AM
    Edited by Jan Nelken 01/17/25 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 01/18/25 04:25 AM
    Edited by Jan Nelken 01/18/25 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 01/19/25 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
    ------------------------------



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

    Posted 01/19/25 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
    ------------------------------



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

    Posted 01/19/25 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


Related Content