IDS Forum

Re: systabauth

Posted By: Art Kagel
Date: Wednesday, 6 July 2011, at 1:17 p.m.

In Response To: Re: systabauth (DAVE GRIFFEN)

Actually, Dave, I didn't try it. I was on my phone traveling when I
posted. And indeed, first there is a bug in my version, the filter on the
tabname (if you need to include one) should also be in the WHERE clause to
work correctly:

select first 50 "grant select on "||tabname||" to public as
"||owner||";",created
from systables as t
left outer join systabauth as a

on t.tabid = a.tabid

and grantee = 'public'
where (tabauth[1,1] = '-' or tabauth is null)
and tabname matches 'bill_cyc*'
order by created desc;

Second, your version actually doesn't work. It MAY return correct data but
that's just an accident. You can see below that if you remove the
"tabauth[1,1] = '-' " filter, it will return unmatched records even for
tables that do have a matching systabauth record. If you reverse the IS
NULL for IS NOT NULL, your version returns the same results it does for IS
NULL, obviously incorrect. The ANSI query on the other hand returns the
correct results always because the WHERE clause is applied post-join in ANSI
queries but pre-join in older Informix style queries. See results below:

> select * from systabauth where tabid in (106,191);

grantor art
grantee public
tabid 106
tabauth -u-idx---

1 row(s) retrieved.

Note that talbe 106 has a systabauth record without SELECT privs and table
191 has no systabauth record.

> select t.tabname, a.*
from systables as t
left outer join systabauth as a

on t.tabid = a.tabid

and grantee = 'public'
where (tabauth[1,1] = '-' or tabauth is null)
and t.tabid in (106,191)
order by created desc;

tabname tst
grantor art
grantee public
tabid 106
tabauth -u-idx---

tabname tt
grantor
grantee
tabid
tabauth

2 row(s) retrieved.

> select t.tabname, a.*
from systables t, outer systabauth a
where t.tabid = a.tabid
and (tabauth[1,1] = '-' or tabauth is null)
and grantee = 'public' and t.tabid in (106,191)
order by created desc;

tabname tst
grantor art
grantee public
tabid 106
tabauth -u-idx---

tabname tt
grantor
grantee
tabid
tabauth

2 row(s) retrieved.

> select t.tabname, a.*
from systables t, outer systabauth a
where t.tabid = a.tabid
and tabauth is null
and grantee = 'public' and t.tabid in (106,191)
order by created desc;

tabname tst
grantor
grantee
tabid
tabauth

tabname tt
grantor
grantee
tabid
tabauth

2 row(s) retrieved.

> select t.tabname, a.*
from systables t, outer systabauth a
where t.tabid = a.tabid
and tabauth is not null
and grantee = 'public' and t.tabid in (106,191)
order by created desc;

tabname tst
grantor art
grantee public
tabid 106
tabauth -u-idx---

tabname tt
grantor
grantee
tabid
tabauth

2 row(s) retrieved.

> select t.tabname, a.*
from systables as t
left outer join systabauth as a

on t.tabid = a.tabid

and grantee = 'public'
where (tabauth[1,1] = '-' or tabauth is not null)
and t.tabid in (106,191)
order by created desc;

tabname tst
grantor art
grantee public
tabid 106
tabauth -u-idx---

1 row(s) retrieved.

Art

Art S. Kagel
Advanced DataTools (www.advancedatatools.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 my employer, Advanced DataTools, 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, Jul 6, 2011 at 9:05 AM, DAVE GRIFFEN <dgriffen@finishline.com>wrote:

> Art Kagel Wrote:
>
>
> --------------------------------------------------------------------------------
> Almost Dave. You can't filter for values in non-matched OUTER join rows
> using the older syntax, you have to use an ANSI style query.
>
>
> --------------------------------------------------------------------------------
> Response:
> Yes, I can and did do it with a Non ANSI style query. The exact syntax I
> posted previously works perfectly fine on my system.
>
> output to grant_bill_cyc.sql
> select first 50 "grant select on "||tabname||" to public as
> "||owner||";",created
> from systables t, outer systabauth a
> where t.tabid = a.tabid
> and tabname matches 'bill_cyc*'
> and (tabauth[1,1] = '-' or tabauth is null)
> and grantee = 'public'
> order by created desc
>
> Did you actually test this syntax or did you dismiss it on sight?
>
>
>
> *******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>

--20cf307c9aa4ddb63b04a769c457

Messages In This Thread

• systabauth
  Dirk Cornelis Moolman [ MTN - Innovation Centre ] -- Tuesday, 5 July 2011, at 9:40 a.m.
  • Re: systabauth
    DAVE GRIFFEN -- Tuesday, 5 July 2011, at 5:45 p.m.
    • Re: systabauth
      Art Kagel -- Tuesday, 5 July 2011, at 6:00 p.m.
      • Re: systabauth
        DAVE GRIFFEN -- Wednesday, 6 July 2011, at 9:05 a.m.
        • Re: systabauth
          DAVE GRIFFEN -- Wednesday, 6 July 2011, at 9:39 a.m.
        • Re: systabauth
          Art Kagel -- Wednesday, 6 July 2011, at 1:17 p.m.
    • RE: systabauth
      Dirk Cornelis Moolman [ MTN - Innovation Centre ] -- Wednesday, 6 July 2011, at 5:33 a.m.

IDS Forum is maintained by Administrator with WebBBS 5.12.