0

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?

1 Answer 1

2

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?

2
  • " I have a problem to understand it" What are you having a problem with? DBA is role, fundamentally no different than if you were to execute "CREATE ROLE MY_ROLE", then assigne privs to it, like 'GRANT SELECT ANY TABLE TO MY_ROLE'. This only difference is that DBA is created, and popluated with privilges, and granted to user SYTEM, by default when the database is created. A role is nothing but a collection of privileges (system privs and object privs) that can then be granted to a user or other role. SYSDBA and SYSOPER are not roles, but special privileges.
    – EdStevens
    Mar 19, 2021 at 21:14
  • " I have a problem to understand it" - I was referring to the formatting errors, which you have already fixed. Now it is quite clear. Thank you for the answer!
    – iwis
    Mar 19, 2021 at 21:24

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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