--- In firebird-support@yahoogroups.com, Helen Borrie <helebor@t...>
wrote:

> 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

I'm a bit confused that you've called the role cm_logs (that's the
name of the table - is that deliberate?).

I created a new database and did the following;

[ncain@cain1 Databases]$ isql -user sysdba -password dbAdmin
Use CONNECT or CREATE DATABASE to specify a database
SQL> connect myDB.fdb ;
Database: myDB.fdb User: sysdba
SQL> set auto on;
SQL> show tables;
CM_DATA CM_MESSAGE

SQL> create role full_access;
SQL> grant all on cm_data to full_access;
SQL> grant all on cm_message to full_access;
SQL> grant full_access to dbUser;
SQL> exit;

dbUser had been created earlier with gsec.

However, my app still gave
no permission for insert/write access to TABLE CM_DATA

I had a brief doubt that I'd messed up passing the dbp in the attach
command (which I didn't show in my post) and had been passing the dpb
pointer rather than dpb_buffer (the pointer had been incremented, and
would be the wrong thing), but this is the attach I'm using now, and
it looks ok;

isc_attach_database(status, 0, dataDB, &newdb, dpb_length, dpb_buffer)

Am I still messing up on the granting part in isql?

nik