#100 TPC-H transaction counter fails with “sql parse error” on Oracle 12c

v1.0_(example)
closed-wont-fix
Steve Shaw
None
1
2014-03-28
2014-02-07
Steve Shaw
No

running TPC-H transaction counter against Oracle 12c fails with sql parse error and succeeds against previous versions. Following statement is and confirms privilege issue with 12c.

SQL> select sum(executions) from v$sqlarea where command_type = 3 and parsing_user_id in (select user# from sys.user$ where type# = 1 and astatus = 0 and name not in ('SYS','SYSTEM','SYSMAN','DBSNMP'));
ERROR at line 1:
ORA-01031: insufficient privileges

Oracle have changed the privileges in 12c so access is no longer granted to system and is no longer backward compatible.

http://docs.oracle.com/cd/E16655_01/server.121/e17906/chapter1.htm#NEWFT406

1.9.2.5 Increased Security When Using SELECT ANY DICTIONARY
The SELECT ANY DICTIONARY privilege no longer permits access to security sensitive data dictionary tables DEFAULT_PWD$, ENC$, LINK$, USER$, USER_HISTORY$, and XS$VERIFIERS.

Workaround - grant select on sys.user$ to system.

Discussion

  • Steve Shaw
    Steve Shaw
    2014-03-28

    • status: open --> closed-wont-fix
    • Priority: 5 --> 1