Fun with Oracle Database Common User in Multitenant Databases for Backup and Recovery - Part 2

Why revoking rights is sometimes more difficult than it looks at first sight! Tests with a Common-Backup user and the consequences.

The first part of the Fun-with-Common-User series focused on "Why proper rights and privileges granting is really important!".
In a nutshell, it was important to use the container=all clause when granting the SysBackup privilege, otherwise it only applies to the current container.
This makes no difference when backing up via CDB$ROOT, but when restoring you can fail quite badly with it. So especially in the situation where the stress level is extremely high anyway.

But enough of the summary, let's now deal with the removal of the SysBackup privileges.

Let's imagine that we have a C##BACKUP_ADMIN1 user in our multitenant database that was set up for a colleague who will have nothing to do with backup via Recovery Manager in the future.

With this common user, all backup and recovery operations on the root container (CDB$ROOT) and the pluggable databases could be performed without any problems.

Purely for testing purposes, to show that this really works, we close PDB19_1 and open it again immediately afterwards.

 

 

Now we remove the SysBackup privileges from the user C##BACKUP_ADMIN1. This must be done with the container=all clause.

 

After the successful execution it should no longer be possible to connect to the Recovery Manager and execute a backup or restore.

 

How can this happen, we just revoked the SysBackup privileges from the user, why can he still connect and perform a backup? How is this possible, despite container=all clause?
Let's remember the first part of this series. Like it is written there, the SysBackup Privilege was granted twice, one time with container=all and another time without container clause.
So let's revoke the SysBackup privileges from the user C##BACKUP_ADMIN1 in CDB$ROOT a second time, this time without the container=all clause (which means per default "container=current").

Now the C##BACKUP_ADMIN1 user has lost all privileges. But let's check it better.

Yeah, the rights are gone now. The C##BACKUP_ADMIN1 user should no longer be allowed to connect to the root container as well as to PDB19_1 (or other PDBs), right?
What happens if we connect against the PDB19_1?

 

We can still connect, but why?

There is a relatively simple answer. Obviously someone had some good intentions and granted, in addition, the SysBackup privilege to PDB19_1. To revoke this, we login into PDB19_1 or change our session. Then we revoke the SysBackup privilege there. 

 

 

 

Finally, C##BACKUP_ADMIN1 can't login to the pluggable database PDB19_1 as a SysBackup user anymore.

That raises the question, what exactly had happened and how can I find the problem?
To explain and show what was really going on, let's take a step back. We take a look at the privileges of the C##BACKUP_ADMIN1 user in the v$pwfile_users view.

It is not hard to see that the user has been granted the SysBackup privilege for three different containers. More precisely, for container '0', '1' and '3'.

So let's have a look at v$containers to see which containers these are.

We find a container with ID '1', our root container, and one with ID '3,' our pluggable database PDB19_1. However, there is no container with ID '0' as seen in v$pwfile_users.
In fact, this is the entry for a grant or revoke with the container=all clause.
Let's check if this is true:

However, a revoke with container=all clause does NOT remove the privileges for all containers, but in fact only the entry for the virtual container with ID '0'.
Next, let's revoke in CDB$ROOT the SysBackup privileges "local":

We are still missing the revoke of the SysBackup privilege in PDB19_1. So we switch to the container, because a revoke with "container=PDB19_1" is not allowed at CDB$ROOT, and revoke the privilege locally.

After we have done the revoke in all the affected containers, the user really has no privileges anymore.

Of course, we could have just locked the account for the user C##BACKUP_ADMIN1 or, even better, drop it. But maybe this common-user will be used for other things in the future, e.g. cloning PDBs from production to test or datapump exports and imports. Therefore, sometimes it is not possible to simply remove or lock a common user. We need to know which privileges this user has and we need to revoke all privileges one by one.

Fun with Oracle Database Common User in Multitenant Databases for Backup and Recovery

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.