Re: Informix privileges

Tom, My recommendations is not to play around with the system catalogs. Some of the data is duplicated at the physical file level. If you login as Informix then you should be able to go in and run a revoke all to get rid of existing permissions. Is the database created by informix? As for the role problem. I believe you do have to explicitly set it. Regards David Ashby ______________________________ Reply Separator _________________________________ Subject: Informix privileges Author: Tom Regan <regant@agric.nsw.gov.au> at WCA-INET Date: 24/3/98 9:51 AM 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