The space budget does not seem to be honored I was hoping at work in that case maybe I’ll see if i get it working as my homework but at least you’ve seen the syntax so what are saying where it gets complicated local and common users we’ve already seen thanks to questions one or two bits of this the fact that local users know what do you have them these are the rules you have to remember a local user exists in one container is defined in the data dictionary for that container you cannot create local users in the root all the usual rules for things like naming apply common users are propagated to all containers you can create them only in the roots I’m not too happy with my windows with my Linux database I’m going to work under windows.
When I’m reasonably confident that my windows’ database is going to do what I wanted to do so users’ duplication then you can have won and lost root container but not the same name in a PDB even though it’s only local to that one that’s a clever a question than you realize the answer is no you can’t, but the reason is really strange sequel + / assist EBA I’ll show you why it’s impossible you’re right but first for a strange reason oops sets Oracle set oracle Sid equal see DBA I go to my root container sequel + / access DBA right show con named you to structure the heart of the issue so I’m in the root here then I’ll connectors sis / Oracle at JW one assist EBA sequel + JW one assist EBA what copy that in paste right so in this one I’m connected to whoops JW one right.
Let’s try creating a couple of users in the PDB absolutely normal crates user SW identified by SW that’s a local user identified by SW select star from all users ordered by created there’s SW, not a common user go to the root and try to create her there invalid common user will roll name you can create only common users in the root previous slide local users I created can’t be created in the root why did it listen it’s told me the invalid name now whoops, so this is where common users create in the route they must conform to a common user prefix show parameter a common user prefix defaults to see hash this parameter did not exist in 12 101 this is 12 102 in 12 101 it was a hidden parameter with an underscore in 12 102 it’s a public parameter and you can change it do not change it if you change that you will confuse every other DBA whoever works on your system.
So don’t change it what it means is that to create a user in the root you must be prefixed whatever this parameter is so I can create only see hash SW right and that’s why it’s impossible to get confused if I go to my pluggable container and I try to do that invalid local user role name, so common users can be created only in the root and must begin with C hash local users are created in pluggable containers and cannot begin with C hash so it’s impossible to get that conflict right so Who am I actually got now well I’ll select username common caller ID from CBD users where username like % SW % and now you see SW is not common and exists only in container 3c hash SW exists in container 6 container one container 3 containers 5 containers for the SSW does not exist container too because container to is the seed container and the seed container you can’t do anything with nice but in this database.
I have got quite a few containers for more than you do add a couple of extra exercises so you see created in the root propagated everywhere now and that that common user prefix is important Oracle maintained users can break all these rules look at this haven’t got one here spatial is a common user he doesn’t begin Missy hash system is a common user he doesn’t begin with C hash I guess that what Oracle decided in some ways I think this is a bit of a mess but they must have decided that they need a clean separation which in common users and local users but it would have been for backward compatibility they could change the names of those users we’ve all been using for decades, so Oracle maintained users don’t have to conform to the prefix all right and if you want to hack it you can alter session summits underscore Oracle scripts equal true remember that and now I can do what I like great user create user Fred identified hi Fred and now it works whereas of course.
If I set that to force which it should be by default and I tried to do that no way so you can break the rules if you want to and there are times you may really want to do that but it would be a pretty extreme case now roles are the same and if users get a few confusing roles get monumentally computing local roles and profiles you connect to your PEB you create your old quick your profile absolutely is normal and assign them to your local users there’s no difference there at all local roles are defined the local data dictionary and assigned to the local users but if you are in the route you can create common roles I can create a role or one and that’s not going to work but I can create role see how fish are one and that will have been propagated everywhere grant c cash are 12 JW grant 02, so that’s a grunting oh it doesn’t exist okay great user JW identified by JW JW.
So he’s a local user in JW one live granted em a common role what privileges at half well we don’t know yet I could also grant him a local role such as connects well, so that’s grant it a local grant of these two rolls of a common role but what’s the point of a common role well you can grant privileges to it I could grant create a table to see hash r1 containers equals all oops container and that’s granted create able to see how HR 1 in every container whereas I could perhaps Grant’s create procedure see ash are one container equals current so see ash are one in the root in the roots has created table and Cray procedure in JW one CHR one has only created the table so if I now connect JW / JW ass JW one what do I have select star from session probes I do not have the great procedure because that was a local grant so and deliberately trying to confuse you here and make you think about things.
And the exercise I hope will make you think some more because this is where it gets difficult the use of common users and common roles and what you grant to them with what scope I’ll tell you my angle on this I have never seen a usage case for a common user or common role I’ve never seen a use case for them I said we don’t have any customers that are licensed multi-tenant but I’ve worked on other contracts with people how where we are using multi-talents it’s just none of our customers all it’s just my boss sort of subcontracts me out to other people and I seem other sites people using multi-tenant and work to Mulsanne and there but I’ve never seen a usage case of common users and common roles I can’t see their purpose if you can see a usage case for them I’ll be very interested to hear about this the closest thing I’ve heard to a usage case was um chapels working with you told me that he was writing loads of software for a database administrator.
And for database administration purposes he wanted to have everything defined in the center and for that purpose, he was going to use common users and common rolls all maintained at the center but then used in the pluggable containers and I thought why would you want to do that first anyway that’s the closest thing I’ve come to a usage case.