2
votes

I have 3 Oracle users A, B, C and want to use B (as intermediate) to import a table from C to A. When connected to B, I can run "create table A.T1 as select * from T1@C". But when I put the sql statement into a procedure that B creates/owns, I keep getting "ORA-01031: insufficient privileges". As sysdba, I have granted all rights (dba) to B. So what am I missing here? Thank you.

2

2 Answers

2
votes

Have you granted the following to user B?

sql> grant create any table to b;
5
votes

In an definer's rights stored procedure (the default), the only privileges that are available are those that are granted directly to the user, not those that have been granted through a role (like DBA). So Pablo is correct that B would need to have the CREATE ANY TABLE privilege as a direct grant.