Lesson 10	Password files and the data dictionary
Objective	List the administrative users in the `COIN` database.

Password Files and Data Dictionary

Using Data Dictionary View

You can find out which users in a database have been granted SYSOPER or SYSDBA privileges by looking at a data dictionary view named v$pwfile_users. This view is not usually accessible to non-privileged users, but if you log in as SYSTEM, INTERNAL, or SYSDBA, you should be able to see it. The following example shows how a list of password file entries may be generated:

SQL> connect system/manager as sysdba
Connected.
SQL> select * from v$pwfile_users;
USERNAME                      SYSDB SYSOP
------------------------------ ----- -----
INTERNAL                       TRUE  TRUE
SYS                                TRUE  TRUE
SYSTEM                          TRUE  FALSE
3 rows selected.
SQL>

INTERNAL and SYS users

When you create the password file, the INTERNAL and SYS users are placed there automatically. The SYSTEM user is there, in this example, because we granted SYSDBA privileges to a SYSTEM user in an earlier lesson.
The two columns at farthest right are character string columns that contain either “TRUE” or “FALSE,” depending on whether a user has the corresponding privilege. The column names are really SYSDBA and SYSOPER, but the headings are truncated because Server Manager (and SQL*PLUS) truncates the names to match the size of the column, which is five characters. The SYSTEM user, in this example, has the SYSDBA privilege (TRUE), but not the SYSOPER privilege (FALSE). The INTERNAL and SYS users have both privileges:

SYSDBA and
SYSOPER.

If a shared password file must be used, ensure that the password for SYS is at least 12 characters long and includes a combination of upper- and lowercase alphabetic, numeric, and special characters to fend off a brute-force attack.
A value of EXCLUSIVE binds the password file to only one database, and other database user accounts can exist in the password file. As soon as the password file is created, use this value to maximize the security of SYSDBA or SYSOPER connections. The dynamic performance view V$PWFILE_USERS lists all the database users who have one of the six available privileges, as shown here:

SQL> select * from v$pwfile_users;