IDS Forum

Re: -272 when using role and synonym

Posted By: MARK COLLINS
Date: Wednesday, 11 February 2015, at 7:13 p.m.

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

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.

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.