Not able to revoke SELECT privilege from sysmaster

Hi All, I have granted select privilege on some of the tables in spymaster database to an OS user. Later I have revoked the permission, even after revoking the permission, that user was able to connect symaster database and execute select query. The query which I have used for granting privilege is as follows CREATE ROLE ROLE1; SET ROLE ROLE1; GRANT SELECT ON SYSCHKIO TO ROLE1; GRANT SELECT ON SYSCHUNKS TO ROLE1; GRANT SELECT ON SYSDATABASES TO ROLE1; GRANT SELECT ON SYSDBSPACES TO ROLE1; GRANT SELECT ON SYSEXTENTS TO ROLE1; GRANT SELECT ON SYSEXTSPACES TO ROLE1; GRANT SELECT ON SYSLOGS TO ROLE1; GRANT SELECT ON SYSLOCKS TO ROLE1; GRANT SELECT ON SYSPTPROF TO ROLE1; GRANT ROLE1 TO 'pratheep'; The query which I have used for revoking the permission is below REVOKE SELECT ON SYSCHKIO FROM ROLE1; REVOKE SELECT ON SYSCHUNKS FROM ROLE1; REVOKE SELECT ON SYSDATABASES FROM ROLE1; REVOKE SELECT ON SYSDBSPACES FROM ROLE1; REVOKE SELECT ON SYSEXTENTS FROM ROLE1; REVOKE SELECT ON SYSEXTSPACES FROM ROLE1; REVOKE SELECT ON SYSLOGS FROM ROLE1; REVOKE SELECT ON SYSLOCKS FROM ROLE1; REVOKE SELECT ON SYSPTPROF FROM ROLE1; REVOKE ROLE1 FROM 'pratheep';DROP ROLE ROLE1; Even after revoking the permission and dropping the role, the user 'pratheep' were able to connect to sysmaster database. What could be the reason? How can I prevent user 'pratheep' from connecting to sysmaster database? Thanks in advance. Pratheep

Pratheep, It is always helpful to provide your version of IDS and version and name of OS. I am running IDS 7.31 and 9.40 on Sun, AIX, and HP. I spot checked the syschkio table on both 7.31 and 9.40. As I suspected, public has select on syschkio. I did not check all the tables you listed, but I am sure they have the same privileges. It is not a good idea to change permissions (or anything else, for that matter) on sysmaster tables. Unexpected consequences could result. Rob Schmitz CenturyLink Data Management 913-534-3474 rob.b.schmitz@embarq.com www.embarq.com -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of PRATHEEP KK Sent: Tuesday, August 11, 2009 8:26 AM To: ids@iiug.org Subject: Not able to revoke SELECT privilege from sysmaster [16649] Hi All, I have granted select privilege on some of the tables in spymaster database to an OS user. Later I have revoked the permission, even after revoking the permission, that user was able to connect symaster database and execute select query. The query which I have used for granting privilege is as follows CREATE ROLE ROLE1; SET ROLE ROLE1; GRANT SELECT ON SYSCHKIO TO ROLE1; GRANT SELECT ON SYSCHUNKS TO ROLE1; GRANT SELECT ON SYSDATABASES TO ROLE1; GRANT SELECT ON SYSDBSPACES TO ROLE1; GRANT SELECT ON SYSEXTENTS TO ROLE1; GRANT SELECT ON SYSEXTSPACES TO ROLE1; GRANT SELECT ON SYSLOGS TO ROLE1; GRANT SELECT ON SYSLOCKS TO ROLE1; GRANT SELECT ON SYSPTPROF TO ROLE1; GRANT ROLE1 TO 'pratheep'; The query which I have used for revoking the permission is below REVOKE SELECT ON SYSCHKIO FROM ROLE1; REVOKE SELECT ON SYSCHUNKS FROM ROLE1; REVOKE SELECT ON SYSDATABASES FROM ROLE1; REVOKE SELECT ON SYSDBSPACES FROM ROLE1; REVOKE SELECT ON SYSEXTENTS FROM ROLE1; REVOKE SELECT ON SYSEXTSPACES FROM ROLE1; REVOKE SELECT ON SYSLOGS FROM ROLE1; REVOKE SELECT ON SYSLOCKS FROM ROLE1; REVOKE SELECT ON SYSPTPROF FROM ROLE1; REVOKE ROLE1 FROM 'pratheep';DROP ROLE ROLE1; Even after revoking the permission and dropping the role, the user 'pratheep' were able to connect to sysmaster database. What could be the reason? How can I prevent user 'pratheep' from connecting to sysmaster database? Thanks in advance. Pratheep ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.

revoke connect from 'pratheep'; revoke connect from 'public'; Art S. Kagel Oninit (www.oninit.com) IIUG Board of Directors (art@iiug.org) Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Oninit, the IIUG, nor any other organization with which I am associated either explicitly or implicitly. 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, Aug 11, 2009 at 8:26 AM, PRATHEEP KK <pratheepkk@gmail.com> wrote: > Hi All, > > I have granted select privilege on some of the tables in spymaster database > to > an OS user. > Later I have revoked the permission, even after revoking the permission, > that > user was able to connect symaster database and execute select query. > > The query which I have used for granting privilege is as follows > > CREATE ROLE ROLE1; > SET ROLE ROLE1; > GRANT SELECT ON SYSCHKIO TO ROLE1; > GRANT SELECT ON SYSCHUNKS TO ROLE1; > GRANT SELECT ON SYSDATABASES TO ROLE1; > GRANT SELECT ON SYSDBSPACES TO ROLE1; > GRANT SELECT ON SYSEXTENTS TO ROLE1; > GRANT SELECT ON SYSEXTSPACES TO ROLE1; > GRANT SELECT ON SYSLOGS TO ROLE1; > GRANT SELECT ON SYSLOCKS TO ROLE1; > GRANT SELECT ON SYSPTPROF TO ROLE1; > GRANT ROLE1 TO 'pratheep';> > The query which I have used for revoking the permission is below > > REVOKE SELECT ON SYSCHKIO FROM ROLE1; > REVOKE SELECT ON SYSCHUNKS FROM ROLE1; > REVOKE SELECT ON SYSDATABASES FROM ROLE1; > REVOKE SELECT ON SYSDBSPACES FROM ROLE1; > REVOKE SELECT ON SYSEXTENTS FROM ROLE1; > REVOKE SELECT ON SYSEXTSPACES FROM ROLE1; > REVOKE SELECT ON SYSLOGS FROM ROLE1; > REVOKE SELECT ON SYSLOCKS FROM ROLE1; > REVOKE SELECT ON SYSPTPROF FROM ROLE1; > REVOKE ROLE1 FROM 'pratheep';> DROP ROLE ROLE1; > > Even after revoking the permission and dropping the role, the user > 'pratheep' > were able to connect to sysmaster database. > What could be the reason? How can I prevent user 'pratheep' from connecting > to > sysmaster database? > > Thanks in advance. > Pratheep > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --001636c5b58f91e3c60470dffd07