1

I don't know if I correctly understand the privileges of these built-in roles and users, but this is a list of the information that I have found:

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

Is my understanding correct?

1 Answer 1

3

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?

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.