We have been reviewing the Oracle12 multitenant database. Before the holidays, I illustrated how to create a PDB using TOAD. Last week, we reviewed how to navigate this PDB in relationship to the CDB and the other PDB’s.
Remember, after we successfully created the PDB using Toad, we got this informative message. This message tells us the service name of the PDB…a useful bit of information required for connecting via SQL*Net. TOAD release 12.5 will also setup SQL*Net for this new PDB as well.
This is what my TNSNames entry looks like to connect to both the Oracle12 CDB (orcl) and the StudentDB PDB. You can use Toad to manage this file but I prefer the old fashioned method…just directly edit the TNSNAMES.ora file…found in Oracle_Home/NETWORK/ADMIN folder. I used the SYSTEM account to setup an admin user for this PDB. I called this user StudentADMIN. You can also create the admin account when creating the PDB but remember, this account will also have no privileges.
***Note*** more and more…these Oracle directories are even locked from direct change for some types of DBA’s…so…using a tool like Toad to maintain the SQL*Net files is a good habit to get into.
I used the ‘create user’ template in Toad. I typed in the information and got the error that this account needed to start with ‘C##’…I was still in the CDB! I can’t create regular users in the CDB!!! They don’t belong there …
I did this: ‘alter session set container = StudentDB’ in the Toad Editor window. I ran the wizard again and this time it worked.
I now have a StudentADMIN account.
Make sure to give this account DBA privileges.
I am logged into Oracle12 as SYSTEM in this example and navigated to the StudentDB PDB where the user account StudentADMIN exists.
Also note that Oracle Corp had been warning us about the roles CONNECT and RESOURCE for years…these roles give backward compatibility to Oracle7.
Well…these two common roles didn’t seem to work very well in my PDB. They didn’t work at all…I have not investigated yet to see why…but I did have to give these three privileges to the Student01 account so it could connect and create a table.
grant create session to student01;
grant create any table to student01;
grant unlimited tablespace to student01;
alter user Student01 profile DEFAULT;
These privileges are easily managed by Toad using the ‘System Privileges’ tab seen above…
I’d create some new roles and distinctly manage who can have access to what…giving global access to all developers allows people to see sensitive information perhaps …
I now connect as Student01 using the proper TNSNAMES.ora entry and Oracle12 will now act just like any other Oracle database. IF you use the wrong connect string, Oracle12 will complain that the user account or password are incorrect…because both the StudentADMIN and Student01 accounts exist in the StudentDB PDB…NOT the other PDBs or in the container DB!
Think of the PDB’s as folders under the CDB. Use the ‘alter system set container’ syntax to navigate to the CDB or PDB while logged in as SYSTEM. Once admin and user accounts are established in the PDB…these accounts will not be able to navigate out of their assigned PDB so the ‘alter system set container’ syntax is useless to those accounts.
I hope you found this information useful.
Dan HotkaOracle ACE DirectorInstructor/Author/CEO
great article again. Just one question: what error message do you receive using the roles CONNECT and RESOURCE on your PDB? in my environment everything works fine. Nevertheless you are totally right that one shouldn't use connect and resource anymore.