Database Reference
In-Depth Information
Then stop and start your database to instantiate the parameter. Once the password file is enabled, you can then
create database users and assign them the
sys*
privileges as required. For example, suppose you had a database user
named
DBA_MAINT
that you wanted to grant
SYSBACKUP
privileges:
$ sqlplus / as sysdba
SQL> grant sysbackup to dba_maint;
The syntax for using a password file to connect to a database is as follows:
$ sqlplus <username>/<password>[@<db conn string>] as sys[dba|oper|backup]
For example, using the
DBA_MAINT
database user, you can connect to the database with
SYSBACKUP
privileges
as follows:
$ sqlplus dba_maint/foo as sysbackup
Because you are providing a username/password and attempting to connect with a
sys*
level privilege (as a
non-
SYS
user), Oracle will verify that a password file is in place (for the local database) and that the supplied username/
password is in the password file. You can verify which users have
sys*
privileges by querying the
V$PWFILE_USERS
view:
SQL> select * from v$pwfile_users;
Here is some sample output:
USERNAME SYSDB SYSOP SYSAS SYSBA SYSDG SYSKM CON_ID
----------------- ----- ----- ----- ----- ----- ----- ----------
SYS TRUE TRUE FALSE FALSE FALSE FALSE 0
DBA_MAINT FALSE FALSE FALSE TRUE FALSE FALSE 0
OS aUtheNtICatION VS. paSSWOrD FILe
For local connections (made while physically logged on to the database server), operating system authentication
takes precedence over password file authentication. in other words, if you're logged on to an OS account that
is a member of an authenticated group, such as
dba
, it doesn't matter what you type in for the username and
password when connecting to a local database with
sys*
privileges. For example, you can connect as
sysdba
with a nonexistent username/password:
$ sqlplus bogus/wrong as sysdba
SQL> show user;
USER is "SYS"
The prior connection works because Oracle ignores the username/password provided, as the user was first
verified via OS authentication. however, a password file is used when you're not using OS authentication to
establish a privileged local connection or when you're trying to make a privileged connection to a remote
database via the network.
