Skip to Main Content
  • Questions
  • Oracle - grant package access not wotking for inner tables

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question, Matheus.

Asked: February 02, 2017 - 6:01 pm UTC

Last updated: February 03, 2017 - 7:31 pm UTC

Version: Oracle 11g

Viewed 1000+ times

You Asked

Hi!

I have two Users on a same Oracle Instance, APP and TRDP. The user APP is a third-party user and the TRDP is one of my own. So the APP user have a package (API) which receives three parameters and updates an internal table. During the processing of this package, it does a series of validations e.g.: check status of the document which I'm trying to update, raise internal workflow events and those validations requires access to some other tables. I have granted EXECUTE on this API from APP user to TRDP user and also created a Synonym on TRDP user for this Package. And my problem now is: in order to run this Package I have to grant select on all subsquent objects it uses. So, for example, I have to grant select on workflow tables, on sequences, on other tables which I don't intend to use.

I looked everywhere for this situation, but for all I know and could find, the Oracle Database grants that I don't need to "know" about the objects this Package uses as long as I have grant to execute it.

I couldn't find any Database parameter that requires me to grant access on each subsequent object, still I have to manually grant access on each object that this API uses.

If someone had gone through this, can I have some advice, please?

Thanks in advance!

and Connor said...

By default, you dont need access to the tables, eg

SQL> create user APP identified by APP;

User created.

SQL> alter user app quota 100m on users;

User altered.

SQL>
SQL> create table app.t1 ( x int );

Table created.

SQL> create table app.t2 ( x int );

Table created.

SQL> create table app.t3 ( x int );

Table created.

SQL>
SQL> create or replace
  2  procedure app.p is
  3  begin
  4    insert into t1 values (1);
  5    insert into t2 values (1);
  6    insert into t3 values (1);
  7  end;
  8  /

Procedure created.

SQL>
SQL> grant execute on app.p to scott;

Grant succeeded.


I now connect as SCOTT and I can run the proc without issue, *even though* I still do not have access to the underlying objects.

SQL> conn scott/tiger
Connected.

SQL> exec app.p

PL/SQL procedure successfully completed.

SQL>
SQL> delete from app.t1;
delete from app.t1
                *
ERROR at line 1:
ORA-00942: table or view does not exist



The procedure (by default) runs with the privileges of the owner of the procedure. The procedure *may* have been defined as authid current user, which means it runs with privilege of the person *executing* the procedure. In this case, you would need access on the underlying tables directly. A DBA can list all of tables owned by APP and grant access to your user.

Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Thanks

Matheus, February 03, 2017 - 10:58 am UTC

Thank you for your answer. Indeed the API package I was calling have the authid current_user in its declaration. Many thanks for your fast and clear reply.
Connor McDonald
February 03, 2017 - 7:31 pm UTC

glad we could help

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library