Privileges for PL/SQL ...

[oracle_faq]

If I want to execute the following command in PL/SQL procedure:

'grant select, insert, update, delete on owner.table_name to public'

using execute immediate.

What kind of privileges does the USER executing the procedure needs.

Assuming the table owner is different than the user running the procedure.

Thanks in advance.

[DevangP]

grant with admin options

[oracle_faq]

What kind of privilege should it be?

example: grant select any table to user1 with admin option. ???

[oracle_faq]

Found it. It is

grant GRANT ANY OBJECT PRIVILEGE to user1;

Thanks.

[gandolf989]

Originally posted by oracle_faq
Found it. It is

grant GRANT ANY OBJECT PRIVILEGE to user1;

Thanks.

Thants like hanging up a picture with a sledge hammer.
You might not want to grant admin option to public.
Just grant execute on the procedure to the people who need it.

grant select, insert, update, delete 
   on owner.table_name to schema_owner with grant option;

[oracle_faq]

The scenario I am trying to solve is as below.

1. Create a table in a procedure.
2. Grant insert update select delete on the table...within the same procedure.

Since I am creating the table in a different schema that the user who is executing the procedure...the procedure execution fails. So was looking for a privilege I need to give to the user to make it work. This user already has DBA privs. The only problem is that PL/SQL does not see these privs...so I have to grant them explicitly.

[marist89]

create any table

[gandolf989]

Originally posted by oracle_faq
The scenario I am trying to solve is as below.

1. Create a table in a procedure.
2. Grant insert update select delete on the table...within the same procedure.

Since I am creating the table in a different schema that the user who is executing the procedure...the procedure execution fails. So was looking for a privilege I need to give to the user to make it work. This user already has DBA privs. The only problem is that PL/SQL does not see these privs...so I have to grant them explicitly.

This privilege Should work.

GRANT create any table TO schema_owner;

It is probably best if you give out privileges that user need and no more.

[stecal]

You may also want to consider the "Privileges granted via role are disabled inside stored procedures" limitation.