Forum Stats

  • 3,872,071 Users
  • 2,266,376 Discussions
  • 7,911,043 Comments

Discussions

restricting accessing public methods in a package in pl/sql

prashweenet
prashweenet Member Posts: 65
edited Jul 10, 2009 6:14AM in SQL & PL/SQL
Hello All,
I have created a oracle package called a DOC_ISSUE and added few public methods.Then i granted permission for my roles and users to execute these methods invoked from a VB clinet.
My problem is, is there any possiblity ito restrict accessing these public methods using PL/SQL tools or SQL Plus other than the clinet application.

Thanks in advance
Prash

Edited by: [email protected] on Jul 10, 2009 2:04 AM
Tagged:
«1

Answers

  • 21205
    21205 Member Posts: 6,168 Gold Trophy
    only expose the procedures and functions (methods as you call them) in the package specification. Keep the once that you don't want "the public" to know about in the package body.
    create or replace 
    create or replace package testing is
       procedure public_acc;
    end;
    /
    
    create or replace package body  testing is
       procedure invisible is
       begin
          null;
       end;
       procedure public_acc is
       begin
          null;
       end;
    end;
    /
    They can see the "public_acc" but the"invisible" cannot be seen by "the public" - only from within the package body
  • 710789
    710789 Member Posts: 9
    Keywords:
    application roles
    local roles

    You can use above methods to make permissions unaviable until application send password, or call special subprogram (which can check application calling by querying v$session).

    You can check information about application calling procedure inside and throw exception when discovered that v$session (program collumn) is not what You expected.

    Best regards
    710789
  • prashweenet
    prashweenet Member Posts: 65
    Thanks for the information.

    Sounds good.Have you got any example of this?
    i am sort of new to this subject :)

    /Prash
  • Hoek
    Hoek Member Posts: 16,087 Gold Crown
    You might want to read some Oracle documentation as well.

    http://www.oracle.com/pls/db102/search?remark=quick_search&word=roles&tab_id=&format=ranked
  • Marwim
    Marwim Member Posts: 3,653 Gold Trophy
    Hello Prash,

    if you need different permissions for your users you can write a simple package as a wrapper
    Using Alex' example with both procedures public
    create or replace package testing is
       procedure public_acc;
       procedure invisible;
    end;
    /
    
    create or replace package body  testing is
       procedure invisible is
       begin
          null;
       end;
       procedure public_acc is
       begin
          null;
       end;
    end;
    /
    Now you write a second package that simply calls the "allowed" procedures
    create or replace package public_a is
      procedure public_acc;
    end public_a; 
    /
    
    create or replace package body public_a is
        procedure public_acc is
        begin
           procedure testing.public_acc();
        end;
    end public_a; 
    /
    Now you can GRANT EXECUTE to user a on public_a and he can only access public_acc.

    Regards
    Marcus
    Marwim
  • prashweenet
    prashweenet Member Posts: 65
    Hi again
    But can the user be restricted to access this method using SUL PLUS or any other pl/sql editor tools?
    /Prash
  • 21205
    21205 Member Posts: 6,168 Gold Trophy
    the user can either
    1) execute it
    2) not execute it

    Those are the choices that you have.
  • BluShadow
    BluShadow Member, Moderator Posts: 42,540 Red Diamond
    There are plenty of valid answers, but perhaps you should be asking yourself "what is the question?"
  • prashweenet
    prashweenet Member Posts: 65
    Hi Again
    All my packages are defined as you have defined in your example.
    Public methods are exposed in the package and package body.

    how do you define 'Execute' or 'Not Execute' to this package.

    My oracle admin knowlege is pretty low :(

    /Prash
  • 21205
    21205 Member Posts: 6,168 Gold Trophy
    you can grant execute privileges on a package.
    grant execute on <pack> to <user>
    and execute the procedure:
    begin
       owner.pack.procedure;
    end;
    21205
This discussion has been closed.