systabauth
Dirk (IDS 10 on AIX) wanted a query over systables/systabauth to list tables matching 'bill_cyc*' with no SELECT privilege for public, filtering on tabauth[1,1]='-', but got no useful results. Dave Griffen explained the flawed assumption: if public has no privileges at all, there is simply no systabauth row, so an outer join plus a 'tabauth is null' test is needed. Art Kagel showed that with the old Informix OUTER syntax the filters are applied pre-join and give wrong results, so an ANSI LEFT OUTER JOIN should be used, with the grantee='public' condition in the ON clause and the tabauth/tabname tests in the WHERE clause (he also suggested rtrim(tabname)). Dave retested and agreed. Working ANSI query posted.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
IDS 10
AIX 5
From the manual:
Column TABAUTH (char 9)
Pattern that specifies privileges on the table, view, synonym, or (IDS)
sequence:
s or S = Select
u or U = Update
* = Column-level privilege
i or I = Insert
d or D = Delete
x or X = Index
a or A = Alter
r or R = References
n or N = Under privilege (IDS)
A hyphen ( - ) indicates the absence of the privilege corresponding to that
position within the tabauth pattern.
I have this query to find all tables starting with "bill_cyc*", that do not
have Select privileges, but it is not working. The problem must be on the (and
tabauth[1,1]) line. What am I doing wrong here ?
output to grant_bill_cyc.sql
select first 50 "grant select on "||tabname||" to public as"||owner||";",created
from systables t, systabauth a
where t.tabid = a.tabid
and tabname matches 'bill_cyc*'
and tabauth[1,1] = '-'
and grantee = 'public'
order by created desc
Dirk
________________________________
NOTE: This e-mail message is subject to the MTN Group disclaimer see
http://www.mtn.co.za/SUPPORT/LEGAL/Pages/EmailDisclaimer.aspx
Dirk Wrote:
--------------------------------------------------------------------------------
IDS 10
AIX 5
From the manual:
Column TABAUTH (char 9)
Pattern that specifies privileges on the table, view, synonym, or (IDS)
sequence:
s or S = Select
u or U = Update
* = Column-level privilege
i or I = Insert
d or D = Delete
x or X = Index
a or A = Alter
r or R = References
n or N = Under privilege (IDS)
A hyphen ( - ) indicates the absence of the privilege corresponding to that
position within the tabauth pattern.
I have this query to find all tables starting with "bill_cyc*", that do not
have Select privileges, but it is not working. The problem must be on the (and
tabauth[1,1]) line. What am I doing wrong here ?
output to grant_bill_cyc.sql
select first 50 "grant select on "||tabname||" to public as"||owner||";",created
from systables t, systabauth a
where t.tabid = a.tabid
and tabname matches 'bill_cyc*'
and tabauth[1,1] = '-'
and grantee = 'public'
order by created desc
Dirk
--------------------------------------------------------------------------------
Response:
You have presumed that a public systabauth record exists for each table. That
is a false presumption. A hyphen in the first position would indicate a lack
of select permissions, but only if public had been granted some other
permission on the table. If public has no permissions at all on a table, there
is no need for a systabauth record.
So what you probably want is something like...
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
HTH,
Dave Griffen
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. So, modify it
to:
output to grant_bill_cyc.sql
select first 50 "grant select on "|| rtrim( tabname ) ||" to public as"||owner||";",created
from systables t
left outer join systabauth a
on
t.tabid = a.tabid
and tabname matches 'bill_cyc*'
and grantee = 'public'
where tabauth[1,1] = '-' or tabauth is null
order by created desc;
I also added the rtrim( tabname ) to eliminate lots of unnecessary trailing
spaces.
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 Tue, Jul 5, 2011 at 5:45 PM, DAVE GRIFFEN <dgriffen@finishline.com>wrote:
> Dirk Wrote:
>
>
>
--------------------------------------------------------------------------------
> IDS 10
> AIX 5
>
> >From the manual:
>
> Column TABAUTH (char 9)
>
> Pattern that specifies privileges on the table, view, synonym, or (IDS)
> sequence:
> s or S = Select
> u or U = Update
> * = Column-level privilege
> i or I = Insert
> d or D = Delete
> x or X = Index
> a or A = Alter
> r or R = References
> n or N = Under privilege (IDS)
>
> A hyphen ( - ) indicates the absence of the privilege corresponding to that
> position within the tabauth pattern.
>
> I have this query to find all tables starting with "bill_cyc*", that do not
> have Select privileges, but it is not working. The problem must be on the
> (and
> tabauth[1,1]) line. What am I doing wrong here ?
>
> output to grant_bill_cyc.sql
> select first 50 "grant select on "||tabname||" to public as> "||owner||";",created
> from systables t, systabauth a
> where t.tabid = a.tabid
> and tabname matches 'bill_cyc*'
> and tabauth[1,1] = '-'
> and grantee = 'public'
> order by created desc
>
> Dirk
>
>
>
--------------------------------------------------------------------------------
>
> Response:
> You have presumed that a public systabauth record exists for each table.
> That
> is a false presumption. A hyphen in the first position would indicate a
> lack
> of select permissions, but only if public had been granted some other
> permission on the table. If public has no permissions at all on a table,
> there
> is no need for a systabauth record.
>
> So what you probably want is something like...
>
> 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
>
> HTH,
> Dave Griffen
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--bcaec53f903dcfcb2e04a7599a22
Thank you very much. I will have to play with the select statement a bit,
especially this part: and grantee = 'public'
I also want the tables that have no public access granted, in my list.
Thank you Dave !
> -----Original Message-----
> From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of
> DAVE GRIFFEN
> Sent: Tuesday, 05 July 2011 11:45 PM
> To: ids@iiug.org
> Subject: Re: systabauth [24224]
>
> Dirk Wrote:
>
> -----------------------------------------------------------------------
> ---------
> IDS 10
> AIX 5
>
> >From the manual:
>
> Column TABAUTH (char 9)
>
> Pattern that specifies privileges on the table, view, synonym, or (IDS)
> sequence:
> s or S = Select
> u or U = Update
> * = Column-level privilege
> i or I = Insert
> d or D = Delete
> x or X = Index
> a or A = Alter
> r or R = References
> n or N = Under privilege (IDS)
>
> A hyphen ( - ) indicates the absence of the privilege corresponding to
> that
> position within the tabauth pattern.
>
> I have this query to find all tables starting with "bill_cyc*", that do
> not
> have Select privileges, but it is not working. The problem must be on
> the (and
> tabauth[1,1]) line. What am I doing wrong here ?
>
> output to grant_bill_cyc.sql
> select first 50 "grant select on "||tabname||" to public as> "||owner||";",created
> from systables t, systabauth a
> where t.tabid = a.tabid
> and tabname matches 'bill_cyc*'
> and tabauth[1,1] = '-'
> and grantee = 'public'
> order by created desc
>
> Dirk
>
> -----------------------------------------------------------------------
> ---------
>
> Response:
> You have presumed that a public systabauth record exists for each
> table. That
> is a false presumption. A hyphen in the first position would indicate a
> lack
> of select permissions, but only if public had been granted some other
> permission on the table. If public has no permissions at all on a
> table, there
> is no need for a systabauth record.
>
> So what you probably want is something like...
>
> 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
>
> HTH,
> Dave Griffen
>
>
> ***********************************************************************
> ********
> Forum Note: Use "Reply" to post a response in the discussion forum.
NOTE: This e-mail message is subject to the MTN Group disclaimer see
http://www.mtn.co.za/SUPPORT/LEGAL/Pages/EmailDisclaimer.aspx
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?
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.
--------------------------------------------------------------------------------
My 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?
************************************************************************
My 2nd Response:
Sorry Art,
After some more testing, I see the problem with the Non ANSI. It continues to
show the record even after select permissions have been granted.
Btw, you may want to move the tabname reference to the main where clause in
your ANSI version. Having it included in the left outer looks like it has some
issues.
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