:

Sqlplus as SYSDBA

Let’s start by taking a look at what SYSDBA and SYSOPER are, and then dig a little deeper…

    • SYSDBA and SYSOPER are special system privileges
    • They are used to perform administrative actions, such as starting and stopping the database
    • You can connect to the database even if it is not open with SYSDBA and SYSOPER privileges
    • You can connect to the database using SQL Plus as the SYS user using the SYSDBA privilege as follows:
SQLPLUS sys/<password>@<database> as sysdba

<password> and <database> need to be changed to your SYS password and to the name of the database you are connecting to. You might be connecting to the database as another SYSDBA user and not necessarily the SYS user.

Note: If you are using OS authentication then you can omit the password and log on with SQLPLUS sys/ as sysdba

Extra Special Privileges…

When you log on with the SYSDBA privilege you are in the SYS schema and can do anything within the database. This is the highest level privilege that you can log on with. You have the ability to make all changes and see all data. Grant this permission with great care. Usually there is only the need for the DBAs to have access to this user, and even that is probably not on a day-to-day basis.

The SYSOPER privilege is still very high level but has some restrictions, such as a user with this privilege cannot see the data owned by other users. They can, however, carry out administrative functions such as starting, stopping and altering the database.

Best Practices

It’s always best to operate on a least privilege basis. That is, a user should only be granted the required and necessary privileges they need to work and no more.

Treat the SYSDBA and SYSOPER privileges with great care as they can perform potentially catastrophic damage in your database.

Authentication as SYSDBA

There are two other main ways to authenticate users with the SYSDBA and SYSOPER privileges:

  1. OS authentication
  2. Password File authentication

There is also a 3rd way, not discussed in this article, and that is:

  • Strong authentication, network-based, such as Oracle Internet Directory (OID)

Note: The old CONNECT INTERNAL method of connecting is no longer supported. OS and Password file authentication have replaced the CONNECT INTERNAL connection method.

Whether you choose to connect using OS or password file authentication primarily depends on how you are going to connect to the database:

  • Connecting remotely, over a non-secure connection (TCP/IP) = password file
  • Connecting locally, you may use either, it’s your preference

It should be noted that if you authenticate with the OS then you will not need to authenticate with the password file as well. OS authentication takes priority over password file.

Let’s look at the first two methods in a little more detail.

– OS Authentication –

This requires the operating system username for the user to be added to a special group. The group is usually called OSDBA or ORADBA and something like OSOPER for the SYSOPER user.

– Password File Authentication –

This requires there to be a password file in place. You can create one using the following command:

D:\Oracle\Product\11.2.0\dbhome\bin\orapwd file=D:\db\pwdTEST.ora password=test force=y ignorecase=y

This calls the orapwd utility and creates a new file called pwdTEST.ora with a password of test. It forces the creation even if another file with the same name already exists and says that the password is not case-sensitive.

REMOTE_LOGIN_PASSWORDFILE should be set to EXCLUSIVE. Well, that’s what the Oracle docs say. If you are running RAC or you have data guard configured you will need it set to SHARED. This is fine, just make sure that all the DBs are also set to SHARED.

Connect to the database as the SYS user and grant the privilege to the user:

GRANT SYSDBA to USERA;

Changing SYSDBA User Passwords

If you want to change the password for a user who has been granted the SYSDBA privilege and you are using a password file, you can use the ALTER USER SYS IDENTIFIED BY statement for the SYS user. If the user with the SYSDBA or SYSOPER privilege is a non SYS user you MUST revoke and regrant the privilege to the user. This is how:

    1. Find users with SYSDBA privilege
SELECT USERNAME
FROM V$PWFILE_USERS
WHERE USERNAME != 'SYS'
AND SYSDBA='TRUE';
    1. Revoke and regrant the privilege to those users from step 1
REVOKE SYSDBA FROM non-SYS-user;

GRANT SYSDBA TO non-SYS-user;
    1. Find users with SYSOPER privilege
SELECT USERNAME
FROM V$PWFILE_USERS
WHERE USERNAME != 'SYS'
AND SYSOPER='TRUE';
    1. Revoke and regrant the privilege to those users from step 3
REVOKE SYSOPER FROM non-SYS-user;

GRANT SYSOPER TO non-SYS-user;

Hopefully this post has helped you learn a little more about the SYSDBA and SYSOPER users and how to manage them.

Like it, share it...

Category: Articles


Related Posts

Leave a Reply

Your email address will not be published. Required fields are marked *