Question Re: Remote Server External Login Permissions in Technology Q&A

Remote Server External Login Permissions

Former Member — Thu, 16 Jun 2016 03:53:51 GMT

I have created a Remote Server connection called TeleMed from one Sybase ASA 12 database (db1) to another Sybase ASA 12 database (db2) to access a table from the second database in the first database, a Proxy Table

I have created a user called Therapist in the db2, which has permissions to Select and Reference only the one table being the proxy table in db1.

I have a User Group Therapist in db1, with many members. The Group is then used as an External Login to db2 using the Therapist user id and password from db2.

When I log in to db1 as a user XYZ, whom belongs to the User Group Therapist in db1, I can't access the proxy table.

If I add the user XYZ as an External Login using the Therapist user id and password from db2, all works fine.

Why doesn't the external login based on a group allow the user to connect/select the proxy table data, I would have thought that user would be granted access via the membership to the Therapist group.

Is there something I am missing here?

Thanks in advance

Re: Remote Server External Login Permissions

VolkerBarth — Thu, 16 Jun 2016 05:32:40 GMT

AFAIK, remote connections are never based on group membership. Extern logins are created for single users, not for groups.

So I guess you will have to

either add an externlogin for each user of the group or
create users with identical credentials on both databases so they can connect from db1 to db2 with the same credentials, and no externlogin has to be created.

(If my assumptions are correct, than I would share your impression that this seems rather surprising and complex ...)

Note, with v16 and above, remote connections by default are made based on the effective user, not the logged-in user, so you could turn the proxy access in, say, a procedure with SQL SECURITY DEFINER, and then only the owner of the procedure would need an externlogin - that and the different v12 behaviour are discussed here (note particularly Karim's comments) and here.

Re: Remote Server External Login Permissions

Former Member — Thu, 16 Jun 2016 20:17:38 GMT

Hi Volker,

Thanks for your answer, and yes I am a bit surprised that the Group can be added to the external login, but then members of the group don't "inherit" those permissions.

Will just have to add each user to the external login.

THanks

Re: Remote Server External Login Permissions

VolkerBarth — Fri, 17 Jun 2016 03:58:38 GMT

I am a bit surprised that the Group can be added to the external login

That detail should not come as a surprise because up to v12, a group is a user itself by design, and so the group as a user can have an externlogin, too.