At 03:30 PM 19/04/2005 -0500, Sergio wrote:

>Hi,
>I have a couple of roles on my database (FB v1.5.2):
> R1 and
> R2
>Users of "R1" role aren't members of "R2" role, and what I want is that
>users of R1 are able to manage R2 role, that is, add or delete users of
>R2. Is that possible?

First, understand that roles are not like privileged groups. A role is a
way to package a group of privileges. Then, all of the privileges in that
package can be granted to a user. That user then logs in with his username
and the role, which will enable the role's privileges for that user, for
that session.

Secondly, there is only one user that can add or delete
users: SYSDBA. This user has full destructive privileges for *all*
databases, including the security database. Users other than the SYSDBA
have very restricted privileges in the security database.

In contrast, roles apply only to the database in which they are
created. If your question meant "Is it possible to create a role with
SYSDBA privileges?" then the answer is No. A role cannot "reach out" from
the database it belongs to, in order to gain privileges on other databases.

If you meant "Is it possible to create a role that can grant or revoke
privileges to or from another role?" then the answer is Yes. However, it
is a large and rather complex thing to do. The right and simple way to
address this situation is not by way of a "superuser role" but by creating
a user and then making that user the *owner* of the database AND of all of
the objects in the database. The owner of the database then has complete
control of all roles and privileges in that database.

In order to be this "Superuser", i.e. the owner of everything, this user
must have created the database and the objects, typically by being the
logged-in user when the scripts to create and modify the database schema
were run.

If the owner of your database currently is SYSDBA then you have a task to
do, in order to simplify the management of privileges. You will literally
have to reconstruct the database from a metadata script and pump the data
from the existing db into the new one - making sure that your Superuser is
the logged-in user for creating the db & objects and for pumping the data.

./heLen