problem on Connection

[zhenchen17]

Hi gordy and everyone,
I use OpenAPI for a while but have a few problems:
1. What is the difference between the two methods to set connection username and password:
(A) set co_username and co_password member in IIAPI_CONNPARM structure when passed to IIapi_connect function.
(B) use IIapi_setConnectParam function to set the IIAPI_CP_EFFECTIVE_USER and IIAPI_CP_DBMS_PASSWORD parameters to the session.

I wonder (1) if both the two steps are set, and the usernames r totally different, which one is the true one connection to the database?
(2) if I use method (B), is it true that application constructed by OpenAPI in same computer will share the same config(Username and password)?

2. I tried to use method (A) to send username and password to the server to ask for a connection. However, I traced that the Username and password are correct when the function IIapi_connect are called (in IIAPI_CONNPARM) but the password are blank when passed to sequencer(scs_sequencer) in src\back\scf\scs\scsqncr.c in the variable of scb->scb_sscb.sscb_ics.ics_rupass. Meanwhile, the username in scb->scb_sscb.sscb_ics.ics_rusername is correct.

So I wonder (1) is this normal for the empty password or may be bugs in GCA communication or Is designed like this?
(2) If i want to trace how these username and password are packaged and sent and interpretered at both client and server, which part of source codes should I read?

3. (1)Is Ingres .Net provider offer the same functions as (B) to set the connect parameters of usernames and password?
(2) what is the difference between the two parameters in \src\common\dotnet2_win\provider\advan\advanconnec t.cs : MSG_CP_PASSWORD and MSG_CP_DBPASSWORD

Thanks ahead
Regards

Chenzhen
zhenchen17@gmail.com

[thoda04]

Hi Chenzhen,

I can offer some information on the Ingres .NET Data Provider parts of your question.

In addtion to the usual "userid" and "password" keywords, the Ingres .NET Data Provider, beginning with release 9.2, does support connection string keywords of "dbms_user" and "dbms_password" to allow specification of an effective user and a DBMS password. These keywords are equivalent to the Ingres -u flag and the Ingres -P flags respectively. The Connectivity Guide can give more detail and restrictions on these keywords.

Internally, the MSG_CP_USERNAME, MSG_CP_PASSWORD, MSG_CP_DBUSERNAME, and MSG_CP_DBPASSWORD messages are used to pass the "userid", "password", "dbms_user", and "dbms_password" connection string values respectively to the DAS and DBMS.

Hope this helps,
Dave

[grant]

Originally Posted by zhenchen17

Hi gordy and everyone,
I use OpenAPI for a while but have a few problems:
1. What is the difference between the two methods to set connection username and password:
(A) set co_username and co_password member in IIAPI_CONNPARM structure when passed to IIapi_connect function.
(B) use IIapi_setConnectParam function to set the IIAPI_CP_EFFECTIVE_USER and IIAPI_CP_DBMS_PASSWORD parameters to the session.

I wonder (1) if both the two steps are set, and the usernames r totally different, which one is the true one connection to the database?
(2) if I use method (B), is it true that application constructed by OpenAPI in same computer will share the same config(Username and password)?

Chenzhen

OpenAPI will allow you connect as the process owner of the running application if co_username, co_password are blank. That assumes that the process owner is a valid Ingres user.

IIAPI_CP_EFFECTIVE_USER allows the user (implied or set using co_username) to impersonate the user sent to IIapi_setConnectParam(). To use this flag you need the security administrator privilege.

IIAPI_CP_DBMS_PASSWORD allows the OpenAPI application to authenticate against a database password. This is an additional password not a replacement for the password used in co_password. If memory serves me correctly the dbms password is used with co_username not with the user to be impersonated (if using IIAPI_CP_EFFECTIVE_USER).

Originally Posted by zhenchen17

2. I tried to use method (A) to send username and password to the server to ask for a connection. However, I traced that the Username and password are correct when the function IIapi_connect are called (in IIAPI_CONNPARM) but the password are blank when passed to sequencer(scs_sequencer) in src\back\scf\scs\scsqncr.c in the variable of scb->scb_sscb.sscb_ics.ics_rupass. Meanwhile, the username in scb->scb_sscb.sscb_ics.ics_rusername is correct.

So I wonder (1) is this normal for the empty password or may be bugs in GCA communication or Is designed like this?
(2) If i want to trace how these username and password are packaged and sent and interpreted at both client and server, which part of source codes should I read?

Take a look at the GC CL spec - http://community.ingres.com/wiki/Ingres_CL_GC - specifically the section on user/password validation.

regards

grant

[thogo01]

Originally Posted by zhenchen17

1. What is the difference between the two methods to set connection username and password:
(A) set co_username and co_password member in IIAPI_CONNPARM structure when passed to IIapi_connect function.

These correspond to an OS user and password. This is the client ID reported to the DBMS.

Originally Posted by zhenchen17

(B) use IIapi_setConnectParam function to set the IIAPI_CP_EFFECTIVE_USER and IIAPI_CP_DBMS_PASSWORD parameters to the session.

These are parameters which are passed directly to the DBMS. Effective user is the same as the -u flag for terminal monitor and other utilities. DBMS password is the password assigned to the user within the DBMS and corresponds to the -P flag in terminal monitor. These are not generally used together. A system administrator may use -u to run a session as a particular user. A given user must use the -P flag to specify their DBMS password when assigned.

Originally Posted by zhenchen17

(1) if both the two steps are set, and the usernames r totally different, which one is the true one connection to the database?

The DBMS is told that the client is the ID provided in co_username. If this is an administrator, then the session will be given the effective user ID provided in IIAPI_CP_EFFECTIVE_USER.

Originally Posted by zhenchen17

(2) if I use method (B), is it true that application constructed by OpenAPI in same computer will share the same config(Username and password)?

As long as the same ID is provided in each instance of the application, both methods will result in sessions running with the same user ID. The difference is that the user running the application must be an administrator to use method B (or the ID provided in co_username must be an administrator if both methods are used), while method A requires an OS password be provided for the co_username ID.

Originally Posted by zhenchen17

2. I tried to use method (A) to send username and password to the server to ask for a connection. However, I traced that the Username and password are correct when the function IIapi_connect are called (in IIAPI_CONNPARM) but the password are blank when passed to sequencer(scs_sequencer) in src\back\scf\scs\scsqncr.c in the variable of scb->scb_sscb.sscb_ics.ics_rupass. Meanwhile, the username in scb->scb_sscb.sscb_ics.ics_rusername is correct.

So I wonder (1) is this normal for the empty password or may be bugs in GCA communication or Is designed like this?
(2) If i want to trace how these username and password are packaged and sent and interpretered at both client and server, which part of source codes should I read?

The OS user and password are validated by GCA and the Name Server prior to the connection being passed to the DBMS. Only the validated user ID is passed to the DBMS in this case. All this occurs within the GCF communications facility within GCA, GCN, and GCS modules.

Gordy

[zhenchen17]

Originally Posted by thoda04

In addtion to the usual "userid" and "password" keywords, the Ingres .NET Data Provider, beginning with release 9.2, does support connection string keywords of "dbms_user" and "dbms_password" to allow specification of an effective user and a DBMS password. These keywords are equivalent to the Ingres -u flag and the Ingres -P flags respectively. The Connectivity Guide can give more detail and restrictions on these keywords.

Internally, the MSG_CP_USERNAME, MSG_CP_PASSWORD, MSG_CP_DBUSERNAME, and MSG_CP_DBPASSWORD messages are used to pass the "userid", "password", "dbms_user", and "dbms_password" connection string values respectively to the DAS and DBMS.

Thanks Dave.
I refreshed my pdfs from SVN so that the explaination of the keywords Dbms_user and Dbms_password could be seen. Accroding to Gordy, userid and password are OS level while dbms_user and dbms_password are DBMS level. To my common sense of other .Net Database connection such as SQL Server, UserID/UID, Password/PWD are for the DBMS user. That is easy to think and write. But why Ingres .Net Provider are not the same? It looks like very strange...

Originally Posted by grant

OpenAPI will allow you connect as the process owner of the running application if co_username, co_password are blank. That assumes that the process owner is a valid Ingres user.

Thanks a lot Grant, you are always warm-hearted!
But I can't quite understand. If co_username and co_password are blank, the owner of process(May be called A) will be used for authorization in GCusrpwd function. If A is not a user name in DBMS, is that matters? What do u mean "That assumes that the process owner is a valid Ingres user."?

Originally Posted by thogo01

If this is an administrator, then the session will be given the effective user ID provided in IIAPI_CP_EFFECTIVE_USER.

Thanks very much Gordy.
If it is not a administrator(is that means the administrator of OS ?), for example a normal user, IIAPI_CP_EFFECTIVE_USER still will be used?
Also I wonder why should OS level authorization at GCA?

[zhenchen17]

I am still confused by the username and password, So I want to put up a case to improve my understanding:
Suppose there are two machines named client and server, here is the list of both machines' account:
clinet:
account name password role
client_admin client_admin_123 Administrator (OS) ---ca
client_user client_usr_123 normal user(OS) ---cu

Server:
account name password role
svr_admin svr_admin_123 Administrator (OS) ---sa
svr_usr svr_usr_123 normal user(OS) ---su

DBMS acount:
account name password role
dbms_admin dbms_admin_123 Administrator (DBMS) ---da
dbms_usr dbms_usr_123 normal user(DBMS) ---du

Also 3 vnodes could be build in client(each for sa da and du when asked for usrname/pwd): vsa, vda and vdu.
if someone want to connect to Server from client as specific DBMS account, Is the following combination are possible?
(For example, runing applications on a system login as client_user but should be connect to DBMS as dbms_admin)
client DBMS vnode co_username/co_password IIAPI_CP_EFFECTIVE_USER/IIAPI_CP_DBMS_PASSWORD connection
cu du vdu NULL/NULL du ?
cu da vdu sa du ?
ca da vsa NULL/NULL NULL/NULL ?


if someone could give any explaination, I will be appreciated...

Best
Chenzhen

[thogo01]

Originally Posted by zhenchen17

If co_username and co_password are blank, the owner of process(May be called A) will be used for authorization in GCusrpwd function. If A is not a user name in DBMS, is that matters? What do u mean "That assumes that the process owner is a valid Ingres user."?

If co_username/co_password are NULL, GCusrpwd() will not be called. The DBMS will be informed that the client user ID is A, A will be the user ID associated with the connection/session and therefore A must be a valid Ingres user who has been granted access to the desired database.

Originally Posted by zhenchen17

If it is not a administrator(is that means the administrator of OS ?), for example a normal user, IIAPI_CP_EFFECTIVE_USER still will be used?

To use the -u flag or IIAPI_CP_EFFECTIVE_USER parameter, the user ID of the client must have proper Ingres permissions. An Ingres administrator has these privileges. Depending on the system, certain OS administrators are also automatically added to the DBMS with Ingres admin permissions. If the client user ID does not have the correct permission, an error will be returned if an alternate effective user is requested.

Originally Posted by zhenchen17

Also I wonder why should OS level authorization at GCA?

Ingres user authorization is based on OS authentication and identification. Generally, a user runs a process on a machine and the connection to the database is made based on the OS user ID that owns the process. As an example, 'sql dbname' connects to the DBMS with whatever user ID is logged into the command shell. This is the same as when co_username/co_password are set to NULL.

A user may connect as another user but must either have permission in the DBMS to do so (the Ingres administrative permision to use -u effective user) or provide proof that they have the authority to act as the alias (provide the alternate user's password). Just as the OS user ID is used for the connection if no alias is used, so must the alias and password be a valid OS user and their password registered with the OS. Setting co_username/co_password is similar to using the Unix su command or logging in as the desired alias and then running 'sql dbname' as the alternate user ID.

Note that thus far only the user identity of the client has been discussed, either the owner of the client process or an OS alias authenticated with a password. This is validated in GCA because it is used prior to reaching the DBMS. Besides connecting to an Ingres DBMS, GCA connections may also be made to other DBMS servers (using gateways) and to non-DBMS servers such as the Name Server to setup and maintain vnodes. These non-Ingres connections require user authentication which does not involve the Ingres DBMS and are therefore based around OS authentication.

Vnodes add the ability to make network connections. An OS user ID and password for the remote system are used to provide proof that remote access is permitted and it is this user ID stored in the vnode which is used to make the connection to the target server. This is similar to using telnet or rlogin to access the remote machine and then running 'sql dbname' directly on the remote machine.

Ingres adds additional user identification capabilities. A DBMS password may be assigned to an Ingres user which is independent of the OS. As a convienience in database administration, Ingres administrators are granted the permission to act as other Ingres users using the -u effective user flag. Ingres verifies that the client user ID (OS user ID) has the correct administrative permission and then permits the effective user ID of the session to be changed.

The effective user flag is not generally used by applications. An administrator uses the -u flag when running terminal monitor and other utilities to act for other users.

Gordy