Dnia Fri, 25 Jun 2004 17:34:06 +0200, Antonio M.S.
<antonio-firebird@...> napisał:

> Is possible modify the table RDB$USER_PRIVILEGES directy.
>
> I like agregate the privileges from roles to user, example, is possible
> this (is a dirty e imcomplete sentence of example):
>
> Variables:
> user: User to grant the privileges
> role: Role from suply the privileges
>
> 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 ?
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.

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.

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.


Sorry for my poor English.
Best Regards
Bogusław Brandys

>
> This system + 2 tables:
>
> TS_USER
> USER
>
> TS_ROLES
> USER
> ROLE
>
> Permit a system for manager privileges for user in the database baseed
> in rights of roles:
>
> Example of "possible" (not testing) store procedure:
>
> Variables:
> user: User to grant the privileges
>
>
> delete from RDB$USER_PRIVILEGES
> where RDB$USER = :user;
>
> 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((select ROLE from TS_ROLES where
> USER = :user) union ('PUBLICO')));
>
>
> Distinct for delete the same rights for several roles.
>
> The table TS_USER is necesary for work why a role PUBLIC ("PUBLICO")
> private for the specific database.
> The rights for roles are manager normally.
>
> For this system is necesary can modify the table RDB$USER_PRIVILEGES
> directy. Is possible??
>
> Un Saludo;
> Antonio Muñoz
>
> P.D. Perdón por mi mal inglés
>
>
>
>
>
> Yahoo! Groups Links
>
>
>
>
>

--
Best Regards
Bogusław Brandys