Monday Mar 02, 2015

New Behaviour in Oracle Database 12c and 11.2.0.4: SELECT ANY DICTIONARY with reduced privilege set

You've just upgraded to Oracle Database 12c - but your favorite admin tool receives an ORA-1031: Insufficient Privileges after connection?

Then the reason may be the reduced set of privileges for the SELECT ANY DICTIONARY privilege. This privilege does not allow access to tables USER$, ENC$ and DEFAULT_PWD$, LINK$, USER_HISTORY$, CDB_LOCAL_ADMINAUTH$, and XS$VERIFIERS. Actually such changes are not new. For instance in Oracle 10.1 we removed the access to  LINK$ in SELECT ANY DICTIONARY (well, this may have happened because the dblink's password was stored in clear text in LINK$ - a misbehavior which is fixed since Oracle 10.2).

Please be very careful with granting this privilege. Furthermore, you need to be aware that it can't be granted either through a role, nor is it included in the GRANT ALL PRIVILEGES.

Oracle 11.2:

Oracle 12.1:

Documentation can be found here: 

  1. SELECT ANY DICTIONARY Privilege No Longer Accesses Some SYS Data Dictionary Tables
    For better security, the SELECT ANY DICTIONARY system privilege no longer permits you to query the SYS schema system tables DEFAULT_PWD$, ENC$, LINK$, USER$, USER_HISTORY$, CDB_LOCAL_ADMINAUTH$, and XS$VERIFIERS. Only user SYShas access to these tables, but user SYS can grant object privileges (such as GRANT SELECT ON USER$ TO sec_admin) to other users.
    .
  2. 2.9.2.5 Increased Security When Using SELECT ANY DICTIONARY

Please be aware that you can't query anywhere inside the database which privileges are included in the SELECT ANY DICTIONARY privilege as this is embedded in our code.

 --Mike

PS: Credits go to Andy Kielhorn for highlighting this to me and thanks to Gopal for providing me with the doc links

Thursday Jan 17, 2013

Potential check for corruptions

Having a corruption somewhere in the database is one of the worst case scenarios I could ever imagine - especially if it "sleeps" somewhere in the data dictionary. Recently I did talk to a customer who encountered a failing upgrade due to a data dictionary corruption gotten introduced in an earlier release.

What can you do to check your database(s) prior to an upgrade or generally from time to time? Actually I know now two powerful possibilities: 

  • hcheck.sql
    See MOS Note:136697.1
    This script will check for known problems in Oracle8i, Oracle9i, Oracle10g and Oracle 11g.
    You will need to create
    hOut Helper Package first - please see MOS Note:101468.1 to download the script hout.sql
  • RMAN validation:
    RMAN> backup check logical validate database;
    See MOS Note:836658.1 for further details - and you can run this with multiple parallel channels to speed up the run

Tuesday Apr 03, 2012

New Slides - and a discussion about Dictionary Statistics

First of all we have just upoaded a new version of the Upgrade and Migration Workshop slides with some added information. So please feel free to download them from here.The slides have one new interesting information which lead to a discussion I've had in the past days with a very large customer regarding their upgrades - and internally on the mailing list targeting an EBS database upgrade from Oracle 10.2 to Oracle 11.2.

Why are we creating dictionary statistics during upgrade?

I'd believe this forced dictionary statistics creation got introduced with the desupport of the Rule Based Optimizer in Oracle 10g. The goal: as RBO is not supported anymore we have to make sure that the data dictionary has fresh and non-stale statistics. Actually that would have led in Oracle 9i to strange behaviour in some databases - so in Oracle 9i this was strongly disrecommended.

The upgrade scripts got hardcoded to create these stats. But during tests we had the following findings:

It's important to create dictionary statistics the night before the upgrade. Not two weeks before, not 60 minutes before your downtime begins. But very close to the upgrade. From Oracle 10g onwards you'd just say:

$ execute DBMS_STATS.GATHER_DICTIONARY_STATS;

This is important to make sure you have fresh dictionary statistics during upgrade for performance reasons. Tests have shown that running an upgrade without valid dictionary statistics might slow down the whole upgrade by factors of 2x-3x.

And it would be also a great idea post upgrade to create again fresh dictionary statistics when you've did suppress the stats creation during the upgrade process. Suppress? Yes, you could set this underscore parameter in the init.ora:

_optim_dict_stats_at_db_cr_upg=FALSE

to suppress the forced dictionary statistics collection during an upgrade. We believe strongly that (a) people using the default statistics creation process which will create dictionary statistics by default and (b) create fresh stats before upgrade on the dictionary. Therefore we find it save once you have followed our advice to use the underscore during upgrade. And we've taken out that forced statistics collection during upgrade in the next release of the database.

Please note: If you are using the DBUA for the upgrade it will remove underscore parameters for the upgrade run to improve performance - which is generally a good idea. So you'll have to start the DBUA with that call:

$ dbua -initParam "_optim_dict_stats_at_cb_cr_upg"=FALSE

-Mike

Monday May 16, 2011

Move to Locally Managed Tablespaces

As I've got asked during the workshop in Warsaw how to migrate Dictionary Managed to Locally Managed tablespaces here's some additional information and an example.

To find out if a tablespace is dictionary or locally managed you'd use this query:

SQL> select TABLESPACE_NAME, EXTENT_MANAGEMENT from DBA_TABLESPACES;


The procedure to migrate to locally managed tablespace is:

SQL> EXECUTE DBMS_SPACE_ADMIN.TABLESPACE_MIGRATE_TO_LOCAL('tbs');

And the whole migration procedure would look like this:

STARTUP RESTRICT EXCLUSIVE;
EXEC DBMS_SPACE_ADMIN.TABLESPACE_MIGRATE_TO_LOCAL('TBS');
-- do this for all tablespaces except SYSTEM, TEMP and SYSAUX
DROP TABLESPACE TEMP;
-- necessary if there's no "real" temp tablespace definded yet - see Note:160426.1
CREATE TEMPORARY TABLESPACE TEMP TEMPFILE 'f' SIZE 10M;
ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TEMP;
ALTER TABLESPACE SYSAUX OFFLINE;
ALTER TABLESPACE USERS READ ONLY;
-- for all tablespaces except SYSTEM, RBS, TEMP and SYSAUX
EXEC DBMS_SPACE_ADMIN.TABLESPACE_MIGRATE_TO_LOCAL('SYSTEM');
ALTER TABLESPACE SYSAUX ONLINE;
ALTER TABLESPACE USERS READ WRITE;
-- for all tablespaces except SYSTEM, RBS, TEMP and SYSAUX
SHUTDOWN IMMEDIATE

STARTUP


About

Mike Dietrich - Oracle Mike Dietrich
Master Product Manager - Database Upgrade & Migrations - Oracle Corp

Based near Munich/Germany and spending plenty of time in airplanes to run either upgrade workshops or work onsite or remotely with reference customers. Acting as interlink between customers/partners and the Upgrade Development.

Follow me on TWITTER

Contact me via LinkedIn or XING

Search

Archives
« May 2015
SunMonTueWedThuFriSat
     
1
2
3
4
5
6
7
8
9
10
12
13
14
15
16
17
19
20
21
22
23
24
25
28
29
30
31
      
Today
Oracle related Tech Blogs
Slides Download Center
Visitors since 17-OCT-2011
White Paper and Docs
Workshops
Viewlets and Videos
This week on my Rega/iPod/CD
Workshop Map
Upgrade Reference Papers