CODEWITHSUNDEEP
oracle-databaseplsqloracle10gschemasql-grant
abg
abg

Reputation: 2092

How to grant to stored procedure, privileges to select from a different schema?

I need that the user does not have access to another scheme, but the stored procedure could access a different schema.

Upvotes: 1

Views: 8562

Answers (2)

APC
APC

Reputation: 146239

Privileges cannot be granted to objects, only to users or roles.

The way to implement this granularity of control is for the other schema to define the procedure which operates on its own tables. It then grants execute on the procedure to the other user. This is one valuable use case for procedures, encapsulating operations on schema objects.

The mechanism for controlling privileges in PL/SQL objects is the AUTHID. There are two options, CURRENT_USER and DEFINER. In this case you want to use the definer's rights, AUTHID DEFINER (which is the default). Find out more.

Upvotes: 3

Colin 't Hart
Colin 't Hart

Reputation: 7729

What you are describing is not possible.

If b.procedure can select, insert, update or delete on a.table, then anyone logged in as b could do so too.

What you should do instead is create procedure a.procedure and grant execute on a.procedure to b.

Upvotes: 5

Related Questions