Results 1 to 5 of 5
  1. #1
    Join Date
    Dec 2003
    Posts
    24

    Unanswered: Insufficient privilages

    Hi,

    I have an acount which can perform any DML activity to another user object. The update statement works fine when I execute the statement directly in sql prompt. But the same if I am putting in a precedure I am facing Insufficient privilage. I am using username.objectname. My oracle version is 9i and os is windows 2000

    Can any body tell we what is the issue?

    Thanks in advance.

    -Mohit.

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: Insufficient privilages

    Procedures require privileges to be granted to the procedure owner directly, not via a role.

  3. #3
    Join Date
    Jan 2003
    Location
    The Netherlands
    Posts
    149

    Correct

    That's correct and you also need to have execution rights of the object. I have another question on this subject. I have created a stored procedure applsp_addtable( blabla). I have to have the rights granted directly to me to do whatever happens in this procedure. So I do this and it works! But i have a sort of same procedure which is : applsp_deletetable.

    sStatement :=
    'CREATE OR REPLACE PROCEDURE ish.applsp_DeleteTable
    (
    cDbd IN VARCHAR2,
    cTable IN VARCHAR2
    ) AS
    Dummy VARCHAR2(1);
    BEGIN
    SELECT ''X''
    INTO Dummy
    FROM DBA_TABLES
    WHERE OWNER = ''ISH''
    AND TABLE_NAME = UPPER(cTable)
    ;
    EXECUTE IMMEDIATE ''DROP TABLE ISH.'' || ish.applfn_FixName( cTable );
    ish.applsp_DeleteTableFromUCT(cDbd, cTable);
    EXCEPTION
    WHEN NO_DATA_FOUND THEN
    ish.applsp_DeleteTableFromUCT(cDbd, cTable);
    END;
    ';
    EXECUTE IMMEDIATE sStatement;

    Now I don't have to have the rights granted directly to me, why is this. is there any connection with the create table permission?

    gr. coen

  4. #4
    Join Date
    Dec 2003
    Posts
    18

    Re: Insufficient privilages

    hi,

    connect to user, of which table u want to update through procedure than 'grant update to ur account on table_name'. Connect to ur account and try.




    Originally posted by mohit_sharan
    Hi,

    I have an acount which can perform any DML activity to another user object. The update statement works fine when I execute the statement directly in sql prompt. But the same if I am putting in a precedure I am facing Insufficient privilage. I am using username.objectname. My oracle version is 9i and os is windows 2000

    Can any body tell we what is the issue?

    Thanks in advance.

    -Mohit.

  5. #5
    Join Date
    Oct 2003
    Posts
    706

    Lightbulb

    You might call that a "feature" of stored-procedures in any SQL system: stored-procedures (and also views) can be granted privileges different from (i.e. more powerful than) those possessed by the user who is executing or opening them. This is what puts blinders and chains on what a particular user is able to do with the data. You set up these more-powerful views and procs, and then (in addition) you limit who's allowed to use them. Users thus have no other way to do things except to use these views and procs.

    And... the knife cuts both ways. Just as a stored-proc or view can have more privileges than you do, they can also have less or even none! Obviously that's not too useful, but it's undoubtedly what is happening here. All you need to do is to adjust the permissions appropriately.

    You'll find these features (implemented different ways of course) in pretty much any/all SQL server systems.
    ChimneySweep(R): fast, automatic
    table repair at a click of the
    mouse! http://www.sundialservices.com

Posting Permissions

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