Results 1 to 8 of 8
  1. #1
    Join Date
    Sep 2014
    Posts
    4

    Red face Unanswered: SQL0552N User does not have privilege to perform GRANT SQLSTATE=42502

    Hi All,
    I am facing a weird issue here on my local machine. Struggling with this from almost a week now.

    I tried to alter a table under one schema and it said I do not have authorization to do so! So I tried creating as user and granting an authorization but still get the same

    Getting the below error while trying to perform a grant.
    Code:
    SQL0552N "NS280C" does not have privilege to perform "GRANT". SQLSTATE=42502
    I am connected as NS280C in the database.

    The user NS280C belongs to Administrator, db2admns,db2dbagrp on the local machine


    Code:
    C:\Windows\System32>db2 get authorizations
    
     Administrative Authorizations for Current User
    
     Direct SYSADM authority                    = NO
     Direct SYSCTRL authority                   = NO
     Direct SYSMAINT authority                  = NO
     Direct DBADM authority                     = NO
     Direct CREATETAB authority                 = NO
     Direct BINDADD authority                   = NO
     Direct CONNECT authority                   = NO
     Direct CREATE_NOT_FENC authority           = NO
     Direct IMPLICIT_SCHEMA authority           = NO
     Direct LOAD authority                      = NO
     Direct QUIESCE_CONNECT authority           = NO
     Direct CREATE_EXTERNAL_ROUTINE authority   = NO
     Direct SYSMON authority                    = NO
    
     Indirect SYSADM authority                  = YES
     Indirect SYSCTRL authority                 = YES
     Indirect SYSMAINT authority                = YES
     Indirect DBADM authority                   = NO
     Indirect CREATETAB authority               = YES
     Indirect BINDADD authority                 = YES
     Indirect CONNECT authority                 = YES
     Indirect CREATE_NOT_FENC authority         = NO
     Indirect IMPLICIT_SCHEMA authority         = YES
     Indirect LOAD authority                    = NO
     Indirect QUIESCE_CONNECT authority         = NO
     Indirect CREATE_EXTERNAL_ROUTINE authority = NO
     Indirect SYSMON authority                  = YES


    DB2 Version and details ----

    C:\Windows\System32>db2level
    DB21085I Instance "DB2" uses "64" bits and DB2 code release "SQL09075" with level identifier "08060107".
    Informational tokens are "DB2 v9.7.500.4299", "special_27924", "IP23287_27924",
    and Fix Pack "5".
    Product is installed at "C:\PROGRA~1\IBM\SQLLIB" with DB2 Copy Name "".

    C:\Windows\System32>db2licm -l
    Product name: "DB2 Express-C"
    License type: "Unwarranted"
    Expiry date: "Permanent"
    Product identifier: "db2expc"
    Version information: "9.7"
    Max number of CPUs: "2"
    Max amount of memory (GB): "2"
    Enforcement policy: "Soft Stop"




    BR,
    NSS280

  2. #2
    Join Date
    Sep 2013
    Location
    India
    Posts
    246
    Quote Originally Posted by nss280 View Post
    Hi All,
    I am facing a weird issue here on my local machine. Struggling with this from almost a week now.

    I tried to alter a table under one schema and it said I do not have authorization to do so! So I tried creating as user and granting an authorization but still get the same

    Getting the below error while trying to perform a grant.
    Code:
    SQL0552N "NS280C" does not have privilege to perform "GRANT". SQLSTATE=42502
    I am connected as NS280C in the database.

    The user NS280C belongs to Administrator, db2admns,db2dbagrp on the local machine


    Code:
    C:\Windows\System32>db2 get authorizations
    
     Administrative Authorizations for Current User
    
     Direct SYSADM authority                    = NO
     Direct SYSCTRL authority                   = NO
     Direct SYSMAINT authority                  = NO
     Direct DBADM authority                     = NO
     Direct CREATETAB authority                 = NO
     Direct BINDADD authority                   = NO
     Direct CONNECT authority                   = NO
     Direct CREATE_NOT_FENC authority           = NO
     Direct IMPLICIT_SCHEMA authority           = NO
     Direct LOAD authority                      = NO
     Direct QUIESCE_CONNECT authority           = NO
     Direct CREATE_EXTERNAL_ROUTINE authority   = NO
     Direct SYSMON authority                    = NO
    
     Indirect SYSADM authority                  = YES
     Indirect SYSCTRL authority                 = YES
     Indirect SYSMAINT authority                = YES
     Indirect DBADM authority                   = NO
     Indirect CREATETAB authority               = YES
     Indirect BINDADD authority                 = YES
     Indirect CONNECT authority                 = YES
     Indirect CREATE_NOT_FENC authority         = NO
     Indirect IMPLICIT_SCHEMA authority         = YES
     Indirect LOAD authority                    = NO
     Indirect QUIESCE_CONNECT authority         = NO
     Indirect CREATE_EXTERNAL_ROUTINE authority = NO
     Indirect SYSMON authority                  = YES


    DB2 Version and details ----

    C:\Windows\System32>db2level
    DB21085I Instance "DB2" uses "64" bits and DB2 code release "SQL09075" with level identifier "08060107".
    Informational tokens are "DB2 v9.7.500.4299", "special_27924", "IP23287_27924",
    and Fix Pack "5".
    Product is installed at "C:\PROGRA~1\IBM\SQLLIB" with DB2 Copy Name "".

    C:\Windows\System32>db2licm -l
    Product name: "DB2 Express-C"
    License type: "Unwarranted"
    Expiry date: "Permanent"
    Product identifier: "db2expc"
    Version information: "9.7"
    Max number of CPUs: "2"
    Max amount of memory (GB): "2"
    Enforcement policy: "Soft Stop"




    BR,
    NSS280

    Hi,

    Does the table your are trying to alter is created by user NS280C ?

    If not than either you should have alter privilege on that table (granted to you by the creator or DBADM). or you (NS280C ) should hold DBADM while performing alter.

    output you have post shows you dont have DBADM directly or indirectly.
    ssumit

  3. #3
    Join Date
    Jul 2013
    Location
    Moscow, Russia
    Posts
    666
    Provided Answers: 55
    Hi,

    What do you want to GRANT?
    To have an ability to run a GRANT (table, view, or nickname privileges) statement, you have to have ACCESSCTRL or SECADM or CONTROL on this table. Do you have one of them?
    Regards,
    Mark.

  4. #4
    Join Date
    Sep 2014
    Posts
    4

    Red face

    This db was restored from a copy of the database I had earlier. The dba then who made this copy had all privileges but now i dont have any. He isnt working here anymore either to grant me the access.

    What option do I have here. How can I get the access to the tables in the db.

  5. #5
    Join Date
    Sep 2013
    Location
    India
    Posts
    246
    Quote Originally Posted by nss280 View Post
    This db was restored from a copy of the database I had earlier. The dba then who made this copy had all privileges but now i dont have any. He isnt working here anymore either to grant me the access.

    What option do I have here. How can I get the access to the tables in the db.
    If it is the case:

    1. You have to find out the user/ instance owner, who have taken the backup (which you have used as source.

    2. If the same user / instance owner of source backup system is not there in your system, create it , connect through it and grant the necessary privileges (SYSADM / DBADM) to any existing user or to user nss280 if you want.

    3. after this you may remove that new created user (GRANTOR) and use the newly granted user (GRANTEE) to administer your DB.


    You can get help from view syscat.dbauth

    cheers
    ssumit

  6. #6
    Join Date
    Jul 2013
    Location
    Moscow, Russia
    Posts
    666
    Provided Answers: 55
    You should find the users or groups who has these authorities:
    Code:
    select ACCESSCTRLAUTH, SECURITYADMAUTH, granteetype, grantee 
    from syscat.dbauth
    where 'Y' in (ACCESSCTRLAUTH, SECURITYADMAUTH)
    Alternatively, you could set the following db2 registry variable before a database restore from a backup.
    db2set DB2_RESTORE_GRANT_ADMIN_AUTHORITIES=YES
    db2stop
    db2start
    See the description of this variable here: System environment variables
    Regards,
    Mark.

  7. #7
    Join Date
    Sep 2014
    Posts
    4
    Quote Originally Posted by ssumit View Post
    If it is the case:

    1. You have to find out the user/ instance owner, who have taken the backup (which you have used as source.

    2. If the same user / instance owner of source backup system is not there in your system, create it , connect through it and grant the necessary privileges (SYSADM / DBADM) to any existing user or to user nss280 if you want.

    3. after this you may remove that new created user (GRANTOR) and use the newly granted user (GRANTEE) to administer your DB.


    You can get help from view syscat.dbauth

    cheers
    I followed this approach. It worked Thank you very much ssumit .
    Now I have Direct DBADM and I am able to alter the tables and even perform grant.
    Thank you again

  8. #8
    Join Date
    Sep 2014
    Posts
    4
    Quote Originally Posted by mark.b View Post
    You should find the users or groups who has these authorities:
    Code:
    select ACCESSCTRLAUTH, SECURITYADMAUTH, granteetype, grantee 
    from syscat.dbauth
    where 'Y' in (ACCESSCTRLAUTH, SECURITYADMAUTH)
    Alternatively, you could set the following db2 registry variable before a database restore from a backup.
    db2set DB2_RESTORE_GRANT_ADMIN_AUTHORITIES=YES
    db2stop
    db2start
    See the description of this variable here: System environment variables
    Hi mark,
    Thank you for this approach. I have not yet tried it, but I will restore the db again and try this one. It is a much quicker solution I will reply back on this.

    Thank you

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •