How are the DBA, SYSDBA, SYSOPER roles and SYS, SYSTEM users related in Oracle?

Posted on

Question :

I don’t know if I understand correctly the privileges of these built-in users and roles.

This is the list of information I have found:

  • I think that DBA and SYSDBA roles enable to do everything – if this is true, then why are there two names for the same thing?
  • the SYSOPER role enables to do everything except of accessing users’ data,
  • the SYS user has DBA and SYSDBA roles, so he can do anything,
  • the SYSTEM user has DBA role, so it seems that he could do anything, but I have read that he cannot do backups, recovery, and DB upgrade.

Do I understand it correctly?

Answer :

I think that DBA and SYSDBA roles enable to do everything – if this is
true, then why are there two names for the same thing?

They aren’t the same thing. SYSDBA is not a role but a special privilege in and of itself. It gives the grantee ultimate authority, including the ability to issue shutdown and startup commands, override some other limitations, and a few other items that escape me. Unlike roles, when one is granted SYSDBA, they are added to the password file (external of the database), allowing them to connect over the network to an idle instance.

DBA is just a role. And while it is created by default, and assigned to user SYSTEM by default, it is fundamentally no different than any role that you might create and assign privileges to. While it is not recommended that privs be added or removed, there is nothing to stop you from doing so.

the SYSOPER role enables to do everything except of accessing users’
data,

Like SYSDBA, SYSOPER is not a role but a special, hard-coded privilege. Off the top of my head, I cannot remember the limitations, but it’s primary purpose is to allow a granted user the ability to shutdown and startup, without giving it a lot of other privileges. It is not granted to anyone by default, and in my entire career (working in small shops with at most 3 DBAs) I’ve never had cause to grant it.

the SYS user has DBA and SYSDBA roles, so he can do anything,

Yes.

the SYSTEM user has DBA role, so it seems that he could do anything,
but I have read that he cannot do backups, recovery, and DB upgrade.

Without being granted SYSDBA or SYSOPER (and he shouldn’t be) he cannot stop or start the instance. If he cannot stop the instance, he cannot perform upgrades or recovery. He can perform online backups (IF the database is in archivelog mode) because that doesn’t require a shutdown. But what would be the point?

Leave a Reply

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