Hi!

> SN> In Oracle and most (all?) other databases roles act exactly as
> SN> normal groups.
> SN> Effective rights for the user are equal to union of
> rights granted
> SN> to it directly and via roles.
>
> Nope. That discussion (roles, groups, etc) was long time ago
> in epsylon.public.interbase. What I have found for myself
> that standard declares role as "alternate user identifier".
> I.e. user can login using USERNAME, or login as ROLENAME, but
> not both. Thus it is different with what we have in IB/FB now.

You are not correct. Here are the snippets from the "Basic security"
chapter of recent standard:
--------------------------
4.34.1 Authorization identifiers
An <authorization identifier> identifies a set of privileges. An
<authorization identifier> is either a user identifier
or a role name. A user identifier represents a user of the database
system. The mapping of user identifiers to
operating system users is implementation-dependent. A role name
represents a role.
4.34.1.1 SQL-session authorization identifiers
An SQL-session has a <user identifier> called the SQL-session user
identifier. When an SQL-session is initiated,
the SQL-session user identifier is determined in an
implementation-defined manner, unless the session is initiated
using a <connect statement>. The value of the SQL-session user
identifier can never be the null value. The
SQL-session user identifier can be determined by using SESSION_USER.
An SQL-session context contains a time-varying sequence of cells, known
as the authorization stack, each cell
of which contains either a user identifier, a role name, or both. This
stack is maintained using a "last-in, firstout"
discipline, and effectively only the top cell is visible. When an
SQL-session is started, by explicit or implicit
execution of a <connect statement>, the authorization stack is
initialized with one cell, which contains only the
user identifier known as the SQL-session user identifier; a role name,
known as the SQL-session role name
may be added subsequently.
Let E be an externally-invoked procedure, SQL-invoked routine, triggered
action, prepared statement, or directly
executed statement. When E is invoked, a copy of the top cell is pushed
onto the authorization stack. If the
invocation of E is to be under definer's rights, then the contents of
the top cell are replaced with the authorization
identifier of the owner of E. On completion of the execution of E, the
top cell is removed.
The contents of the top cell in the authorization stack of the current
SQL-session context determine the privileges
for the execution of each SQL-statement. The user identifier, if any, in
this cell is known as the current user
identifier; the role name, if any, is known as the current role name.
They may be determined using CURRENT_
USER and CURRENT_ROLE, respectively.
At a given time, there may be no current user identifier or no current
role name, but at least one or the other is
always present.
--------------------------

--------------------------
4.34.3 Roles
A role, identified by a <role name>, is a set of privileges defined by
the union of the privileges defined by the
privilege descriptors whose grantee is that <role name> and the sets of
privileges for the <role name>s defined
by the role authorization descriptors whose grantee is the first <role
name>. A role may be granted to <authorization
identifier>s with a <grant role statement>. No cycles of role grants are
allowed.
--------------------------

------------------
4.34.4 Security model definitions
The term enabled authorization identifiers denotes the set of
authorization identifiers whose members are the
current user identifier, the current role name, and every role name that
is contained in the current role name.
The term applicable privileges for an authorization identifier A denotes
the union of the set of privileges whose
grantee is PUBLIC with the set of privileges whose grantees are A and,
if A is a role name, every role name
contained in A.
The term current privileges denotes the union of the applicable
privileges for the current user identifier with
the applicable privileges for the current role name.
------------------

> SN> Actually Firebird roles may be more or less trivially fixed to
> SN> support standards-compliant behavior
>
> Hmmm, not sure people want roles as they declared in standard.
> Mostly people want user groups for SQL permissions.
> So, if there statement to extend current roles functionality,
> it must be:
> a) allow user automatically "load" all role grants, if user
> was added to role. I mean without specifying role with login
> information
> b) make all roles granted to user additive. If he is member
> of role A and role B, let user have all grants given to USER,
> PUBLIC, role A and role B.
>
> But, don't allow granting role A to role B, to disable
> potential circular reference (at least).
>
> And, roles will be groups :-)

And this is what standard specifies.

Row-level access control is now also very well understood and is
standartized.
Oracle and DB2 implement it very nicely.

> Dmitri Kouzmenko

Nickolay