Administrator privileges, authentification and password files
The Oracle group SYSDBA can perform the following:
- Perform STARTUP and SHUTDOWN operations
-ALTER DATABASE: open, mount, back up, or change character set
-CREATE DATABASE
-CREATE SPFILE
-ARCHIVELOG and RECOVERY
Includes the RESTRICTED SESSION privilege, effectively, this system privilege allows a user to connect as user SYS.
The Oracle group SYSOPER can perform the following:
- Perform STARTUP and SHUTDOWN operations
- CREATE SPFILE
- ALTER DATABASE OPEN/MOUNT/BACKUP
- ARCHIVELOG and RECOVERY
Includes the RESTRICTED SESSION privilege, this privilege allows a user to perform basic operational tasks, but without the ability to look at user data, the schema is public.
The two methods that are available for authenticating database administrators are Operating system (OS) authentication and password files.
These methods replace the CONNECT INTERNAL syntax provided with earlier versions of Oracle. CONNECT INTERNAL is no longer allowed.
You can connect using password file authentication, provided the database has a password file and you have been granted the SYSDBA or SYSOPER system privilege.
If the server is not using a password file, or if you have not been granted SYSDBA or SYSOPER privileges and are therefore not in the password file, you can use OS authentication. On most operating systems, OS authentication for database administrators involves placing the OS username of the database administrator in a special group, generically referred to as OSDBA.
Whichever method you use connect as sys in the following way:
sqlplus /nolog
connect / as sysdba
startup
or simply
sqlplus "/ as sysdba"
startup
To enable operating system authentification you must do the following:
- Create an operating system account for the user.
- Add the user to the OSDBA or OSOPER operating system defined groups.
- Ensure that the init parameter, REMOTE_LOGIN_PASSWORDFILE, is set to NONE (default).
Connecting Using OS Authentication
CONNECT / AS SYSDBA
or
CONNECT / AS SYSOPER
Preparing to Use Password File Authentication
- If not already created, Create the password file using the ORAPWD utility: ORAPWD FILE=filename PASSWORD=password ENTRIES=max_users
- Set the REMOTE_LOGIN_PASSWORDFILE initialization parameter to EXCLUSIVE.
- Connect to the database as user SYS (or as another user with the administrative privilege).
- If the user does not already exist in the database, create the user. Grant the SYSDBA or SYSOPER system privilege to the user. This statement adds the user to the password file, thereby enabling connection AS SYSDBA or SYSOPER.
Connecting Using Password File Authentication
CONNECT scott/tiger AS SYSDBA
If you are a UNIX user in the group dba you can connect as 'sys as sysdba' without a password - as before.
To view the users who have SYSDBA or SYSOPER privileges by querying the V$PWFILE_USERS view.