show parameters cmd needs dba priv in 9i - why?

[raygg]

In 8i 'show parameters' can be executed with only connect and resource privileges - but in 9i it seems to be restricted to dba. What privilege does a user have to have short of dba to execute 'show parameters' in 9.2.0?

 

[ngibbs]

User needs to be granted select any dictionary to show parameters. I would not grant this privilege to an ordinary user unless they really need it and you are aware of the implications of the privilege

 

[raygg]

In a strictly test - with no production - environment - what could be the downside? I am a DBA and former auditor.

 

[AOLEURO]

Hi Raygg


From 9i Oracle change the parameter O7_DICTIONARY_ACCESSIBILITY from TRUE to FALSE.

Ex:

SQL>connect allan@databaser as sysdba;

SQL>grant select any table to allan;

SQL>connect allan@databaser;

SQL>show parameter;
ORA-00942 – table or view does not exists

SQL>connect allan@databaser as sysdba;

SQL>show parameter;
….
….
….
….
Long list of parameters.

SQL>Alter system set O7_DICTIONARY_ACCESSIBILITY = TRUE scope=spfile;

SQL>shutdown immediate;

SQL>startup;

SQL>connect allan@databaser;

SQL>show parameter;
….
….
….
….
Long list of parameters.


Conclusion:
------------
You need to have O7_DICTIONARY_ACCESSIBILITY = TRUE so Oracle 9i reacts the same way as 8i.


Regards
Allan
Icq: 346225948

 

[raygg]

I think I read somewhere that turning this on allows dropping/updating/trancating any system table when granting drop/update/truncate any table. By turning off ora7 compatibility - dropping/updating/truncating any table does not include updates to system tables. Has anyone else run across this?

Ray

 

[AOLEURO]

Hi Ray

Yes – your questing is the same as the first questing. The “SHOW parameter” is equal to “select any table”.

O7_DICTIONARY_ACCESSIBILITY = TRUE is needed so Oracle 9i reacts the same way as 8i – so they reacts as an Oracle 7 system.


In Oracle 7 the SYSTEM user just granted users (schema) ANY rights and they got these rights:

• select any table (and you can use SHOW parameter)
• create any table
• update any table
• delete any table
• drop any table

From Oracle 8 – Oracle want us to use CONNECT xxxxx AS SYSDBA | SYSOPER.

But to avoid losing tons of code in the real world – Oracle created the parameter O7_DICTIONARY_ACCESSIBILITY and the default was TRUE so version 8 was similar to Oracle 7.

However Oracle wants us to stop using this function, and if you create new databases then the parameter now defaults to FALSE.

…Back to the beginning – There is NO change from Oracle 8 to 9 in Oracle methods – no new function or no new roles – they just change a default value from TRUE to FALSE.

So if you are using code back to the Oracle 7 days – then change to value to TRUE – else use AS SYSDBA | SYSOPER instead.


Regards
Allan
Icq: 346225948