This content has been marked as final. Show 2 replies
Ok so a user gets the create table, create index, ... function, ... procedure, and and other system privileges to create and manage objects. Quick way to do that is grant them the resource role. And the create view privilege, that one is not included in the resource role.
in his own shcema he should be able to read ,write update
That is the default behavior. If you do not want other users to see objects, do not grant privileges to other users or roles. The schema objects belong to the owner/creator- appropriate rights to the objects can be granted, or not.
should not be able to see and acess [ ... ] other schema objects
The exception, anyone with the DBA role can select/update/delete/alter/drop any object in the database. So that is a role not often granted to anyone. Unless they would be willing to help out with the restores and other admin tasks as well.