User Authentication
Each Vector user must be defined in the master database. For example, the installation owner creates a user in iidbdb:
CREATE USER fred WITH PASSWORD = 'secret';
Vector can use either of the following methods to authenticate users:
• Through the operating system (OS) account and password together with the corresponding user object definition in the master database.
• By passing the user name and password directly to the DBMS Server for DBMS authentication against the list of Vector users and their passwords. If dbms_authentication is enabled for the server (which it is by default for new installations), the user does not have to be defined as an operating system user.
Ingres Net can be configured to allow users access to remote nodes directly (through an installation password) or by providing a user name and password. The user name and password can be authenticated by the DBMS (if the server has DBMS authentication enabled), or against a local OS user account. The password is encrypted when sent across the network.
User Password
A password can be specified as part of the user definition. The usage of the user password depends on whether DBMS authentication is enabled.
If DBMS authentication is enabled, the user name and password supplied by the connection attempt must match the user name and password defined in the user definition. If no password is supplied by the connecting application, the connection attempt fails.
If DBMS authentication is not enabled, the DBMS password operates as a second level of password after the initial connection is established (using the configured GCF security mechanism, such as authentication against an OS user and password). In this case, the application sends the DBMS password in the clear, after the connection is established. If the application does not provide a DBMS password, the DBMS server asks the client to prompt for one; if prompting is impossible, the connection attempt fails.
Note: If a user with the Security privilege starts a session using the –u flag to impersonate another user, the real user’s password--not the impersonator’s--is required.
A user with the CHANGE_PASSWORD privilege is permitted to change their own password; to do so, however, they must supply their old password. A user with the maintain_users privilege can change the password of another user in addition to changing the method of password validation or removing the password.
Connecting to a Database
The syntax for connecting to a database depends on whether the target database server is local or remote.
Connecting to a Local Server
The following connection examples assume that the configuration parameter dbms_authentication=on for the server.
A user logged in locally (on the server machine) can connect as follows:
sql dbname
This assumes that the user is defined WITH DBMS_AUTHENTICATION='OPTIONAL'. Even if the user has a DBMS password, neither password prompt nor DBMS password validation occurs. If the user is defined WITH DBMS_AUTHENTICATION='REQUIRED', the connection attempt fails because no user name or password was supplied.
A user can also connect, either as himself or as another Vector user, using DBMS authentication. He can use the +user option or the local-vnode syntax:
sql +user dbname
The user is prompted for the password and connects as the logged-in OS user.
sql +user=fred dbname
The user is prompted for the password and connects as user fred. Fred need not be an OS user.
sql @[fred,secret]::dbname
The user enters the password on the command line. Fred need not be an OS user.
Linux: Enclose the connection string in single quotes: sql '@[fred,secret]::dbname'
Note: If dbms_authentication is not enabled on the server, the last two examples will work, except that fred would have to be an OS user, and the password would have to be fred's OS password. If fred has a DBMS password defined, with dbms_authentication off, the server requests a password prompt for the DBMS password (thus, the +user=fred example might get two password prompts).
The +user option does not conflict with the effective user option (‑u). For example, if user fred has DB_ADMIN privileges for the database, he can connect and authenticate as himself, but then run the session with a different effective user:
sql +user=fred –u ralph dbname
The user (fred) is prompted for his password, and fred and password are authenticated by the DBMS. After the session is established, the effective user (ralph) is set.
Connecting to a Remote Server
If connecting to a remote server where dbms_authentication=on, a user can use either a vnode or dynamic vnode syntax:
sql vnode::dbname
The user name and password in the vnode definition are used.
sql @host,tcp_ip,VW[fred,secret]::dbname
The user name and password are entered on the command line.
Linux: Enclose the connection string in single quotes:
sql '@host,tcp_ip,VW[fred,secret]::dbname'
sql +user=fred @host,tcp_ip,VW::dbname
Prompts for password. Because the +user flag is used, the [username, password] syntax cannot be specified on the dynamic vnode.
Connections established through API-based applications such as ODBC, JDBC, and .NET Data Provider directly specify a user name and password. A DBMS password sent by such an application to a dbms_authentication enabled server is meaningless and ignored.
Connecting to a Non-UTF8 Server that Does Not Support Transliteration
When establishing a connection from a Vector instance to a non-UTF8 server that does not support transliteration (such as an Ingres instance that uses a native character set), you must declare the character set used for the connection.
The character set specified must match or be compatible with the character set of the target server instance. So if you are connecting from a Vector client on Linux to an Ingres instance on Windows, you can specify WIN1252, which matches the character set on the Ingres for Windows installation.
The character set can be declared in a vnode or in the connection string by using the connection attribute "character_set" (or the shorter "charset"), using the following format:
@host,port;character_set=charset[user,pwd]::dbname
where port is the listen address of the instance on the remote node.
For example, the following dynamic vnode specification starts the terminal monitor and uses the WIN1252 character set to connect to the inventorydb database:
sql @hosta,VW;character_set=win1252[Susan,mypassword]::inventorydb