This discussion is archived
4 Replies Latest reply: Feb 25, 2012 9:41 AM by rp0428 RSS

GRANT INSERT ON TABLE - Oracle 11g

user633278 Newbie
Currently Being Moderated
Hi

I have a user in Oracle which I have granted the insert command on a table using GRANT INSERT ON ACCOUNTS TO INSERTUSER;

The grant was successfully granted and there is no problem when inserting data into the table when logging in as that user. My problem is when testing the privilege that it is still possible to run the select command as that user. Therefore, how do I only ensure that this user is only able to insert into this table and not able to select from it? Further, the insert is running under a stored procedure, and I have granted the permission to run execute on that procedure.

Thanks
  • 1. Re: GRANT INSERT ON TABLE - Oracle 11g
    rp0428 Guru
    Currently Being Moderated
    Unless a user has SELECT privilges they can't select directly from a table. So if INSERTUSER can select directly from ACCOUNTS then they do have SELECT privileges on the table, perhaps through a role.

    You can test this easily by creating a new table and granting only INSERT privileges to INSERTUSER. They will not be able to select from the table unless they have been granted SELECT ANY TABLE privileges.

    To ensure that procedures are executed using the INVOKERs privileges rather than the privileges of the user that created the procedure code the procedure this way
    create or replace procedure p
    authid current_user
    This 'authid' declaration will use the privileges granted to the user that executes the procedure. Since roles are disabled in PL/SQL the only way a calling user can execute a SELECT on a table is if the privileges on the table were granted directly to the user and not through a role.

    See Using Invoker's Rights Versus Definer's Rights (AUTHID Clause) in the PL/SQL User's Guide

    http://docs.oracle.com/cd/B19306_01/appdev.102/b14261/subprograms.htm#i18574
  • 2. Re: GRANT INSERT ON TABLE - Oracle 11g
    user633278 Newbie
    Currently Being Moderated
    Hi

    Thanks for replying.

    I am not too sure what role when the user was created they have. When the user, insertuser, was created, the EM placed the user as an authenticated user. So, taking your point, then authenticated user in Oracle must have SELECT permissions.

    If want to create a user who I can log in through sqlplus, and be able to execute the stored procedure along with executing insert command, what be the best way to do this?

    Sorry asking such daft questions, but I am not familiar with this and so on a steep learning curve!
  • 3. Re: GRANT INSERT ON TABLE - Oracle 11g
    Mark D Powell Guru
    Currently Being Moderated
    The following short article should be of interest. It introduces the security related views so you can see what system privileges, roles, and direct grants a user has.

    How do I find out which users have the rights, or privileges, to access a given object ?
    http://www.jlcomp.demon.co.uk/faq/privileges.html


    HTH -- Mark D Powell --
  • 4. Re: GRANT INSERT ON TABLE - Oracle 11g
    rp0428 Guru
    Currently Being Moderated
    >
    Sorry asking such daft questions
    >
    The question wasn't 'daft' but so far you have asked, 35 (35 unresolved) and haven't given HELPFUL or ANSWERED credit to anyone that tried to help you with those previous questions. So some people just won't try to help anymore. Please keep the forum clean by resolving your previous questions.

    >
    If want to create a user who I can log in through sqlplus, and be able to execute the stored procedure along with executing insert command, what be the best way to do this?
    >
    I just showed how to do that. When a user creates a procedure it is created with DEFINER privileges: any user that has EXECUTE privilege on the procedure can execute it and it will execute with the privileges of the user that created the procedure.

    If that user can SELECT from a table directly (not thru a role) then the procedure can select from that table. Other users can only SELECT from tables they have the SELECT privilege on either directly or thru a role.

    I didn't say anything about 'authenticated' users.

Legend

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