Hi, : Please grant nessesary permissions using SQL*PLUS. Logon as SYS. : Here is the complete script for the profiler part: create table : ora_monitor.profiler_data ( : GRANT SELECT, INSERT, DELETE, UPDATE ON ora_monitor.profiler_data TO PUBLIC; Looked for a reason to receiving ORA-28009 when installing DBTools 4.1 to access Oracle 9i databases, and found something very useful (again) in AskTom site: ORA-28009: connection to sys should be as sysdba or sysoper is not really new to Oracle9i but more people will hit it in 9i then in 8i and before. Oracle9i defaults the init.ora parameter "O7_DICTIONARY_ACCESSIBILITY" to FALSE whereas all prior releases defaulted it to TRUE. There are two side effects of this parameter being set to FALSE: o connect sys/password will not function, you will get the ORA-28009 error o Access to the "real" data dictionary owned by SYS will not be available to users, even if they have the SELECT ANY TABLE privelege. These are not the data dictionary views like ALL_OBJECTS but rather the base tables like SYS.OBJ$ that will be unaccessible. Now the first side effect, that "sys/password" will not work without sysoper/sysdba, is not clearly documented anywhere. The fact that the O7_DICTIONARY_ACCESSIBILITY init.ora parameter causes this is somewhat hard to track down -- but that is what is causing this. The other side effect -- that the "real" data dictionary is not accessible to normal users is well documented. That is after all the purpose of this initialization parameter. That said however, there is of course a caveat to this. Portions of the documentation still state: If this parameter is set to false and you need to access objects in the SYS schema, then you must be granted explicit object privilege. Also, the following roles, which can be granted to the database administrator, also allow access to dictionary objects: SELECT_CATALOG_ROLE, EXECUTE_CATALOG_ROLE, and DELETE_CATALOG_ROLE. But in Oracle9i that is not accurate. There is a new system privilege "SELECT ANY DICTIONARY" the permits access to the SYS schema (and by default the DBA role has this privilege granted to it). The "Oracle9i Database README Release Notes" included with the 9i software does describe this change: 18.3 Data Dictionary Protection Data dictionary protection is now enabled by default. Specifically, the O7_DICTIONARY_ACCESSIBILITY init.ora parameter is set to FALSE on installation. As a result, regular users who are not database administrators with ANY privileges (for example, SELECT ANY TABLE) can no longer use the ANY privilege upon data dictionary objects; however, the user can access non-SYS schema objects using the ANY privilege. Users making a database administrator user type connection (for example, CONNECT / AS SYSBDA) can exercise privilege on data dictionary objects, since SYSDBA has all privileges. A new system privilege, SELECT ANY DICTIONARY, provides users with SELECT access to any object in the SYS schema without giving them DBA privileges. Oracle recommends that the dictionary protection feature remain enabled as it is a more secure configuration. If O7_DICTIONARY_ACCESSIBILITY is set to TRUE, then regular users with the ANY privilege can use these privileges -- perhaps maliciously -- to alter data dictionary objects. My recommendation would be to leave the the O7_DICTIONARY_ACCESSIBILITY set to false and change scripts that connect as SYS to connect as some other user. Using the SYS account should be avoided in any case -- you should never use it to create objects, cannot use it to create triggers and some commands (for example "set transaction read only") don't even work when connected as SYS. Consider SYS to be a special account that you never need to use anymore. More info can be found in the link mentioned below, and I imagine that this could solve what is happening when installing DBTools to access Oracle 9i instances. Now, I imagine that some messages requiring connection as SYS must be changed, too... Rgds, Sbleck
|