| Oracle Privlages [message #4587] |
Fri, 27 December 2002 22:37  |
Keith
Messages: 88
Registered: March 2000
|
Member |
|
|
I am trying to use a generated SQL statement to 'copy' all tables under one schema, to another. I know there are probably a few ways this can be handled, but I know this way should work..... I am using a procedure similar to:
create or replace procedure cpytbl
is
begin
declare
cursor get_data is
select table_name from user_tables;
v_sql varchar2(100);
begin
for r1 in get_data
loop
v_sql := 'create table user2.'||r1.table_name||' as select * from user1.'||r1.table_name;
execute immediate v_sql;
end loop;
end;
end cpytbl;
I can create the table as using just sql code, but when I use execute immediate, it does not work. In the original code, I put a drop table command before, to drop the table if it exists first, that statement worked!!
I checked privileges, the username running the procedure has execute any procedure, create table, create any table.... everything there sounds fine.
When I execute the procedure though, I get an error stating that I have insufficient privileges.
I know this script works, because I used it in another database, but I just can't figure out why it isn't working now.
Any idea what this missing privilege is?
|
|
|
|
| Re: Oracle Privlages [message #4588 is a reply to message #4587] |
Fri, 27 December 2002 23:45  |
 |
Barbara Boehmer
Messages: 9096
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
It is not clear, from the information that you have given, which user is creating the procedure and which user is attempting to execute the procedure.
By default, unless specified otherwise, the procedure is executed under the privileges of the creator (definer) of the procedure, not the one executing (invoker) of the procedure. However, if you add authid current_user, then it is executed under the privileges of the invoker, not the definer.
If it is created under the default definer's rights, then the owner of the procedure must have been explicitly granted the necessary object privileges for all objects referenced within the body of the code; the owner cannot have obtained required privileges through roles.
So, if, under the default definer's rights, user1 creates the procedure and grants execute on the procedure to user2, if user2 then attempts to execute the procedure, user2 will receive an "insufficent privileges" error, because user1, who created the procedure, does not have explicit privileges (not granted through a role) to create tables in user2's schema.
If, on the other hand, user1 creates the procedure, using authid current_user and grants execute on the procedure to user2, then when user2 attempts to execute the procedure, it is executed using user2's privileges, and user2 can succesfully execute it, because user2 can create tables in user2's own schema. I suspect that this is your situation and that would be the easiest workaround. Also, you have some unnecessary code in your procedure. The following is how I would write it:
CREATE OR REPLACE PROCEDURE cpytbl
AUTHID CURRENT_USER
IS
BEGIN
FOR r1 IN
(SELECT table_name
FROM user_tables)
LOOP
EXECUTE IMMEDIATE
'CREATE TABLE user2.' || r1.table_name
|| ' AS SELECT * FROM user1.' || r1.table_name;
END LOOP;
END cpytbl;
/
Please click on the following link for further explanation by Tom Kyte:
|
|
|
|
| Re: Oracle Privlages [message #4592 is a reply to message #4587] |
Sat, 28 December 2002 13:53 |
Keith
Messages: 88
Registered: March 2000
|
Member |
|
|
Thanks!!! You solved the problem. The rights to create the table were obtained using a role.
I instead granted the 'create any table' privilege to the the user who had tables to be exported (who also compiles and runs the procedure).
Thanks again...
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
