Jan 17 min read

Login to Oracle Database using sqlplus "/ as sysdba"

When a user inputs their credentials, they will first get authenticated. If nothing is wrong with the user’s OIM entitlements, that person receives authorization to log in to the database. For this to happen, much is built into the process and code. Let’s explore what happens behind the curtains!

Before you connect to Oracle Database, Do you know what happens?

ORACLE DATABASE User Authentication System, Illustration by Shashi Kallae.

The Operating System authentication is a method where users authenticate outside the database instance from one of the operating system groups. Membership is one of the Operating System groups where the users trying to connect to the database get authenticated without resupplying the credentials to the database.

SYSDBA and SYSOPER are the two main administrative privileges in the Oracle Database echo system. They are special because these privileges exist outside the Oracle database and allow access to it, even when the database is down or offline. The user password may exist in the password file, or if the user is a part of the Unix or Linux OS Oracle database groups (OSOPER, OSDBA), they can connect to the database passwordless.

SYSOPER

SYSOPER Privilege comes with the below operations to perform basic database administration tasks such as,

Create database.
Instance Startup, Mount, and database open.
Instance Shutdown, Unmount, and Database close.
Alter database Backup, place database in archive Log mode, and Recover.
Drop a database and remove it from archive log mode.
Create SPFILE.
Allows users to do basic operational tasks without the ability to look at the user data.

SYSDBA

SYSDBA privilege includes all the access similar to that of SYSOPER. On top of it, SYSDBA also has access to all the data dictionary tables (dba views).

Start-up and shut down the Oracle database.
Create and drop an Oracle database.
Open and mount a database.
Place an Oracle database in archive log mode or remove it from the archive log mode.
Alter the database, such as unmounting, backing up, or changing the character set.
Create a SPFILE.
Alter the database recovery.

Remote Connections

Remote connections are those connections when an Oracle database is configured to allow remote DBA operations to the users. In this scenario, the user must supply the password to connect as SYSDBA.

Prerequisites for the Remote Connections

Set up a password file for the Oracle database on the database server.
If this is a RAC database setup, then make sure to set up the password file on each node.
Configure init.ora parameters accordingly.
Configure any required SQL*Net options.

List the users who have SYSDBA privileges.

In this scenario, sqlplus is used as a client to connect to the Oracle database.

The below SQLs are just examples (Disclaimer: Use at your own risk. They are just for educational purposes only).

SQL>conn sys/password as sysdba
SQL>select * from v$pwfile_users;

Substitute the userID with the username of an Individual.

select * from DBA_SYS_PRIVS where grantee = 'userID';
select * from DBA_ROLE_PRIVS where grantee = 'userID';

If you are looking for all the views related to the privileges, then use the below,

SQL>conn sys/password as sysdba
SQL>select table_name from dict where table_name like '%PRIV%';

To find the users who have DBA roles,

SQL> select * from dba_role_privs where granted_role='DBA';
GRANTEE   GRANTED_ROLE ADM DEF
--------- ------------ --- ---
SYS       DBA          YES YES
SYSTEM    DBA          YES YES

orapwd Utility (Password Authentication - UNIX/LINUX)

Password Authentication. Illustration by Shashi Kallae.

Critical Warning: It must be noted that executing orapwd utility on other users gives them superuser privileges similar to SYSOPER and SYSDBA.

The Oracle orapwd utility grants users super user privileges similar to SYSDBA and SYSOPER. When a new database is installed and configured, the SYS user acquires these privileges by default. Running this utility on other users enables them to remotely connect to the Oracle database via SQL*NET.

Before:

Set your ORACLE_HOME env variables
cd $ORACLE_HOME/dbs

SQL> select username,sysdba from v$pwfile_users;

USERNAME   SYSDB
---------  -----
SYS        TRUE

orapwd Utility:

An Oracle password file controls password protection. The file name is important and should be specified in the below-mentioned ways. You should create this file when the database is

shut down or offline.

Model: 
orapwd file=filename  password=password entries=max_users
(or)
orapwd file=$ORACLE_HOME/dbs/orapw$ORACLE_SID password=mypasswd

To change a password, you need to recreate the password file.

shut down the database.
Rename the $ORACLE_HOME/dbs/orapw$ORACLE_SID file.
Issue a new orapwd command with a new password.

When the database is open or in use, use the below SQL to change the password. This will propagate the hashed password to the password file.

alter user SYS identified by &newpassword;

After:

SQL> select username,sysdba from v$pwfile_users;

USERNAME SYSDB
--------- -----
SYS       TRUE
USERNAME  TRUE

filename: File that will hold the orapwd Password Information.
File location: File location will default to the current directory unless the full path of the filename is specified.
Password: Password of the user with superuser privileges.

init.ora

Edit the init.ora file and set the parameter “remote_login_passwordfile” to either Exclusive or Shared.

SQL> show parameter password 

NAME                          TYPE        VALUE
----------------------------- ----------- ----------
remote_login_passwordfile     string      EXCLUSIVE

SQL> show parameter password 

NAME                          TYPE        VALUE
----------------------------- ----------- ----------
remote_login_passwordfile     string      SHARED

SQL> show parameter password 

NAME                          TYPE        VALUE
----------------------------- ----------- ----------
remote_login_passwordfile     string      NONE

Exclusive: When the parameter is set to EXCLUSIVE, the password file is used exclusively on one database, even though multiple users may exist in that file. Otherwise, EXCLUSIVE forces the password file to be tied exclusively to a single instance.

Shared: When the parameter is set to SHARED, multiple databases can use the password file, but only the SYS user is recognized.

Disable remote Internal connections: To disable remote internal connections, set

remote_login_passwordfile to NONE.

How do you grant SYSDBA or SYSOPER to a user?

As mentioned above, a user can get SYSDBA or SYSOPER privileges per the steps below.

SQL> grant sysdba to example_user; 
Grant succeeded.

SQL> select * from v$pwfile_users; 

USERNAME                       SYSDBA SYSOPER
------------------------------ ------ -------
SYS                            TRUE   TRUE
example_user                   TRUE   FALSE

SQL> grant sysoper to example_user; 
Grant succeeded.

SQL> select * from v$pwfile_users; 

USERNAME                       SYSDBA SYSOPER
------------------------------ ------ -------
SYS                            TRUE   TRUE
example_user                   FALSE  TRUE

Operating System Authentication

To make Local connections to an Oracle database Instance, whether it is offline or online, a user must either,

Be a Part of the particular Unix/Linux ‘dba’ group.
Or Supply a password when connecting to the database instance.

OSOPER and OSDBA groups for Unix or Linux Operating Systems

The above groups are chosen at the Installation time.
The ‘dba’ group is chosen at the Installation time and is usually the group ‘dba’ by default.
The ‘dba’ group is compiled into the Oracle executables; that’s why it is the same for all the databases running from a given ORACLE_HOME directory.
If you run multiple database instances from the same ORACLE_HOME directory, these groups are compiled into the ‘oracle’ executable to make it consistent for all the databases.
The following can be done on the Linux or Unix servers to check these groups.

Set your ORACLE_HOME env variables
echo $ORACLE_HOME
cd $ORACLE_HOME/rdbms/lib
cat config.[cs]

From the config.[cs] file, the below lines represent the chosen DBA group.

The line '#define SS_DBA_GRP "group"’ represents the OSDBA group.
The line '#define SS_OPER_GRP "group"' represents the OSOPER group.

If you have any custom groups or wish to change the DBA group to a different one per your company’s policies, then change the group name as per the below.

Change From: #define SS_DBA_GRP "dba"
to: #define SS_DBA_GRP "mygroup"

After the above changes are done, it’s time to compile them to effect any changes to the DBA group and relink the group defined above to the Oracle executables. For that, the following should be done.

cd $ORACLE_HOME/rdbms/lib
mv config.o config.o.orig_$(date)
make -f ins_rdbms.mk config.o ioracle
(Note the above instructions may vary depending on your Linux or Unix platform/Operating System release).

Note: The file config.o will be re-created automatically by the make command because of dependencies.

What are some crucial points to be taken into account?

For a group to be accepted by Oracle as the OSDBA or OSOPER group, it must,

Be compiled into the Oracle executable (see the make command above).
The group name must exist in /etc/group on the Linux server.
It cannot be a part of the Linux group ‘daemon.'

Oracle Groups for Unix OS Authentication. Illustration by Shashi Kallae.

If the connection to the Oracle database Instance is secured, and if the user exists in the Unix/Linux operating system with the OSOPER and OSDBA privileges, in this scenario, no password is required to connect to the database.

Note: Any Unix user with the groups OSDBA or OSOPER assigned can log in as "/ as sysdba,” and it will not check for the password file.

C:\Users\.....>sqlplus / as sysdba

SQL*Plus: Release 19.x.x.x.0 Production on Fri May xx xx:xx:xx 20xx
Copyright (c) 1982, 2010, Oracle.  All rights reserved.
Connected to:
Oracle Database 19x Enterprise Edition Release 19.x.x.x.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select name from v$database;

NAME
---------
ORACLEDB

What happens when the ‘root’ user is part of the dba and daemon groups?

It is very common for Unix or Linux users to be a part of multiple groups.
The above users should be able to connect to the Oracle Instance as privileged users, provided they can satisfy the below requirements.
- It is pretty standard for the ‘root’ user to have administrative privileges in Oracle. On the contrary, it is pretty standard for the same ‘root’ user primary group to be the ‘daemon’ group.
- One of the groups they are should be the ‘dba’ group as defined in the config.c file linked to the Oracle executable.
- The DBA group must be valid as defined in the /etc/group.
- When you execute the command ‘id,’ the user’s primary group should not be the ‘daemon’ group.
Some of the ways to tackle this issue are mentioned below.
- Make the root users' primary group a dba group.
  - Create a new group and make that as users’ primary group.

$ newgrp dbagroup
$ sqlplus /nolog
sqlplus> connect / as sysdba

This can also be used in shellscripts thus:
:
newgrp dbagroup <<!
# Commands requiring connect internal privilege
# Eg: dbstart
!

Due to company policies and regulations, if you cannot create a new group, use ‘sudo’ to log in to the Oracle user.

:
su - oracle <<!
# Commands requiring connect internal privilege
!
Note: The user you 'su' to should be able to 'connect / as sysdba'
without a password, for example by having their primary
group as DBA.

Some critical factors to be taken into account as below.
- Beware of syntax errors in the /etc/group file when adding accounts manually. Potential causes for problems are multiple entries for the same group or Oracle user

A quick overview of the Oracle database configuration settings and file structures

1. Set your environment variables by executing the env variable file.
2. echo $ORACLE_HOME
3. echo $TNS_ADMIN
4. id
5. whoami
6. groups
7. env
8. ls -l $ORACLE_HOME/rdbms/lib/config.[cs]
9. cat $ORACLE_HOME/rdbms/lib/config.[cs]
10. cat $ORACLE_HOME/network/admin/sqlnet.ora
11. cat $TNS_ADMIN/sqlnet.ora
12. ls -l $ORACLE_HOME/bin/oracle
13. ls -l /etc/passwd
14. ls -l /etc/group

ANANTHA EXPEDITIONS