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);

char* dataDB = "/opt/firebird/myDatabases/myLogging.fdb";

if (isc_attach_database(status, 0, dataDB, &newdb, dpb_length,
dpb_buffer)) {
cout << "attach error" << endl;
isc_print_status(status);
}
ostringstream op;
op << "insert into cm_logs (loglevel, logcode) values (";
op << "'Error','" << code << ": " << desc << "')";
string st = op.str();
cout << st << endl;
isc_start_transaction(status, &trans, 1, &newdb, 0, NULL);
if (isc_dsql_execute_immediate(status, &newdb, &trans, 0,
(char*)st.c_str(), 1, NULL)) {
cout << "execute error" << endl;
isc_print_status(status);
}
isc_commit_transaction(status, &trans);

thanks,
nik