Home » RDBMS Server » Security » example to give privs on packages instead of give privs directly on table to user
example to give privs on packages instead of give privs directly on table to user [message #46989] Wed, 14 July 2004 08:51 Go to next message
Raúl Castro
Messages: 2
Registered: July 2004
Junior Member
Astored procedure executes under the owners identity, not under the identity of the caller. Therefore I have knowed that many applications use packages to execute insert, update, select, delete statements instead of give userprivileges directly on table. I'm creating a web application using php and Oracle, but I want to use this programing method, Iwould like to have some example to help me to understand, do you have one? This method is suitable on web applications?
Re: example to give privs on packages instead of give privs directly on table to user [message #46992 is a reply to message #46989] Wed, 14 July 2004 09:31 Go to previous message
Todd Barry
Messages: 4819
Registered: August 2001
Senior Member
For example, if I have the EMP table owned by SCOTT, I would create a package owned by SCOTT that had insert/update/delete procedures (methods) for the EMP table. SCOTT would then grant execute on this package to the user(s) that need to manipulate the EMP table.

The package spec might look something like:

create or replace package pkg_emp
is
begin
  procedure p_insert(
    p_empno  out emp.empno%type,
    p_ename  in  emp.ename%type,
    ...
    p_deptno in  emp.deptno%type);
     
  procedure p_update(
    p_empno  in  emp.empno%type,
    p_ename  in  emp.ename%type,
    ...
    p_deptno in  emp.deptno%type);
     
  procedure p_delete(
    p_empno  in  emp.empno%type);
end;


The insert proc would insert a row and then return the generated empno (assuming we use a sequence to gen the value) as an OUT parameter. Or it could be passed in as a parameter.

The update proc would use the empno passed in to locate the row and then update using:

update emp
   set ename = nvl(p_ename, ename),
       ...
       deptno = nvl(p_deptno, deptno)
 where empno = p_empno;


You need some way to allow setting a column to NULL using this approach though.

The delete proc is straightforward.

Hope this gets you started.
Previous Topic: roles and users-access(urgent)
Next Topic: grant privileges
Goto Forum:
  


Current Time: Thu Sep 16 15:54:00 CDT 2021