- 3,723,939 Users
- 2,244,655 Discussions
- 7,850,767 Comments
Forum Stats
Discussions
Categories
- 16 Data
- 362.2K Big Data Appliance
- 7 Data Science
- 2.1K Databases
- 618 General Database Discussions
- 3.7K Java and JavaScript in the Database
- 32 Multilingual Engine
- 497 MySQL Community Space
- 7 NoSQL Database
- 7.7K Oracle Database Express Edition (XE)
- 2.8K ORDS, SODA & JSON in the Database
- 422 SQLcl
- 62 SQL Developer Data Modeler
- 185.1K SQL & PL/SQL
- 21.1K SQL Developer
- 2.4K Development
- 3 Developer Projects
- 32 Programming Languages
- 135.6K Development Tools
- 14 DevOps
- 3K QA/Testing
- 337 Java
- 10 Java Learning Subscription
- 12 Database Connectivity
- 72 Java Community Process
- 2 Java 25
- 12 Java APIs
- 141.2K Java Development Tools
- 8 Java EE (Java Enterprise Edition)
- 153K Java Essentials
- 135 Java 8 Questions
- 86.2K Java Programming
- 270 Java Lambda MOOC
- 65.1K New To Java
- 1.7K Training / Learning / Certification
- 13.8K Java HotSpot Virtual Machine
- 16 Java SE
- 13.8K Java Security
- 4 Java User Groups
- 22 JavaScript - Nashorn
- 18 Programs
- 147 LiveLabs
- 34 Workshops
- 10 Software
- 4 Berkeley DB Family
- 3.5K JHeadstart
- 5.7K Other Languages
- 2.3K Chinese
- 4 Deutsche Oracle Community
- 16 Español
- 1.9K Japanese
- 3 Portuguese
TRUNCATE TABLE giving error even with DROP ANY TABLE system privilege

Hi
I have a table called TEST1.T1:
create table test1.t1 (id int, constraint pk_id primary key(id));
and another called TEST1.T2:
create table test1.t2 (id int, constraint fk_id foreign key(id) references test1.t1(id) on delete cascade);
I have created a stored procedure in another schema TEST2.TPROC
create or replace procedure test2.tproc as
begin
execute immediate 'TRUNCATE TABLE TEST1.T1 CASCADE';
end tproc;
My user id is smitra and I have granted execute role to the user on this procedure:
grant execute on test2.tproc to smitra;
I also granted DROP ANY TABLE TEST2 user:
grant drop any table to test2;
When I execute this SP:
exec test2.tproc;
I get error:
BEGIN test2.tproc; END;
ERROR at line 1:
ORA-01031: insufficient privileges
ORA-06512: at "TEST2.TPROC", line 4
ORA-06512: at line 1
My question is when the TEST2 user, in which the procedure is defined, has the DROP ANY TABLE system privilege why it's throwing this error?
The only I was able to resolve the error is to change the DEFINER RIGHTS of the SP to INVOKER RIGHTS. Can somebody please explain why I am getting this error? I am using Oracle 12.1
Answers
-
grant drop any table to smitra;
and try again.
Thanks,
Hemendra Singh
-
Is there any reason you are not creating the tproc procedure in TEST1 schema?
Thanks,
Brett
-
Hi Hemendra - the user smitra has DROP ANY TABLE already.
-
Hi Brett - Yes that would solve the issue. TEST1 and TEST2 are just example schema I have given here. TEST1 is meant to contain only table while TEST2 for procedures/functions/packages etc. Given that TEST2 has DROP ANY TABLE System privilege should it not be able to truncate any table (regardless if it's in its own schema or someone else's schema)? That's my question.
-
Yes, you should be able to truncate the table. But for some reason it is not working. I was able to duplicate your problem and I think the problem is with the CASCADE option. I can successfully execute the following 2 statements as TEST2:
truncate table test1.t2; -- truncate the child table
truncate table test1.t1; -- truncate the parent table
However, I get the error when I execute this statement:
truncate table test1.t1 cascade;
Would it be possible to revise your procedure to use the above 2 statements? If not, I recommend you open a service request on My Oracle Support.
Thanks,
Brett