When I grant a user access to a table through a role, then try to link to that table from within Microsoft Access using the user's credentials, the table does not show up as being one to which the user has access. Therefore the user cannot link to the table. At least not without also granting select access to the table directly to the user.
Here is a script. This happens using a 11.6.2 back end and using 11.6.x front end.
Code: Select all
connect island 1 sysadm/sysadm;
REMARK
\
Create a user and a role and grant the role to the user.
/
grant connect to Me identified by My_Password;
create role My_Role;
grant role My_Role to Me;
commit;
REMARK
\
Create a table and grant select on the table to the role.
/
create table My_Table_to_Link (
A_Field smallint not null);
insert into My_Table_to_Link values (1);
grant select on My_Table_to_Link to role My_Role;
commit;
REMARK
\
Test accessing the table by the new user.
/
connect island 2 Me/My_Password;
select * from SysAdm.My_Table_to_Link;
disconnect 2;
REMARK
\
Start up Microsoft Access, create a new database and try to link to the table using the Me user.
When I do it, the table does not show up in the list of tables.
/
REMARK
\
Now grant the user direct access to the table.
/
grant select on My_Table_to_Link to Me;
commit;
REMARK
\
Back in Microsoft Access, try again to link to the table using the Me user.
When I do it, the table now shows (not surprisingly).
Now remove the user's direct access to the table.
/
revoke select on My_Table_to_Link from Me;
commit;
REMARK
\
Quit MS Access and reopen database see if the Me user can access the newly linked table.
It works for me. So the problem is only when getting the list of tables to which the user has access.
/
REMARK
\
Finally remove the user if desired.
/
revoke role My_Role from Me;
revoke connect from Me;
commit;