Actually, I really like this idea. Just tried it out (works, of course), and
it does provide better security. thanks for the tip!
lc
-----Original Message-----
Tim
Sent: Thursday, June 21, 2001 3:06 PM
To: Multiple recipients of list ORACLE-L
If possible, I prefer to create the procedure in the table owners schema and
then grant execute to the user that needs to perform the truncate... That
way you don't have to grant privileges like "DROP ANY TABLE"...
Tim
-----Original Message-----
Sent: Thursday, June 21, 2001 2:44 PM
To: Multiple recipients of list ORACLE-L
It should only be drop any table.
"Walking on water and developing software from a specification are easy if
both are frozen."
Christopher R. Spence
Oracle DBA
Fuelspot
-----Original Message-----
Sent: Thursday, June 21, 2001 2:10 PM
To: Multiple recipients of list ORACLE-L
Lisa,
Just tried my steps on 816 and I needed one more system priv - drop any
table.
the stored procedure then worked fine.
nice catch!
Tom Mercadante
Oracle Certified Professional
-----Original Message-----
Sent: Thursday, June 21, 2001 1:40 PM
To: Multiple recipients of list ORACLE-L
Not so fast my friend...unless I am missing a step, this still does not
work.
USER: cogroster (user--not a role, but DOES have DBA privs, and does have
the system priv of *delete any table* specifically granted) -- also contains
the procedure
USER: user01 (schema to which I want to truncate a table, and the explicit
grants to update/delete/select are granted to cogroster)
**************************
command: execute truncate_table('user01.TABLE_NAME');
begin truncate_table('user01.TABLE_NAME');
*
ERROR at line 1:
ORA-01031: insufficient privileges
yaddah
yaddah
Not that I want to beat a dead horse, but I don't see what I am missing...
lc
-----Original Message-----
Thomas F
Sent: Thursday, June 21, 2001 10:51 AM
To: Multiple recipients of list ORACLE-L
All,
that's because there are special security rules attached to stored
procedures and packages.
from sql*plus, you can do a truncate if you have the DBA priv. The DBA priv
is a ROLE, and the ROLE gives you the {delete any table} system priv.
from a stored procedure/package, you may NOT perform a system priv (like
delete any table as Rachel explained) on a db table that is inherited thru a
ROLE. you must have the delete any table system priv granted directly to
the user where the stored procedure/package exists.
So, to summarize,
grant delete and table directly to an Oracle account.
create the stored proc/pack in that users schema.
execute the proc/pack.
should work fine.
hope this helps.
Tom Mercadante
Oracle Certified Professional
-----Original Message-----
Sent: Thursday, June 21, 2001 10:31 AM
To: Multiple recipients of list ORACLE-L
Not true...If I open a sqlplus window and issue a truncate on the schema--no
problem. If I try and do it through a procedure like below--I get the
errors....
lc
-----Original Message-----
[EMAIL PROTECTED]
Sent: Thursday, June 21, 2001 8:47 AM
To: Multiple recipients of list ORACLE-L
Not 100% sure but I think only the table owner can use "TRUNCATE"
Witold
Raymond Lee Meng Hong <[EMAIL PROTECTED]> on 06/21/2001 01:10:52
Please respond to [EMAIL PROTECTED]
To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
cc: (bcc: Witold Iwaniec/ATL_BLUECROSS_CA)
Got these error ? may be my DBA restrict these ?
ORA-00903: invalid table name
ORA-06512: at "SYS.DBMS_SYS_SQL", line 239
ORA-06512: at "SYS.DBMS_SQL", line 32
ORA-06512: at line 8
-----Original Message-----
Sent: Thursday, June 21, 2001 11:20 AM
To: Multiple recipients of list ORACLE-L
You can try the following , not sure if it will work though......let me know
if it does... :-)
DECLARE
myCur number;
mySQL varchar2(2000);
BEGIN
mySQL := 'TRUNCATE TABLE (table_name)';
myCur := DBMS_SQL.open_cursor;
DBMS_SQL.PARSE(myCur, mySQL, DBMS_SQL.NATIVE);
DBMS_SQL.CLOSE_CURSOR(myCur);
END;
Regards,
Karthik
-----Original Message-----
Sent: Thursday, June 21, 2001 10:55 AM
To: Multiple recipients of list ORACLE-L
Hello guru , how can I execute a truncate table in PL/SQL ?? It only work
for delete DML only ? why ?
Raymond Lee
Infopro Sdn Bhd
Block B3 Level 8, Leisure Commerce Square
No. 9, Jalan PJS 8/9 46150 Petaling Jaya
Selangor , Malaysia
Tel : 603-78766666 ext : 266 Fax : 603-78761233
Email : [EMAIL PROTECTED]
"Friendship with oneself is all important, because without it one cannot be
friend with anyone else in the world "
- Eleanor Roosevelt
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Mohan, Karthik (GEP)
INET: [EMAIL PROTECTED]
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Raymond Lee Meng Hong
INET: [EMAIL PROTECTED]
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author:
INET: [EMAIL PROTECTED]
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Lisa Clary
INET: [EMAIL PROTECTED]
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Mercadante, Thomas F
INET: [EMAIL PROTECTED]
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Lisa Clary
INET: [EMAIL PROTECTED]
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Mercadante, Thomas F
INET: [EMAIL PROTECTED]
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Christopher Spence
INET: [EMAIL PROTECTED]
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Johnston, Tim
INET: [EMAIL PROTECTED]
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Lisa Clary
INET: [EMAIL PROTECTED]
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
