IDS Forum

Re: -272 when using role and synonym

Posted By: Fernando Nunes
Date: Wednesday, 11 February 2015, at 8:24 p.m.

In Response To: Re: -272 when using role and synonym (Fernando Nunes)

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
>
>
>
> *******************************************************************************
> 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...

--047d7b5d341ee7944b050ed9f714

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.