This discussion is archived
2 Replies Latest reply: Feb 12, 2013 9:33 AM by clcarter RSS

how to stop accessing other schema objects.

947771 Newbie
Currently Being Moderated
Hi,
oracle 11g express r2
what are the minimum permision i should give to user , while create new schema.
so that he should not be able to see and acess in any case other schema objects.
but in his own shcema he should be able to read ,write update ,should be able to create all kind
of objects and directary also so that he can upload data from filesystem files into columns.


yours sincerely
  • 1. Re: how to stop accessing other schema objects.
    asahide Expert
    Currently Being Moderated
    Hi,

    To my understanding,
    You grant "create session" and "object privs".
    Object privs is follows.
    <<http://docs.oracle.com/cd/E11882_01/server.112/e26088/statements_9013.htm#BGBCIIEG>>


    But What do you think about public schema?

    Regards,
  • 2. Re: how to stop accessing other schema objects.
    clcarter Expert
    Currently Being Moderated
    in his own shcema he should be able to read ,write update
    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.
    should not be able to see and acess [ ... ] other schema objects
    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.

    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.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points