Oracle database 12c wish list
In spite I’m an Oracle database expert I work also partly with Microsoft SQL Server and Sybase.
This gives me the opportunity to compare different RDBMSs and on the other hand identify weak areas in Oracle compared to others.
This article is about missing functionality or features that probably every Oracle DBA would like to have in the future Oracle database releases (Oracle Server 12c for example). And I’ll start with the database access control that is not so flexible on Oracle as in Microsoft SQL Server or Sybase.
Ability to prevent a schema user to perform following tasks
- dropping own objects (probably by introducing DROP system privileges)
- creating indexes on own tables
- some others..
The idea is that a schema should not have any DLL rights by default.
Ability to create a private DB link in different schema
At the moment this is not possible to do even for a SYS user.
Extended information about database users in dba_users view
- full_name field (to place some information about a user; similar to Sybase)
- schema_user (to distinguish a schema from end user)
- default_schema (similar to default_database concept in Microsoft SQL Server / Sybase)
Possibility for a DBA user to create a materialized view in a different schema
At the moment a DBA can not create a materialized view in a different schema if that schema user has no CREATE TABLE system privilege.
Possibility to grant read access ONLY on PL/SQL source code to a different schema
No available at the moment but potentially can be achieved with introducing SELECT or READ privilege on procedures, triggers, views, etc.
Default possibility to grant read-only and read/write access on all the schema objects to another user
Potentially “system roles” can be introduced Oracle 12c like DATAREADER / DATAWRITER that can be granted on every schema user, similar to Microsoft SQL Server. This is required to avoid manual workaround that some DBAs implement using a system trigger. So, this should be a standard DB functionality.
Improved mechanism of redirecting a user to a different schema
Implement a concept of “default schema” with following way
- add an additional field in dba_users “default_schema” (mentioned above already)
- extend create/alter user SQL statement syntax
- implement a standard mechanism of redirecting a user to a default_schema on logon (this is again to avoid a workaround with a system trigger).
- add “set schema” command to sql+ (a SQL+ shortcut for alter session set current_schema…)
- improve “alter session set current_schema” behaviour since it does not work for all the cases properly.
Availability of some more system privileges:
- create temporary table
- truncate table
Create table is a very powerful privilege for a schema user, however if it requires only temporary table you have to grant it anyway.
Possibility to do incomplete recovery of one schema / tablespace on the same DB instance
I’ll continue update this article with more wish items if time permits in the hope that some of these small but handy features will be implemented in the new database version Oracle 12c.