Question: Is the CONNECT INTERNAL command in Oracle 11g still valid, when connecting from a PC client to an Oracle Database server?
In Oracle Database 11g, the CONNECT INTERNAL command is no longer valid. It has been deprecated and replaced with the
SYSDBA and
SYSOPER
privileged connections. These changes were made to increase security and offer more granular control over database administrative roles.
To connect as an administrative user, you need to use one of these privileges. The syntax is as follows:
CONNECT username/password AS SYSDBA;
or
CONNECT username/password AS SYSOPER;
Here, the username should be replaced with the name of a user that has been granted the SYSDBA or SYSOPER privilege, and password should be replaced with the corresponding password for that user. If you are using an Oracle Network (through SQL*Net from a client machine) to connect to your database, then the network service name would also need to be included, like so:
CONNECT username/password@service_name AS SYSDBA;
The service_name should be replaced with the name of the Oracle Net service you are connecting to. This service name would be defined in your tnsnames.ora file.
It's important to note that connections as SYSDBA or SYSOPER allow you to perform privileged operations, so these should be used sparingly and securely. Always remember to follow the principle of least privilege, assigning only the necessary permissions required for a user to fulfill their role.
CONNECT INTERNAL deprecated
Assuming that everything has worked for you so far, you are now ready to connect to your database from your client PC.
If you are doing all this on one machine the process is exactly the same. Up until now, you have always used the
following, simple form of the CONNECT command to connect to a database from the same machine:
CONNECT INTERNAL
The more general form of the command, and the one that you need to use when connecting remotely, is:
CONNECT username/password@service
The username and password identify you to Oracle as a valid user.
Connecting to a service as the Internal User
It is possible to connect to a database over Oracle Net as the internal user. To do this for the COIN database, you would use the
following command:
CONNECT INTERNAL@coin
If you try this command from the database server itself, it will very likely work. However, if you try this command from a remote
PC, you will probably get the results shown in this example:
SVRMGR> connect internal@coin Password:
Because you are connecting remotely, Oracle will not let you connect as the internal user without first authenticating you. This
is because the internal user can do anything to the database. You can't just let anyone connect that way, especially not over
a network. It would be a horrible security risk. Oracle authenticates you as the internal user by asking you for a special
password known as the internal password[1]. You have not created one yet, at least not as
part of this course, so you can't connect this way. You already have a regular password, but the internal password is an
entirely different thing.
The next module covers this exact topic. When you are done with it, you will be able to connect remotely as the internal user in
order to start and stop the database, or do whatever else you may need to do.
connect remotely as the internal user
It is possible to connect remotely as the internal user, but you will not be able to do that until you have created a password file.
For now, you can connect as the user named system. This is the default DBA username that you always get when you create a new database. You should know the password, because you set it yourself back in Course 1, Database Creation and Architecture, just after creating your database. The service[2] parameter in the above example corresponds to a Oracle Net service name entry in your tnsnames.ora file. Thus, to connect to your coin database as the user system, using the default password of manager, you would issue this command from Server Manager:
CONNECT system/manager@coin
Go through the following series of images to see how this works using the COIN database as an example.
Now for a trip down memory lane.The Oracle Server Manager has been deprecated since Oracle 9i.
Run the same commands listed below using SQL* Plus or SQL Developer.
Connecting a PC to a database
1) We start at the Windows command prompt
2) We enter the svrmgrl command
3) Here you see the Server Manager command prompt. You also see a TNS protcol adapter error. I set this up on purpose so that you would see it.
4) Here the CONNECT command has been entered. Notice that it specifies the service named COIN.
5) The CONNECT command succeeds, and you are now logged into the COIN database as the system user.
Connect Oracle Net Service - Exercise
Now that you have seen it, go ahead and try it yourself. Click on the Exercise link below to connect to your COIN service.
Connect Oracle Net Service - Exercise
[1]internal password: The password that you need to use when you issue a CONNECT INTERNAL command from Server Manager.
[2]service: NT.A service under Windows NT is software that runs in the background, independently of any logged on user.
A Oracle Net service is a usually a database instance, but could be some other software, that is accessible via Oracle Network Services.
