CODEWITHSUNDEEP
oracle-databaseoracle10g
user1469630
user1469630

Reputation: 301

I need to grant access to use tables of one schema in another schema

I have an schema [A], which has a package in which a function calls another schema's[B] table [tableB]. When I compiled the package I got the error, "table or view doesnt exit" I googled and found that the error is because my package is calling another schema's table. Later I found that I have to grant privilage in the called schema[B]. In Schema B's package I wrote the following code

procedure givePrivilege begin GRANT SELECT ON tableB TO A; end;

Im new to Oracle and Im struck.

Upvotes: 3

Views: 57882

Answers (2)

Chandu
Chandu

Reputation: 82903

Follow these steps:

1) Login to schema B
2) Run the statement GRANT SELECT ON tableB TO A;
3) In the package make sure the tableB is referenced as B.tableB
4) Compile the package.

Upvotes: 6

Justin Cave
Justin Cave

Reputation: 231661

As B, you simply need to grant A SELECT privileges on the table. You don't need to create a procedure that grants the privilege. You simply need to execute the GRANT statement while logged in as B.

GRANT SELECT ON tableB
   TO a

Upvotes: 2

Related Questions