Topic
  • 13 replies
  • Latest Post - ‏2009-12-01T00:25:31Z by Wamiduku
SystemAdmin
SystemAdmin
5837 Posts

Pinned topic Express-C 9.7 install, Windows, unable to act as SYSADM

‏2009-07-16T06:49:36Z |
Have just laid down Express-C 9.7 on Windows XP Home Edition system. Install went fine. Now trying the Control Center, able to see SAMPLE database and all tables. However, when attempting to SELECT from table, use does not have the auth to SELECT.

com.ibm.db.DataException: A database manager error occurred. : IBMCLI DriverDB2/NT SQL0551N "DB2ADMIN" does not have the required authorization or privilege to perform operation "SELECT" on object "SYSTEM.EMPLOYEE". SQLSTATE=42501

The account is an Administrator level account, no Domains, simple Windows XP Home. XP has no groups that I can create, at least I am not aware of any. I have tried setting the SYSADM_GROUP to ADMINISTRATORS, no luck.

How to get this working ???

TIA
Updated on 2009-12-01T00:25:31Z at 2009-12-01T00:25:31Z by Wamiduku
  • _mihai_
    _mihai_
    10 Posts

    Re: Express-C 9.7 install, Windows, unable to act as SYSADM

    ‏2009-07-16T15:00:32Z  
    In releases prior to Version 9.7, SYSADM authority included implicit DBADM authority and also provided the ability to grant and revoke all authorities and privileges. In Version 9.7, the DB2® authorization model has been updated to clearly separate the duties of the system administrator, the database administrator, and the security administrator. As part of this enhancement, the abilities given by the SYSADM authority have been reduced.

    DBADM has also been split into 3 separate authorities: DBADM, DATAACCESS, and ACCESSCTRL.

    If you want a certain user to access any data in the database un need to grant him DATAACCESS.
    The users that can grant/revoke privileges are SECADM and ACCESSCTRL (ACCESSCTRL is a subset of SECADM).

    When a database is created the creator received DBADM,DATAACCESS,ACCESSCTRL and SECADM.

    For an overview of the DB2 9.7 authorizations see this link:
    http://publib.boulder.ibm.com/infocenter/db2luw/v9r7/topic/com.ibm.db2.luw.admin.sec.doc/doc/c0055206.html

    SYSADM:
    http://publib.boulder.ibm.com/infocenter/db2luw/v9r7/topic/com.ibm.db2.luw.admin.sec.doc/doc/c0005479.html
  • SystemAdmin
    SystemAdmin
    5837 Posts

    Re: Express-C 9.7 install, Windows, unable to act as SYSADM

    ‏2009-07-17T06:10:09Z  
    • _mihai_
    • ‏2009-07-16T15:00:32Z
    In releases prior to Version 9.7, SYSADM authority included implicit DBADM authority and also provided the ability to grant and revoke all authorities and privileges. In Version 9.7, the DB2® authorization model has been updated to clearly separate the duties of the system administrator, the database administrator, and the security administrator. As part of this enhancement, the abilities given by the SYSADM authority have been reduced.

    DBADM has also been split into 3 separate authorities: DBADM, DATAACCESS, and ACCESSCTRL.

    If you want a certain user to access any data in the database un need to grant him DATAACCESS.
    The users that can grant/revoke privileges are SECADM and ACCESSCTRL (ACCESSCTRL is a subset of SECADM).

    When a database is created the creator received DBADM,DATAACCESS,ACCESSCTRL and SECADM.

    For an overview of the DB2 9.7 authorizations see this link:
    http://publib.boulder.ibm.com/infocenter/db2luw/v9r7/topic/com.ibm.db2.luw.admin.sec.doc/doc/c0055206.html

    SYSADM:
    http://publib.boulder.ibm.com/infocenter/db2luw/v9r7/topic/com.ibm.db2.luw.admin.sec.doc/doc/c0005479.html
    I see what you mean about SYSADM not having access to the data and how 9.7 has changed its meaning.

    OK, here is the quandry. When I look at authorizations on the database, I see that SYSTEM is the ONLY one that appears. SYSTEM has DBADM and SECADM. All of the tables use schema = SYSTEM. So, looks like SYSTEM may be the only one who can issue a GRANT at the database level. BUT, how to issue the grant as SYSTEM?

    The only two accounts I've used thus far are my personal account and the DB2ADMIN which was created by the install. Both are administrator level but neither has the authority to issue the GRANT at the database level. I've tried to create an account named SYSTEM but Windows XP will not let me because there already is one (although it is not an interactive account).

    BTW, If SAMPLE had been created using DB2ADMIN as the creator, not SYSTEM, seems like I would not have this problem.

    Is there a solution to being able to access the data in SAMPLE? Only thing I can think of would be to run a GRANT from a DB2CMD.exe that with a bit of trickery I can get to run from LOGON AS SYSTEM. Haven't tried this but might work.

    Seems like there should be something more direct to resolve this. Any suggestions?
    TIA
  • _mihai_
    _mihai_
    10 Posts

    Re: Express-C 9.7 install, Windows, unable to act as SYSADM

    ‏2009-07-17T13:53:22Z  
    I see what you mean about SYSADM not having access to the data and how 9.7 has changed its meaning.

    OK, here is the quandry. When I look at authorizations on the database, I see that SYSTEM is the ONLY one that appears. SYSTEM has DBADM and SECADM. All of the tables use schema = SYSTEM. So, looks like SYSTEM may be the only one who can issue a GRANT at the database level. BUT, how to issue the grant as SYSTEM?

    The only two accounts I've used thus far are my personal account and the DB2ADMIN which was created by the install. Both are administrator level but neither has the authority to issue the GRANT at the database level. I've tried to create an account named SYSTEM but Windows XP will not let me because there already is one (although it is not an interactive account).

    BTW, If SAMPLE had been created using DB2ADMIN as the creator, not SYSTEM, seems like I would not have this problem.

    Is there a solution to being able to access the data in SAMPLE? Only thing I can think of would be to run a GRANT from a DB2CMD.exe that with a bit of trickery I can get to run from LOGON AS SYSTEM. Haven't tried this but might work.

    Seems like there should be something more direct to resolve this. Any suggestions?
    TIA
    SYSTEM is the authid for the LocalSystem account.
    You would have to run an application as a service under the LocalSystem account and issue a grant like that.

    Note LocalSystem is considered to have SYSADM when the sysadm_group database manager parameter is NULL, this is the reason LocalSystem was able to create the database in the first place.

    Mihai.
  • SystemAdmin
    SystemAdmin
    5837 Posts

    Re: Express-C 9.7 install, Windows, unable to act as SYSADM

    ‏2009-07-19T08:56:18Z  
    • _mihai_
    • ‏2009-07-17T13:53:22Z
    SYSTEM is the authid for the LocalSystem account.
    You would have to run an application as a service under the LocalSystem account and issue a grant like that.

    Note LocalSystem is considered to have SYSADM when the sysadm_group database manager parameter is NULL, this is the reason LocalSystem was able to create the database in the first place.

    Mihai.
    Yes I did get it to work that way. I was able to start dmbcmd.exe running under LocalSystem account and issue the grant of DBADM with DATAACCESS. Was able to SELECT from the table, but strangely, there was no data in the tables. I belive the installer was created the tables but then was unable to insert any data. Well, in the end, I dropped SAMPLE, then recreated it under my personal id, this time the tables were created with my id as creator, not SYSTEM, and everything is OK. Thanks for your input.
  • _mihai_
    _mihai_
    10 Posts

    Re: Express-C 9.7 install, Windows, unable to act as SYSADM

    ‏2009-07-20T14:59:55Z  
    Yes I did get it to work that way. I was able to start dmbcmd.exe running under LocalSystem account and issue the grant of DBADM with DATAACCESS. Was able to SELECT from the table, but strangely, there was no data in the tables. I belive the installer was created the tables but then was unable to insert any data. Well, in the end, I dropped SAMPLE, then recreated it under my personal id, this time the tables were created with my id as creator, not SYSTEM, and everything is OK. Thanks for your input.
    How did the sample get created under SYSTEM in the first place?
    Was this something intentional?
    Was it default for the installation?

    Mihai
  • SystemAdmin
    SystemAdmin
    5837 Posts

    Re: Express-C 9.7 install, Windows, unable to act as SYSADM

    ‏2009-07-21T04:58:47Z  
    • _mihai_
    • ‏2009-07-20T14:59:55Z
    How did the sample get created under SYSTEM in the first place?
    Was this something intentional?
    Was it default for the installation?

    Mihai
    It seems like this is the default behavior, at least in a Windows XP Home Edition environment.

    I ran the installer from my own personal id. I selected creating the DAS with a Windows account, DB2ADMIN, LocalSystem was not recommended. The installer created the DB2ADMIN account for me, SAMPLE was created, everything seemed just fine. But when I brought up the control center, I could see the tables, but not access them. That's because the creator was SYSTEM, the table names were SYSTEM.EMPLOYEE, etc, and when I checked auths to the database, the only thing that showed was SYSTEM having DBADM and SECADM. Seems like a glitch somewhere.

    However, DROP, then CREATE seems to behave normally.
  • SystemAdmin
    SystemAdmin
    5837 Posts

    Re: Express-C 9.7 install, Windows, unable to act as SYSADM

    ‏2009-08-05T21:40:37Z  
    Yes I did get it to work that way. I was able to start dmbcmd.exe running under LocalSystem account and issue the grant of DBADM with DATAACCESS. Was able to SELECT from the table, but strangely, there was no data in the tables. I belive the installer was created the tables but then was unable to insert any data. Well, in the end, I dropped SAMPLE, then recreated it under my personal id, this time the tables were created with my id as creator, not SYSTEM, and everything is OK. Thanks for your input.
    stever,
    I am having the exact same problem as you and I follow what you are saying, but how did you "start dmbcmd.exe running under LocalSystem account and issue the grant of DBADM with DATAACCESS." I'm reading up on localsystem account 'system' to see how to login to my pc as system. I found a forum that explains how to do it using "code:" command at cmd prompt but it does not work for me. So how did you do this?....thanks
  • SystemAdmin
    SystemAdmin
    5837 Posts

    Re: Express-C 9.7 install, Windows, unable to act as SYSADM

    ‏2009-08-05T22:16:18Z  
    stever,
    I am having the exact same problem as you and I follow what you are saying, but how did you "start dmbcmd.exe running under LocalSystem account and issue the grant of DBADM with DATAACCESS." I'm reading up on localsystem account 'system' to see how to login to my pc as system. I found a forum that explains how to do it using "code:" command at cmd prompt but it does not work for me. So how did you do this?....thanks
    I fixed it. I was able to login to Windows as system user and grant privileges to my userid and db2admin. As you said, my sample tables were also empty. However, thanks. I do have one more question that I will start another thread. I have been trying to see how/if I can use the db2 connections utilities to connect to Micro Focus cobol. I want to code cobol/db2 programs and access the data in my DB2 Express tables. I will look in the forum....thanks again
  • vBIT
    vBIT
    1 Post

    Re: Express-C 9.7 install, Windows, unable to act as SYSADM

    ‏2009-08-25T10:26:35Z  
    Had exactly the same problem on XP Pro. After several attempts at following the tutorials, I got fed up, and started googling for the password of the "system" user, which of course does not exist! Then found this thread, and voila! Fixed... Thanks steve!

    Bit disconcerting though, the way a bug like this slips in...

    Also, having recreated the SAMPLE database using "First Steps", the database only has my userid as user, and the db2admin user is not there. Coming from an Oracle background, it's a bit of a head-scratch, since there the system and sysdba users are part of all databases. But I suppose that's called learning...
  • mmpdba
    mmpdba
    1 Post

    Re: Express-C 9.7 install, Windows, unable to act as SYSADM

    ‏2009-10-31T13:43:15Z  
    • vBIT
    • ‏2009-08-25T10:26:35Z
    Had exactly the same problem on XP Pro. After several attempts at following the tutorials, I got fed up, and started googling for the password of the "system" user, which of course does not exist! Then found this thread, and voila! Fixed... Thanks steve!

    Bit disconcerting though, the way a bug like this slips in...

    Also, having recreated the SAMPLE database using "First Steps", the database only has my userid as user, and the db2admin user is not there. Coming from an Oracle background, it's a bit of a head-scratch, since there the system and sysdba users are part of all databases. But I suppose that's called learning...
    I ran to into same issue and the simplest fix was to drop/recreate the SAMPLE database.
  • asp1028
    asp1028
    66 Posts

    Re: Express-C 9.7 install, Windows, unable to act as SYSADM

    ‏2009-11-01T17:01:53Z  
    Seems like Express-C created this funny account whether you like it or not during installation. That is not the account anybody wants to log in. Save you effort to fight with that. Just DROP the SAMPLE database and re-install it using the 'First Step'. I installed Express-C many time on XP, VISTA and W7 and this becomes my standard procedure.

    Frank
  • Wamiduku
    Wamiduku
    2 Posts

    Re: Express-C 9.7 install, Windows, unable to act as SYSADM

    ‏2009-12-01T00:24:46Z  
    I fixed it. I was able to login to Windows as system user and grant privileges to my userid and db2admin. As you said, my sample tables were also empty. However, thanks. I do have one more question that I will start another thread. I have been trying to see how/if I can use the db2 connections utilities to connect to Micro Focus cobol. I want to code cobol/db2 programs and access the data in my DB2 Express tables. I will look in the forum....thanks again
    > jrm6334 wrote:
    > I was able to login to Windows as system user ...

    How do you do that? I have the same problem with the demo database.
  • Wamiduku
    Wamiduku
    2 Posts

    Re: Express-C 9.7 install, Windows, unable to act as SYSADM

    ‏2009-12-01T00:25:31Z  
    I fixed it. I was able to login to Windows as system user and grant privileges to my userid and db2admin. As you said, my sample tables were also empty. However, thanks. I do have one more question that I will start another thread. I have been trying to see how/if I can use the db2 connections utilities to connect to Micro Focus cobol. I want to code cobol/db2 programs and access the data in my DB2 Express tables. I will look in the forum....thanks again
    > jrm6334 wrote:
    > I was able to login to Windows as system user ...

    How do you do that? I have the same problem with the demo database.