6

Read Only Access for providing backend support for an Oracle Application

 3 years ago
source link: https://mikesmithers.wordpress.com/2018/07/25/read-only-access-for-providing-backend-support-for-an-oracle-application/
Go to the source link to view the article. You can view the picture content, updated content and better typesetting reading experience. If the link is broken, please click the button below to view the snapshot at that time.

Read Only Access for providing backend support for an Oracle Application

Posted on July 25, 2018

The World Cup is finally over and “It’s Coming Home !”
For quite a long time, we English laboured under the illusion that “it” was football.
Fortunately for Scots everywhere, “It” turned out to be the World Cup which, like so many international sporting competitions, was conceived in France.

Another area that is often subject to flawed assumptions is what privileges are required to provide read-only access for someone to provide support to an Oracle Application.
So, for any passing auditors who may be wondering why “read only” access to an Oracle application sometimes means Write, or even Execute on certain objects…

The Application

We’re using the standard HR sample application provided with any Oracle database. For the purposes of this post, we’ve added couple of enhancements.
The application has use of a directory object called HR_FILES :

create directory hr_files as '/u01/app/oracle/hr_files'
/
grant read, write on directory hr_files to hr
/

There is an External Table which is used for an ETL process…

create table countries_xt
(
iso_code varchar2(2),
country_name varchar2(100)
)
organization external
(
type oracle_loader
default directory hr_files
access parameters
(
records delimited by newline
badfile 'countries.bad'
logfile 'countries.log'
skip 1
fields terminated by ','
(
iso_code char(2),
country_name char(100)
)
)
location('countries.csv')
)
reject limit unlimited
/

In addition to it’s standard grants, HR also has create any context :

grant create any context to hr
/

…which means the application can use contexts in a view.

The context package created for this is :

create or replace package hr_deptno_ctx as
procedure set_ctx_val( i_dept_id departments.department_id%type);
function get_ctx_val return departments.department_id%type;
end hr_deptno_ctx;
/
create or replace package body hr_deptno_ctx as
procedure set_ctx_val( i_dept_id departments.department_id%type)
is
begin
dbms_session.set_context('hr_deptno', 'department_id', i_dept_id);
end set_ctx_val;
function get_ctx_val
return departments.department_id%type
is
begin  
return sys_context('hr_deptno', 'department_id');
end get_ctx_val;
end hr_deptno_ctx;
/

The context itself is created as follows :

create context hr_deptno using hr_deptno_ctx
/

…and used in a view…

create or replace view emp_restricted as
select *
from employees
where department_id = hr_deptno_ctx.get_ctx_val
/   

Remember, what we want to do here, is give “Read Only” access to this application to a database user. Let’s start with something simple…

Creating a Read Only Role

To begin with, let’s simply create a role called HR_READONLY and grant select on all HR tables and views to that role :

create role hr_readonly
/
grant select on hr.countries to hr_readonly;
grant select on hr.countries_xt to hr_readonly;
grant select on hr.departments to hr_readonly;
grant select on hr.employees to hr_readonly;
grant select on hr.jobs to hr_readonly;
grant select on hr.job_history to hr_readonly;
grant select on hr.locations to hr_readonly;
grant select on hr.regions to hr_readonly;
grant select on hr.emp_details_view to hr_readonly;
grant select on hr.emp_restricted to hr_readonly;

Now we can simply grant this role to our read only user …

set verify off
accept pwd prompt 'Enter password for new user MIKE_RO : ' hide
create user mike_ro identified by &pwd;
grant create session, hr_readonly to mike_ro
/

…and this is a really short post…

Selecting from External Tables

Let’s just connect as MIKE_RO and confirm that all is well…

select region_id, region_name
from hr.regions
order by 1
/
REGION_ID REGION_NAME             
--------- -------------------------
1 Europe                  
2 Americas                
3 Asia                    
4 Middle East and Africa 

…see, no problem. Let’s try the new external table…

no_dir_err.png?w=584&h=264

…so, it looks like our read only user will need READ access on the directory. No biggie, it’s still “READ” only…

grant read on directory hr_files to mike_ro
/

It’s when we have this privilege and then attempt to access the external table again, where things get interesting…

select *
from hr.countries_xt
/
Error starting at line : 1 in command -
select *
from hr.countries_xt
Error report -
ORA-29913: error in executing ODCIEXTTABLEOPEN callout
ORA-29400: data cartridge error
KUP-04074: no write access to directory object HR_FILES

The problem here is that the act of selecting from an external table will cause one or more files to be written (logfile, badfile, discardfile). Therefore, WRITE permissions are required on the directories to which these files will be written. In our case, the files are all written to HR_FILES so…

grant write on directory hr_files to mike_ro
/

…means that we can now select from the table :

select *
from hr.countries_xt
/
ISO_CODE                  COUNTRY_NAME            
------------------------- -------------------------
FR                        FRANCE                  
HR                        CROATIA                 
BE                        BELGIUM                 

NOTE – it’s always an outstandingly good idea to check the privileges a user already has on a Directory object before granting more.

“That’s not a problem”, I hear you say, “after all, our read only user won’t have EXECUTE permissions on anything. Well…

Changing context values in a session

Now let’s have a look at our new view. Connected as HR, we can see that the context value must be set for any rows to be returned…

select hr.hr_deptno_ctx.get_ctx_val
from dual
/
GET_CTX_VAL
-----------
select count(*)
from hr.emp_restricted
/
COUNT(*)
----------
0
exec hr.hr_deptno_ctx.set_ctx_val(60)
PL/SQL procedure successfully completed.
select count(*)
from hr.emp_restricted
/
COUNT(*)
----------
5

So, in order for our Read-Only account to be able to “read” this view, it will require execute privileges on a package.
Now, you might wonder why we can’t simply grant execute on DBMS_SESSION, which is the package called by HR_DEPTNO_CTX to get and set the context values.
The answer can be found in the Oracle docs for the SET_CONTEXT procedure in DBMS_SESSION which state :

“The caller of SET_CONTEXT must be in the calling stack of a procedure that has been associated to the context namespace through a CREATE CONTEXT statement. ”

Therefore, our read only user needs to be granted execute on the HR package itself :

grant execute on hr.hr_deptno_ctx to mike_ro
/

Viewing stored source code

The next requirement for our read only user is to be able to see the source code that’s actually in the data dictionary (as opposed to say, in a source control repository somewhere).
Yes, I know that your Source Control Repo master/trunk/main branch should be a faithful copy of your production code. However, biter experience to the contrary leaves me reluctant to make this assumption. It’s much safer to see the actual code that’s being executed, not what it probably is.

Unfortunately, as things stand, we do not even have access to DBA_SOURCE.

At this point though, we can give our auditor a moment’s respite, we only want to grant the SELECT_CATALOG_ROLE role.

grant select_catalog_role to mike_ro
/

This now enables our Read Only account to view the source for HR’s objects…

set lines 130
set heading off
set feedback off
select text
from dba_source
where owner = 'HR'
and name = 'SECURE_DML'
and type = 'PROCEDURE'
order by line
/
PROCEDURE secure_dml
IS
BEGIN
IF TO_CHAR (SYSDATE, 'HH24:MI') NOT BETWEEN '08:00' AND '18:00'
OR TO_CHAR (SYSDATE, 'DY') IN ('SAT', 'SUN') THEN
RAISE_APPLICATION_ERROR (-20205,
'You may only make changes during normal office hours'); 
END IF;
END secure_dml;

An additional benefit of this role is that it now gives us access to the dynamic performance views which in turn allows us to do some performance investigations.
For example, we can now run the Session Monitor in SQLDeveloper.

Conclusion

As we’ve demonstrated, the definition of Read Only access to an application running on an Oracle database is usually dependent on the features being used in that application.
Hopefully our imaginary auditor now has some understanding of this and won’t get their SOX in a twist when they find out that the developers providing Level 3 support have these privileges.


report

Recommend

About Joyk


Aggregate valuable and interesting links.
Joyk means Joy of geeK