8.3 Connecting to the Database Without a Password There are three approaches commonly used to enable a user to connect to a database without explicitly typing a password. All of the approaches take advantage of the OPS$ account. With the first approach, the account is created with the actual prefix "OPS$" used in the account name , and a password is assigned to the user. The user can connect from the operating system level to a database by using just a "/" in place of the username/password string. The user does not have to type his username or password to gain access to the database. If, however, the user wants to connect to the database from a remote client, he can use his username and password and connect successfully. The advantage of this approach is that the user can hide both his username and password when using a command-line connection and still retain the ability to use SQL*Net to connect to the database from a client machine. With the second approach, you set the INIT.ORA parameter OS_AUTHENT_PREFIX="", and the actual username without the "OPS$" prefix is used in creating the user account. No password is assigned to the account. Instead, the account is created with the IDENTIFIED EXTERNALLY option, as described later in this chapter. The user can connect to the database from the operating system level but has no password to enable remote connection from a client machine. The only difference between the second and third approach is the use of the INIT.ORA parameter REMOTE_OS_AUTHENT set to "TRUE" to enable an "identified externally" account to connect to the database from a remote client. The disadvantage of this form of access is that database security could potentially be compromised by someone other than the intended user accessing the database without needing to know either a username or a password. 8.3.1 OPS$ Accounts As we mentioned in the last section, Oracle provides the ability to access the database from the operating system level without providing a username and password. When this mechanism, set with the IDENTIFIED EXTERNALLY option, is used, Oracle views the operating system authentication as "good enough" and allows access to the database based on the operating system account name. The advantage of using an account that relies on operating system verification is that there is no need to type a password on the operating system command line. This is a great aid to a developer who is testing code from an operating system prompt and does not want his password to be exposed. This approach is also excellent for running a command procedure for an application without having to embed a password in the procedure and provide a possible security breach. The down side to this approach is one that was cited earlier. If a developer has an "identified externally" account and does not lock his terminal when he walks away from his machine, anyone passing by has access to his database account without the need to know a password.  | In most cases, the "identified externally" approach should only be used on development and test databases. | | 8.3.1.1 Identified externally accounts The only way the "identified externally" approach may be safe to use on a production system is from an account created in a way that ensures that the user cannot get to the operating system level. In an OpenVMS system, for example, keeping users from reaching the operating system level can be accomplished by having the system administrator create a "captive" account. In an OpenVMS system, the operating system treats an account that has been flagged as "captive" as having no privilege at the operating system level. Thus, if a user attempts to break out of the application he or she is using and tries to get to the operating system level, the user's current process will be terminated . The user will be automatically logged off the account he or she is using. In a UNIX environment, the user could be placed in a " trusted shell" (tsh). The login profile ( .profile or . cshrc /.login ) could include a START command for an application, followed by an EXIT command. In this manner, the user's application is started at login, and access to the operating system is controlled or eliminated. When the application exits, the login session also exits. The .profile for the account would have to be customized to capture all control characters so the user could not break out of the login process. In UNIX systems, this is easily done through the TRAP statement. Keeping a user from accessing the operating system level can also be accomplished from a normal shell, but you would have to trap all escape routes to keep the user from getting to a shell prompt. Even with tight controls on the application account, the roles and privileges for the account would have to be strictly evaluated to ensure the least possible jeopardy to the production system. 8.3.1.2 OS_AUTHENT_PREFIX and OPS$ Within the database initialization file ( INIT.ORA ), the OS_AUTHENT_PREFIX parameter can be set to the prefix, if any, that will be used to identify a database that will rely on operating system verification of the user alone. As delivered, the Oracle RDBMS default value for OS_AUTHENT_PREFIX is set to "OPS$". Therefore, the convention used to identify an account that is able to log on to the database without using a username and password is to precede the username with the value "OPS$". For example, if the operating system account name is mary , the database username would be ops$mary , and the CREATE statement for the database account would be: CREATE USER ops$mary IDENTIFIED BY abc75!d... Note the use of a password assignment in the above statement. The password is required by the syntax of the statement, but because this is an OPS$ account, the user will not have to supply the assigned password at login. By default, an account created with the OPS$ prefix can be used to access the database without presenting a username/password from an operating system account with the matching name (minus the OPS$ prefix). Therefore, if the DBA wants an account that can also be used from a client machine to SQL*Net to the database, and can be used from the operating system without using a username and password, this convention will work very nicely . When the user connects using SQL*Net, he types the username "OPS$<username>" and places his password in the requested "password" area. When the user is logged directly in to the operating system account, he uses just "/" as his username and password. For example, from a client PC, the user mary would log on using: username: ops$mary password: abc75!d From the operating system, the user would log on using: sqlplus / 8.3.1.3 OPS$ in version 7 With version 7, the "OPS$" account took on two possible approaches with very different connotations . The DBA could create a user account and dub that account "identified externally" as follows : CREATE USER mary IDENTIFIED EXTERNALLY ... The DBA would also have to modify the INIT.ORA file to include the following line: OS_AUTHENT_PREFIX = "" to let Oracle know that the operating system prefix was to be blank and that the default for authentication should be just the user's name without the "OPS$" prefix. By creating the user this way and declaring the operating system prefix to be blank, the DBA eliminates the ability to assign a password to the account since, if a password is assigned to the account, the ability to connect to the database and rely on the operating system authentication is negated. In all cases where an account is allowed to connect without a password, the syntax for connecting to the database is: sqlplus / If the account is set up as "identified externally," no password can be assigned and the user will not be able to use the account in a dual manner. He will not be able to use the account both as a SQL*Net account with a password and as an operating system account without a username and password. 8.3.1.4 Another approach Another available approach is to use the "identified externally" account creation and set the INIT.ORA parameter REMOTE_OS_AUTHENT to "TRUE". This approach enables an account that has been created using the same name as the client account to connect to the database via SQL*Net without the use of a password. Let's say the account on a PC is identified with the name mary and an account is created in the SMOKE database for "mary identified externally." mary can now select SQL*Plus from her PC start/program menu and, at the username prompt, type "/@SMOKE" and connect to the SQL> command line in the SMOKE instance. If mary is logged on directly to the operating system on which SMOKE is running, she can still just type "sqlplus /" to access the database. 8.3.1.5 Two problems with REMOTE_OS_AUTHENT In the scenario in the last section, the security danger is that mary will walk away from her PC without locking her terminal. Anyone would then be able to access the database without knowing mary 's username or password and have mary 's privileges on the system. The second danger is that you are trusting a remote operating system over which you may have no control. All somebody needs to do is determine an Oracle user in your database who has the desired role including DBA with an account that has been IDENTIFIED EXTERNALLY. Then, the person creates an account with the same username as the privileged database account, fires up SQL*Plus, and WHAMMO! That person is in your database with the highest possible privileges and there is nothing you can do except set REMOTE_OS_AUTHENT=FALSE. An alternative method you can use in Oracle8 for account authorization the ORAPWD utility is described next . 8.3.2 The ORAPWD Utility Another method Oracle uses for account authentication is the ORAPWD utility (named ORAPWD80 under Oracle8). This utility is used to create a password file to enable accounts from SQL*Net clients to connect to an Oracle database without the use of a password. It's used by the Oracle Enterprise Manager (discussed in Chapter 13) to connect to remote databases to perform various DBA tasks . 8.3.2.1 Steps to setting up the password file There are two steps involved in establishing the environment to use a password file. The actual file must be created using the ORAPWD utility, then a value must be assigned to the INIT.ORA variable REMOTE_LOGIN_PASSWORD. To see the syntax for the ORAPWD utility on any operating system with version 7 or higher, type the command ORAPWD (in lowercase on a UNIX or Windows NT system) at the operating system prompt. You should see the following information: Usage: orapwd file=<fname> password=<password> entries=<users> where file - name of password file (mand), password - password for SYS and INTERNAL (mand), entries - maximum number of distinct DBA and OPERs (opt), There are no spaces around the equal-to (=) character. The syntax used to create the password file should be the same from operating system to operating system. [1] [1] We include the information on how to get the operating system to present the syntax here because we found that having the syntax available when we went to create the password file was very helpful and made the process easier. Parameters have the following meanings: - FILE
-
Must be a fully qualified path name; (i.e., the name must include the disk name, directory name, and file name). The file parameter may have other requirements established for it within the operating system-specific installation guide. - PASSWORD
-
Establishes the password used for the sys and internal users. If you alter the user to change the password established in the password file, the password will be changed both in the database and in the password file. - ENTRIES
-
Used only when the REMOTE_LOGIN_PASSWORDFILE parameter in the INIT.ORA file is set to "EXCLUSIVE." This value is the maximum number of users that can connect to the database with SYSDBA or SYSOPER privileges. Since the file must be recreated if the maximum number of allowable users needs to be exceeded, the number might be set high on initial creation. However, the person creating this file may want to carefully examine the ramifications of allowing several people to have DBA authority within their databases. To use the ORAPWD or ORAPWD80 utility, you must first decide where you want the created password file to be placed for each database and what password(s) you will use. You must also decide whether you want to set up one shared password file or enable an exclusive file for each database available on your system. If a shared file is used, the accounts that access the database(s) can only connect as sys or internal . This means you cannot restrict the actions the account can perform. If an exclusive file is used, the accounts that access the database can be defined by creating the users in that database and granting them SYSDBA or SYSOPER privileges. Thus, when you create an exclusive password file, you can control to a finer granularity the privileges available to the users granted access to the database. When you create an account that is granted SYSDBA and/or SYSOPER, the password assigned to the account is also recorded in an encrypted format in the password file. The second step in establishing an environment in which an account can access the database using the password file is to assign a value to the INIT.ORA parameter, REMOTE_LOGIN_PASSWORDFILE. The three possible values are: - NONE
-
No password file will be used - EXCLUSIVE
-
The password file will be used by one specific database - SHARED
-
The password file will be shared among more than one database | | In order to make entries in the ORAPWD file, the value for REMOTE_OS_AUTHENT must be set to "EXCLUSIVE." | |  | 
