detail information of user's grant
Hi, How can I obtain information of user's grant for specific table? For example grant and level of privileges are located in sysusers (I suppose), but that is information for all database. Is a table, where this information is? Or the other way to obtain this information? GRANT DELETE, SELECT, UPDATE (customer_num, fname, lname) ON customer TO mary, john TIA andrew
andrew <kuraprok@friko5.onet.pl> schrieb in im Newsbeitrag: 87rghb$d7r$1@netra.daewoo.com.pl... > Hi, > How can I obtain information of user's grant for specific table? For example > grant and level of privileges are located in sysusers (I suppose), but that > is information for all database. Is a table, where this information is? Or > the other way to obtain this information? > > > GRANT DELETE, SELECT, UPDATE (customer_num, fname, lname) > ON customer TO mary, john > > TIA > andrew > > Andrew, systabauth, syscolauth will provide what youare searching for HTH, Reinhard
The easiest way is use (from the command line):
dbschema -d dbname -t tabname
If you want to know all the permissions granted to a user:
dbschema -d dbname -p username
"andrew" <kuraprok@friko5.onet.pl> wrote in message
news:87rghb$d7r$1@netra.daewoo.com.pl...
Hi,
How can I obtain information of user's grant for specific table? For example
grant and level of privileges are located in sysusers (I suppose), but that
is information for all database. Is a table, where this information is? Or
the other way to obtain this information?
GRANT DELETE, SELECT, UPDATE (customer_num, fname, lname)
ON customer TO mary, john
TIA
andrew
In article <87rghb$d7r$1@netra.daewoo.com.pl>,
"andrew" <kuraprok@friko5.onet.pl> wrote:
> Hi,
> How can I obtain information of user's grant for specific table? For
example
> grant and level of privileges are located in sysusers (I suppose),
but that
> is information for all database. Is a table, where this information
is? Or
> the other way to obtain this information?
>
> GRANT DELETE, SELECT, UPDATE (customer_num, fname, lname)
> ON customer TO mary, john
>
> TIA
> andrew
>
>
Try :-
select a.grantor, a.grantee, b.tabname, a.tabauth
from systabauth a, systables b
where a.tabid = b.tabid
and a.tabid > 99
order by 3,2,1
simple, but effective
Regards
Mr Creosote
--
"Just a waffer thin mint?"
Sent via Deja.com http://www.deja.com/
Before you buy.
Mister Creosote wrote:
> In article <87rghb$d7r$1@netra.daewoo.com.pl>,
> "andrew" <kuraprok@friko5.onet.pl> wrote:
> > How can I obtain information of user's grant for specific table? For
> example
> > grant and level of privileges are located in sysusers (I suppose), but
> that
> > is information for all database. Is a table, where this information
> is? Or
> > the other way to obtain this information?
> >
> > GRANT DELETE, SELECT, UPDATE (customer_num, fname, lname)
> > ON customer TO mary, john
>
> Try :-
>
> select a.grantor, a.grantee, b.tabname, a.tabauth
> from systabauth a, systables b
> where a.tabid = b.tabid
> and a.tabid > 99
> order by 3,2,1>
> simple, but effective
...and adequate for the majority of GRANT statements, but not for
the example shown. If the data in systabauth has a * in position 3,
then there is detailed information about the columns on which
privileges were granted in the syscolauth table.
Writing a single SELECT statement to deal with these as well as the
regular case is highly non-trivial.
SELECT A.Grantor, A.Grantee, B.Tabname, A.TabAuth, "" Colname, "" ColAuth
FROM SysTabAuth A, SysTables B
WHERE A.TabID = B.TabID
AND A.TabID > 100
AND A.TabAuth[3] != '*'
UNION
SELECT A.Grantor, A.Grantee, B.Tabname, A.TabAuth, C.Colname, D.ColAuth
FROM SysTabAuth A, SysTables B, OUTER(SysColAuth D, SysColumns C)
WHERE A.TabID = B.TabID
AND A.TabID > 100
AND A.TabAuth[3] = '*'
AND A.Grantor = D.Grantor
AND A.Grantee = D.Grantee
AND A.TabID = D.TabID
AND D.ColNo = C.ColNo
AND D.TabID = C.TabID
ORDER BY 1, 2, 3, 5;
If this won't work, then it may be because of the types of the empty
columns in the first part of the UNION. You may need to make the
ColName string into a literal of length 18 characters (128, I suppose,
if you have IDS.2000 or Foundation.2000), and the ColAuth into a
string of 3 blanks. These will then be type compatible with the
corresponding columns in the second half of this disjoint UNION query.
--
Jonathan Leffler (jleffler@informix.com, jleffler@earthlink.net)
Guardian of DBD::Informix v0.95 -- see http://www.perl.com/CPAN
#include <disclaimer.h>