New to DB2: problems with user management
in [DB2]
|
Prev: Spam
Next: Single System View (SSV) with DB2 9.7 DPF
From: Thomas Kellerer on 3 Nov 2009 12:55 Hi, I just installed DB2 express and would like to authenticate a "regular" user on my machine, so that I don't have to use the admin user when querying the database. During installation one Windows user called "db2admin" was created. I can successfully log in to DB2 using that user (either through DataStudio or from the commandline). But when trying to grant connect to another (windows) user, the statement fails. What I am doing: db2 connect to tkdb user db2admin using xxxxx when I run db2 get authorizations I get the following display: 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 = NO Indirect SYSMAINT authority = NO 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 = NO As db2admin has SYSADM authority I assumed I can grant connect to a different (Windows) user: db2 grant connect on database to user bob but I get the following error message: DB21034E The command was processed as an SQL statement because it was not a valid Command Line Processor command. During SQL processing it returned: SQL0552N "DB2ADMIN" does not have the privilege to perform operation "GRANT". SQLSTATE=42502 This confuses me. My understanding was that whoever created the database (db2admin) should have the privileges to grant connect to other users. I also defined the Windows "Administrators" group as the SYSADM group (db2admin is part of the Administrators group) db2 get dbm cfg returns: SYSADM group name (SYSADM_GROUP) = ADMINISTRATORS SYSCTRL group name (SYSCTRL_GROUP) = SYSMAINT group name (SYSMAINT_GROUP) = SYSMON group name (SYSMON_GROUP) = So what am I missing here? How can I grant the (Windows) user bob the privilege to connect to my database? (The Windows user bob does exist) Regards Thomas
From: Frederik Engelen on 3 Nov 2009 15:08 On Nov 3, 6:55 pm, Thomas Kellerer <OTPXDAJCS...(a)spammotel.com> wrote: > Hi, > > I just installed DB2 express and would like to authenticate a "regular" user on my machine, so that I don't have to use the admin user when querying the database. > > During installation one Windows user called "db2admin" was created. > I can successfully log in to DB2 using that user (either through DataStudio or from the commandline). > > But when trying to grant connect to another (windows) user, the statement fails. > > What I am doing: > db2 connect to tkdb user db2admin using xxxxx > > when I run db2 get authorizations I get the following display: > > 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 = NO > Indirect SYSMAINT authority = NO > 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 = NO > > As db2admin has SYSADM authority I assumed I can grant connect to a different (Windows) user: > > db2 grant connect on database to user bob > > but I get the following error message: > > DB21034E The command was processed as an SQL statement because it was not a > valid Command Line Processor command. During SQL processing it returned: > SQL0552N "DB2ADMIN" does not have the privilege to perform operation "GRANT". > SQLSTATE=42502 > > This confuses me. > My understanding was that whoever created the database (db2admin) should have the privileges to grant connect to other users. > > I also defined the Windows "Administrators" group as the SYSADM group (db2admin is part of the Administrators group) > > db2 get dbm cfg returns: > > SYSADM group name (SYSADM_GROUP) = ADMINISTRATORS > SYSCTRL group name (SYSCTRL_GROUP) = > SYSMAINT group name (SYSMAINT_GROUP) = > SYSMON group name (SYSMON_GROUP) = > > So what am I missing here? > > How can I grant the (Windows) user bob the privilege to connect to my database? > (The Windows user bob does exist) > > Regards > Thomas I guess you are on v9.7? Since this version SYSADM no longer has implicit DBADM. Try to grant DBADM to this user instead. -- Frederik
From: Thomas Kellerer on 4 Nov 2009 02:50 Frederik Engelen, 03.11.2009 21:08: >> As db2admin has SYSADM authority I assumed I can grant connect to a different (Windows) user: >> >> db2 grant connect on database to user bob >> >> but I get the following error message: >> >> DB21034E The command was processed as an SQL statement because it was not a >> valid Command Line Processor command. During SQL processing it returned: >> SQL0552N "DB2ADMIN" does not have the privilege to perform operation "GRANT". >> SQLSTATE=42502 >> >> This confuses me. >> My understanding was that whoever created the database (db2admin) should have the privileges to grant connect to other users. >> >> I also defined the Windows "Administrators" group as the SYSADM group (db2admin is part of the Administrators group) >> >> db2 get dbm cfg returns: >> >> SYSADM group name (SYSADM_GROUP) = ADMINISTRATORS >> SYSCTRL group name (SYSCTRL_GROUP) = >> SYSMAINT group name (SYSMAINT_GROUP) = >> SYSMON group name (SYSMON_GROUP) = >> >> So what am I missing here? >> >> How can I grant the (Windows) user bob the privilege to connect to my database? >> (The Windows user bob does exist) >> >> Regards >> Thomas > > I guess you are on v9.7? Since this version SYSADM no longer has > implicit DBADM. > > Try to grant DBADM to this user instead. Thanks for your answer, yes I am on 9.7. Granting DBADM does not work either :( C:\Program Files\IBM\SQLLIB\BIN>db2 grant dbadm on database to user db2admin DB21034E The command was processed as an SQL statement because it was not a valid Command Line Processor command. During SQL processing it returned: SQL0552N "DB2ADMIN" does not have the privilege to perform operation "GRANT". SQLSTATE=42502 Btw: According to my documentation (from the a 9.7 new features training) the user creating a database is initially granted the SECADM and DBADM roles? So how do I proceed from here? Is there some kind of "super user" that is not mapped to a OS user that I can use to log in? Regards Thomas
From: Frederik Engelen on 4 Nov 2009 04:15 On Nov 4, 8:50 am, Thomas Kellerer <OTPXDAJCS...(a)spammotel.com> wrote: > Frederik Engelen, 03.11.2009 21:08: > > > > > > >> As db2admin has SYSADM authority I assumed I can grant connect to a different (Windows) user: > > >> db2 grant connect on database to user bob > > >> but I get the following error message: > > >> DB21034E The command was processed as an SQL statement because it was not a > >> valid Command Line Processor command. During SQL processing it returned: > >> SQL0552N "DB2ADMIN" does not have the privilege to perform operation "GRANT". > >> SQLSTATE=42502 > > >> This confuses me. > >> My understanding was that whoever created the database (db2admin) should have the privileges to grant connect to other users. > > >> I also defined the Windows "Administrators" group as the SYSADM group (db2admin is part of the Administrators group) > > >> db2 get dbm cfg returns: > > >> SYSADM group name (SYSADM_GROUP) = ADMINISTRATORS > >> SYSCTRL group name (SYSCTRL_GROUP) = > >> SYSMAINT group name (SYSMAINT_GROUP) = > >> SYSMON group name (SYSMON_GROUP) = > > >> So what am I missing here? > > >> How can I grant the (Windows) user bob the privilege to connect to my database? > >> (The Windows user bob does exist) > > >> Regards > >> Thomas > > > I guess you are on v9.7? Since this version SYSADM no longer has > > implicit DBADM. > > > Try to grant DBADM to this user instead. > > Thanks for your answer, yes I am on 9.7. > > Granting DBADM does not work either :( > > C:\Program Files\IBM\SQLLIB\BIN>db2 grant dbadm on database to user db2admin > DB21034E The command was processed as an SQL statement because it was not a > valid Command Line Processor command. During SQL processing it returned: > SQL0552N "DB2ADMIN" does not have the privilege to perform operation "GRANT". > SQLSTATE=42502 > > Btw: According to my documentation (from the a 9.7 new features training) the user creating a database is initially granted the SECADM and DBADM roles? > > So how do I proceed from here? > Is there some kind of "super user" that is not mapped to a OS user that I can use to log in? > > Regards > Thomas Thomas, There is no such superuser. It's strange that SECADM/ACCESSCTRL/... authorities aren't mentioned in the output of "get authorizations", because according to the Info Center SECADM is required to grant DBADM. Perhaps you can check the syscat.dbauth view for some more info on the configuration. Normally, you shouldn't have to set the SYSADM_GROUP parameter, as Administrators would be used anyway. Did you enable extended security? Is the DB2_GRP_LOOKUP variable useful in your case? -- Frederik
From: Thomas Kellerer on 4 Nov 2009 04:52
Frederik Engelen, 04.11.2009 10:15: > It's strange that SECADM/ACCESSCTRL/... authorities aren't mentioned > in the output of "get authorizations", because according to the Info > Center SECADM is required to grant DBADM. Perhaps you can check the > syscat.dbauth view for some more info on the configuration. syscat.dbauth contains the following ---- [Row 1] ------------------------------- GRANTOR : SYSIBM GRANTORTYPE : S GRANTEE : SYSTEM GRANTEETYPE : U BINDADDAUTH : N CONNECTAUTH : N CREATETABAUTH : N DBADMAUTH : Y EXTERNALROUTINEAUTH : N IMPLSCHEMAAUTH : N LOADAUTH : N NOFENCEAUTH : N QUIESCECONNECTAUTH : N LIBRARYADMAUTH : N SECURITYADMAUTH : Y SQLADMAUTH : N WLMADMAUTH : N EXPLAINAUTH : N DATAACCESSAUTH : Y ACCESSCTRLAUTH : Y ---- [Row 2] ------------------------------- GRANTOR : SYSIBM GRANTORTYPE : S GRANTEE : PUBLIC GRANTEETYPE : G BINDADDAUTH : Y CONNECTAUTH : Y CREATETABAUTH : Y DBADMAUTH : N EXTERNALROUTINEAUTH : N IMPLSCHEMAAUTH : Y LOADAUTH : N NOFENCEAUTH : N QUIESCECONNECTAUTH : N LIBRARYADMAUTH : N SECURITYADMAUTH : N SQLADMAUTH : N WLMADMAUTH : N EXPLAINAUTH : N DATAACCESSAUTH : N ACCESSCTRLAUTH : N > Normally, you shouldn't have to set the SYSADM_GROUP parameter, as > Administrators would be used anyway. Did you enable extended security? > Is the DB2_GRP_LOOKUP variable useful in your case? OK, I changed it to local (because I am using a computer that is part of a domain) but that didn't change anything. After setting it, stopped and started db2 and then tried the grant DBADM again (running the commandline as db2admin) Thanks for your help! Regards Thomas |