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

> At 02:26 PM 20/02/2005 +0000, you wrote:
>
>
> >I have a table called cm_logs and a user called dbUser.
> >I wanted a role called full_access which dbUser could use to gain
> >insert permissions etc.
> >
> >wouldn't it be;
> >
> >SQL> create role full_access;
> >SQL> grant all on cm_logs to full_access;
> >SQL> grant full_access to dbUser;
> >
> >?
> >
> >I was assuming using cm_logs earlier was a typo,
>
> Yes, sorry about that.
>
> > but since things still aren't working for me I'd like to check...
>
> dbUser has to exist in the server's security database. Then
>
> 1. Create the role and commit it.
> 2. Grant the permissions to the role and commit.
> 3. Grant the role to the user and commit.
>
> isc_expand_dpb(&dpb, &dpb_length, isc_dpb_user_name, "dbUser",
> isc_dpb_password, "dbUserpw", isc_dpb_sql_role_name, "full_access",
> NULL);
>
> Double quotes are probably the culprit here.
>
> Remove the quotes from the role altogether (a role is a database

object).

> Double quotes have a particular syntactic use in SQL identifiers.

Your role

> is stored as FULL_ACCESS, not "full_access".
>
> User name and password are strings. Try putting single quotes on
> them. Password is case-sensitive, btw. User name is stored as

upper case,

> but Firebird automatically uppercases it.
>
> ./heLen

I need the quotes for the C++ call. Anyway, I've figured out what's
gone wrong.

the dbp structure that came out of isc_expand_dpb was garbage. I've no
idea why (there was a few shameful pointer mess-ups on my part, but
even with those fixed it still didn't make any sense).

in the end this worked;

char dpb_buffer[256], *dpb, *p;
short dpb_length;
dpb = dpb_buffer;
*dpb++ = isc_dpb_version1;
*dpb++ = isc_dpb_num_buffers;
*dpb++ = 1;
*dpb++ = 90;

char* username = "dbUser";
*dpb++ = isc_dpb_user_name;
*dpb++ = strlen(username);
for (p = username; *p;) {
*dpb++ = *p++;
}

char* pw = "dbUserpassword";
*dpb++ = isc_dpb_password;
*dpb++ = strlen(pw);
for (p = pw; *p;) {
*dpb++ = *p++;
}
char* role = "FULL_ACCESS";
*dpb++ = isc_dpb_sql_role_name;
*dpb++ = strlen(role);
for (p = role; *p;) {
*dpb++ = *p++;
}
dpb_length = dpb - dpb_buffer;
dpb = dpb_buffer;

/*
this is the original call, not that I can see anything wrong with
it....
dpb_length = dpb - dpb_buffer;
dpb = dpb_buffer;
isc_expand_dpb(&dpb, &dpb_length, isc_dpb_user_name, "dbUser",
isc_dpb_password, "dbUserpassword",
isc_dpb_sql_role_name, "FULL_ACCESS", NULL);
*/


Thanks for everyones help, it was greatly appreciated.

Nik