Oracle database 12c wish list

March 13, 2012 by 8 Comments 

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

– etc.

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.

Enjoyed this article? Please share it with others using the social site of your choice:

Comments

8 Responses to “Oracle database 12c wish list”
  1. Joshua Huber says:

    Great wish list. Especially the datareader/datawriter. Something like how MySQL works would be nice: GRANT SELECT ON HR.* TO SCOTT.

    About the private DB link, it actually is possible to create/drop private links as SYS (or other privileged user) in 10g/11g, perhaps earlier, too. It’s not pretty but it works, and it’s one of the scripts in my toolbox:

    DECLARE
    uid number;
    sqltext varchar2(1000) := ‘create database link test_link connect to target_user?? identified by target_user_password?? using ”target_DB_TNS??”’;
    myint integer;
    BEGIN
    select user_id into uid from all_users where username like ‘SCOTT’;
    myint:=sys.dbms_sys_sql.open_cursor();
    sys.dbms_sys_sql.parse_as_user(myint,sqltext,dbms_sql.native,UID);
    sys.dbms_sys_sql.close_cursor(myint);
    END ;

  2. yes, indeed this should work as a workaround.
    But be careful and never grant DBMS_SYS_SQL to public or non-privileged users
    since they can become DBA in that case.

    — Kirill

  3. Sleeke says:

    I would like to see an ability to reame a user once created?

    Alter user bob rename to bill;

    This will save the issue of expdb/impdb needs

    It would be good to have a few exta options when creating tables to give extra info to the gather _table stats or gather schematic stats package, like % sample, all columns/index columns/ or specific columns

  4. Alex says:

    > Ability to create a private DB link in different schema
    there is a trick to do this 🙂

  5. Kirill Loifman says:

    Hi Alex
    Simply share your trick.
    — Kirill

  6. Nickita says:

    connected as sys:

    create or replace procedure someschema.nick_proc (a varchar2) authid definer
    is
    begin
    execute immediate a;
    end;
    /

    exec someschema.nick_proc(‘create database link bla bla bla…’);

  7. sudhamani says:

    Does this feature implemented in Oracle 12C ?
    “Default possibility to grant read-only and read/write access on all the schema objects to another user”

  8. Kirill Loifman says:

    I do not think so

Add a Comment

We welcome thoughtful and constructive comments from readers.
If you want your own picture to show with your comment?
Go get a Globally Recognized Avatar!

DBMS Blog Updates : Subscribe RSS RSS: Subscribe to Articles · Subscribe to Comments Subscribe RSS Receive site updates via email