Remote DBA Authentication
CONNECT INTERNAL for the purposes of carrying out ultra-privileged DBA operations. That command has been deprecated, so what has since replaced it?
Introducing Consistency
The answer to the question is CONNECT username/password AS SYSDBA and CONNECT username/password AS SYSOPER. It turns out that CONNECT INTERNAL was earmarked for deprecation since the days of ORACLE 7. The reason was in order to introduce consistency across all ORACLE operating system platforms. For example, under Windows, one was able to issue CONNECT INTERNAL/password but not under Unix.
To keep things uniform, all forms of the CONNECT INTERNAL syntax were deprecated in favour of the new scheme. As far as the end result is concerned, there is absolutely no difference between CONNECT INTERNAL and CONNECT username/password AS SYSDBA. A user connecting as a SYSOPER gains only the permissions to start up, shut down, mount, and dismount the instance.
With all that in mind, onwards to the subject at hand: remote authentication of DBAs and operators. If it is discovered that it is possible to connect as a SYSDBA only on the host where the database server resides, the chances are that the user is missing an entry in the ORACLE database's password file. This file, often built during database creation, is always consulted during authentication involving the computer network. Local access uses operating system authentication as an alternative and bypasses this file altogether. This password file is maintained using the orapwd utility (figure 1.0).
Figure 1.0
$ orapwd file=filename password=sys-password entries=max_users force=y
The usage above forces the creation of a new password file, overwriting the existing file if necessary, and initialises it with an entry for the SYS user. In addition to creating the password file, it is necessary to ensure that the database parameter REMOTE_LOGIN_PASSWORDFILE is set up as either EXCLUSIVE or SHARED, whichever is applicable. A change to this parameter necessitates recycling the instance (a shutdown followed by a startup).
Since a newly created password file will be initially set up for the SYS user only, DBAs and operators must be added separately. This is fairly straightforward. For example, connect to ORACLE as a SYSDBA and perform the following (figure 1.1):
Figure 1.1
SQL> GRANT sysdba TO mildred; SQL> GRANT sysoper TO george;
The above example ensures ORACLE creates password file entries for the users mildred and george. Subsequent to this, ORACLE will synchronise any future changes to their password with the file. They should also find that it is now possible to connect as SYSDBA or SYSOPER over the network.
Here comes the SQL pearl: to verify who has an entry in the password file, issue the following query (figure 1.2):
Figure 1.2
SQL> SELECT * FROM v$pwfile_users ORDER BY username; USERNAME SYSDBA SYSOPER ------------------------------ ------ ------- GEORGE FALSE TRUE MILDRED TRUE FALSE SYS TRUE TRUE
Comments
1 comment postedI have since found that, under Unix and Oracle 11g, an operating system user is no longer required to be a member of Oracle's dba group in order to connect as SYSDBA.