Using Net
Connection to Remote Databases
In general, Ingres Net provides users with transparent access to remote databases. Only when the connection is first established must you specify the node on which the database resides and, in some circumstances, the type of server. After you are connected, you can work in the database as if it were local; no further reference to its location is necessary.
Note: If a default remote node is defined on the local instance, you do not have to specify a vnode name when you make a connection to that node. For information about using this feature, see
Default Remote Nodes.
Database Access Syntax--Connect to Remote Database
The syntax for accessing a remote database through an operating system-level command is:
command [auth_user] vnode::dbname
where:
command
Is any command used to invoke a Vector tool, such as sql.
auth_user
Specifies the user name and password used for connection authentication, whether through OS authentication, DBMS authentication, or another mechanism. This syntax is useful when using DBMS authentication because it allows password entry or prompting with simplified syntax.
The auth_user can be one of the following:
+user
Prompts for the password of the user.
+user=username
Connects as the specified user and prompts for password.
+user=username,password
Connects as the specified user and password. Password is exposed on the command line.
@[username, password]
Connects as the specified user and password. Password is exposed on the command line.
Note: The @[username,password] must be specified as part of the full database name including vnode. No arguments can appear between it and the node name. For example:
sql -F8f20.10 @[me,mypassword]nodename::mydb
vnode::
Is the remote node where the database resides. The two colons are required.
The remote node can be specified as either of the following:
@host+
vnode_name
Is the virtual node name that points to the connection data and authorization data necessary to access a particular remote instance.
dbname
Is the name of the database.
Examples:
Start the terminal monitor (sql) and connect using vnode "production" to the mydb database:
sql production::mydb
Connect as the logged in OS user. Prompt for the password:
sql +user production::mydb
Connect as user fred, and prompt for the password. Fred need not be an OS user:
sql +user=fred production::mydb
Connect as user fred and enter the password on the command line. Fred need not be an OS user:
sql +user=fred,secret production::mydb
Connect as user fred and enter the password on the command line. Fred need not be an OS user.
sql '@[fred,secret]production::mydb'
Dynamic Vnode Specification--Connect to Remote Database
When connecting to a remote database (using the vnode::dbname syntax), you can specify a dynamic vnode instead of a vnode name. The dynamic vnode specification includes the connection data, user authorization, and attributes that are associated with a remote node.
Note: A dynamic vnode can be used wherever a vnode is allowed, unless otherwise stated.
A dynamic vnode specification has the following format:
@host,protocol,port[;attribute=value{;attribute=value}][[user,password]]
Linux: In bash shell, enclose the semicolon in single or double quotes (so it will not be treated as a command terminator). Alternatively, place the entire dynamic vnode specification in single or double quotes.
@host
Identifies the network name or address of the node on which the remote database is located. The @ character is required because it identifies this specification as a dynamic vnode rather than a vnode name.
protocol
Identifies the network protocol to be used by the local node to connect to the remote node.
The valid value is: tcp_ip.
port
Identifies the listen address of the instance on the remote node.
The default Vector instances use the following symbolic ports:
Vector: VW
Vector in Hadoop: VH
attribute=value
(Optional) Is one or more additional connection, encryption, and authentication attributes for the connection, as described in
Vnode Attributes Configuration.
[user,password]
(Optional) Identifies the user name and password on the remote system.
Note: The user name and password must be enclosed in brackets.
Rather than specifying [user, password] in the dynamic vnode specification, you can use the +user username syntax before the dynamic vnode specification. The +user flag prompts for the password, which protects the password from exposure. For example:
sql +user=johnny @machine01,tcp_ip,VW::inventory
If the +user flag is used, you cannot also specify the [user, password] option as part of the dynamic vnode.
Examples of dynamic vnode specification:
Run the terminal monitor (sql) and connect to node hosta using protocol tcp_ip to remote Vector symbolic port VW. The login and password are Johnny and secretpwd. The remote database name is salesfact:
sql @hosta,tcp_ip,VW;[Johnny,secretpwd]::salesfact
Linux: sql '@hosta,tcp_ip,VW;[Johnny,secretpwd]::salesfact'
Establish a direct connection by using the connection_type attribute:
sql @hosta,tcp_ip,VW;connection_type=direct[Johnny,secretpwd]::salesfact
Start the terminal monitor, connect to node myhost01 as user johnny using TCP-IP protocol (the default), and prompt for the password:
sql +user=johnny @myhost01,VW::salesfact
Linux: sql '+user=johnny @myhost01,VW::salesfact'
Use of the SQL Connect Statement with Net
If you are using the connect statement in an application, connect to a database on a remote instance using the following syntax:
exec sql connect 'vnode::dbname[/server_class]'
Note: The vnode can be either a dynamic vnode specification (@host+) or a vnode name.
You must use the single quotes around the designation of the vnode and database names (and server class, if applicable). For example, assume that you have an application residing on “napoleon” that wants to open a session with the database “advertisers” on “eugenie.” The following statement performs this task (assuming also that “lady” is a valid vnode name for “eugenie”):
exec sql connect 'lady::advertisers';
Note that a server class is not specified in this statement; therefore the default server class defined on “eugenie” is used.
If the target database is accessed through an Enterprise Access or EDBC products, be sure to include the appropriate keyword for the server class. For example:
exec sql connect 'lady::advertisers/db2';
If the target database is accessed through Ingres Star, be sure to include the appropriate keyword for the server class. For example:
exec sql connect 'lady::advertisers/star';
When you are working over Ingres Net, you can use the -u flag with a command to imitate another user provided the User ID that you are working under on the remote node has the SECURITY privilege.
Commands and Net
You can run any of the following Vector commands against a remote database:
Note: The optimizedb command works across Ingres Net only if the client and server machines have identical architectures. Do not use this command across Ingres Net if the client and server have different architectures.
You cannot run the following Vector commands against a remote database:
User Identity on Remote Instance
A user’s identity when working on a remote instance depends upon the type of access authorized.
• When access to a remote instance is authorized using an Installation Password, users retain their local identities (User IDs) when working on the remote instance.
• When access is authorized through a user name and password (whether an operating system account or DBMS authentication) on the remote instance’s host machine, users take on the identity of this account when working on the remote instance.
In either case, the user’s privileges and permissions on the remote instance can differ from those on the local instance. For example, a user can have system administrator privileges on the local instance but only very general, low-level privileges and permissions on the remote instance. It is important to make sure that the privileges and permissions assigned to you on the remote instance are adequate for the work that you intend to perform.
User privileges and permissions are set up individually for each instance using Visual DBA or the CREATE USER statement. They apply only to the instance on which they are set up. For more information about this procedure, see the Security Guide.
Note: Using Network Utility or Visual DBA, you can access a list of users on Vector server nodes and establish a connection at the user level under a different user name. For more information, see Impersonating Another User in online help for either of these visual tools.
-u Command Flag--Impersonate User
You can use the -u command flag on a remote instance to impersonate another user provided your user ID on the remote instance has the SECURITY permission. (Typically, a system administrator has this privilege.)
This command flag has the following format:
-u user_ID
where user_ID is the user ID of the user you are impersonating.
Verify Your Identity
When impersonating a user using the -u command flag, you may need to verify your identity.
To verify your identity
Use the following command:
dbmsinfo ('username')
The user ID that you are working under is displayed.