The new PDB/CDB concept has introduced the concept of local (specfic to a pluggable database) and common (same username/password in all databases) i was interested to where these were actually being stored.
In all previous versions this information is stored in the dictionary table USER$ so i thought i'd see how this is now implemented in 12c. I wasn't sure if the users table would be stored in just the container database, somehow replicated into all the 'plugs' or if there would be 2 copies somehow maintained.
I'm still not sure how this is really implemented but I can share what I've seen so far.
In both the CDB and PDB there appears to be a 'table' called USER$.
When querying this tabledepending on your container context(alter session set container=xxxx) you see different rows. For example a local user in plug1 is not visible in plug2 (or actually even in cdb).
If you query the rowid's from user$ in the PDB/CDB all the 'common' users come out with the same rowid so they must be stored in the same physical place and interestingly the local users have rowid's from the same physical table object as the common users - you can see that in the screenshot below from excel so there dont seem to be user related records stored directly in the 'plug'. Rows 1->128 all have the same rowids in both pdb/cdb and row 129 from the PDB is the next rowid in sequence.
SO that all kind of makes sense users are all stored in the PDB and oracle is somehow using VPD like functionality to restrict what is seen in each containers view of the world (i.e. appending "and container=xxxx" to any dictionary queries against user$)
At this point i was reasonably happy how it was working but them i thought what if you move that PDB to a different container - how do local users 'go with it'. I'm still not sure how this works - i thought maybe the xml file generated by the process of unplug/plug perhaps contained some of this data (but it seems not to). I can only assume that the unplug step populates a user$ table in the PDB with the current contents of the local users from the user$ table held in the CDB. Replugging this in somewhere else then extracts these local users, puts them in its CDB and then hides/removes the data from the PDB? sounds good in theory to me anyway.
To see all the users from all containers in one place you can query PDB_USERS as long as you have enough permissions.
Perhaps all this will make more sense after more reading and more actually using the new functionality. I keep thinking i've got it then i find something else out and then 'i dont got it' again.....
Update: just found this link http://www.oracle.com/technetwork/database/multitenant-wp-12c-1949736.pdf
which is basically saying there are 2 copies of user$ one in PDB and one in CDB and then oracle is essentially unioning those two to give you what you see. That makes more sense about how things actually could work but I'm confused now about rowid's - there must be something going on here too - i can;t see how otherwise you end up with a rowid that looks sequential but has actually come from a different physical file entirely...
more reading still required....
In all previous versions this information is stored in the dictionary table USER$ so i thought i'd see how this is now implemented in 12c. I wasn't sure if the users table would be stored in just the container database, somehow replicated into all the 'plugs' or if there would be 2 copies somehow maintained.
I'm still not sure how this is really implemented but I can share what I've seen so far.
In both the CDB and PDB there appears to be a 'table' called USER$.
When querying this tabledepending on your container context(alter session set container=xxxx) you see different rows. For example a local user in plug1 is not visible in plug2 (or actually even in cdb).
If you query the rowid's from user$ in the PDB/CDB all the 'common' users come out with the same rowid so they must be stored in the same physical place and interestingly the local users have rowid's from the same physical table object as the common users - you can see that in the screenshot below from excel so there dont seem to be user related records stored directly in the 'plug'. Rows 1->128 all have the same rowids in both pdb/cdb and row 129 from the PDB is the next rowid in sequence.
SO that all kind of makes sense users are all stored in the PDB and oracle is somehow using VPD like functionality to restrict what is seen in each containers view of the world (i.e. appending "and container=xxxx" to any dictionary queries against user$)
At this point i was reasonably happy how it was working but them i thought what if you move that PDB to a different container - how do local users 'go with it'. I'm still not sure how this works - i thought maybe the xml file generated by the process of unplug/plug perhaps contained some of this data (but it seems not to). I can only assume that the unplug step populates a user$ table in the PDB with the current contents of the local users from the user$ table held in the CDB. Replugging this in somewhere else then extracts these local users, puts them in its CDB and then hides/removes the data from the PDB? sounds good in theory to me anyway.
To see all the users from all containers in one place you can query PDB_USERS as long as you have enough permissions.
Perhaps all this will make more sense after more reading and more actually using the new functionality. I keep thinking i've got it then i find something else out and then 'i dont got it' again.....
Update: just found this link http://www.oracle.com/technetwork/database/multitenant-wp-12c-1949736.pdf
which is basically saying there are 2 copies of user$ one in PDB and one in CDB and then oracle is essentially unioning those two to give you what you see. That makes more sense about how things actually could work but I'm confused now about rowid's - there must be something going on here too - i can;t see how otherwise you end up with a rowid that looks sequential but has actually come from a different physical file entirely...
more reading still required....
Comments
Post a Comment