Oracle database restrictions and workarounds at daily DBA work

October 4, 2011 by 12 Comments 

Oracle introduces hundreds of new database features in every database release. At the same time some tiny small restrictions in the code exist for a long time that bother database developers and DBAs at their daily work. Below I explore some of the common Oracle restrictions I often encounter during database administration. I give the workarounds for most of them and compare some with Microsoft SQL Server implementations.

So in Oracle database server:

Not possible to prevent a schema user to perform following tasks:

– dropping own objects
– creating indexes on own tables
– and some more…

Versions affected: Oracle 7, 8i, 9i, 10g, 11g, 11gR2

Not possible for a DBA user to create a DB link in different schema

Versions affected:
Oracle 7, 8i, 9i, 10g, 11g, 11gR2

SQL example:

sys@SQL+>   alter session set current_schema=scott;
sys@SQL+>   create database link link1 connect to jane identified by doe using 'us_supply';
ERROR at line 1:
ORA-01031: insufficient privileges

Oracle workaround:

–          grant rights to create a database link to a schema owner

–          connect as the schema owner

–          create a private database link


Not possible for a DBA user to create a materialized view in a different schema.

Versions affected:
Oracle 7, 8i, 9i, 10g, 11g, 11gR2


DBA@SQL+> create materialized view hr.test_mv
build immediate
refresh on demand
as
select * from hr. resource_reporting_v
/
select * from hr.resource_reporting_v
*
ERROR at line 5:
ORA-01031: insufficient privileges

Oracle workaround:

a) Use always SYSDBA account for that operation

sys@SQL+> create materialized view hr.test_mv
build immediate
refresh on demand
as
select * from hr.resource_reporting_v
/

Materialized view created.
b) Grant create table to the materialized view owner and only then create a view:

ALTER SESSION SET CURRENT_SCHEMA = HR;
grant create table to HR;
@@mv.sql
revoke create table from HR;

Not possible to grant read access ONLY on PL/SQL source code (procedures, packages, triggers, views) to different schema (except DBA user, who has access on all PL/SQL Source code )

You can perform the following operations to give read access on the source code to other user but then other user can also execute the code. Basically no read restriction:

–          To see procedure/function code => grant execute on procedure/function

–          To see trigger code => grant select on hr.customer to test

–          To see types => grant execute on hr.entry to test;

Versions affected: Oracle 7, 8i, 9i, 10g, 11g, 11gR2

Not possible being a DBA to create view / materialized view in different schema based on it’s private DB link


create view …. from table1@DB_LINK
*
ERROR at line 21:
ORA-02019: connection description for remote database not found

Versions affected: Oracle 7, 8i, 9i, 10g, 11g (Fixed in 11gR2)

Oracle workaround:

– connect as schema_owner
– grant create table, create materialized view to < schema_owner>;

Not possible to grant easily read only or read/write access on all the schema objects to another user

Versions affected: Oracle 7, 8i, 9i, 10g, 11g, 11gR2

Microsoft SQL Server (MS SQL)  implementation
: easily done with system roles: db_datareader / db_datawriter

Oracle workaround:

Grant SELECT or SELECT + INSERT + UPDATE + DELETE to a user on all the schema objects + create a trigger that does this for all new created objects.

Not possible easily to redirect user to a different schema

Versions affected: Oracle 7, 8i, 9i, 10g, 11g, 11gR2

ALTER SESSION SET CURRENT SCHEMA=<>;

Unfortunately this does not work for some situations. Imagine: select * from user_tables; still shows your user tables

Not possible to assign a user to a default schema on logon

Versions affected: Oracle 7, 8i, 9i, 10g, 11g, 11gR2

Microsoft SQL Server (MS SQL) implementation: Easily done with default database concept
(why not implement default schema concept in Oracle 12c…)

Oracle workaround:

a) Create a trigger ON LOGON with ALTER SESSION SET CURRENT SCHEMA=<>;
b) Use private synonyms

Not possible to create temporary tables without create table privilege
(frustrating since create table is very powerful right for oracle schema; better having create temporary table right in the future Oracle releases)

Versions affected: Oracle 7, 8i, 9i, 10g, 11g, 11gR2

SQL Example:

Create global temporary table t as select 1 num from dual
*
ERROR at line 1:
ORA-01031: insufficient privileges

Not possible to do incomplete restore of one schema / tablespace on the same DB instance.

Versions affected: Oracle 7, 8i, 9i, 10g, 11g, 11gR2

Microsoft SQL Server (MS SQL) implementation: Easily done based on the database concept

Oracle workaround:

For that type of recovery an auxiliary instance needs to be build first where the actual recovery happens

Add your own cases if they bother you considerably…

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

Comments

12 Responses to “Oracle database restrictions and workarounds at daily DBA work”
  1. Brad H says:

    How does granting read access to source code allow the code to be updated?

  2. Kirill Loifman says:

    You are right!
    What I mean is mainly that granting EXECUTE right gives the ability to see PL/SQL code and execute it. To modify the code in different schema there is only one way of granting ALTER ANY PROCEDURE that gives you full rights to all PL/SQL code of the DB server.

  3. Zoran P says:

    Hi Kirill,

    In Oracle you can prevent a schema user to drop own objects and create indexes on own tables by using Database Vault Option.

  4. Kirill Loifman says:

    Yes, you are right, but this is more a workaround that requires :
    – additional development effort,
    – Oracle Database Enterprise Edition
    – Extra cost since Oracle Database Vault is a licensable option

    — Kirill

  5. “Not possible to grant read access ONLY on PL/SQL source code”

    I give developers this privilege:
    SQL> grant select_catalog_role to user ;
    then they can read source code in tools like SQL*Developer, but can’t execute anything.

    That gives them read access on more than just source code though, if you wanted to be more restrictive then:
    SQL> grant select on dba_source to user ;

    Or even more specific with:
    SQL> create view some_source as select * from dba_source where owner = ‘SCHEMA’ and name in () ;
    SQL> grant select on some_source to user ;

    Andrew.

  6. Kirill Loifman says:

    Hi Andrew
    Thanks for your workarounds and yes, they will partly work.
    Why partly?
    Because first, I never recommend granting select_catalog_role to developers due to security, etc.
    Second, grants on dba_source and on a custom view will allow developers extract source code from that views only using SELECT statement, but not using GUI of SQL developer or any other tool.
    Again, these are only workarounds, that was point
    — Kirill

  7. Tony says:

    Question regarding the HR schema & using the following to connect to the pdb:
    connect hr/hr@localhost:1521/PDBORCL12C as sysdba in lieu of what’s in the documentation & earlier versions, I was using this approach to login as HR, but after a recent shutdown, the HR schema has not been the default schema for HR. Likewise for OE & SH. It wasn’t setting the schema as HR until I applied your current_schema statement. I’m not sure what I did to cause this.

    I look forward to seeing more of your comparison & contrast of Oracle & MSSQL.

  8. Alex says:

    > Not possible to prevent a schema user to perform following tasks
    Database trigger?

    >Not possible easily to redirect user to a different schema
    proxy users?

  9. Kirill Loifman says:

    Hi Alex

    Definitely you can apply different workarounds. However they are usually painful and not fulfil the standard functionality.
    – Database triggers are consume lot of DB resources and are hard to maintain compared to Oracle access rights mechanism.
    – Proxy user will not work 100% in this case since you would need to establish a new DB connection and this is not always possible in app.

    — Kirill

  10. Rajesh Jayagopi says:

    Hi,
    Could anyone tell me why I get the below error?
    Connect / as sysdba
    sql> CREATE OR REPLACE procedure sysproc_name is
    BEGIN
    dbms_output.put_line(‘Procedure Executed’);
    END;
    /

    sql> BEGIN
    sysproc_name;
    END;
    /

    Error in grants: ORA-02019: connection description for remote database not found

  11. Kirill Loifman says:

    Hi Rajesh
    Normally it should work. It works for me at least.
    Usually this error is releted to database links’ issues.
    Please double check in case there are some in between of your code.
    — Kirill

  12. Kirill Loifman says:

    Try Oracle Database Vault

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