Leyne, Sean wrote:

>>Both are non-standard, but changing the rules on roles is less so, less
>>
>>
>>work to implement, less work to document, and less confusing to the poor
>>
>>
>>DBA who would need to figure out when to use a role, when to use a
>>group, and how to figure it when he guessed wrong.
>>
>>
>
>Doesn't that apply to your proposed 'latent' roles?
>
>

No. A role is a role is a role. With standard SQL, a role is latent if
the user has the right login with it and active if he does. In the
extension, a user can activate other latent roles at runtime, giving him
the union of privileges of active roles. It's a very, very minor
extension and absent explicit action of the programmer is strictly
compatible with the standard.

Now, having said all that, I think it would be nicer to a server to be
able to set what roles would automatically be active on login, but I'm
prepared to forgo this for the larger point.

>What roles where active? Which were deactivated? Did the user login
>with an explicit role?
>
>

For the sake of argument, assume all roles are latent until the user
logs in with an explicit role, in which case it becomes active. All
other roles remain latent unless explicitly activated.

>The whole idea of activation/deactivation seems unnecessary and brings
>it's own set of support issues.
>
>

Why don't you spell out those issues rather than making us guess?

>
>Speaking of non-standard, I found it interesting that MS SQL doesn't
>have a standard implementation of Role. They allow users to have
>multiple roles.
>
>

The SQL standard is braindead on this issue. Even Microsoft noticed.

>
>So with MS in mind, and with the view of simplifying the groups/role
>usage issue, and maintaining some backward compatibility. I propose:
>
>- Allow users to have multiple roles, which are fixed (no
>activation/deactivation)
>
>

Explicit activation is extremely useful under certain circumstance.
Consider a web server, for example. It's acting on behalf of a user,
but can't know the user's privileges without making a database query.
But once the privileges have been determined, the server can't activate
the roles without detaching and reattaching. That doubles the number of
attachments requires.

And, if I may be so bold, you haven't actually raised any objects to
flexible roles other than waving you arms around.

>- Allow user to login with specific/singular role (as they can today)
>
>- If user logs in without role then all the user's roles would apply.
>
>

This means that logging on with a role only restricts privileges. This
is the worst of all worlds. The whole idea is to restrict privileges to
the minimum required to do a specific job.



--

Jim Starkey
Netfrastructure, Inc.
978 526-1376