Subject Re: [IBO] Role not working
Author G. Allen Casteran
At 04:50 PM 7/14/2002, you wrote:
>I have a database with a role defined and privileges granted to a set of
>stored procs.
> From the IBConsole ISQL window I can login with that username and role and
>execute the procs just fine.
>When my IBO app logs in to the server and executes the procs I am getting
>an errors "no permission for exececute access to procedure xxxx". If I
>grant execute permission to the user that allows the proc to run. It
>appears that the IB_Connection is not actually logging in with the role
>To connect we run this code.
> conLDSRD01.Server := conLDSServerName;
> conLDSRD01.Path := conLDSDBPath;
> conLDSRD01.Username := conLDSUserName;
> conLDSRD01.Password := conLDSPassword;
> conLDSRD01.SQLRole := conLDSRole;
> conLDSRD01.Connect;
>conLDSRD01 is the TIB_Connection object on the data module. The
>TIB_StoredProc has its IB_Connection property pointing to conLDSRD01.
>I assumed that this would work but no luck yet. I DID check the spelling
>and capitalization of the Role just in case.

OK More info. The problem seems to stem from the role we assigned and the
permissions. Somewhere along the line IB hosed something. Looking at he
permissions in IBConsole it looks like the role has all the permission it
needs to access the table (which the SP was hitting). But just selecting
from the table in IBConsole while logged in as the user with permission
granted via the role, gives the "no permission for read/select access to

If I grant the user permission to the table, then the SELECT works OK.

I even built a test case with a new user, new role, and new tables to
simulate the problem. New table (Foo) structure looks the same as the other
table. Role granted OK, user granted to role OK. SELECT works fine. Strange
that its not working on the old table, but it works on the test table.

For expediency of product release, i just went with granting the user
permission and left the role issue for later debugging.