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

> 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_at_iinet.net.au
--
http://www.freelists.org/webpage/oracle-l