Oracle database restrictions and workarounds at daily DBA work
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…
How does granting read access to source code allow the code to be updated?
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.
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.
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
“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.
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
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.
> 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?
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
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
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
Try Oracle Database Vault