jim@tiamat.fsc.com (Jim O'Connor) (08/28/90)
Using Informix-SQL I can issue the statements grant select on table1 to user1, user2, user3; grant select on table1 to user2, user2, user3; grant insert, update on table1 to user1; grant insert, update, delete on table2 to user2, user3; to grant selected privileges to individual users. Is there any way to remove all of these granted privileges with one statement? That is, I'd like to be able to issue one statement and know that I've wiped out all priviliges that have ever been granted to any user. Is it safe to tinker with the sys table that has the privilege data in it directly? For example, "delete from systabauth;" and "delete from syscolauth;". Thanks for any suggestion you may have. ------------- James B. O'Connor jim@tiamat.fsc.com Ahlstrom Filtration, Inc. 615/821-4022 x. 651
bochner@lange.harvard.EDU (Harry Bochner) (08/30/90)
In article <767@tiamat.fsc.com>, jim@tiamat.fsc.com (Jim O'Connor) writes: ... > Is there any way to remove all of these granted privileges with > one statement? That is, I'd like to be able to issue one statement > and know that I've wiped out all priviliges that have ever been granted to > any user. The best thing I know is: revoke all on table1 from public; " " " table2 " "; ... This isn't quite what you want, but it's pretty good: you can do select tabname from systables; to get a list of tables, and then just edit the list into SQL commands as above; this way you can be sure you haven't missed any tables. > Is it safe to tinker with the sys table that has the privilege > data in it directly? For example, "delete from systabauth;" and "delete > from syscolauth;". It probably can be done this way, but I hesitate to alter the database's private data. I don't _know_ that it isn't safe, but they haven't told us it _is_ safe :-} Harry Bochner bochner@endor.harvard.edu
cortesi@infmx.UUCP (David Cortesi) (09/01/90)
In article <4034@husc6.harvard.edu> bochner@lange.harvard.EDU (Harry Bochner) writes: >In article <767@tiamat.fsc.com>, jim@tiamat.fsc.com (Jim O'Connor) writes: > >grant select on table1 to user1, user2, user3; > >grant select on table1 to user2, user2, user3; > >grant insert, update on table1 to user1; > >grant insert, update, delete on table2 to user2, user3; > > > > Is there any way to remove all of these granted privileges with > > one statement? That is, I'd like to be able to issue one statement > > and know that I've wiped out all priviliges that have ever been granted to > > any user. > >The best thing I know is: >revoke all on table1 from public; >" " " table2 " "; >This isn't quite what you want, but it's pretty good... Sorry, Harry, I don't think that's what he wanted at all. Grants to "public" are independent of grants to individuals. Revoking all from public leaves the grants to users 1-3 in force. One trick that might help is to know that (at least in Informix version 4.0 engines), any privileges granted under the authority bestowed WITH GRANT OPTION are revoked when that authority is revoked. (What did he say?) OK, here's a scenario: you the DBA: GRANT INSERT,UPDATE,DELETE ON TAB1 TO fred WITH GRANT OPTION fred: GRANT INSERT ON TAB1 TO felix fred: GRANT UPDATE ON TAB1 TO myron fred: GRANT UPDATE,DELETE ON TAB1 TO gaston you the DBA: REVOKE UPDATE ON TAB1 FROM fred When you revoke the UPDATE privilege that fred had WITH GRANT OPTION, the system also revokes any privileges that fred granted under that authority. In the example, felix's INSERT privilege and gaston's DELETE privilege are unaffected, but myron and gaston can't UPDATE TAB1 any more. (Unless of course they also hold UPDATE privileges granted by somebody else, which is perfectly feasible; the key of the systabauth table is a composite of table-id, grantor, and grantee.) To return to the original question: if the grants to users 1-3 had all been made by one user under the authority of WITH GRANT OPTION, you could revoke the privileges all at once by revoking them from the grantor. The only other solution I can think of is the one Harry Bochner proposes: fetch the contents of systabauth and use them to compose REVOKE statements. This could be done manually, or using a program that constructed, PREPAREd and EXECUTEd the statements. The heart would be a query like: select * from systabauth where tabid = (select tabid from systables where tabname = "name-of-table-of-interest" ) > > > Is it safe to tinker with the sys table that has the privilege > > data in it directly? For example, "delete from systabauth;" and "delete > > from syscolauth;". That is definitely *not* recommended. You might be able to do it, but as they say in the china department, if it breaks, you bought it.