Why granting rights/privileges right is absolutely important!
Backup, restore/recover and security are always among the most important topics for a database administrator. Data must be kept secure and it is critical that the data can be made available to the business again if something happens - be it with the server, an applicative data loss or e.g. due to a security issue like a ransomware attack.
To improve security (and the least privilege principle) for backups, Oracle introduced new privileges and users a few years ago. For backup and recovery operations, "SYSBACKUP" has been added. "SYSBACKUP" is available as a predefined user and as a predefined collection of roles and privileges which can be granted to a previously created user.
But does it make sense to use a user whose name, for example, a hacker, really knows as it is standard? Wouldn't it be better to have a separate user to whom we grant the SysBackup privilege? It is really easy to do, therefore let us connect to a 19c database to the root container and create a new user. We name this user c##backup_problem and give him a password. Then we grant this user the SysBackup privilege.
Two simple commands and we have created our user and granted the necessary privilege. This user doesn't need a tablespace quota or any other privileges/roles, so we'll not grant him anything in addition.
Now let's try to perform a backup and use the Recovery Manager to do it. To connect, it is important to explicitly specify the phrase "as sysbackup" for this user. This is not implicitly set (like you are used with sys, where "as sysdba" is done in the background).
Now it should be easy to back up the database with all its containers (in my example these are: CDB$ROOT, PDB$SEED and PDB19_1).
As you can easily see from the screenshot - this worked. With that, I think/do you think, we are done, our user works flawlessly?
Let's do a simple test. We simulate the loss of a datafile in our pluggable database PDB19_1 and simply delete a datafile.
To reproduce the error without any time loss, we restart the database and see our PDB19_1 is now just mounted. When
we try to open it, we get an error message (ORA-01157 / ORA-01110). This is the behaviour we want to see.
Luckily, we do have created a backup and we know the advantages of Data Recovery Advisor (DRA) to restore and recover the Oracle database. We can use it very quick and the DRA easily restores the (missing parts of the) database and the users can continue working on the applications. We just need to log in as c##backup_problem user and we can use "List failure;", "Advise failure;" and "Repair failure;" in the Recovery Manager. So let's start the Recovery Manager and see what happens with List failure and Advise failure.
Everything is like it should be, the DRA tells us, "the repair includes complete media recovery with no data loss". Perfect, that's what we need. Let us repair the failure now by running "repair failure;".
Oops, it does run only parts of a seconds and we did not expect this: An "ORA-01031: insufficient privileges" on the "alter session set container" command? But we have created a common user and granted the SYSBACKUP privilege? How can this happen?
First, let's take a look at the repair script that the DRA wants to run to restore and recover the database.
As you can easily see, the Data Recovery Advisor wants to offline the deleted datafile in the pluggable database, restore and recover it and then bring it back online. Implicitly there is an "alter session set container" for both SQLs and exactly this fails. What can we do in a situation like this? Sure, you can log on to the database as “SYS” or "/", but the DBA who takes care of the backups might not have the right to do so and it might take hours to get the passwords from the colleagues.
That's why we'd rather not let it come to such a situation in the first place.
We need to find out where this error comes from and how to prevent it from being raised. As described before, the c##backup_problem seems to lack some user rights for the PDB. However, we have granted the SYSBACKUP privilege like it is found anywhere (documentation, support.oracle.com, other blogs), but it seems to be missing in the PDB.
Both, the documentation and the support (the only note that suggests sysbackup grants is Oracle Support Document 2140670.1 (How to take RMAN Full DB backup using SYSBACKUP )) are not helpful for our problem. Maybe we create a new user and grant it the SysBackup privileges again? This time, however, with "container=all" clause?
Again, we connect to our database with using rman and the fresh user as sysbackup in the same way as we did as it failed and run the Data Recovery Advisor again. (I skip the output of the list failure and advise failure, that works anyway).
Hooray! The datafile has been restored and recovered - we should be able to open the PDB19_1 for the application again.
So it is certain what before was only a guess: the missing "container=all" clause is responsible for the missing grant of the SYSBACKUP privilege to the user c##backup_problem in our PDB19_1.
Lessons learned?
As always, half the work is not all the work. If we didn't tried to restore the datafile (or maybe if we tested only a restore of the CDB datafiles) we would never ever have noticed the error. The backup using the root container worked without any problems. But is it possible to test all scenarios for all databases and containers if you grant such privileges? Of course, NO!
And you should not trust the documentation, neither at oracle (including oracle support notes), nor at mostly any other blog (maybe mine if you test it). Most of them just tell you to grant the SYSBACKUP privilege (without the container=all clause). This is part of the learning process handling a multitenant database!
However, if you do get from any colleague a common user with a SYSBACKUP (or any other SYS* privilege) you should test it carefully. For SYSBACKUP e.g. there is a small test case. You can simply open or close the pdb(s) using rman. If the attempt fails with "insufficient privileges" the user was not granted the SYSBACKUP privilege for all containers.
Why container=all does not revoke the privileges from all containers? Find the answer at the second blog post regarding common users in oracle databases.
