At 10:22 AM 20/02/2005 +0000, you wrote:

>hi,
>
>I'm using firebird 1.5.2, and am having problems with user
>privileges.
>
>I've created a table and granted all to a user. I've also created a
>role and granted all to it as well.
>
>I've then written a small app that connects to the db and tries to
>insert some values using the api.
>
>I'm trying to attach to the database using a dpb with the username
>and password of the user I created with gsec. When I run the
>application as myself (ie not the database user), it connects but I
>get a
>no permission for insert/write access to TABLE CM_LOGS
>error.
>When I run it from a shell where the db user is logged in (the
>username and p/w is the same as a user on the system), it inserts
>fine.
>
>What I don't understand is shouldn't the user specified by the dpb
>give me permission to insert into the table?
>
>The code I've tried is below - as you can see I've also tried using
>the role for that user as well. In the meantime I've granted all to
>public and this gets around the problem, but I'd like to use a more
>sensible security approach. What am I missing?
>
>---------------------
>after doing;
>isql -user sysdba -pw blah myLogging.fdb
>SQL> grant all on cm_logs to full_access;
>SQL> grant all on cm_logs to dbUser;
>
>and then code is;
>
> isc_db_handle newdb = NULL; /* database handle */
> isc_tr_handle trans = NULL; /* transaction handle */
> ISC_STATUS_ARRAY status; /* status vector */
> char dpb_buffer[256], *dpb;
> short dpb_length;
> dpb = dpb_buffer;
> *dpb++ = isc_dpb_version1;
> *dpb++ = isc_dpb_num_buffers;
> *dpb++ = 1;
> *dpb++ = 90;
> dpb_length = dpb - dpb_buffer;
>
> isc_expand_dpb(&dpb, &dpb_length, isc_dpb_user_name, "dbUser",
>isc_dpb_password, "dbUserpw", isc_dpb_sql_role_name, "full_access",
>NULL);

You misunderstand what a role is. What it is NOT is a user group! It is a
package of permissions that you can grant to a user. In that case, you
would not grant any permissions to the user.

This is what you would do to use the role cm_logs:
SQL>set auto on;
SQL>create role cm_logs;
SQL> grant all on cm_logs to full_access;
SQL> grant cm_logs to dbUser;

After granting the role to the user, the user then logs in using both his
user name and password and the role. Then your login parameters would work.

./heLen