table level Permissions - Usage of 'PUBLIC'

Once we grant select,update,insert,delete to public on a table, does anybody knows how to restrict a single user to have only select permission. Please don't tell me to grant privileges for each individual user for every table...! Thanks and regards Mohamed Anas Sent via Deja.com http://www.deja.com/ Before you buy.

what about revoke index,alter,delete,update,insert on <tablename> from <username> ? if you needed to do this for a lot of users it is fairly easy to write a script to do this... mohdanas@my-deja.com wrote: > Once we grant select,update,insert,delete to public on a table, does > anybody knows how to restrict a single user to have only select > permission. > > Please don't tell me to grant privileges for each individual user for > every table...! > > Thanks and regards > > Mohamed Anas > > Sent via Deja.com http://www.deja.com/ > Before you buy.

In article <39360AA2.98722775@home.com>, Edward Rosenthal <edrosenthal@home.com> wrote: > what about revoke index,alter,delete,update,insert on <tablename> from > <username> ? > if you needed to do this for a lot of users it is fairly easy to write > a script to do this... > > mohdanas@my-deja.com wrote: > > > Once we grant select,update,insert,delete to public on a table, does > > anybody knows how to restrict a single user to have only select > > permission. > > > > Please don't tell me to grant privileges for each individual user for > > every table...! > > > > Thanks and regards > > > > Mohamed Anas > > > > Sent via Deja.com http://www.deja.com/ > > Before you buy. > > Thanks Edward.. But it doesn't seems to be working Grant statement is executed only for public. Since there is no grant statement executed for any perticular user, getting following error message. -580 Cannot revoke permission. This REVOKE statement cannot be carried out. Either it revokes a database-level privilege, but you are not a Database Administrator in this database, or it revokes a table-level privilege that your account name did not grant. Review the privilege and the user names in the statement to ensure that they are correct. To summarize the table-level privileges you have granted, query systabauth as follows: SELECT A.grantee, T.tabname FROM systabauth A, systables T WHERE A.grantor = USER AND A.tabid = T.tabid Regards Anas Sent via Deja.com http://www.deja.com/ Before you buy.

mohdanas@my-deja.com wrote: > > Once we grant select,update,insert,delete to public on a table, does > anybody knows how to restrict a single user to have only select > permission. > > Please don't tell me to grant privileges for each individual user for > every table...! I won't but that does not change the truth. Besides that is what scripting languages are for. The only alternative is to create a role with the proper priveleges and only allow certain users to use that role. Art S. Kagel

sounds like these users all all members of group public, but have never been really given privileges on their own. i think the problem is biting the bullet and granting permissions for these users then revoking the ones you do not want. sorry for the bad news. the good news is with very little effort you can have the fix. just learn korn shell, awk, sed, perl, tcl etc... just kidding. probably a 25 line shell could do it... mohdanas@my-deja.com wrote: > In article <39360AA2.98722775@home.com>, > Edward Rosenthal <edrosenthal@home.com> wrote: > > what about revoke index,alter,delete,update,insert on <tablename> > from > > <username> ? > > if you needed to do this for a lot of users it is fairly easy to write > > a script to do this... > > > > mohdanas@my-deja.com wrote: > > > > > Once we grant select,update,insert,delete to public on a table, does > > > anybody knows how to restrict a single user to have only select > > > permission. > > > > > > Please don't tell me to grant privileges for each individual user > for > > > every table...! > > > > > > Thanks and regards > > > > > > Mohamed Anas > > > > > > Sent via Deja.com http://www.deja.com/ > > > Before you buy. > > > > > > Thanks Edward.. > > But it doesn't seems to be working > > Grant statement is executed only for public. > Since there is no grant statement executed for any perticular user, > getting following error message. > > -580 Cannot revoke permission. > > This REVOKE statement cannot be carried out. Either it revokes a > database-level privilege, but you are not a Database Administrator in > this database, or it revokes a table-level privilege that your account > name did not grant. Review the privilege and the user names in the > statement to ensure that they are correct. To summarize the table-level > privileges you have granted, query systabauth as follows: > > SELECT A.grantee, T.tabname FROM systabauth A, systables T > WHERE A.grantor = USER AND A.tabid = T.tabid> > Regards > Anas > > Sent via Deja.com http://www.deja.com/ > Before you buy.