Thursday, April 10, 2008

Un-documented and Un-used privilege

A recent question on Oracle Forums about a privilege caught my attention (got a few others interested too) and I started looking for an answer...And in the end it turned out to be un-documented and un-utilized privilege. The privilege in question is "UNDER ANY TABLE". The Privilege does exists in the database; I checked in the database (DBA_SYS_PRIVS and ROLE_SYS_PRIVS) but its not documented anywhere. I searched 9i, 10g and 11g documentation but no luck.

SIPRAS@orademo> select * from ROLE_SYS_PRIVS where privilege like 'UNDER%';

ROLE PRIVILEGE ADM
------------------------------ ---------------------------------------- ---
DBA UNDER ANY TYPE YES
DBA UNDER ANY TABLE YES
DBA UNDER ANY VIEW YES

The other 2 privileges "UNDER ANY VIEW" and "UNDER ANY TYPE" are documented. Here is the link.

One of the respondent mentioned that :- In 2002 someone else asked this question on the Metalink forums and support replied, "This privilege is currently not used today. It was added for future functionality." May be oracle thought of it but did not use it.

My search lead me to "IBM Informix Dynamix Server". They have a UNDER privilege for TABLE and here is an excerpt from the documentation -

Under Privileges for Typed Tables (IDS)

You can grant or revoke the Under privilege to control whether users can use a typed table as a supertable in an inheritance hierarchy. The Under privilege is granted to PUBLIC automatically when a table is created (except in ANSI-compliant databases). In an ANSI-compliant database, the Under privilege on a table is granted to the owner of the table. To restrict which users can define a table as a supertable in an inheritance hierarchy, you must first revoke the Under privilege for PUBLIC and then specify the users to whom you want to grant the Under privilege. For example, to specify that only a limited group of users can use the employee table as a supertable in an inheritance hierarchy, you might execute the following statements:

REVOKE UNDER ON employee
FROM PUBLIC;

GRANT UNDER ON employee
TO johns, cmiles, paulz
Sounds like an ANSI compliant feature and something to do with "User-defined types" and "Nested tables" in Oracle; May be Oracle wanted to use for something similar but did not implement it hence the privilege is still sticking around without any use...or May be the "UNDER ANY TYPE" is sufficing the requirement but the "UNDER ANY TABLE" has not be taken out...

1 comment:

Ahmad Bilal said...

Good Sharing

--
Thanks.......Ahmad Bilal

Moderator
www.erpstuff.com
Oracle ERP Portal

http://oracleebusinesssuite.wordpress.com/