Grant privileges for an Oracle package?

sql oracle ddl
mcvkr picture mcvkr · May 22, 2017 · Viewed 54.3k times · Source

I have a package A, which uses some variables and procedures in another package B in the same schema. Now I want to move package A to a new schema. What privileges should I grant to the new schema for using the package B same way? What is the grant statement for that ?

Answer

JohnHC picture JohnHC · May 22, 2017

Use GRANT to give execute privileges

grant execute on PACKAGE_B to new_schema;

Then, you need to ensure that any reference in package A includes the full path:

PACKAGE_B.SOME_PROC

It might be worth creating a public synonym in for the package, so that you can avoid referencing the schema too.

Related questions

Get VIEW ddl using query

For database re-architecture I need to get DDL of each table and view in the database(Oracle). I don't want to go to property of each table/view and get SQL out of it in SQL Developer. I successfully got …

Read More
How can I generate (or get) a ddl script on an existing table in oracle? I have to re-create them in Hive

How can I generate a DDL script on an existing table in oracle? I am working on a project where I have to re-create some tables that are present in Oracle table into Hive.

Read More
Insufficient Privileges when creating tables in Oracle SQL Developer

I have granted the user in my connection to create tables, triggers, procedures, and sequence using sql+ (grant create table to <my_user>); however, that still does not allow me to create a table in that schema showing …

Read More