All Forums Database
BBR2 96 posts Joined 12/04
18 Jan 2006
PUBLIC User

All,I need some information on GRANTS/REVOKE on system user PUBLIC.I believe if we grant any privilege to PUBLIC, all system wide users get that privileges implicitly.I also know that this is not the best way of granting access to the RDBMS.I have a situation, where in I need to revoke permissions from Users which will prohibit from issuing statements like.GRANT SELECT ON SANDBOX_DB TO PUBLIC; (Assume User ABC executed this)Can we limit users ability to grant/revoke on PUBLIC.I wanted to limit this ability of the user for us to have a tighter control on the grants and there by keeping DBC.AllRights table from growing.When I run REVOKE ALL ON PUBLIC FROM ABC; I get error 3707. Any ideas?Vinay

TD_Arch 35 posts Joined 07/05
19 Jan 2006

Vinay,It should be REVOKE ALL ON ABC FROM PUBLIC;

BBR2 96 posts Joined 12/04
19 Jan 2006

I wanted to limit the user to anything on PUBLIC database.I don't want user ABC to give GRANTS such as GRANT SELECT ON SANDBOX_DB.TABLE TO PUBLIC;User may have done this to provide implicit access to SELECT SANDBOX_DB.TABLE data to all system wide users.I think below revoke statement does not limit user from giving grants.REVOKE ALL ON ABC FROM PUBLIC; What do you think?Vinay

Fred 1096 posts Joined 08/04
19 Jan 2006

If the user holds a right WITH GRANT OPTION, he is free to grant that right to ANY database, user, or role. And the creator of a table automatically is given rights WITH GRANT OPTION.

You must sign in to leave a comment.