> > insert into RDB$USER_PRIVILEGES (RDB$USER, RDB$GRANTOR,

RDB$PRIVILEGE,

> > RDB$GRANT_OPTION, RDB$RELATION_NAME, RDB$FIELD_NAME,

RDB$USER_TYPE,

> > RDB$OBJECT_TYPE)
> > select :user, RDB$GRANTOR, RDB$PRIVILEGE, NULL, RDB$RELATION_NAME,
> > RDB$FIELD_NAME, 8, 0
> > from RDB$USER_PRIVILEGES
> > where (RDB$USER_PRIVILEGES = :role)
> >
>
>
> I think You can ,if You are SYSDBA (or grant insert,delete privilege for
> system table rdb$user_privileges) however ,what are the advantages of Your
> solution ?

Yes, or grant all privileges to owner and admin of the database.

> Besides if You grant role to user automatically all role privileges are
> granted eighter the only obvious problem is to remember about role name on
> login time.That's why the info about roles granted for user should be
> inserted into You table TL_ROLES.

My application (a ERP in kylix/delphi for linux and windows) use roles for control of
rights but the enterprise is small and the same user haves several roles
(FAX_MASTER, ADMIN, TECNIC, ...) change between the different roles is a bad
solution. The control of privileges with the roles scale very well the application.

>
> I'm trying to make something similiar but without direct system table
> manipulation, rather I create stored procedure and grant/revoke using
> execute statement.
> Becouse I must store my own privilege as a set of SQL ordinary privileges
> (I must incorporate simple grant manager into my program and users tend to
> be lost with SQL privileges) I create a couple of tables and procedures.
> Now for example I can execute one procedure GRANTEE to set many privileges
> for user/role at once and all those I can simply list from an another
> table.Additionally I can assign role for a user.

Is a good solution but my database and applicattion is modify constantly.

> I think Firebird would benefit from named group of SQL privileges.There
> are many cases when one logic operation is complex and uses a few
> insert,update and execute procedure statement, and almost always this
> group of operation could be named (like "Updating stock") and grant to
> user as a set. Role is not sufficient in this situation becouse I can put
> only one role name when connect to database.
>
>
> In Your case You could also use stored procedure to avoid access to system
> tables directly. Later just grant execute procedure privilege on this
> procedure to someone and insert,delete,select privilege on table
> rdb$user_privileges to this procedure only.

Is a good solution for not use the user SYSDBA and grant the execute right to
owner/admin.

> > This system + 2 tables:
> >
> > TS_USER
> > USER

> > TS_ROLES
> > USER
> > ROLE

Thes table not is necesary is we use:

select * from RDB$USER_PRIVILEGES where RDB$USER = :admin and
RDB$PRIVILEGE = 'M';

In the next setence:

> >
> > insert into RDB$USER_PRIVILEGES (RDB$USER, RDB$GRANTOR,

RDB$PRIVILEGE,

> > RDB$GRANT_OPTION, RDB$RELATION_NAME, RDB$FIELD_NAME,

RDB$USER_TYPE,

> > RDB$OBJECT_TYPE)
> > select distinct :user, RDB$GRANTOR, RDB$PRIVILEGE, NULL,
> > RDB$RELATION_NAME, RDB$FIELD_NAME, 8, 0
> > from RDB$USER_PRIVILEGES
> > where RDB$USER_PRIVILEGES in (select * from RDB$USER_PRIVILEGES

where RDB$USER = :admin and RDB$PRIVILEGE = 'M') union ('PUBLICO')));