Re: TRUNCATE & Version 7.1.3 (and other 7.1 changes)

jshowers_at_cmi.on.ca (Jodi Showers) writes:

:> |> Starting with 7.1.x, a user granted the DELETE privilege to a
:> |> given table will no longer be allowed to TRUNCATE the table - only
:> |> the owner of the table will have this privilege.
 

:I don't know which port (or if there is a port difference) you are on
:, but in my experience (RDMBS v7.0.16.6.0 on Pyramid) a user requires
:only table delete permissions for truncation.
 

:If this is not the case in 7.1.x please let me know.

According to the documentation for 7.1.3, in order to truncate a table in someone elses schema, you need the DROP ANY TABLE privilege, whereas before 7.1 you needed the DELETE ANY TABLE privilege.

Another change in 7.1 that might have some consequences for some sites is that a user must now have SELECT permission on a table in order to do any UPDATEs or DELETEs which cause a full table scan. (In addition to UPDATE or DELETE permission, obviously.) The documentation explains this as a way to avoid covert information leaks. For example, a user could perform a 'DELETE * FROM EMP WHERE SALARY > 50000' and a rollback to discover how many employees have a salary greater than $50,000.

7.1 also supports the SQL92 standard permitting column aliases to be used in the ORDER BY clause. In fact, the documentation goes on to say that position ordering (ie, 'order by 2') is not an official part of the SQL92 standard and recommends that users not use them, as they may be dropped from future releases of Oracle. (I guess this means that column aliases will now be _required_ in order to use ORDER BY clauses in selects with a UNION, earlier releases only permitted ordering by position number in unioned selects.)

I haven't actually tried any of this yet, but will be installing an instance of 7.1.3 for testing purposes next week and will report if I find out anything else 'interesting'.

---
Michael Nolan, Sysop for the DBMS RoundTable on GEnie
nolan_at_notes.tssi.com, dbms_at_genie.geis.com
(posted from nolan_at_helios.unl.edu)