I have a production database that has GRANTs TO PUBLIC for INSERT, UPDATE, DELETE on specific tables of the production schema, table t1 being one of these tables.

Say I want to create a user user1 that cannot INSERT, UPDATE, DELETE on table t1. Is there a way to revoke these privileges for user1 while keeping the GRANTs TO PUBLIC?

  • 2
    Have you tried? Did it work?
    – mustaccio
    Apr 28 at 13:57
  • You're right, I reformulated. Notice that I said "revoke" and not "REVOKE". Apr 28 at 14:02
  • 1
    Granting critical rights to PUBLIC is a very bad idea. Very bad. At the very least, create a specific role, grant the rights to that role, and grant that role to the actual users that should be able to do those updates. Another option is to have application roles - a role that is dynamically enabled by the specific applications that need them. Apr 29 at 16:31
  • @AlbertGodfrind: Agreed, and that's what I'll do. But if the GRANTS to PUBLIC have been in production for decades, you have to take utmost care think, ask questions, rinse and repeat :) And actually learn smething in the process. Apr 30 at 6:01
  • @mustaccio : Your question is fair, but the upvotes are undue. It is an "is it possible?" question. If I had an in-depth knowledge of the subject, I would be able to answer the question myself, and simply not come here ask the question. Whatever I have tried is on the grounds of my incomplete knowledge of the subject. I have actually tried to REVOKE the rights on the user, and it failed. But since I am not competent enough, this is not sufficient to conclude "No it's not possible". Apr 30 at 6:06

If something is granted to PUBLIC, that's it. There is no 'blacklist' or 'grant to public but not to Joe'. You should manage this with profiles.

  • That's what I imagined. I find the Oracle documentation rather unclear on the "We're working on whitelist mode" point, although the technical explanations on the commands are OK. Apr 29 at 7:15
  • 2
    Please cite your source for ""We're working on whitelist mode" point"
    – EdStevens
    Apr 29 at 14:19
  • There is none, AFAICT, 'whitelist mode' is an oversimplification of mine. Your answer is the one talking about 'blacklist' or anything close to 'whitelist'. I figure there is more to Oracle security than what is said there. I think it's better not to elaborate any fuirther on the subject. Apr 30 at 5:57

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.