6
Connecting to a Database

This chapter describes how to connect to a database from the client machine. Specific topics discussed are:

• Connecting to a Database from a Client Machine
• Start Server Services
• Connect to a Database

Connecting to a Database from a Client Machine

Client workstations and other servers connect to a network listener with a service name when logging onto an Oracle Server. The appropriate Oracle Protocol Adapter is used when the database alias (or service name) used to request a connection specifies that protocol in the configuration file.

To connect to a database from a client machine:

1. Start Server Services
2. Connect to a Database

Start Server Services

To start server services, perform these tasks:

• Starting Oracle Names (if applicable)
• Starting the Listener

  Note: See Chapter 5, "Administration Tasks" for detailed instructions on how to perform these tasks.

Connect to a Database

Net8 provides different methods of connecting to a database from the client machine:

• Using Server Manager
• Using SQL*Plus
• Using Different Naming Methods

Using Server Manager

To connect to a database using Server Manager:

1. Enter the following for other operating systems:

   SVRMGR30
   

   The SVRMGR> prompt appears:

2. Connect to the database. Enter:

   SVRMGR>CONNECT SYSTEM/MANAGER@service_name
   

   or

   SVRMGR>CONNECT SYSTEM/MANAGER@global_dbname
   

   The service_name is the database alias you defined earlier and the global_dbname is the global database name you defined in the LISTENER.ORA for the Oracle Names Server.

   Server Manager returns a Connected message to the screen.

3. Issue SQL commands.
4. When done, enter:

   SVRMGR>EXIT
   

   Exiting Server Manager closes the database connection.

Using SQL*Plus

To connect to a remote database using SQL*Plus:

1. Start the SQL*Plus application:

   From the Start menu, select Programs > Oracle for Windows NT or Windows 95 > SQL*Plus 8.0.

   A Log On dialog box appears.

2. Enter:

   User Name	SYSTEM
   Password	MANAGER
   Host String	(database alias from TNSNAMES.ORA or global database name from LISTENER.ORA)

3. Choose OK.

   The Oracle SQL*Plus window appears with a SQL> prompt.

4. Issue SQL commands.
5. When done, enter:

   SQL>EXIT
   

   Exiting SQL*Plus closes the database connection.

Using Different Naming Methods

Use the Oracle Net8 Assistant to specify the use and order of naming methods in your profile. The naming methods available are:

• Host Naming
• Local Naming
• Centralized Naming (Names Server)
• External Naming

  Note: See Net8 Administrator's Guide for a detailed discussion of name resolution.

Host Naming

Host Naming can only be used in a TCP/IP network environment. No client configuration is necessary for using the Host Name Adapter. This naming method is enabled by default. You can disable (or enable it again) using Oracle Net8 Assistant. See the section in Chapter 4, "Configuring the Network" for instructions on how to enable or disable Host Naming.

The parameter GLOBAL_DBNAME="HOSTNAME" is specified in the LISTENER.ORA configuration file on the server, where HOSTNAME is the TCP/IP host name or alias of the server system. During the installation of Net8, the GLOBAL_DBNAME of the default database is set to the system name. To establish a client session using host naming, the client needs only to specify the system name of the server when the connect string, database name, or service name is requested.

If you need to support multiple SIDs on the same server, or you want to change the GLOBAL_DBNAME

• edit the definition of the parameter in the LISTENER.ORA file on the server.
• configure clients TCP/IP components such that GLOBAL_DBNAME is the TCP/IP alias for the server system.

  Note: You must use the default listener port 1521 in order to use host naming.

Local Naming

Local naming resolves a service name by using information configured and stored on each client. The local configuration file is called TNSNAMES.ORA.

Use Oracle Net8 Assistant to enable local naming. See the section in Chapter 4, "Configuring the Network" for more information.

Centralized Naming (Names Server)

Centralized naming uses Oracle Names to resolve service names. Oracle Names stores names and addresses of all database services on a network on a Names Server. Connection requests are routed through the Names Server, which resolves the service name to a network address. The information is then returned to the client.

Use Oracle Net8 Assistant to enable Oracle Names. See the section in Chapter 4, "Configuring the Network" for more information.

External Naming

External naming refers to service name resolution by using a supported third-party naming service. Oracle Native Naming Adapters resolve service names stored in a native naming service. Net8 provides these external naming methods:

• Windows NT Networking Authentication Adapter
• NDS Authentication Adapter

Note: These Native Naming Adapters were previously included as part of the Oracle Advanced Networking Option (a separately available option of Oracle Networking Products).

Distributed Computing Environment (DCE) Cell Directory Services (CDS)

Note: DCE CDS is shipped as part of Oracle Advanced Networking Option. For information about DCE CDS, see Chapter 7, "Using the Advanced Networking Option".

Windows NT Networking Authentication Adapter

The Windows NT Networking Authentication Adapter allows clients connecting to an Oracle database on a Windows NT server to be authenticated at the operating system level. The database maps Windows NT user accounts to Oracle database accounts. By default, the authentication adapter does not perform any additional checks once it obtains the operating system user name. A user logged into a Windows NT server is automatically authenticated to use that database without entering an additional username and password.

Note: The operating system user name that is seen by the database is not prefixed with the Windows NT domain name for domain users by default. If qualification of all domain user accounts in the database with their Windows NT domain name is necessary, the Registry entry HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\OSAUTH_PREFIX_DOMAIN can be set to TRUE.

To connect with the Windows NT Authentication Adapter:

1. Install and configure Net8 on clients and servers.

   The Windows NT Authentication Adapter is automatically installed with Net8 on both the clients and server.

2. For both clients and server, use Oracle Net8 Assistant to enable the adapter. The Oracle Net8 Assistant modifies the SQLNET.ORA file to include the following entry:

   SQLNET.AUTHENTICATIONS_SERVICES = (NTS)
   

3. Save the changes.
4. Configure the clients to log in to the Windows NT domain to which that Windows NT server belongs or to a Windows NT domain that trusts the domain.

   If qualification of all domain user accounts with their Windows NT domain name is necessary, continue to Step 5. Otherwise, go to Step 6.

5. Set the NT Registry variable HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\OSAUTH_ENFORCE_STRICT to TRUE on the server to enforce the adapter to verify the membership of the user account of the operating system to one of the following Windows NT local groups:
   • ORA_SID_USER
   • ORA_SID_DBA
   • ORA_SID_OPER
   • ORA_USER
   • ORA_DBA
   • ORA_OPER

     Additional Information: See Appendix A, "Modifying Configurations" for instructions on how to modify the Windows NT registry.

6. Create a special Oracle Server account for the database you want to access on the machine where the Oracle Server database is installed. Do not create an operating system account.
   1. Open SQL*Plus on your server.
   2. Enter:

      SQL>CONNECT SYSTEM/MANAGER;
      SQL>CREATE USER ``OPS$[DOMAIN]\USERNAME IDENTIFIED EXTERNALLY'';
      

      if the registry entry HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\OSAUTH_PREFIX_DOMAIN

      is set to TRUE, or

      SQL>CREATE USER ``OPS$USERNAME IDENTIFIED EXTERNALLY'';
      

      if the registry entry is not set to TRUE (set to FALSE or not present in the registry).

      DOMAIN and USERNAME must be specified in uppercase.

      Note: The prefix OPS must match the parameter OS_AUTH_PREFIX in the INITSID.ORA file. For example, if you want to use the Kerberos Authentication Adapter, which requires OS_AUTH_PREFIX to be null, the user name should be specified as ``[DOMAIN]USERNAME IDENTIFIED EXTERNALLY''

7. Grant the privileges to this user account.

   SQL>GRANT role to ``OPS$[DOMAIN]\USERNAME'';
   SQL>GRANT CONNECT to ``OPS$[DOMAIN]\USERNAME'';
   

8. Establish a networking session between your client and Windows NT server.
   • If clients and server are in the same domain or in trusted domains, then operating system names are retrieved on the server will always work.
   • If clients and server are in unrelated domains, then the adapter may not be able to retrieve the user name dependency on user ID. In this case, user accounts and passwords must match on both server and clients. If a pre-existing connection exists between clients and server, the operating system user name retrieved by the adapter will be the user name of the pre-existing session, allowing for a connection.
9. Open SQL*Plus on your client.
10. Enter the following command to access an Oracle database:

    CONNECT /@service_name
    

    where service_name identifies the database alias.

NDS Authentication Adapter

The NDS Authentication Adapter enables client applications and users access a multi-server and multi-database network. A user logged into an NDS directory tree can be automatically authenticated to use an Oracle database in the same NDS directory tree, thus allowing a user access an Oracle Server without entering an additional username and password.

If the NDS Naming Adapter is also used, an NDS user can view the entire network under a single NDS directory tree.

Additional Information: See: Chapter 7, "Using the Advanced Networking Option" in for more information about NDS Native Naming Adapter Novell NetWare documentation for more information about NDS Oracle7 Getting Started for NetWare

Note: To use the NDS Authentication Adapter, you must run NetWare 4.1 or above with NDS.

To connect with the NDS Authentication Adapter:

1. Install and configure Net8 on client and server.
2. For both clients and server, open the SQLNET.ORA file using a text editor, and add the following entry:

   SQLNET.AUTHENTICATIONS_SERVICE = (NDS)
   

3. Log into the NDS tree.
4. Open SQL*Plus on your client.
5. Enter the following command to access an Oracle Server for NetWare database:

   	CONNECT /@service_name
   

   where service_name identifies the database alias.

Working with Security for the Named Pipes Protocol Adapter

If you use Named Pipes Protocol Adapter with Oracle Names Server or Connection Manager, set up the proper security permissions.

To set up the Named Pipes permissions:

1. Double-click Services in the Control Panel window.

   The Services dialog box appears.

2. Double-click OracleNamesService80 or OracleCMAdminService80.

   The Service dialog box appears.

3. Click:
   • This Account option button
   • ... button next to the This Account option button

   The Add User dialog box appears.

4. Select your login ID (user ID) from the Names list box, and click Add.

   The user ID appears in the Add Name field.

5. Choose OK.

   The Services dialog box appears with the user ID displayed in the This Account field.

6. Enter your password in the Password field.
7. Confirm Password.
8. Choose OK.

---