New to DB2: problems with user management
Thomas Kellerer
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
Frederik Engelen
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
Thomas Kellerer
>> 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
Frederik Engelen
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
Thomas Kellerer
> 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
Thomas Kellerer
> Administrators would be used anyway. Did you enable extended security?
> Is the DB2_GRP_LOOKUP variable useful in your case?
What I find really confusing is that according to the manual:
http://publib.boulder.ibm.com/infocenter/db2luw/v9r7/topic/com.ibm.db2.luw.admin.sec.doc/doc/c0005375.html
db2admin should already have all those privileges (because it was the user created during installation and that was used to create the database)
Do you think if I remove and re-install DB2 this would be fixed?
Regards
Thomas
Thomas Kellerer
> implicit DBADM.
OK, I decided to drop the database I created and recreate it (as I did not have anything stored in it anyway)
Apparently when I created the initial database I was using my regular Windows account and that apparently messed things up.
I ran a new commandline (using my db2admin Windows user) dropped the existing database, and created a new one
With that database db2admin *does* have dbadm authority (directly, as shown by db2 get authorizations) and I could grant the connect privilege to my regular user.
Thanks for your help
Thomas