| example to give privs on packages instead of give privs directly on table to user [message #46989] |
Wed, 14 July 2004 08:51  |
Raúl Castro
Messages: 2
Registered: July 2004
|
Junior Member |
|
|
|
A stored 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 user privileges directly on table. I'm creating a web application using php and Oracle, but I want to use this programing method, I would 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  |
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.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
