-272 when using role and synonym

IDS 11.50.FC6, HP-UX 11.31 PA-RISC We have two databases, with a table in one db and some synonyms in the other db pointing to the tables in the first db. In other words, in DB_1 we have: create table abc (...); create table xyz (...); and in DB_2 we have: create synonym abc for db_1:abc; create synonym xyz for db_1:xyz; Our application connects to DB_1 and then does a SET ROLE statement, followed by a SELECT statement. If we try 'SELECT * FROM abc', the query runs successfully and returns data. If we try 'SELECT * FROM xyz', it returns sqlcode -272 SELECT permission for xyz. I have confirmed that both of the tables (in DB_2) have SELECT privileges granted to the role that is being used. Everything that I can think of to compare these two tables, relative to security, looks the same. I'm sure it's something simple that I've overlooked. Any help appreciated.

I should mention, the application is in Genero, but I have replicated the problem with dbaccess as well, so it is not an issue with the application, but with the permissions inside the database(s).

Mark, check to see if sysdbopen() procedure exists for the user (or public) and if so check to see what it is doing. Mark

Privileges on the synonyms OK? Art Art S. Kagel, President and Principal Consultant ASK Database Management www.askdbmgt.com Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Wed, Feb 11, 2015 at 1:23 PM, MARK COLLINS <markc@myfastmail.com> wrote: > IDS 11.50.FC6, HP-UX 11.31 PA-RISC > > We have two databases, with a table in one db and some synonyms in the > other > db pointing to the tables in the first db. In other words, in DB_1 we have: > > create table abc (...); > create table xyz (...);> > and in DB_2 we have: > > create synonym abc for db_1:abc; > create synonym xyz for db_1:xyz;> > Our application connects to DB_1 and then does a SET ROLE statement, > followed > by a SELECT statement. If we try 'SELECT * FROM abc', the query runs > successfully and returns data. If we try 'SELECT * FROM xyz', it returns > sqlcode -272 SELECT permission for xyz. > > I have confirmed that both of the tables (in DB_2) have SELECT privileges > granted to the role that is being used. Everything that I can think of to > compare these two tables, relative to security, looks the same. > > I'm sure it's something simple that I've overlooked. Any help appreciated. > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --089e011766d3310b85050ed55831

Mark, There is no procedure named sysdbopen in sysprocedures in either DB_1 or DB_2. Mark, check to see if sysdbopen() procedure exists for the user (or public) and if so check to see what it is doing. Mark

Art, Not sure which privileges you are referring to. There are no privileges granted to the synonyms in DB_2. I have checked both the xyz and abc synonyms, and there are no entries in systabauth. Just to confirm, I tried to do GRANT SELECT ON xyz TO app_role, but I got "556: Cannot create, drop, or modify an object that is external to current database". Looking at the real tables in DB_1 to which the synonyms (in DB_2) point, yes, the role has the correct (SELECT) permission for both abc and xyz, but only abc works. Mark Privileges on the synonyms OK? Art

Does dbschema (or myschema) report that xyz is a private synonym? Art Art S. Kagel, President and Principal Consultant ASK Database Management www.askdbmgt.com Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Wed, Feb 11, 2015 at 3:39 PM, MARK COLLINS <markc@myfastmail.com> wrote: > Art, > > Not sure which privileges you are referring to. There are no privileges > granted to the synonyms in DB_2. I have checked both the xyz and abc > synonyms, > and there are no entries in systabauth. > > Just to confirm, I tried to do GRANT SELECT ON xyz TO app_role, but I got > "556: Cannot create, drop, or modify an object that is external to current > database". > > Looking at the real tables in DB_1 to which the synonyms (in DB_2) point, > yes, > the role has the correct (SELECT) permission for both abc and xyz, but only > abc works. > > Mark > > Privileges on the synonyms OK? > > Art > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --089e013d17b62bda4f050ed62e96

Art, No, the synonyms look identical (using dbschema): CREATE SYNONYM abc ON db_1:abc; CREATE SYNONYM xzy ON db_1:xyz; There is nothing specifying public or private in the CREATE SYNONYM statement for either synonym, so implicitly they should both be PUBLIC (for a non-ANSI database, right?). Mark Does dbschema (or myschema) report that xyz is a private synonym? Art

Yup, public. Stumper. Art Art S. Kagel, President and Principal Consultant ASK Database Management www.askdbmgt.com Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Wed, Feb 11, 2015 at 4:24 PM, MARK COLLINS <markc@myfastmail.com> wrote: > Art, > > No, the synonyms look identical (using dbschema): > > CREATE SYNONYM abc ON db_1:abc; > CREATE SYNONYM xzy ON db_1:xyz;> > There is nothing specifying public or private in the CREATE SYNONYM > statement > for either synonym, so implicitly they should both be PUBLIC (for a > non-ANSI > database, right?). > > Mark > > Does dbschema (or myschema) report that xyz is a private synonym? > > Art > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --001a11c3440e24fb10050ed6d493

I guess at this stage : (on db1) dbschema -ss -d <db> -p "all" | grep <table> dbschema -ss -d <db> -r "all" | grep <user> once you confirm all permissions/roles are correct etc.. contact tech support. other than that you can try dropping the synonym and recreating it again.

I'm sorry, but I think there's something strange in the scenario. Please see my comments below and clarify my doubts. On Wed, Feb 11, 2015 at 6:23 PM, MARK COLLINS <markc@myfastmail.com> wrote: > IDS 11.50.FC6, HP-UX 11.31 PA-RISC > > We have two databases, with a table in one db and some synonyms in the > other > db pointing to the tables in the first db. In other words, in DB_1 we have: > > create table abc (...); > create table xyz (...);> Tables are on DB_1. Ok > > and in DB_2 we have: > > create synonym abc for db_1:abc; > create synonym xyz for db_1:xyz;> > Synonym are in DB_2. Ok > Our application connects to DB_1 and then does a SET ROLE statement, > followed > by a SELECT statement. If we try 'SELECT * FROM abc', the query runs > successfully and returns data. If we try 'SELECT * FROM xyz', it returns > sqlcode -272 SELECT permission for xyz. > So you connect to the database where the roles are created, and you SELECT and it fails for one of the tables. > > I have confirmed that both of the tables (in DB_2) have SELECT privileges > granted to the role that is being used. Everything that I can think of to > compare these two tables, relative to security, looks the same. > Hmmmm... Now you seem to suggest tables are on DB_2. But above you described that they were in DB_1. > > I'm sure it's something simple that I've overlooked. Any help appreciated. > > Not necessarily, but: 1- We need confirmation that the tables are in DB_1, that you're connecting to DB_1 and doing the SELECTs there... And in that case why did you mention the synonyms in DB_2? 2- We would need the tables permissions. A copy/paste of the dbaccess showing that could be the best way, if they fit on one screen. 3- Did you try, or could you try to restart the instance? I have some idea about a cache issue and unfortunately we don't have a way to clear the caches (if you like the idea please find the RFE for that and vote). Table permissions AFAIK are kept in the dictionary cache... 4- If for some reason you mixed things and you're effectively connecting to DB_2 where the synonyms are created and you're doing the SELECT on the synonyms, the role would be irrelevant... ROLEs are a database object. They don't cross databases. If this is your scenario, you may want to check which default role the user has on the remote database and if that role has SELECT permissions on the both tables or just the one that works... or if there are specific permissions on the table that works to the user itself. Regards. -- Fernando Nunes Portugal http://informix-technology.blogspot.com My email works... but I don't check it frequently... --001a113d1024784ca5050ed8428c

Restart the server, run oncheck -cR , oncheck -ce, oncheck -cc. Check for hardware issues Check systables,syssyntable,systabauth for both synonyms Are there any different data types in the 2 base tables? Regards, David. > On 11 February 2015 at 23:21 Fernando Nunes <domusonline@gmail.com> wrote: > > > I'm sorry, but I think there's something strange in the scenario. Please > see my comments below and clarify my doubts. > > On Wed, Feb 11, 2015 at 6:23 PM, MARK COLLINS <markc@myfastmail.com> wrote: > > > IDS 11.50.FC6, HP-UX 11.31 PA-RISC > > > > We have two databases, with a table in one db and some synonyms in the > > other > > db pointing to the tables in the first db. In other words, in DB_1 we have: > > > > create table abc (...); > > create table xyz (...);> > > > Tables are on DB_1. Ok > > > > > and in DB_2 we have: > > > > create synonym abc for db_1:abc; > > create synonym xyz for db_1:xyz;> > > > > Synonym are in DB_2. Ok > > > Our application connects to DB_1 and then does a SET ROLE statement, > > followed > > by a SELECT statement. If we try 'SELECT * FROM abc', the query runs > > successfully and returns data. If we try 'SELECT * FROM xyz', it returns > > sqlcode -272 SELECT permission for xyz. > > > > So you connect to the database where the roles are created, and you SELECT > and it fails for one of the tables. > > > > > I have confirmed that both of the tables (in DB_2) have SELECT privileges > > granted to the role that is being used. Everything that I can think of to > > compare these two tables, relative to security, looks the same. > > > > Hmmmm... Now you seem to suggest tables are on DB_2. But above you > described that they were in DB_1. > > > > > I'm sure it's something simple that I've overlooked. Any help appreciated. > > > > > Not necessarily, but: > > 1- We need confirmation that the tables are in DB_1, that you're connecting > to DB_1 and doing the SELECTs there... And in that case why did you mention > the synonyms in DB_2? > 2- We would need the tables permissions. A copy/paste of the dbaccess > showing that could be the best way, if they fit on one screen. > 3- Did you try, or could you try to restart the instance? I have some idea > about a cache issue and unfortunately we don't have a way to clear the > caches (if you like the idea please find the RFE for that and vote). Table > permissions AFAIK are kept in the dictionary cache... > 4- If for some reason you mixed things and you're effectively connecting to > DB_2 where the synonyms are created and you're doing the SELECT on the > synonyms, the role would be irrelevant... ROLEs are a database object. They > don't cross databases. If this is your scenario, you may want to check > which default role the user has on the remote database and if that role has > SELECT permissions on the both tables or just the one that works... or if > there are specific permissions on the table that works to the user itself. > > Regards. > > -- > Fernando Nunes > Portugal > > http://informix-technology.blogspot.com > My email works... but I don't check it frequently... > > --001a113d1024784ca5050ed8428c > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. >

Fernando, Sorry, there was a typo. The tables are in db_1, the synonyms are in db_2, the application (or my test case in dbaccess) is connecting to db_2, and trying to do a SELECT against the synonym. The role is defined in both db_1 and db_2, and the permissions on the tables in db_1 are set so that the role has SELECT permission (no other permissions). I ran a query joining systabauth to systables so that the tabname can be displayed along with the info from systabauth: grantor db_owner grantee user_app_role tabid 153 tabauth s-------- tabname abc grantor db_owner grantee user_app_role tabid 145 tabauth s-------- tabname xyz So, it is the situation that you described in your point 4. And that suggests that the problem is, as you stated, that the role from db_2 (where the connection is done) is not transferring to the db_1 database where the tables live. The only part that doesn't make sense is that I can do a SELECT against synonym abc, but when I try to do one against xyz, I get the -272 error. Neither of the tables in db_1 have any permissions granted to the user, yet the query against abc is successful and xyz is not. Ah - but abc has permissions granted to public (it should NOT, especially since it has more than just SELECT, but that's a different problem altogether). So, by granting SELECT to the user for both abc and xyz, I now am able to retrieve data from both. Thank you for pointing me in the correct direction. Even though I did not have to restart the instance to fix this issue, I do like your RFE, and will track it down and vote on it. So, as a follow-up question (or observation), this implies that there is no way to use roles for permissions on any synonym where the base table is in an external database. Is that correct? Or is there some way to work around that? The idea of having to grant explicit permissions on all of these tables to individual users, rather than to a single role, is not appealing. I'm sorry, but I think there's something strange in the scenario. Please see my comments below and clarify my doubts. On Wed, Feb 11, 2015 at 6:23 PM, MARK COLLINS <markc@myfastmail.com> wrote: > IDS 11.50.FC6, HP-UX 11.31 PA-RISC > > We have two databases, with a table in one db and some synonyms in the > other > db pointing to the tables in the first db. In other words, in DB_1 we have: > > create table abc (...); > create table xyz (...);> Tables are on DB_1. Ok > > and in DB_2 we have: > > create synonym abc for db_1:abc; > create synonym xyz for db_1:xyz;> > Synonym are in DB_2. Ok > Our application connects to DB_1 and then does a SET ROLE statement, > followed > by a SELECT statement. If we try 'SELECT * FROM abc', the query runs > successfully and returns data. If we try 'SELECT * FROM xyz', it returns > sqlcode -272 SELECT permission for xyz. > So you connect to the database where the roles are created, and you SELECT and it fails for one of the tables. > > I have confirmed that both of the tables (in DB_2) have SELECT privileges > granted to the role that is being used. Everything that I can think of to > compare these two tables, relative to security, looks the same. > Hmmmm... Now you seem to suggest tables are on DB_2. But above you described that they were in DB_1. > > I'm sure it's something simple that I've overlooked. Any help appreciated. > > Not necessarily, but: 1- We need confirmation that the tables are in DB_1, that you're connecting to DB_1 and doing the SELECTs there... And in that case why did you mention the synonyms in DB_2? 2- We would need the tables permissions. A copy/paste of the dbaccess showing that could be the best way, if they fit on one screen. 3- Did you try, or could you try to restart the instance? I have some idea about a cache issue and unfortunately we don't have a way to clear the caches (if you like the idea please find the RFE for that and vote). Table permissions AFAIK are kept in the dictionary cache... 4- If for some reason you mixed things and you're effectively connecting to DB_2 where the synonyms are created and you're doing the SELECT on the synonyms, the role would be irrelevant... ROLEs are a database object. They don't cross databases. If this is your scenario, you may want to check which default role the user has on the remote database and if that role has SELECT permissions on the both tables or just the one that works... or if there are specific permissions on the table that works to the user itself. Regards.

Good that it's solved. As I mentioned the roles are local to the database. There was (at least in the internal database) a feature request to implement something else (instance roles probably, but I can't remember exactly). My suggestion would be to establish the default role of those users in DB_1 to the role that you plan to use for the grants. The default role should apply... other than that, but I would need to test it, I can only think about using sysdbopen() on DB_1 to establish the role. But I'm not sure if sysdbopen() is triggered in a distributed query. And even if it is, you'd probably want to establish a different role whether it was a "direct" connection or a distributed connection and to be honest at this moment I don't know if there's a way to distinguish between them. Tell us if the default role is not an option. Default role was introduced in V10 so you can use it in your version. Regards. On Thu, Feb 12, 2015 at 12:13 AM, MARK COLLINS <markc@myfastmail.com> wrote: > Fernando, > > Sorry, there was a typo. The tables are in db_1, the synonyms are in db_2, > the > application (or my test case in dbaccess) is connecting to db_2, and > trying to > do a SELECT against the synonym. > > The role is defined in both db_1 and db_2, and the permissions on the > tables > in db_1 are set so that the role has SELECT permission (no other > permissions). > I ran a query joining systabauth to systables so that the tabname can be > displayed along with the info from systabauth: > > grantor db_owner > grantee user_app_role > tabid 153 > tabauth s-------- > tabname abc > > grantor db_owner > grantee user_app_role > tabid 145 > tabauth s-------- > tabname xyz > > So, it is the situation that you described in your point 4. And that > suggests > that the problem is, as you stated, that the role from db_2 (where the > connection is done) is not transferring to the db_1 database where the > tables > live. > > The only part that doesn't make sense is that I can do a SELECT against > synonym abc, but when I try to do one against xyz, I get the -272 error. > Neither of the tables in db_1 have any permissions granted to the user, yet > the query against abc is successful and xyz is not. > > Ah - but abc has permissions granted to public (it should NOT, especially > since it has more than just SELECT, but that's a different problem > altogether). So, by granting SELECT to the user for both abc and xyz, I > now am > able to retrieve data from both. > > Thank you for pointing me in the correct direction. > > Even though I did not have to restart the instance to fix this issue, I do > like your RFE, and will track it down and vote on it. > > So, as a follow-up question (or observation), this implies that there is no > way to use roles for permissions on any synonym where the base table is in > an > external database. Is that correct? Or is there some way to work around > that? > The idea of having to grant explicit permissions on all of these tables to > individual users, rather than to a single role, is not appealing. > > I'm sorry, but I think there's something strange in the scenario. Please > see my comments below and clarify my doubts. > > On Wed, Feb 11, 2015 at 6:23 PM, MARK COLLINS <markc@myfastmail.com> > wrote: > > > IDS 11.50.FC6, HP-UX 11.31 PA-RISC > > > > We have two databases, with a table in one db and some synonyms in the > > other > > db pointing to the tables in the first db. In other words, in DB_1 we > have: > > > > create table abc (...); > > create table xyz (...);> > > > Tables are on DB_1. Ok > > > > > and in DB_2 we have: > > > > create synonym abc for db_1:abc; > > create synonym xyz for db_1:xyz;> > > > > Synonym are in DB_2. Ok > > > Our application connects to DB_1 and then does a SET ROLE statement, > > followed > > by a SELECT statement. If we try 'SELECT * FROM abc', the query runs > > successfully and returns data. If we try 'SELECT * FROM xyz', it returns > > sqlcode -272 SELECT permission for xyz. > > > > So you connect to the database where the roles are created, and you SELECT > and it fails for one of the tables. > > > > > I have confirmed that both of the tables (in DB_2) have SELECT privileges > > granted to the role that is being used. Everything that I can think of to > > compare these two tables, relative to security, looks the same. > > > > Hmmmm... Now you seem to suggest tables are on DB_2. But above you > described that they were in DB_1. > > > > > I'm sure it's something simple that I've overlooked. Any help > appreciated. > > > > > Not necessarily, but: > > 1- We need confirmation that the tables are in DB_1, that you're connecting > to DB_1 and doing the SELECTs there... And in that case why did you mention > the synonyms in DB_2? > 2- We would need the tables permissions. A copy/paste of the dbaccess > showing that could be the best way, if they fit on one screen. > 3- Did you try, or could you try to restart the instance? I have some idea > about a cache issue and unfortunately we don't have a way to clear the > caches (if you like the idea please find the RFE for that and vote). Table > permissions AFAIK are kept in the dictionary cache... > 4- If for some reason you mixed things and you're effectively connecting to > DB_2 where the synonyms are created and you're doing the SELECT on the > synonyms, the role would be irrelevant... ROLEs are a database object. They > don't cross databases. If this is your scenario, you may want to check > which default role the user has on the remote database and if that role has > SELECT permissions on the both tables or just the one that works... or if > there are specific permissions on the table that works to the user itself. > > Regards. > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > -- Fernando Nunes Portugal http://informix-technology.blogspot.com My email works... but I don't check it frequently... --001a1138ea24e3a72f050ed985be

sysdbopen() doesn't get executed on distributed queries. Which actually is not a big deal... If you need to have a role for direct connections to the DB and a different role when running a distributed remote query you can create a sysdbopen() procedure and set the role for direct connections in there. And leave the default role as the default for the remote connections. Regards On Thu, Feb 12, 2015 at 12:51 AM, Fernando Nunes <domusonline@gmail.com> wrote: > Good that it's solved. > As I mentioned the roles are local to the database. There was (at least in > the internal database) a feature request to implement something else > (instance roles probably, but I can't remember exactly). > My suggestion would be to establish the default role of those users in DB_1 > to the role that you plan to use for the grants. The default role should > apply... > other than that, but I would need to test it, I can only think about using > sysdbopen() on DB_1 to establish the role. But I'm not sure if sysdbopen() > is triggered in a distributed query. And even if it is, you'd probably want > to establish a different role whether it was a "direct" connection or a > distributed connection and to be honest at this moment I don't know if > there's a way to distinguish between them. > > Tell us if the default role is not an option. Default role was introduced > in V10 so you can use it in your version. > > Regards. > > On Thu, Feb 12, 2015 at 12:13 AM, MARK COLLINS <markc@myfastmail.com> > wrote: > > > Fernando, > > > > Sorry, there was a typo. The tables are in db_1, the synonyms are in > db_2, > > the > > application (or my test case in dbaccess) is connecting to db_2, and > > trying to > > do a SELECT against the synonym. > > > > The role is defined in both db_1 and db_2, and the permissions on the > > tables > > in db_1 are set so that the role has SELECT permission (no other > > permissions). > > I ran a query joining systabauth to systables so that the tabname can be > > displayed along with the info from systabauth: > > > > grantor db_owner > > grantee user_app_role > > tabid 153 > > tabauth s-------- > > tabname abc > > > > grantor db_owner > > grantee user_app_role > > tabid 145 > > tabauth s-------- > > tabname xyz > > > > So, it is the situation that you described in your point 4. And that > > suggests > > that the problem is, as you stated, that the role from db_2 (where the > > connection is done) is not transferring to the db_1 database where the > > tables > > live. > > > > The only part that doesn't make sense is that I can do a SELECT against > > synonym abc, but when I try to do one against xyz, I get the -272 error. > > Neither of the tables in db_1 have any permissions granted to the user, > yet > > the query against abc is successful and xyz is not. > > > > Ah - but abc has permissions granted to public (it should NOT, especially > > since it has more than just SELECT, but that's a different problem > > altogether). So, by granting SELECT to the user for both abc and xyz, I > > now am > > able to retrieve data from both. > > > > Thank you for pointing me in the correct direction. > > > > Even though I did not have to restart the instance to fix this issue, I > do > > like your RFE, and will track it down and vote on it. > > > > So, as a follow-up question (or observation), this implies that there is > no > > way to use roles for permissions on any synonym where the base table is > in > > an > > external database. Is that correct? Or is there some way to work around > > that? > > The idea of having to grant explicit permissions on all of these tables > to > > individual users, rather than to a single role, is not appealing. > > > > I'm sorry, but I think there's something strange in the scenario. Please > > see my comments below and clarify my doubts. > > > > On Wed, Feb 11, 2015 at 6:23 PM, MARK COLLINS <markc@myfastmail.com> > > wrote: > > > > > IDS 11.50.FC6, HP-UX 11.31 PA-RISC > > > > > > We have two databases, with a table in one db and some synonyms in the > > > other > > > db pointing to the tables in the first db. In other words, in DB_1 we > > have: > > > > > > create table abc (...); > > > create table xyz (...);> > > > > > > Tables are on DB_1. Ok > > > > > > > > and in DB_2 we have: > > > > > > create synonym abc for db_1:abc; > > > create synonym xyz for db_1:xyz;> > > > > > > > Synonym are in DB_2. Ok > > > > > Our application connects to DB_1 and then does a SET ROLE statement, > > > followed > > > by a SELECT statement. If we try 'SELECT * FROM abc', the query runs > > > successfully and returns data. If we try 'SELECT * FROM xyz', it > returns > > > sqlcode -272 SELECT permission for xyz. > > > > > > > So you connect to the database where the roles are created, and you > SELECT > > and it fails for one of the tables. > > > > > > > > I have confirmed that both of the tables (in DB_2) have SELECT > privileges > > > granted to the role that is being used. Everything that I can think of > to > > > compare these two tables, relative to security, looks the same. > > > > > > > Hmmmm... Now you seem to suggest tables are on DB_2. But above you > > described that they were in DB_1. > > > > > > > > I'm sure it's something simple that I've overlooked. Any help > > appreciated. > > > > > > > > Not necessarily, but: > > > > 1- We need confirmation that the tables are in DB_1, that you're > connecting > > to DB_1 and doing the SELECTs there... And in that case why did you > mention > > the synonyms in DB_2? > > 2- We would need the tables permissions. A copy/paste of the dbaccess > > showing that could be the best way, if they fit on one screen. > > 3- Did you try, or could you try to restart the instance? I have some > idea > > about a cache issue and unfortunately we don't have a way to clear the > > caches (if you like the idea please find the RFE for that and vote). > Table > > permissions AFAIK are kept in the dictionary cache... > > 4- If for some reason you mixed things and you're effectively connecting > to > > DB_2 where the synonyms are created and you're doing the SELECT on the > > synonyms, the role would be irrelevant... ROLEs are a database object. > They > > don't cross databases. If this is your scenario, you may want to check > > which default role the user has on the remote database and if that role > has > > SELECT permissions on the both tables or just the one that works... or if > > there are specific permissions on the table that works to the user > itself. > > > > Regards. > > > > > > > > > > ******************************************************************************* > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > -- > Fernando Nunes > Portugal > > http://informix-technology.blogspot.com > My email works... but I don't check it frequently... > > --001a1138ea24e3a72f050ed985be >

Fernando, Thanks. I had read the docs on sysdbopen() and it looked like it wouldn't work for implicit connections like distributed queries. I have tested the default role and it does address the issue at hand. I like your idea of using sysdbopen() if we need a different role for the explicit connections. Mark sysdbopen() doesn't get executed on distributed queries. Which actually is not a big deal... If you need to have a role for direct connections to the DB and a different role when running a distributed remote query you can create a sysdbopen() procedure and set the role for direct connections in there. And leave the default role as the default for the remote connections. Regards