No select permission
Hari Gupta
Just a quick and simple/complex question - When a user gets "No select
permission" (err -272), Is there any way (using SQL) to find out which
table user doesn't have select permission ? Using onaudit, I can get
this but would be great if this can be achived by quering sysmaster
tables.
TIA.
Jonathan Leffler
You don't need sysmaster - you need the current database's system
catalog...
You need to find the tables which that user can select from, and then
generate the list of tables which isn't in the list.
A user can select from a table if:
1. the corresponding systabauth entry for the given user includes 's'
or 'S' in column 1, or
2. the corresponding systabauth entry for the user's current role
includes 's' or 'S' in column 1, or
3. the corresponding systabauth entry for the pseudo-user PUBLIC
includes 's' or 'S' in column 1.
Good, clean fun:
SELECT DISTINCT a.tabid
FROM 'informix'.systabauth a, 'informix'.systables t
WHERE t.tabid = a.tabid
AND t.tabtype = 'T'
AND a.tabauth[1] IN ('s', 'S') -- might be too tricky!
--AND a.tabauth MATCHES "[sS]*" -- should work regardless
AND (a.grantee = USER OR a.grantee = 'public' OR
USER IN (SELECT r.grantee
FROM 'informix'.sysroleauth r
WHERE r.rolename = a.grantee
)
)
The distinct isn't 100% necessary. I think the role determination is
correct, but I'm not completely sure of that.
You then generate the list of tables where the tabid is not in list
above. Call that mouthful '<select-A>' and you can write:
SELECT DISTINCT 1t.owner, t1.tabname, t1.tabid
FROM 'informix'.systables t1
WHERE t1.tabtype = 'T'
AND t1.tabid NOT IN (<select-A>)
This generates the list of true (base) tables for which the current
user does not have SELECT permission on the table through the PUBLIC
permissions, the user's explicit permissions, or the permissions of
one of the roles which the user is permitted to set as the current role.
That hasn't been past a server - I reserve the right to have silly
mistakes in it.
Residual problem - what about synonyms (public and private), views,
and so on? Answer - the query above does not address these issues.
You can modify the tabtype queries to permit other types.
--
Jonathan Leffler #include <disclaimer.h>
Email: jlef...@earthlink.net, jlef...@us.ibm.com
Guardian of DBD::Informix v2003.04 -- http://dbi.perl.org/
Hari Gupta
Solution given by you has almost resolved my 98% problem, however,
remaining 2% may still need to address. I can now find out a list of
tables to which user does not have permission (there are couple of
100s of such tables for that user) but how do I know which table(s)
require SELECT permission when user session is active. In other words
when a user connects database and try to run a query and gets "No
Select Permission", how do I find out which table(s) are being
accessed. onstat -g ses <sid> does not display current sql.
Can some one please help me out ?
TIA
Jonathan Leffler
> Thanks a ton Jonathan for very clear explaination as always you do.
I note that I forgot to mention that if you own a table, you have
select permission on it -- and you cannot remove that permission.
(Side effect: you cannot remove the table owner's permission to update
the rows in a table, nor their permission to insert or delete rows.
> Solution given by you has almost resolved my 98% problem, however,
> remaining 2% may still need to address. I can now find out a list of
> tables to which user does not have permission (there are couple of
> 100s of such tables for that user) but how do I know which table(s)
> require SELECT permission when user session is active. In other words
> when a user connects database and try to run a query and gets "No
> Select Permission", how do I find out which table(s) are being
> accessed. onstat -g ses <sid> does not display current sql.
Is it an application that you wrote that gets the error? Or one over
which you have influence? If so, you can look to modifying the code
that reports the error so that it logs more information - ideally, the
SQL statement but maybe the source file and line number are the best
that's readily available. If it is a library function that is failing
on some dynamic SQL, getting decent data about the calling sequence
may be harder.
If you have no control over the application and the error reporting,
you are in for a harder time. Platform and version information was
not given - and wasn't particularly important either. Now it might
be. You could look at onaudit auditing - that might be more verbose
than you want, though. If you can get hold of sessions reliably, then
maybe onstat and some options would do the trick, but what worries me
in such cases is that the session probably terminates too quickly
after detecting an error, losing the important data.
scottishpoet
However, just cause he tries to access it doesn't mean he should have
permission to read it.
probably worth a conversation with your application people to find out
what tables different types of people should be able to access.
If anyone can read anything then just grant select to "public"
har...@yahoo.com (Hari Gupta) wrote in message news:<1a1cd35b.04091...@posting.google.com>...
Hari Gupta
display SQL stmt if a user has got an err -272. I do not want to give
public read access to all tables as we need to restrict access for
sensitive info. tables. I am sure engine must be storing some where
when user tries to access those restricted tables. onaudit does record
tabid and error number but I don't want to start onaudit at the moment
on production as I am assessing load/space impact of onaudit. May be
some one shed lights on this.
Thanks anyway.
Jonathan Leffler
> Thanks Scott for your time to reply. onstat -g sql <sid> does not
> display SQL stmt if a user has got an err -272.
I think you're correct. That shows the last successfully parsed SQL;
failed SQL isn't tracked.
> I do not want to give public read access to all tables as we need
> to restrict access for sensitive info. tables.
Fair enough. You may want to assess which tables are truly sensitive
and which are not, and grant more general access (select-only for
public is often - but not always - reasonable) to most tables, without
exposing the sensitive tables.
> I am sure engine must be storing some where when user tries to
> access those restricted tables.
Why do you think that is the case? It is (or, at least, probably
should be) an auditable event; it is otherwise of minimal consequence
to the server and it has no interest in storing the information. So
it doesn't.
> onaudit does record tabid and error number but I don't want to
> start onaudit at the moment on production as I am assessing
> load/space impact of onaudit.
If you don't want to use the main tool other than an application error
log that can resolve the problem for you, that is your business -- but
you need to understand that when you choose to ignore the information
coming your way that tells you what is what, people will be less
inclined to continue answerng your questions.
I originally wrote 'only tool' instead of 'main tool'; I'm not sure I
can think of an alternative, but there might be some such tool.
> May be some one shed lights on this.
I've tried.
superboer
eq:
export SQLIDEBUG=2:<somedirwithloootsofspace>/<somename>
start your program;
after that in somedirwithloootsofspace there will be a file called
somename_????
unset SQLIDEBUG
then run sqliprint <somedirwithloootsofspace>/somename_???? > afile
(sqliprint comes with the clientsdk.)
you might find:
# values: 0
CMD.....: "select * from xx
" [17]
SQ_NDESCRIBE
SQ_WANTDONE
SQ_EOT
S->C (12) Time: 2004-09-13 17:55:14.43234
SQ_ERR
SQL error..........: -272
ISAM/RSAM error....: 0
Offset in statement: 17
Error message......: "" [0]
SQ_EOT
or use a blunt axe:
onmode -I 272
run your program and the engine will puke an assertion;
WARNING you may need to bounce the engine afterwards...
See you
Superboer.
dryb...@yahoo.com (scottishpoet) wrote in message news:<81714288.04091...@posting.google.com>...
Hari Gupta
Jonathan script helped me a lot. Also, Jonathan I am sorry for my
ignorance.
Thanks again.