Let’s pick some questions on Multi-tenant in Database 12c.

There are a huge number of questions on multi-tenant a huge number and a lot of them are really confusing questions and they deal principally with the security things it’s the security issues a where it gets really confusing it uses there is any number of questions on users also which I’ve got wrong but I did the upgrade example I still got through you can get low drop so we will be looking at security, in particular, that’s where it gets computing startup you’d already security can be pretty awkward space management is easy backup we’ll see the syntax so startup shut down, of course, you can only start up and shut down in the roots within a container all you can do is open or close the container making it available for use but the usual sin tax as you’ve seen works.

So you can log on connect to sis DBA to a container and you shut down a boss but it won’t it won’t abort anything but it will close the container to which you are connected and you’ve also done open read-write and open read-only something with noise everybody is that by default these things are only in mount mode so if I connect to the roots show call named here am on the root and bounce the in our environment everybody gets irritated by the facts your containers will be left in mount mode well we’ll see how to fix so the mode vedalia containers veetle the PDP’s you’ve seen it and then there’s some syntax you can do from the root alter pluggable database Kibler name open by default read right yes but it could also be open read-only but if you look at the situation here selects name open mode from the dollar con cleaners you see by default all left in Mount load this annoys everybody closing it syntax from the root alter pluggable database close and that will have the effect of disconnecting everybody notes.

If you got an active session it won’t close they’ll remain until they disconnected that’s where you might want to use some stronger syntax there interestingly enough in mount modes a service remains registered with the listener which is not the case for a real database of Mount load because services are defined in the data dictionary but because the PDB has this has got the route data dictionary as well it can remain registered know the routes knows about the services now password file authenticated users that’s an interesting one so if I connect it to my pluggable container so alter session so I can figure sequel net I’ll connect say this swash Oracle s P D be an assist EBA so I have configured that and they are being logged amis s2p DBA I can create a user create user JW identified by JW looks hahaha thirdly mount road alter their relation open.

This will be creating a local user in the data dictionary of PD be a local user will see in about two slides time then create my user water by Grant says TBA to him grant sis DBA to JW it’s done it right so connect JW / JW s P D be an as sis DBA so you can actually do this and if we look at the select star from the vedala PW file users oops from look at the password file I see this connection ID container ID 0 so he’s from the root container he’s a common user from roots, but there’s JW who’s my local user ID local user so if I could try to connect as JW to PDB be assisted EBA no way because JW exists only in PDB a bit I can, of course, connectors sis/oracle at PD VBSS DBA and in this one the password file is visible from here we see the only cess so even at the level of using sis connections you can still maintain the isolation.

So you as senior DBA can create your pluggable your CD be you can plug in your 20 p DBS and each PDP can have his own database administrator and that database administrator can have full control within his container but can’t do anything outside of it they’ve really done a brilliant job with that even keeps sis connections isolated this is what everybody does automate the startup releases 12 101 you had to create a trigger you go to the root container and create a trigger create trigger anything you like after startup on database and in that trigger you would put the code to open your pluggable containers, so that’s what everybody did with 12 101 there are two other trigger times after clone and before unplugging these are really useful or they may be really useful the point about the after clone I don’t have any of you have been the situation there’s only been in a couple of times of absolute disasters when people have cloned databases because they have not remaps external references.

When you have been there if any of you have ever been there you have my sympathy and if you haven’t, it’s only a matter of time the worst case I came across with this it wasn’t my fault I’m glad to say it was the company with the cell phone company in nigeria I worked after I lived in words in Africa for many years honestly we’ve got there it’s JW assist EBA can open yes and that’s because Martin JW doesn’t even exist in the other PD bees he doesn’t even exist in the roots he’s a local user who exists only in that one container and within that container, he’s got total power system we’re jumping ahead of us a bit system is what we call a common user a common user take a look at this if I select username common container con ID from cdb users order by created and we see this JW is not a common user and exists only in container 3 whereas system exists in container 1 as a common user and he also exists in oh gosh just ed where username like this % Oh ma’am Audubon.

I promise I can write a better sequel mass I promise to meet going to eat system exists in container three and then containing one he exists everywhere and he’s will be called a common user so totally different passwords yeah passwords genuine interesting one truong it I’ll connect a system password oracle at PD be a-okay here I am logged on as show user oops co-user show con name right system however is a common user ok porter user system identified by JW haha advance is your question specified change must apply to all containers so common users are defined in the route propagated to every container and because they’re defined in the roots you’ll find you can change their password only in the roots will see the syntax later in the chapter and might we’ll do it now if I try to append containers equals all oops sorry container equals all that’s an attempt to change his password in every container can’t do it.

So what I have to do is to connect the root container and then I can change his password and that’s changed it globally because when you are in the route the default is indeed container all that’s getting ahead of ourselves, but this is where it gets monumentally confusing just come back to the question a common user has one password that applies to all containers and you can only manipulate a common user in the route right now back here I was talking about the triggers this company I was working on it was it was a cellphone environment and on that there was a method for selling airtime for prepaid cell phones and they were rolling out new products so they cloned the data but I cloned the database to the test system and through a workload simulator artists that basically generated loads of sales of airtime to the giving airtime to vast numbers of users in the test system and what they didn’t realize they haven’t remapped the external references.
So yes, they were working on running their workload generator against the test database, but the test database was sending the output to the production.

Leave a Comment