DB2 cannot create database err=SQL1092N as a domain user

choracy
choracy used Ask the Experts™
on
I have very little DB2 experience.

I've just installed DB2 9.7 FP4 on Win2008R2x64 using a Domain account having local Admin privileges. (setup.exe "Run as Administrator").

Selected Server authentication. Local Groups and accounts for DB2 were created.

Added domain account to local DB2ADMNS and DB2USERS.

I tried almost all possible values and combinations for DB2_GRP_LOOKUP (local, token, domain) but could not create a database.

Got the error : SQL1092N  "SVC_USER  " does not have the authority to perform the requested command or operation.

I suppose logging on windows as DB2Admin will create the database ok, also if I start the DB2 instance as the domain SVC_USER, will allow database creation but I do not want to try either.

Need a way to be able to create databases using the SVC_USER domain account.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Hi choracy,

Lot into the command line processor and run this command:

  db2 GET AUTHORIZATIONS

The user needs the CREATEDB or DBADM authority.

You can add them from the admin account if they aren't there.



Good Luck,
Kent
It's not clear from your description if you have set the SYSADM_GROUP instance configuration parameter to DB2ADMNS. If not, you should, otherwise DB2 does not know that the DB2ADMNS members are supposed to have that authority. By default only members of the local Administrators group get SYSADM.

Author

Commented:
I did not setup manually any value to SYSADM_GROUP.

My domain account IS a member of local Administrators AND DB2ADMNS.
Announcing the Winners!

The results are in for the 15th Annual Expert Awards! Congratulations to the winners, and thank you to everyone who participated in the nominations. We are so grateful for the valuable contributions experts make on a daily basis. Click to read more about this year’s recipients!

Hi Choracy,

Let's look at what the user's permissions really are.  Run the GET AUTHORIZATIONS command.

db2 => connect to mydb

   Database Connection Information

 Database server        = DB2/NT 9.7.4
 SQL authorization ID   = USER1
 Local database alias   = MYDB

db2 => get authorizations

 Administrative Authorizations for Current User

 Direct SYSADM authority                    = NO
 Direct SYSCTRL authority                   = NO
 Direct SYSMAINT authority                  = NO
 Direct DBADM authority                     = YES
 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

Open in new window


This will tell you what you can do.  Then it's a simple matter of determining what authority you want the user to have.


Kent

Author

Commented:
Ken I tried to run 'GET AUTHORIZATIONS' but the command was not valid.

The documentation said it was deprecated since 9.5 and proposed the AUTH_LIST_AUTHORITIES_FOR_AUTHID table function, which did not tell me much.
> which did not tell me much

Is this some kind of a game, where we're supposed to guess what you believe it didn't tell you? OK, I'm in. Please post the 1st line of the output of the query. Tomorrow I'll ask you to post the 2nd line.

Author

Commented:
Wow! we have a wise guy here !

Guess what ? Both GET AUTHORIZATIONS and AUTH_LIST_AUTHORITIES_FOR_AUTHID, need the user to be connected to a database !

Guess again! My problem is that I CANNOT create a database!  But you are too clever to figure that out aren't you!

I ran the command on a similar environment (OS,DB), only the installation was performed by the local Administrator and database creation has no problem. Anyway checking AUTH_LIST, shows both DB2Admin and local Administrator have a SYSADM=Yes on Group. Their common group is local Administrators.
Then added a domain user in local Administrators group and checked for the domain user. SYSADM=No, IMPLICT_SCHEMA=YES.

I understand that there is a default or Inherited security from Groups or Roles the user belongs to and a Direct one which is assigned directly to the user.

I believe in that DB2 somehow fails to see that the domain user is a member of local Administrators.

Doing some research found that in some cases, people having the same problem managed to resolve it, by setting the DB2_GRP_LOOKUP variable. I tried a few but could not resolve.

I guess I can add the domain user to SYSADM_GROUP and most probably will work but I do not like using direct security assignments if I can avoid them.
Hi Choracy,

GET AUTHORIZATIONS does require that you be connected to a database.  The example above shows that, and it shows that the command still works at version 9.7.  It may be deprecated, but it's not gone yet.  :)  

Note that the user in the example has direct DBADM authority, but nothing else in the "direct" list.  In the list of indirect authorities, the user acquires 5 of the authorities.  If your user can connect to any database on the instance, the GET AUTHORIZATIONS command becomes relevant because it will show you the direct authorities, especially the SYS* and DBADM items.

At some point, you'll need to query the system tables to see what permissions have been granted to that user.  

To see all of the explicit grants to DB2ADMIN:

  select * from sysibm.sysdbauth where grantee = 'DB2ADMIN'

Change the user name to see the DB authorizations granted to your user.

Note that to query these tables, you'll still need to connect to a database.

You can then GRANT any permission to your user that you wish, as long as you're running from a user with sufficient privilege to do so.  (Hence my reference to DB2ADMIN above.)


Kent


Kent

Author

Commented:
Thanks Kent, I will have access to the system on Thursday morning and I will give it a try.

Actually there are several things I want to try with local and domain users.

I think DB2Admin is the instance owner, yes?

Will keep you posted.
DB2ADMIN is MY instance owner.  Yours could well be different.  :)

And their are other tables/views to query for other user-level permissions within a database, but they aren't applicable to the problem at hand.

Kent

Author

Commented:
Ok, findings.

First, I verified that a database connection is absolutely needed to use GET AUTHORIZATIONS.

Second, a user needs SYSADM (and perhaps SYSCTRL in 9.7)  authority to create database.

Users who get SYSADM authority are described here: http://tinyurl.com/culxbby

Anyway I gave DB2ADMNS both SYSADM and SYSCTRL authority but still my domain user, member of DB2ADMNS could not create sa database.

I also run into a confising statement in DB2 9.1 documentation saying
 "For a domain user to have SYSADM authority, they must belong to the local Administrators group or the Administrators group at the domain controller. Since the DB2 database server always performs authorization at the machine where the account is defined, adding a domain user to the local Administrators group on the server does not grant the domain user SYSADM authority to this group." ???

Confusing and too complex for me. This should be quite straightforward.

So, I logged on as DB2ADMIN, created the databases and granted 'dbadmin' to the domain user.
Hi Choracy,

Sorry that you find it confusing.  Reference material often tends to read that way so that they can cover all of the bases.

DB2 authenticates users by querying the underlying operating system.  If all of the users are defined on the system (as would often occur on a unix system), the authentication uses the definitions on that system even if the user is also defined on the network (such as an Active Directory).

That confusing paragraph is trying to say that if a user is given SYSADM authority (or any authority, for that matter) it MUST be given at the level where the host O/S will validate the user.


Kent

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial