Re: Are there features on other RDBMS's that it would be good for Oracle to have?

Jonathan Lewis wrote,on my timestamp of 29/05/2012 4:20 PM:

> I'm surprised by this - in my 10.2.0.3, with the sample I gave, only the
> roles
> associated with the target by the 'connect through' clause are activated as
> the
> user connects

Believe me, tried it many times before and could never get it working.
More than likely a snag of the particular release I was using, but annoying 
enough to make me come up with the trigger!  :-)

> |Still would like to have everything controlled by context.  I can do that
> easily
> |with MSSQL: assign a default schema to any login, with access rights
> specified
> |by schema.
>
> If SchemaX owns tableY, can userA logon as SchemaX but not be allowed
> to delete from tableY ? To my mind it's that requirement that makes "spare"
> schema with a name-resolution problem necessary.

Yes, for sure.  With MSSQL the access right is given to the login and its 
default schema, but the user remains itself.  Ie, there is no "proxy" or 
"connect through".  So if I never grant "dbwriter" to the login, it never can 
update any table in a default schema - if not its native one.  What I can do 
then is grant individual write access to certain tables only.  Or grant it 
write 
access on another schema, which can be its own.  A login can have multiple 
accesses - including dbs - granted, it's then decided at runtime which it uses 
via a "USE" command.

Doesn't hold a candle to the "ROLE" functionality in Oracle but given the ease 
with which I can assign default schemas to let me have more than one copy of 
the 
schema in a single instance, I'm willing to pay a price.

It's particularly handy to setup for example dev and unit testing schemas in a 
development instance or the multi-regional schemas I mentioned before.  One 
login for each environment/schema.  Or one that can access both, if/when needed.

Of course when *simultaneous* access is required to multiple schemas, things go 
pear-shaped very fast. That's common to both MSSQL and Oracle: don't know of 
any 
db that has solved that easily.  DB2?  Oracle's synonyms are the only solution 
I 
know of that really works, in combination with roles. Short of coding 
everything 
with <owner>.<tname>, which forever condemns us to run only one copy per 
instance...


-- 
Cheers
Nuno Souto
in sunny Sydney, Australia
dbvision@xxxxxxxxxxxx
--
http://www.freelists.org/webpage/oracle-l

Other related posts: