Revvoking not possible
I created a user whcih I gave rights to create any objects. He is not allowed to do a select on another schema. when I try to do a select on table A in another schema I get
ora-00942:table or view does not exist.
That is the way it should be.
When I try a select on table B in the same schema as table A it will give me all data. I never granted him any permission on that table.
How come he can still do a select on that table?
How can I prevent him from reading the data from tables in another schema while not
well, he has the privilege else it wouldn't work, perhaps access is granted to any - look in user_tab_privs
Unfortunately no such entry.
You can use dynamic SQL passed into a stored procedure and run with execute immediate. Then you only need to grant execute privilege on the stored procedure. But the downside is you can't really control what DDL is run through the stored procedure. You are probably better off creating tables as the schema owner.