CODEWITHSUNDEEP
oracle-database
Dominique Fortin
Dominique Fortin

Reputation: 2238

Securing a service's secret

I have a TABLE sec.sec_secret_tab in the SEC schema

OWNER SERVICE_NAME SECRET
ALPHA service_1 A1
ALPHA service_2 A2
BETA service_1 B1
BETA service_2 B2

and this function:

FUNCTION sec.sec_get_secret (p_service_name VARCHAR2) 
IS
  vc_onwer VARCHAR2(32767); 
  vc_secret VARCHAR2(32767);
BEGIN
  vc_onwer := sec.mystery_func;

  SELECT secret
    INTO vc_secret
    FROM sec.sec_secret_tab
   WHERE service_name = p_service_name
     AND onwer = vc_onwer;

  RETURN vc_secret;

END sec_get_secret;

In the schema alpha, there is this procedure:

PROCEDURE alpha.show 
IS
  vc_secret VARCHAR2(32767);
BEGIN
  vc_secret := sec.sec_get_secret('service_1');

  dbms_output.put_line(vc_secret);

  vc_secret := sec.sec_get_secret('service_2');

  dbms_output.put_line(vc_secret);

END show;

In the schema beta, there is this procedure:

PROCEDURE beta.show 
IS
  vc_secret VARCHAR2(32767);
BEGIN
  vc_secret := sec.sec_get_secret('service_1');

  dbms_output.put_line(vc_secret);

  vc_secret := sec.sec_get_secret('service_2');

  dbms_output.put_line(vc_secret);

END show;

Knowing that

How can I make the function sec.mystery_func to get the following output:

Upvotes: 1

Views: 44

Answers (1)

Alex Poole
Alex Poole

Reputation: 191285

You can use the UTL_CALL_STACK package, specifically the OWNER function:

This function returns the owner name of the unit of the subprogram at the specified dynamic depth.

You want the owner of the calling package, so that's level 2:

vc_onwer := utl_call_stack.owner(2);

That package is available from Oracle 12cR1.

Upvotes: 1

Related Questions