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.
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.
In fact, this is the entry for a grant or revoke with the container=all clause.
Let's check if this is true:
Next, let's revoke in CDB$ROOT the SysBackup privileges "local":
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.
