Table permissions

I have a dilemma. My database tables belong to lots of different users, depending if they are in IT, management information, or other departments. I need to revoke permissions for a certain user, but also have the user "public" on my system, which overrides my other usernames. This means I have to get rid of user "public", and manage my users individually. Any suggestions, ideas, comments ? Dirk IDS 7.31FD3, Solaris 9 Dirk Moolman Database and Unix Administrator MXGROUP "People demand freedom of speech as a compensation for the freedom of thought which they seldom use." -Kierkegaard sending to informix-list

Dirk Moolman wrote: > I have a dilemma. My database tables belong to lots of different > users, depending if they are in IT, management information, or other > departments. > > I need to revoke permissions for a certain user, but also have the user > "public" on my system, which overrides my other usernames. > > This means I have to get rid of user "public", and manage my users > individually. Any suggestions, ideas, comments ? > > > Dirk > > IDS 7.31FD3, Solaris 9 > Table permissions (GRANT, REVOKE) are often ignored at development time and hence later the more difficult to manage. A good solution would be to CREATE ROLEs. You can grant and revoke permissions to roles the same way as you grant to users. A role can be granted to another role, so it is possible to implement a kind of inheritance. By using roles you can reduce the number of grant/revoke commands significantly. The bad thing about roles is that you must change some of your code - to use roles apps must contain SET ROLE rolename! (XPS has a built in function for setting a default role, this feature might be found in a future IDS release) Under all circumstances you need to revoke public (this is good security policy) and then grant table permissions to users (or roles). You should also consider revoking connect from public. The latest version of Server Studio JE has a utility for maintaining permissions. If you cannot change the code in application, you can still benefit from roles. When you create a role an entry in sysusers with type 'G' is made. When you grant permissions to roles entries are made in the systabauth table. You can then use SQL to create scripts to grant the permissions to users. UNLOAD TO grant.sql delimiter ';' SELECT "GRANT ALL ON " || trim(tabname) || " TO username" FROM systable t, systabauth a WHERE t.tabid = a.tabid AND a.user = 'rolename' It gets a little more trickier, if you're not granting all, but fx only select and insert. Then you have to interpretate the tabauth column to determine the permissions. Instead of entering usernames it would be easier to create a table with usernames and rolenames I'm sure Art must have a decent utility to handle these problems. Good luck > > > > > Dirk Moolman > Database and Unix Administrator > MXGROUP > > > "People demand freedom of speech as a compensation for the freedom of > thought which they seldom use." > -Kierkegaard > > > sending to informix-list