How to correctly grant privileges and permissions in 11g

[AndyNewBe]

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

[PAVB]

Either the "other user" has "select any table" system privilege granted to it or select privilege on target table was granted to "public".

[AndyNewBe]

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.

[PAVB]

How about "select" privilege to "public" on the table?... such a privilege would allow "any" user to select from it.

[AndyNewBe]

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?

[PAVB]

Check DBA_TAB_PRIVS, DBA_ROLE_PRIVS and DBA_SYS_PRIVS system views.

[AndyNewBe]

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

[PAVB]

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.

[AndyNewBe]

Thanks for all your help.

Have it all fixed now.