How to correctly grant privileges and permissions in 11g
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 9 of 9

Thread: How to correctly grant privileges and permissions in 11g

  1. #1
    Join Date
    Feb 2012
    Posts
    5

    How to correctly grant privileges and permissions in 11g

    Hi

    I have a 11g database, where under one user, which has full privileges, etc, I created a table and stored procedure.

    However, the table and store procedure will be accessed and executed by another. Therefore, I used the grant command to this other user permissions to execute the stored procedure and to insert on the table.

    The problem being I do not feel that I have set this up correctly because when I log into SQLPLUS as this other user, I can still run the select command, which I felt it would block me in doing.

    So, can someone explain to me in simple terms how do I correctly set up a user who can insert only into a table and execute the stored procedure for running the insert command?

    Thanks

  2. #2
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    Either the "other user" has "select any table" system privilege granted to it or select privilege on target table was granted to "public".
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  3. #3
    Join Date
    Feb 2012
    Posts
    5
    When I created the user it only had insert on to that table and execute on the stored procedure. I never granted select on the table where I only wanted that user to have insert on it.

    When you create a user through Oracle Enterprise Manager does it set any defaults? Because this is how I created the user.

  4. #4
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    How about "select" privilege to "public" on the table?... such a privilege would allow "any" user to select from it.
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  5. #5
    Join Date
    Feb 2012
    Posts
    5
    I will have to check EM, but I do not recall seeing this on table, because I only set insert on the table for that user. Is there anything else that you could suggest?

  6. #6
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    Check DBA_TAB_PRIVS, DBA_ROLE_PRIVS and DBA_SYS_PRIVS system views.
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  7. #7
    Join Date
    Feb 2012
    Posts
    5
    Hi

    Thanks for your reply.

    Where do I find those views in the EM, and further, you mentioned that if the table is set to public where do I check to see where this is set, and how do I amend it?

    Thanks

  8. #8
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    sqlplus... use sqlplus. You find everything you need on the three system views I mentioned.

    In regards to the second part of the question... revoke is what you use to take away unwanted granted privileges.
    Last edited by PAVB; 02-26-2012 at 08:23 AM. Reason: typo
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  9. #9
    Join Date
    Feb 2012
    Posts
    5
    Thanks for all your help.

    Have it all fixed now.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width