Oracle has had an embedded Java Virtual Machine (JVM) for several years, but it has always been a bit behind the current release of Java. Prior to Oracle 11g, Java 1.4 JVM was the latest available and was called the Aurora JVM, in Oracle. In Oracle 11g, finally Java 1.5 JVM was available, and it was based on the Java Security features of that release that I wrote my book, “Expert Oracle and Java Security”. Now the production release of Oracle 12c is available, and the first thing I wanted to know is what version of the JVM was included.
To test what version of Java is available in Oracle 12c, I wrote this small Java class, and a Java Stored Procedure (Function) to encapsulate it.
CREATE OR REPLACE AND RESOLVE JAVA SOURCE NAMED JSProc AS
public class JavaStoredProcedures {
public static String getVersion() {
return System.getProperty("java.version");
}
}
/
CREATE OR REPLACE FUNCTION f_get_java_version
RETURN VARCHAR2
AS LANGUAGE JAVA
NAME 'JavaStoredProcedures.getVersion() return java.String';
Running the Java Stored Procedure, I found that Java 1.6 was available in Oracle 12c. Yay! Even though 1.7 is out and 1.8 is coming soon, new encryption algorithms that I pre-programmed for in the code of my book are now supported with 1.6, along with even more secure algorithms. This is great!
SELECT f_get_java_version FROM DUAL;
-- 1.6.0 --
Cleaning up after running my Java Stored Procedure requires removing the function and the Java class. Cleaning up after ourselves is part of good, secure programming.
DROP FUNCTION f_get_java_version;
DROP JAVA SOURCE JSProc;
Friday, September 27, 2013
Oracle 12c Multitenant Architecture Option
This is a very brief introduction and starter concepts for the Multitenant Architecture option available with Oracle 12c. The idea is that you can host several Oracle instances on a single computer without separate virtual machines and while sharing core Oracle code – no multiple Oracle homes or installation directories.
This is accomplished by having ONE Container Database (CDB) which should not be used to support your database applications. The CDB supports MANY Pluggable Database (PDB) instances. The PDBs are equivalent to your separate Oracle home / database instances that you’re used to.
For example, if you elect to use the Multitenant Architecture option when you install Oracle 12c, you will end up with a CDB named “CDB$ROOT” and a PDB named, for example “PDBORCL”. The CDB will be available via the base instance / service name, for example “ORCL”. Normal installation will configure a Service or startup script to start the “ORCL” instance and the Listener. However, the PDB(s) which are configured in the CDB are not started automatically (after a reboot). This we can fix with a startup trigger. Let’s examine these concepts a bit:
Connect to your Oracle 12c instance (as SYS) and view the containers that are available:
SELECT CON_ID, NAME, OPEN_MODE FROM V$CONTAINERS;
You will see something like the following table. The PDB$SEED container is like a template that can be used when creating additional PDBs and when converting stand-alone Oracle instances into PDB containers.
1 CDB$ROOT READ WRITE
2 PDB$SEED READ ONLY
3 PDBORCL MOUNTED
There are great resources in the Oracle Documents and on-line that you can use when you go on to create PDBs from scratch, from PDB$SEED or from a clone PDB; when you plug or unplug PDB into or from a CDB; and other CDB / PDB tasks. I will not be discussing those items here.
You are, by default, in the CDB container, and you can return there from a PDB by setting your container:
ALTER SESSION SET CONTAINER = CDB$ROOT;
As I mentioned, after shutdown, the PDBs are not automatically started back up. Use this command to manually change the status of a PDB from “MOUNTED” to “READ/WRITE”:
ALTER PLUGGABLE DATABASE pdborcl OPEN;
Typically, we would want to have all our PDB containers opened whenever the CDB is restarted, without manual intervention. We can accomplish this with a startup trigger:
CREATE OR REPLACE TRIGGER SYS.g_after_startup AFTER STARTUP ON DATABASE
BEGIN
-- Opens all pluggable databases
EXECUTE IMMEDIATE 'ALTER PLUGGABLE DATABASE ALL OPEN';
END;
/
Before we move on to the PDB, let’s create a user within the CDB in order to demonstrate the naming requirements:
ALTER SESSION SET CONTAINER = CDB$ROOT;
CREATE USER tuser IDENTIFIED BY tpassword;
This generates an Oracle error, “ORA-65096: invalid common user or role name”. All non-default users and roles in the CDB require the ugly naming convention prefix “C##”. That helps distinguish items that you will see, merged into every PDB, but that may not exist when the PDB is plugged into a different CDB. Typically, you would not create any items in the CDB – it is a “Container Database”, not a “Central Database”. However, there may be exceptions to that plan, and there will be technical considerations for those exceptions that would need to be addressed and that we will explore here. Let’s create a user and role, using “Common User” naming conventions, in the CDB to use as an example:
CREATE USER C##tuser IDENTIFIED BY tpassword;
CREATE ROLE C##create_session_role;
GRANT CREATE SESSION TO C##create_session_role;
GRANT C##create_session_role TO C##tuser;
SELECT * FROM ALL_USERS;
You will see our new user, C##tuser listed, with the designation “YES” in the COMMON column. This user will appear in all PDBs. Observe the data from ALL_USERS in the following commands – again the C##tuser user is listed as a COMMON user, within the PDB. First, we switch containers to the PDB (e.g., pdborcl), still as the SYS user. SYS is a default common user, and many of the “common” SYS views are merged with the “local” SYS views on the PDB.
ALTER SESSION SET CONTAINER = pdborcl;
SELECT * FROM ALL_USERS;
The common users exist in the PDB; however, the grants we made above only exist in the CDB! To allow the common user to connect within the PDB, you need similar grants in that container:
GRANT CREATE SESSION TO C##create_session_role;
GRANT C##create_session_role TO C##tuser;
There is where it gets a bit convoluted – a system privilege (CREATE SESSION), specific to this PDB, is granted to a common role; and a common role is specifically granted in “this PDB only” to a common user. Alternatively, and I recommend this alternate approach, unless there is some exceptional reason for using a common role, create a local (PDB) role to receive local system privileges. If needed, grant the local role to a common user.
REVOKE CREATE SESSION FROM C##create_session_role;
REVOKE C##create_session_role FROM C##tuser;
CREATE ROLE create_session_role;
GRANT CREATE SESSION TO create_session_role;
GRANT create_session_role TO C##tuser;
You can now connect as this user in both the CDB and the PDB. Note that there will be naming conventions in your connection strings, based on your Listener configuration that may not be consistent. Again, I will direct you to Oracle and on-line sources for configuring your Listener.ora and related files. Here are a couple example connections, using SQLPlus:
SQLPlus C##tuser@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)
(PORT=1521))(CONNECT_DATA=(SID=orcl)))
SQLPlus C##tuser@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)
(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=pdborcl.org.com)))
OK, now let’s see the limitations of data stored in the CDB, and how it will not be specifically beneficial to our database applications, running from PDBs. We will create a local user while we are in the PDB container, then we will return to the CDB and create a table of data:
ALTER SESSION SET CONTAINER = pdborcl;
CREATE USER tuser IDENTIFIED BY tpassword;
-- or
-- CREATE USER tuser IDENTIFIED BY tpassword CONTAINER=CURRENT;
GRANT create_session_role TO tuser;
You could grant system privileges or a role with privileges to tuser in order to create tables, but we will just do this as SYS, for now. Please refer to my book, “Expert Oracle and Java Security” from Apress for a thorough discussion of user and role security.
ALTER SESSION SET CONTAINER = CDB$ROOT;
ALTER USER c##tuser QUOTA 10M ON USERS;
CREATE TABLE C##tuser.test_table
(text_col VARCHAR2 (256));
/
INSERT INTO C##tuser.test_table
(text_col) VALUES ('test one');
SELECT * FROM C##tuser.test_table;
GRANT SELECT ON C##tuser.test_table TO tuser;
This last command generates the Oracle error, “ORA-01917: user or role 'TUSER' does not exist”. Here is the first problem with data in the CDB; non-common, PDB users do not exist in the CDB, so they cannot be granted access to data in the CDB. But, hey, the common user, C##tuser exists in the PDB, so let’s just read his data from there:
ALTER SESSION SET CONTAINER = PDBORCL;
SELECT * FROM C##tuser.test_table;
This generates the Oracle error, “ORA-00942: table or view does not exist”. The common user account exists in the PDB, and with sufficient grants, the user can connect to the PDB, but his schema and all his data are in the CDB – not available within the PDB. So, let’s try another tack. Let’s create a link in the PDB to access data in the CDB, and try reading data through the link:
CREATE DATABASE LINK l_cdb_tuser
CONNECT TO C##tuser IDENTIFIED BY tpassword
USING '(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)
(PORT=1521))(CONNECT_DATA=(SID=orcl)))';
SELECT * FROM C##tuser.test_table@l_cdb_tuser;
That worked, so it is possible to read data in the CDB from a user session on the PDB over a database link. Well, that’s no different from reading data over database links to other PDBs or non-Multitenant-Architecture Oracle instances. There is no benefit to placing data in a CDB versus a PDB, and in fact there are strong reasons to argue that it should not be done. You could, but should NOT do that, because the idea behind PDBs is that you can export them from one server and plug them into another server to run there without change. That would hardly work if you were placing central data or code in the CDB.
Perhaps you can imagine a scenario where all PDBs need to share some centralized data, and that data is different on each server (CDB). In that case, I suggest you do not put data in the CDB; but rather, create a “core” PDB on each CDB to hold that shared data.
This is accomplished by having ONE Container Database (CDB) which should not be used to support your database applications. The CDB supports MANY Pluggable Database (PDB) instances. The PDBs are equivalent to your separate Oracle home / database instances that you’re used to.
For example, if you elect to use the Multitenant Architecture option when you install Oracle 12c, you will end up with a CDB named “CDB$ROOT” and a PDB named, for example “PDBORCL”. The CDB will be available via the base instance / service name, for example “ORCL”. Normal installation will configure a Service or startup script to start the “ORCL” instance and the Listener. However, the PDB(s) which are configured in the CDB are not started automatically (after a reboot). This we can fix with a startup trigger. Let’s examine these concepts a bit:
Connect to your Oracle 12c instance (as SYS) and view the containers that are available:
SELECT CON_ID, NAME, OPEN_MODE FROM V$CONTAINERS;
You will see something like the following table. The PDB$SEED container is like a template that can be used when creating additional PDBs and when converting stand-alone Oracle instances into PDB containers.
1 CDB$ROOT READ WRITE
2 PDB$SEED READ ONLY
3 PDBORCL MOUNTED
There are great resources in the Oracle Documents and on-line that you can use when you go on to create PDBs from scratch, from PDB$SEED or from a clone PDB; when you plug or unplug PDB into or from a CDB; and other CDB / PDB tasks. I will not be discussing those items here.
You are, by default, in the CDB container, and you can return there from a PDB by setting your container:
ALTER SESSION SET CONTAINER = CDB$ROOT;
As I mentioned, after shutdown, the PDBs are not automatically started back up. Use this command to manually change the status of a PDB from “MOUNTED” to “READ/WRITE”:
ALTER PLUGGABLE DATABASE pdborcl OPEN;
Typically, we would want to have all our PDB containers opened whenever the CDB is restarted, without manual intervention. We can accomplish this with a startup trigger:
CREATE OR REPLACE TRIGGER SYS.g_after_startup AFTER STARTUP ON DATABASE
BEGIN
-- Opens all pluggable databases
EXECUTE IMMEDIATE 'ALTER PLUGGABLE DATABASE ALL OPEN';
END;
/
Before we move on to the PDB, let’s create a user within the CDB in order to demonstrate the naming requirements:
ALTER SESSION SET CONTAINER = CDB$ROOT;
CREATE USER tuser IDENTIFIED BY tpassword;
This generates an Oracle error, “ORA-65096: invalid common user or role name”. All non-default users and roles in the CDB require the ugly naming convention prefix “C##”. That helps distinguish items that you will see, merged into every PDB, but that may not exist when the PDB is plugged into a different CDB. Typically, you would not create any items in the CDB – it is a “Container Database”, not a “Central Database”. However, there may be exceptions to that plan, and there will be technical considerations for those exceptions that would need to be addressed and that we will explore here. Let’s create a user and role, using “Common User” naming conventions, in the CDB to use as an example:
CREATE USER C##tuser IDENTIFIED BY tpassword;
CREATE ROLE C##create_session_role;
GRANT CREATE SESSION TO C##create_session_role;
GRANT C##create_session_role TO C##tuser;
SELECT * FROM ALL_USERS;
You will see our new user, C##tuser listed, with the designation “YES” in the COMMON column. This user will appear in all PDBs. Observe the data from ALL_USERS in the following commands – again the C##tuser user is listed as a COMMON user, within the PDB. First, we switch containers to the PDB (e.g., pdborcl), still as the SYS user. SYS is a default common user, and many of the “common” SYS views are merged with the “local” SYS views on the PDB.
ALTER SESSION SET CONTAINER = pdborcl;
SELECT * FROM ALL_USERS;
The common users exist in the PDB; however, the grants we made above only exist in the CDB! To allow the common user to connect within the PDB, you need similar grants in that container:
GRANT CREATE SESSION TO C##create_session_role;
GRANT C##create_session_role TO C##tuser;
There is where it gets a bit convoluted – a system privilege (CREATE SESSION), specific to this PDB, is granted to a common role; and a common role is specifically granted in “this PDB only” to a common user. Alternatively, and I recommend this alternate approach, unless there is some exceptional reason for using a common role, create a local (PDB) role to receive local system privileges. If needed, grant the local role to a common user.
REVOKE CREATE SESSION FROM C##create_session_role;
REVOKE C##create_session_role FROM C##tuser;
CREATE ROLE create_session_role;
GRANT CREATE SESSION TO create_session_role;
GRANT create_session_role TO C##tuser;
You can now connect as this user in both the CDB and the PDB. Note that there will be naming conventions in your connection strings, based on your Listener configuration that may not be consistent. Again, I will direct you to Oracle and on-line sources for configuring your Listener.ora and related files. Here are a couple example connections, using SQLPlus:
SQLPlus C##tuser@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)
(PORT=1521))(CONNECT_DATA=(SID=orcl)))
SQLPlus C##tuser@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)
(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=pdborcl.org.com)))
OK, now let’s see the limitations of data stored in the CDB, and how it will not be specifically beneficial to our database applications, running from PDBs. We will create a local user while we are in the PDB container, then we will return to the CDB and create a table of data:
ALTER SESSION SET CONTAINER = pdborcl;
CREATE USER tuser IDENTIFIED BY tpassword;
-- or
-- CREATE USER tuser IDENTIFIED BY tpassword CONTAINER=CURRENT;
GRANT create_session_role TO tuser;
You could grant system privileges or a role with privileges to tuser in order to create tables, but we will just do this as SYS, for now. Please refer to my book, “Expert Oracle and Java Security” from Apress for a thorough discussion of user and role security.
ALTER SESSION SET CONTAINER = CDB$ROOT;
ALTER USER c##tuser QUOTA 10M ON USERS;
CREATE TABLE C##tuser.test_table
(text_col VARCHAR2 (256));
/
INSERT INTO C##tuser.test_table
(text_col) VALUES ('test one');
SELECT * FROM C##tuser.test_table;
GRANT SELECT ON C##tuser.test_table TO tuser;
This last command generates the Oracle error, “ORA-01917: user or role 'TUSER' does not exist”. Here is the first problem with data in the CDB; non-common, PDB users do not exist in the CDB, so they cannot be granted access to data in the CDB. But, hey, the common user, C##tuser exists in the PDB, so let’s just read his data from there:
ALTER SESSION SET CONTAINER = PDBORCL;
SELECT * FROM C##tuser.test_table;
This generates the Oracle error, “ORA-00942: table or view does not exist”. The common user account exists in the PDB, and with sufficient grants, the user can connect to the PDB, but his schema and all his data are in the CDB – not available within the PDB. So, let’s try another tack. Let’s create a link in the PDB to access data in the CDB, and try reading data through the link:
CREATE DATABASE LINK l_cdb_tuser
CONNECT TO C##tuser IDENTIFIED BY tpassword
USING '(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)
(PORT=1521))(CONNECT_DATA=(SID=orcl)))';
SELECT * FROM C##tuser.test_table@l_cdb_tuser;
That worked, so it is possible to read data in the CDB from a user session on the PDB over a database link. Well, that’s no different from reading data over database links to other PDBs or non-Multitenant-Architecture Oracle instances. There is no benefit to placing data in a CDB versus a PDB, and in fact there are strong reasons to argue that it should not be done. You could, but should NOT do that, because the idea behind PDBs is that you can export them from one server and plug them into another server to run there without change. That would hardly work if you were placing central data or code in the CDB.
Perhaps you can imagine a scenario where all PDBs need to share some centralized data, and that data is different on each server (CDB). In that case, I suggest you do not put data in the CDB; but rather, create a “core” PDB on each CDB to hold that shared data.
Subscribe to:
Posts (Atom)