Re: Are there features on other RDBMS's that it would be good for Oracle to have?
- From: Nuno Souto <dbvision@xxxxxxxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Tue, 29 May 2012 20:25:24 +1000
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
--
//www.freelists.org/webpage/oracle-l
Other related posts:
