IDS Forum

Re: -272 when using role and synonym

Posted By: Fernando Nunes
Date: Wednesday, 11 February 2015, at 7:51 p.m.

In Response To: Re: -272 when using role and synonym (MARK COLLINS)

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

Messages In This Thread

• -272 when using role and synonym
  MARK COLLINS -- Wednesday, 11 February 2015, at 1:23 p.m.
  • Re: -272 when using role and synonym
    MARK COLLINS -- Wednesday, 11 February 2015, at 1:25 p.m.
  • Re: -272 when using role and synonym
    MARK JALKIEWICZ -- Wednesday, 11 February 2015, at 2:48 p.m.
    • Re: -272 when using role and synonym
      MARK COLLINS -- Wednesday, 11 February 2015, at 3:32 p.m.
  • Re: -272 when using role and synonym
    Art Kagel -- Wednesday, 11 February 2015, at 2:52 p.m.
    • Re: -272 when using role and synonym
      MARK COLLINS -- Wednesday, 11 February 2015, at 3:39 p.m.
      • Re: -272 when using role and synonym
        Art Kagel -- Wednesday, 11 February 2015, at 3:52 p.m.
        • Re: -272 when using role and synonym
          MARK COLLINS -- Wednesday, 11 February 2015, at 4:24 p.m.
          • Re: -272 when using role and synonym
            Art Kagel -- Wednesday, 11 February 2015, at 4:38 p.m.
          • Re: -272 when using role and synonym
            MARK JALKIEWICZ -- Wednesday, 11 February 2015, at 5:11 p.m.
  • Re: -272 when using role and synonym
    Fernando Nunes -- Wednesday, 11 February 2015, at 6:21 p.m.
    • Re: -272 when using role and synonym
      david@smooth1.co.uk -- Wednesday, 11 February 2015, at 6:58 p.m.
    • Re: -272 when using role and synonym
      MARK COLLINS -- Wednesday, 11 February 2015, at 7:13 p.m.
      • Re: -272 when using role and synonym
        Fernando Nunes -- Wednesday, 11 February 2015, at 7:51 p.m.
        • Re: -272 when using role and synonym
          Fernando Nunes -- Wednesday, 11 February 2015, at 8:24 p.m.
          • Re: -272 when using role and synonym
            MARK COLLINS -- Thursday, 12 February 2015, at 10:10 a.m.

IDS Forum is maintained by Administrator with WebBBS 5.12.