CHAPTER

6

Security

So far in Part II we have described a number of operations you can perform on pluggable databases (PDBs) and the many different ways to connect to them. At this point we also need to factor security considerations into the mix, as the most prevalent attack vector is an abuse of database privileges. Rather than granting administrator rights too widely, we should instead apply the principle of minimal privileges. In 12c, with the Enterprise Edition and the Advanced Security option, the privilege analysis functionality offered can be a great help in this area.

With respect to multitenant, your effective user security administration starts by thinking about which users should have access only to specific PDBs, as users and roles can be created common or locally. Privileges can also be granted common or locally, and beyond this, multitenant (including single-tenant) brings additional fine-grained control via powerful commands, courtesy of lockdown profiles. Of course, when you issue powerful privileges locally to a PDB, you need to prevent any side effects on the container database (CDB), and 12.2 introduced a number of PDB isolation features to mitigate this.

In terms of data security, Oracle Virtual Private Database is still an option to limit access at row level, as is the Oracle Database Vault to protect against rogue database administrators (but we will not detail them here, because they’re not specific to multitenant). Another type of possible attack is network sniffing—that is, reading data directly off the network—and network encryption is available in all Oracle 12c editions without options. Bulk data sets that leave the confines of the company premises, such as backups stored off site or in the cloud, are also potentially vulnerable. We will cover backup encryption in Chapter 7.

Finally, we may want to protect against unauthorized access at disk level, and this is highly recommended in a multitenant database, where data from different sources will be consolidated on the same CDB. This protection becomes mandatory when we put our data on a public cloud, and for these reasons we will cover Transparent Data Encryption at the end of this chapter.

Users, Roles, and Permissions

At a high level, when you connect to an Oracle Database, you do so with a database user that is declared within the database, in the dictionary, along with the user’s privilege definition. We have not yet defined which container this user information is stored in, and they can actually be common and thereby stored in CDB$ROOT or local and stored in a PDB.

Common or Local?

A common user’s information is stored in CDB$ROOT and exists in every single PDB. You create common users for the CDB administrators or for users that have the same identity in all tenants. In both cases, they must connect to CDB$ROOT to change their passwords. In contrast, a local user is stored in a single PDB and exists only in that PDB.

For users, roles, profiles, and privileges, we have to work with the key words common and local. But before we go any further, let’s ensure that we are all on the same page in terms of our definitions of a user and a role in the Oracle Database, because this is not an obvious given.

What Is a User?

In Oracle, a user and a schema are synonymous—or at least that was the case before the introduction of multitenant. When you create a user, you implicitly create a schema for the user objects, and when you have a schema, its owner is a user. Because this chapter is about security and not database objects, we will use the term “user” here. However, keep in mind that the one-to-one relationship between users and schemas has changed with multitenant, and one common user is now a different schema in each PDB.

A user is just a name that is employed when you connect to the database, and a session is always associated with a user. No matter which way you connect to the database, you will have a username, and this user is the vehicle that enables a session to perform operations on the database. You grant privileges to the user, and the connected user can enact whatever is permitted by those privileges.

Because it is highly likely that you will have several users with the same allotted privileges, or users with requirements for the same groups of privileges, you can define roles. In this way, you grant specific privileges to a role, and then grant the role to the users. Roles are also useful to enable the switching of users from one group of privileges to another. For example, the same user may have a read-only role when connecting with SQL Developer and a read/write role when connected through the application, because the application can encapsulate some form of access control.

User and role definitions are actually stored in the same data dictionary table, USER$, where the TYPE# defines whether it is a user (1) or a role (0):

You might think that when you have no database, you would have no user definitions, because there is no dictionary yet; however, as it turns out, some users are actually hard-coded. Here we connect to an “idle instance” as we would do when wanting to create a database:

SYSDBA privilege is mapped to the SYS user and SYSOPER privilege is mapped to the PUBLIC role. The authentication is handled through OS groups or via a password file, because there is no dictionary at this stage.

A number of users and roles are generated when you create the database, and these are maintained by Oracle. Starting in 12c, multitenant or not, there is an easy way to identify system users, because they have the ORACLE_MAINTAINED column set to TRUE in DBA_USERS. The creation of new users and roles is handled with the CREATE USER and CREATE ROLE statements. Both statements have a new optional clause in 12c multitenant, CONTAINER=CURRENT or CONTAINER=ALL, to define whether they are to be created for the current container or are to be common to all of them.

CONTAINER=CURRENT

When connecting to a PDB, we can connect to the root and ALTER SYSTEM SET CONTAINER, but, as we saw in the previous chapter, connecting via the listener to a service switches the session directly to the service’s container:

Once connected, we can then create a user:

In fact, when connected to a PDB, we can create only local users, so the container clause is not mandatory and defaults to local:

If we try something else, we get an error:

So basically nothing changes here when working in a PDB; we simply create users in the way we are used to, and we can add the container=current just to be explicit.

When we want to create a user with Oracle Enterprise Manager, connected to a PDB, we don’t have the choice. We receive a message, “Note: Created user will be a local user since you are in PDB container,” as shown in Figure 6-1.

FIGURE 6-1. Creating a local user in OEM 13c

CONTAINER=COMMON

Now let’s see what we can do from CDB$ROOT:

First of all, trying to create a local user fails:

When dealing with security objects (users, roles, profiles), what we create in the root container must be common. But there is something else to be aware of:

The best practice is to avoid mixing common and local users in the same namespace by setting a prefix for all common users. The prefix is set by the parameter COMMON_USER_PREFIX which defaults to C##:

You can change this prefix and can even set it to null string, although we don’t recommend this. The problem with doing this can occur after some PDB movement operation, when the same name is used by both a common and a local user. Having a prefix reserved for common users brings clarity and prevents such conflicts.

Here is how we create common users connected to CDB$ROOT. Note that the CONTAINER=ALL clause is not mandatory because it is implicit, and it is the only valid value in this context:

Figure 6-2 shows this user creation within OEM, clearly displaying the information, “Common user name must begin with ‘C##’”; it is a common user because it is created from root. Note that this message is static and does not take into account changes made to COMMON_USER_PREFIX.

FIGURE 6-2. Creating a common user in OEM 13c

We can check the users from CDB$ROOT:

In this example, we query only for users we have created recently. Note that the Oracle-maintained users (such as SYS, SYSTEM, and so on) are common users even if they don’t have the common prefix.

And here’s the same query from our PDB:

In the PDB, we inherit the common users along with those defined locally. Once again, we have not displayed the Oracle-maintained users or the admin user that is generated when the PDB is created.

Local Grant

When in a PDB, you see local users in addition to common ones and you can grant them privileges. Of course, as you are in a PDB, the privileges are granted only at the PDB level. So, for example, if you grant CREATE SESSION to PDBUSER1, this user will be able to connect to its PDB, with the ability to grant that privilege to others, only when done so with the admin option, as follows:

This is, in fact, the only possibility, because in a PDB you can grant privileges only locally. The CONTAINER=CURRENT clause is the default, so it is not mandatory, and if you try something else you get this:

You can grant to a common user, but this will be for the local context only. For example, here in a PDB we grant the common user C##USER1 the right to connect to our PDB:

The default, and only possibility, is CONTAINER=CURRENT, which we have omitted. We can see that the grant is there in PDB with COMMON=NO:

However, the C##USER1 is known to all containers but does not have the CREATE SESSION privilege set for these:

In a PDB, you can create users and roles and grant privileges to them locally. In addition, you inherit common users and roles and can also grant privileges to them locally. It is entirely possible for a common user to have different privileges specified in every PDB; the user is the same, because it is common, but it has different behaviors and privileges in each PDB.

Common Grant

In addition to local grants, we can also grant common privileges from CDB$ROOT. For the moment, our three users have no such privileges defined, so let’s look at an example. As we did in our PDB, we can grant CREATE SESSION to the C##USER1 when in CDB$ROOT:

This means that C##USER1 now has the right to connect to CDB$ROOT, in addition to the right to connect to PDB, which was just granted. We can also equip C##USER1 with the ability to connect to any PDB, whether currently existing or to be created later (which we show for theoretical rather than practical purposes) in the CDB:

So let’s see the current grants from CDB$ROOT:

Both grants are there, even if the local ones are redundant, as long as the common ones exist.

NOTE

Be careful with the default value for CONTAINER in a grant statement. The default is CURRENT, even when in CDB$ROOT, which means the privilege will be granted only locally. This is different from the CREATE USER default. Our recommendation is always to specify the CONTAINER clause.

Let’s use the CDB_SYS_PRIVS that shows the result from each container’s DBA_SYS_PRIVS (Chapter 9 will detail these cross-PDB views):

These results mirror the output from our previous examples, in that we see a local grant for CDB$ROOT (CON_ID=1) and PDB (CON_ID=3), and common grants made from CDB$ROOT, which are visible in all containers. From an administration perspective, this lack of clarity does not make sense, and it is better not to mix common and local privileges for the same users.

Conflicts Resolution

Data movement and database plug-in will be addressed in Chapter 9, but you are already aware that multitenant and PDBs bring agility in data movement and cloning. However, you can only imagine the kinds of conflicts you may encounter when plugging in a PDB with a local user that shares the same name as a common one, or vice versa. Oracle will attempt to merge them, but you may have to resort to resolving conflicts manually.

Let’s take an example here with the C##USER1. We unplug the PDB and drop the C##USER1:

Then we plug it back in—that is, a PDB that had a common user then plugged into a PDB without one:

This shows a common user in the PDB, although it is actually unknown from the CDB$ROOT:

Here you see the process of inheritance that we have described as working differently. Common users are not shared; instead, they are propagated to containers. In our example, we dropped the common user but it remained in the unplugged database, with local and common grants all intact.

But no user at CDB$ROOT means we cannot connect:

So are we able to connect to the PDB, because the user is there, with the CREATE SESSION privilege?

In fact, because Oracle was not able to merge the common user automatically with CDB$ROOT, as that user did not exist in CDB$ROOT, the user has been locked until we resolve this issue manually.

If we want to keep this user as the common user, we have to create it from CDB$ROOT:

To avoid such conflicts, we need to close the PDB first:

All conflicts are then resolved at open, because the common user now matches in both containers:

There is no need to unlock the account—everything is OK, and we can successfully connect to the PDB:

Of course, if we want to connect to CDB$ROOT with this user, we need to grant CREATE SESSION from the root, so it’s best to grant it with CONTAINER=ALL and revoke the CREATE SESSION privilege that was granted locally.

Keep It Clear and Simple

Be assured that there is nothing to be afraid of here, because it is all very logical if you understand that, physically speaking, the commonality is neither a link nor a logical inheritance, but only the propagation of privileges when DDL is issued. Second, any conflicts that may appear when plugging in a PDB coming from another CDB are resolved when the PDB is opened. And don’t forget to check PDB_PLUG_IN_VIOLATIONS for more detail.

We can’t include all the conflicts that may appear, but let’s imagine a common user with a local function to validate the password. You must ensure that the function exists in all PDBs. Our recommendation is to keep it simple, and use the prefix, which enforces a name convention to make it clear about what is common or local. In general terms, common users are mainly for administrators, while local users are for application schemas. Note that if you want to use external authentication with common users, you can match COMMON_USER_PREFIX with OS_AUTHENT_PREFIX.

With regard to the common user prefix, you should be aware of two additional points. First, the comparison of the prefix is case-insensitive, and second, even if you change it from its default, the C## is still forbidden for local users, so you will have two prefixes that can lead to ORA-65094: invalid local user or role name.

Note that in 12.2 it is possible to have your own root for your application, which is called an application container, where you can manage application user commonality in the same way. There is the APPLICATION_USER_PREFIX for this, which is empty by default, and it cannot be set to C##.

CONTAINER_DATA

Common users can see information from the whole CDB, so they can query the V$ views because they show information about the instance, and the instance is common. They can also query the CDB_ views, which collate information from the DBA_ views, from each of the containers. However, the CONTAINER_DATA parameter option is a means of implementing fine-grained control, and it enables the administrator to restrict common user access to a subset of containers. Here is an example in which we allow C##USER1 to see V$SESSION common data only from CDB (CON_ID=0), CDB$ROOT (CON_ID=1), and PDB1 (CON_ID=4):

The ALTER USER statement to authorize container access from a query on V$SESSION at root level is as follows:

We can then check what is now permitted, from the DBA_CONTAINER_DATA dictionary view:

Note that the object to which the restrictions apply is V_$SESSION here, which is the dictionary view on the V$SESSION fixed view. However, the V$SESSION in our statement is actually the public synonym that has been resolved to that view. This is interesting to know, because if you attempt to do the same as SYS you will get an error (ORA-02030: can only select from fixed tables/views): from SYS the V$SESSION is the fixed view itself. So if you want to do something similar from SYS, you have to name the dictionary view directly:

In the following, we are connected as SYSTEM and can, therefore, count sessions from all containers:

But if we attempt the same with the C##USER1 user, we don’t see information from PDB2 (CON_ID=5), because it was not included in the authorization list:

Remember that this restriction is only for queries executed from CDB$ROOT. And, as we have granted the SET CONTAINER to C##USER1 on container PDB2, this user can always switch to it and view the sessions there:

The same result is also possible by connecting directly to PDB2 as C##USER1, which has the CREATE SESSION privilege there. So don’t forget to restrict access from all possible avenues: CONNECT_DATA for query from CDB$ROOT, and GRANT/REVOKE within the PDB itself.

Roles

As with users, you can create roles that are either local or common. However, keep in mind that a big advantage of the multitenant architecture is the ability to separate user metadata from system metadata, so don’t mix system roles with user roles. If you want to create roles with a subset of the DBA privileges for your administrators, you can create them common, and perhaps grant them to individual local users in PDBs. But as far as user roles go, these should pertain to a specific PDB.

Proxy Users

Proxy users enable you to connect to a user without knowing the user password. This is useful for a DBA who needs to create an object, which can be created only by its owner, such as a database link. It is also a good way to audit who logs in by name and still behaves as if logged in by the schema user. This option is still possible in multitenant, which means that local users can actually proxy through a common user. For example, in PDB1, the DBA enables the common user C##USER1 to be a proxy user for the local user APPOWNER:

The C##USER1 can then connect to APPOWNER by providing his own password:

And from then on, everything functions as if connected with APPOWNER directly.

It’s a good practice to disallow direct logon so that you are sure to audit who was connected by their actual usernames. You cannot lock the account, because proxy connections will be blocked as well, but you can set a password that nobody knows. There’s also a better option, but this was not yet documented at the time of writing:

With this clause, the connection can be performed via a proxy user. This behavior can be canceled with the following:

This proxy only connect option is interesting, but our recommendation is to wait until it is officially documented before using it.

One final note about proxy users—for security reasons, if you connect as a common user through a local proxy user, you are locked in the container of the proxy user. Here is an example in which we allowed the common user C##USER1 in PDB1 to connect through the local user ADMIN:

When the common user connects directly to the PDB, the user can change to another container later, as long as the user has the SET CONTAINER privilege:

However, this operation is not allowed when connected through a local proxy user:

This is a security lockdown hard-coded since 12.1, and 12.2 has brought more possibilities to control this through lockdown profiles.

Lockdown Profiles

PDBs bring a new separation of database administrator roles. The DBA administers the CDB but can delegate the administration of individual PDBs. Let’s take an example of a CDB that is a dedicated development environment. The fast and thin provisioning features we will see with snapshot clones make it possible to give a PDB to each developer. Because it is their database, the CDB administrator can grant developers the DBA role for the PDB, so that developers can do whatever they want there, as long as their privileges are limited to this PDB.

In 12.1, this strategy is almost impossible to implement. Even if the DBA role is granted locally only to a local PDB user, this privilege enables the user to do things that can potentially break the CDB or the server. For example, a local DBA can create files wherever he wishes, can execute any program on the host (which will run as the oracle user), and can generate massive trace files. If we want to limit what a local DBA can do, we need better control over these privileges, and this is why 12.2 introduced lockdown profiles.

Here, connected to CDB$ROOT, we create a profile for our application DBAs:

Disable Database Options

With profiles, we can disable access to some features available only with licensed options. For example, if we don’t have the partitioning option, we must be sure that nobody will create a partitioned table, so let’s disable it from our application DBA profile:

We can now apply the lockdown profile to PDB1 simply by setting the pdb_lockdown parameter for that container:

So now let’s try to create a partitioned table in that PDB1:

As you can see, this is impossible because the feature has been disabled.

The ENABLE and DISABLE clauses of ALTER LOCKDOWN PROFILE can also be specified with an ALL option:

Disable ALTER SYSTEM

The ALTER SYSTEM privilege is very powerful, but with the GRANT syntax you can only allow or disallow it. However, with lockdown profiles you have fine-grained control, because you can enable or disable specific clauses of the statement. Let’s say, for example, that you want to allow your developers to kill sessions in their PDB, but no other ALTER SYSTEM activities. From CDB$ROOT you can add the following rule:

With this, a user in the PDB who has the lockdown profile assigned will get an “ORA-01031: insufficient privileges” message for any ALTER SYSTEM command, except an ALTER SYSTEM KILL SESSION.

The scope of control can be defined further with the ALTER SYSTEM SET command, because you can even control which parameters are allowed. For example, the following will allow only some parameters to be set at the PDB level:

We can query the dictionary to see these defined:

From a PDB where this lockdown profile is set, we can set one of these allowed parameters:

But we will receive a privilege error when trying to set one that is not in the permitted list:

When disabling the change of a parameter, we can also define a value to be set at the same time, when the PDB_LOCKDOWN parameter is set:

This is an effective means of creating a lockdown profile with several parameters set to values that cannot be changed later.

Disable Features

We will not go into detail on disable features here, but in the same way that you can disable database options, you can also disable features. For example, the following command disables the specified PL/SQL package usage:

NOTE

You can disable all networking packages with the NETWORK_ACCESS feature name.

PDB Isolation

In 12.2, in addition to the PDB_LOCKDOWN parameter that can be used to set a lockdown profile to limit network access, you can also limit the interaction with the OS file system and processes.

PDB_OS_CREDENTIALS

From a dbms_scheduler job, or through an external procedure, it is possible to run a program on the host server. But, more than likely, you will probably not want to let the PDB administrator run anything with the oracle user privileges. In this case, you can create a credential, from the root, defining the OS user and password, and also including a domain if you are on Windows:

You can limit a PDB to this user when running jobs or external procedures:

PATH_PREFIX

In a similar vein, a PDB administrator can create a directory and write files anywhere on the system. This was not a problem before multitenant, because the DBA controls both the database and the host, but in multitenant you can delegate some administration tasks to the PDB administrator and then need more control on how the PDB admin can interact with the host. Since 12.1, it has been possible to define a PATH_PREFIX as the root of all directories created in a PDB, which is then defined with a relative path from there. Note that you cannot change the PATH_PREFIX after creation.

CREATE_FILE_DEST

Another way to write files onto a server is to create tablespaces and add datafiles, and this is also an operation the CDB administrator needs to restrict. Starting with 12.1.0.2, you can now set the CREATE_FILE_DEST to a directory specific for the PDB, so that datafiles are written there. However, a user with CREATE or ALTER TABLESPACE privileges can still specify a fully qualified filename, and then write everywhere the oracle user can write. OS credentials are not used here.

NOTE

We have opened an enhancement request regarding this gap, and we hope to have the option to lockdown a PDB administrator to use OMF only, without specifying an absolute file path or a disk group, in the near future.

Transparent Data Encryption

Encrypting data on disk is a key part of a sound security strategy; however, authorized users can still access the data unencrypted. This means that the database has the decryption key onboard, and such encryption does not prevent an attack that accesses the data through the database software.

There are still other paths to compromise the system, such as getting access at OS level or directly accessing the disks, and data encryption does protect against this.

There are essentially two ways to implement a sound security strategy: programming access procedures and encrypting data ourselves, or using Transparent Data Encryption (TDE).

In the first case, it’s the application that does the encryption and decryption—that is, the database stores and presents the encrypted data, oblivious to the fact that it is encrypted. The disadvantages of this method are that it is more difficult to implement, and we have to be very careful to implement key management correctly; it’s no use encrypting the data if an attacker can compromise the keys himself.

Second, Oracle Database provides, as part of the Advanced Security Option, another solution: TDE. This is part of the Oracle Database, and it’s a trusted, proven, and supported solution that is well documented; many DBAs are familiar with it. If we are really serious about security, TDE also supports hardware security modules, which are dedicated pieces of hardware that securely store encryption keys.

Setting Up TDE

Each encrypted database has a master key that is used to generate all additional keys, whether for separate tables or tablespaces. This master key is the only one stored outside of the database itself, and it’s the only one that the database needs to open.

There are several reasons why the Oracle Database uses multiple keys internally, and not just the master. First of all, encrypting data with the master key directly can be slow and expensive, especially hardware-stored keys, which have very limited throughput and can be licensed by capacity. In addition, we can change the master key when we decide to do so—for example, if the key is compromised, or simply to alter master keys on a schedule. Changing the master key re-encrypts the subordinate (table) keys, but not the data in the database itself.

Let’s go through an example of setting up TDE for a multitenant database. In this example we will use a software keystore, not a hardware one.

Setting Up the Keystore Location

A software keystore is essentially a file in a specified directory. Oracle Database also supports wallets for storing secure information, which enables scripts, for example, to log in without hard-coding passwords. Conceptually, wallets are very similar, and the way to administer them is also similar to the TDE keystore. In fact, in 11g, both were called “wallets,” and some syntax and documentation still refer to both by this name; even the location for each can default to the same value. Nevertheless, we recommend keeping these separate, as they do contain different data with different purposes.

The location of the keystore is defined in sqlnet.ora. It is important that you know that each CDB or non-CDB has its own keystore, so if we have multiple databases running on the same host, we must configure a path that is different for each database.

We could create multiple sqlnet.ora files and make sure each database is started with the correct one, but the easiest method—and the least error-prone—is to include the ORACLE_SID in the path.

Sqlnet.ora is in the TNS_ADMIN path, which is $ORACLE_HOME/network/admin by default. The entry for database encryption can be set as follows:

Creating the Keystore

Creating the keystore involves one simple command in the root container. All the commands that work with the keystore need SYSKM or ADMINISTER KEY MANAGEMENT privilege.

NOTE

This command requires a full path to the directory where the keystore will be created. This must be the same as we specified (or will specify) in sqlnet.ora, and be aware that the command does not check that these two paths match. This command is new in Oracle Database 12c, replacing alter system set encryption commands from earlier versions.

A keystore created in this way will require the same password to open. Oracle Database also supports autologin wallets, which alleviate the need to specify the password. (Refer to the documentation for more details.)

Setting Up Using Cloud Control

Most of these operations can also be performed using Enterprise Manager Cloud Control. Figure 6-3 shows the TDE home screen, from which we can set up the keystore and manage the keys.

FIGURE 6-3. TDE in Enterprise Manager Cloud Control

Opening the Keystore

Before anyone can use the keystore, we must open it in the root container and then in the PDBs. Only then we can set up the master key and read or modify the encrypted data.

We can also use the CONTAINER clause syntax introduced by multitenant, and then in the root container, open the keystore in all PDBs:

It is in this step that we will find out whether the configuration of the wallet location has been done correctly—if not, we will receive an error like this:

Creating the Master Key

Now that we created an empty keystore, the obvious next step is to generate a master key and store it in the keystore; this is where things differ for a multitenant database. As noted earlier, each CDB has one keystore; however, in that keystore, each PDB has its own master key. In Oracle Database 12c Release 2, we should be able to specify keystores specific for each PDB, too, meaning that plugging and cloning would require just copy of the keystore, not an export.

This means a bit more work is required during the setup, but it also means that PDBs can be unplugged and cloned and moved into another CDB (see Chapter 9), with their own key that we move along. This was always an issue with transportable tablespaces, in that they are encrypted by their database key, and the target database, if also encrypted, can’t accommodate two different master keys.

To create the new key, we run the following command:

This can be run in the container for which we want to create the key—either CDB$ROOT or a PDB. And, again, we can add the CONTAINER=ALL clause when running the command in CDB$ROOT to create keys in all PDBs. However, this also sets the same descriptive comment (the tag) for all keys, which may not be desired.

It is also mandatory to specify a backup of the wallet, so that, should the operation go wrong, we would still have the previous copy and would not lose the keys. So, after adding the first key, we now have two files in the wallet directory, as in the following example:

Verifying the Created Keys

We can now simply check which keys have been created so far:

As you can see, the key ID is a long, generated base64 encoded value, while the tag value is a human-readable comment.

Encrypting the Data

Encryption of the data is defined at the PDB level, using the same syntax and rules as in a non-CDB database.

TDE can encrypt column(s) in a table, which we can request by simply adding the ENCRYPT keyword:

The second way TDE can work is at the tablespace level, encrypting all data in this entity:

We can see a list of all TDE-encrypted columns in DBA_ENCRYPTED_COLUMNS:

And the list of encrypted tablespaces is displayed in v$encrypted_tablespaces:

Plug and Clone with TDE

When a PDB is copied/moved to a new CDB, the target CDB needs to know its encryption key. If the operation is a clone, Oracle will do this for us automatically. But if we plug in/unplug a PDB, we must ship the master key along with the XML file and the datafiles. This is achieved by exporting the key on the source into a password-protected file:

We then proceed with the plug-in as per normal (see Chapter 9). The PDB will refuse to open and will instead remain open in restricted mode. But now that the target database knows about the PDB, we can connect or switch to it and import the keystore export file, as follows:

From here we can close and then open the PDB again, and access the encrypted data.

TDE Summary

TDE is a feature that is, on paper at least, easy to use, but it comes with its own limitations, and it is a key management process that is complicated at times.

It’s important that we build a thorough understanding as to how this feature works if we are going to use it. After all, this is data security we are talking about—an area where it is often difficult to assess whether we have done something wrong, until it’s too late.

TDE is a topic that warrants a book on its own, and we have only lightly scratched the surface here. The message is clear, however: TDE still functions the same as before, with the only change in multitenant being one key for each PDB, and thus multiple keys in a single CDB.

Summary

In a multitenant environment, particularly in the cloud, all the features covered in this chapter are must knows—and must use. Some of these can be distilled into simple directives, such as: don’t have all your database administrators connecting as sysdba, and the system administrators should have their common usernames to administer the CDB. It is probable that all other users will be local to PDBs, so their actions are appropriately isolated. Moving beyond this type of access protection, encryption is a powerful means of preventing illegitimate access to data, although this is not sufficient protection alone. Even with the best security policy, an error may occur that results in some data being lost or corrupted. This brings us to the most important facets of data protection, backup and recovery, which are covered in the next chapter.