Topic
  • 3 replies
  • Latest Post - ‏2013-01-02T17:27:57Z by cabrerachaparro
cabrerachaparro
cabrerachaparro
2 Posts

Pinned topic SQL Authorization error

‏2012-12-21T15:06:53Z |
Hello,

I'm having an issue with an SQL select statement. I have a job that run this query:

Query 1:

SELECT A.CTRYNUM, A.CUSTOMER_NO FROM XXXX.AAA A, YYYY.BBB B WHERE A.LAST_UPT_ID = 
'AUOCUST' AND A.CTRYNUM = B.CTY;


The job is failing because the user doesn't have authorization to select in table XXXX.AAA. I checked the authorization in SYSTABAUTH table and I see that group MYTEAM has the authorization to perform select statement in table XXXX.AAA. Then I checked the RACF to see the user that belongs to this group and the user I'm using is in this group so it should have authorization to perform the query. I tried to run these two queries and I get the same error (SQLCODE = -551, ERROR: PC2 DOES NOT HAVE THE PRIVILEGE TO PERFORM OBJECT XXXX.AAA).

Query 2:

SELECT A.CTRYNUM, A.CUSTOMER_NO FROM XXXX.AAA A WHERE A.LAST_UPT_ID = 
'AUOCUST';


Query 3:

SELECT * FROM XXXX.AAA A;

Something that is weird is that I run the same job with user PC1 and it run OK. Users PC1 and PC2 belongs to the same group that has access to select on the table.

I don't know why it works with user PC1 and it doesn't with user PC2 when both belongs to the same group. Please, do you have any idea of this?

Thanks
Updated on 2013-01-02T17:27:57Z at 2013-01-02T17:27:57Z by cabrerachaparro
  • ToddBurchDB2
    ToddBurchDB2
    74 Posts

    Re: SQL Authorization error

    ‏2012-12-21T15:16:55Z  
    If you are using external security, then SYSTABAUTH does not come into play, unless the RACF exit is deferring authorization checking to DB2. Is that happening?
    If you are using external security, you may be getting an ICH408I message, telling you what class and object are being checked.
    If you are using external security, did you compare the permits for both users to see if they are the same.

    "Groups" are a RACF term, not a DB2 term (in regards to security). DB2's term is "secondary authid."

    If you still can't figure it out, I would recommend you open a "how to" PMR.

    Todd
  • ljkarl
    ljkarl
    5 Posts

    Re: SQL Authorization error

    ‏2012-12-21T15:42:39Z  
    If you are using external security, then SYSTABAUTH does not come into play, unless the RACF exit is deferring authorization checking to DB2. Is that happening?
    If you are using external security, you may be getting an ICH408I message, telling you what class and object are being checked.
    If you are using external security, did you compare the permits for both users to see if they are the same.

    "Groups" are a RACF term, not a DB2 term (in regards to security). DB2's term is "secondary authid."

    If you still can't figure it out, I would recommend you open a "how to" PMR.

    Todd
    Not sure what program you are using to execute the sql query and if you are doing it locally or remotely. My experience has been that either the underlying package owner needs to be added to the racf group, or if remote, an explicit select authority is authority.

    Setting up the DB2 communications database is an option. First you would have to update the sysibm.locations table and either the sysibm.ipnames or sysibm.lunames table. Then you can update the sysibm.usernames table to translate primary ids to secondary ids. A recycle of DDF is required when an update to the sysibm.locations and sysibm.lunames is made.
  • cabrerachaparro
    cabrerachaparro
    2 Posts

    Re: SQL Authorization error

    ‏2013-01-02T17:27:57Z  
    Thank you all for your answers. I will try to grant the access directly for the user.