oracle grant execute function,grant execute on package — oracle-tech

Emad wrote:

You can have roles for Insert and Roles for Update and then manage them using Secure Application RolesEmad, stop posting links to irrelevant stuff. This is not about Insert and Update. This is about - let's call it - "fine grained access control to packaged subprograms".

A thing I stated does not exist. Could be that I'm wrong, you already suggested that it does, and linked to the front page of VPD.

I asked you to outline a solution, instead. A thing you reclined to do, instead you linked to something completely different.

@mmtar

The purpose - as I know - of the package is to collect related procedure and functions - in the data issue not the security issue -, am I right?!!Let's just say that a package is a collection of related procedure and functions.

Question is, are they related if these procedures and functions relate to different use/users?

Maybe, maybe not - Your choice.

Earlier I linked to examples on how Oracle solves this, themselves. Link was

http://download.oracle.com/docs/cd/B19306_01/network.102/b14266/authoriz.htm#i1007675

Example where they do it by splitting package:

Packages and Package Objects: Example 1

This example shows four procedures created in the bodies of two packages.

CREATE PACKAGE BODY hire_fire AS

PROCEDURE hire(...) IS

BEGIN

INSERT INTO employees . . .

END hire;

PROCEDURE fire(...) IS

BEGIN

DELETE FROM employees . . .

END fire;

END hire_fire;

CREATE PACKAGE BODY raise_bonus AS

PROCEDURE give_raise(...) IS

BEGIN

UPDATE employees SET salary = . . .

END give_raise;

PROCEDURE give_bonus(...) IS

BEGIN

UPDATE employees SET bonus = . . .

END give_bonus;

END raise_bonus;

Access to execute the procedures is given by granting the EXECUTE privilege for the package by using the following statements:

GRANT EXECUTE ON hire_fire TO big_bosses;

GRANT EXECUTE ON raise_bonus TO little_bosses;Example where they do it with wrappers:

Packages and Package Objects: Example 2

This example shows four procedure definitions within the body of a single package. Two additional standalone procedures and a package are created specifically to provide access to the procedures defined in the main package.

CREATE PACKAGE BODY employee_changes AS

PROCEDURE change_salary(...) IS BEGIN ... END;

PROCEDURE change_bonus(...) IS BEGIN ... END;

PROCEDURE insert_employee(...) IS BEGIN ... END;

PROCEDURE delete_employee(...) IS BEGIN ... END;

END employee_changes;

CREATE PROCEDURE hire

BEGIN

employee_changes.insert_employee(...)

END hire;

CREATE PROCEDURE fire

BEGIN

employee_changes.delete_employee(...)

END fire;

PACKAGE raise_bonus IS

PROCEDURE give_raise(...) AS

BEGIN

employee_changes.change_salary(...)

END give_raise;

PROCEDURE give_bonus(...)

BEGIN

employee_changes.change_bonus(...)

END give_bonus;

Using this method, the procedures that actually do the work (the procedures in the employee_changes package) are defined in a single package and can share declared global variables, cursors, on so on. By declaring top-level procedures, hire and fire, and an additional package, raise_bonus, you can grant selective EXECUTE privileges on procedures in the main package:

GRANT EXECUTE ON hire, fire TO big_bosses;

GRANT EXECUTE ON raise_bonus TO little_bosses;Regards

Peter