Re: Informix privileges

Tom Regan wrote: > > G'day All, > > We are running Online 7.23.UC1 on Sun boxes under Solaris 2.5.1, and I have > hit a couple of snags with the way Informix handles privileges and roles. > > We have a number of client/server applications which use a single account to > attach to the application's database. These accounts have the resource > privilege for the database, and create all database elements via SQL scripts > from the PC end when the applications are initialiased. The problem is that > Informix grants all to public on each table, making it risky to allow any > ODBC connections (e.g. from Crystal Reports) to the database. > > I have tried to revoke the public privileges as the Informix user, > but as the following snippet from a dbaccess session shows, > the privileges are not revoked: > > > revoke all on t920_bank from public;> Permission revoked. > > info privileges for t920_bank ; > User Select Update Insert Delete Index Alter > > public All All Yes Yes Yes Yes > > The fine manual seems to indicate that only the owner of an object, or the > grantor of a privilege, may alter it. Could someone please confirm that this > is correct. I thought of directly manipulating the privileges and/or table > ownership using SQL on the systabauth and systables system catalog tables to > manage privileges, e.g. DELETE FROM SYSTABAUTH WHERE GRANTEE = "public" > AND TABID > 99. Has anyone done this, and were there any adverse > consequeces? Any other workaraounds? > > My last question has to do with roles - is there any way to use these > from an ODBC application such as Microsoft Access? It appears that a > user must explicitly enable a role using the SET ROLE ROLENAME command > for each session. This seems crazy - if I grant someone a role in the database > why should they have to tell the engine they wish to use it??? The real > problem is that there does not appear to be any way to set the role from > MS-ACCESS when the connection is made, making it impossible to link external > tables into Access. Is anyone using roles successfully in a similar situation? > > many thanks, > Tom > > -- > Tom Regan, Operations Manager Email: Tom.Regan@agric.nsw.gov.au > NSW Dept of Agriculture Phone: 0263 913268 > Orange NSW Australia Fax: 0263 913290 I wouldn't recommend doing *anything* as user Informix. Users with DBA privilege should be able to do all that's needed without the risk of touching the system tables (which, by the way, are not protected by the referential integrity mechanisms, at least not in SE). -- Peter Lancashire Information Systems Specialist, Bayer plc Eastern Way, Bury St Edmunds, Suffolk, IP32 7AH, UK Tel: +44-1635-562258, Fax: +44-1635-562281 Mail: Peter.Lancashire.PL1@bayer.co.uk --- My Internet plumbing does not allow me to mail and post news together. Sorry. All opinions are my own and not those of Bayer plc. --- Join Infuse, the UK Informix User Group at http://www.infuse.co.uk/