Fetching Unix Folder Name and Timestamp in SQL/PLSQL [message #670122] |
Fri, 08 June 2018 14:20 |
|
nickz
Messages: 39 Registered: September 2013 Location: US
|
Member |
|
|
I have created a Directory in my Oracle DB Schema pointing to my Unix folder: /dev/product/jobs/PRODUCT_JOBS
Several files get created/updated within each product folder within the above directory on a daily basis through automated scheduled jobs.
I want to be able to fetch a list of all the folder names (product folders) within this Unix directory and their respective last modified timestamps in a TABLE (if PLSQL) or VIEW (if this can be done in a single SQL) in Oracle DB schema.
Folder structure in UNIX as seen with WinSCP
/dev/product/jobs/PRODUCT_JOBS/
Name Changed
P01_C011101 6/17/2018 11:50:56 AM
P01_C021201 6/17/2018 10:24:23 AM
P01_C021202 6/12/2018 10:54:15 PM
P02_C012101 6/13/2018 09:50:55 PM
P03_C013101 4/15/2018 02:22:09 AM
P03_C033301 5/18/2018 06:12:12 PM
P03_C043401 6/03/2018 06:23:33 AM
Output should be in a Oracle table/view as below
(FOLDER_NAME will be last four characters of actual folder name in UNIX)
FOLDER_NAME DATE_MODIFIED_TS
1101 6/17/2018 11:50:56 AM
1201 6/17/2018 10:24:23 AM
1202 6/12/2018 10:54:15 PM
2101 6/13/2018 09:50:55 PM
3101 4/15/2018 02:22:09 AM
3301 5/18/2018 06:12:12 PM
3401 6/03/2018 06:23:33 AM
Could you please help? Thanks.
|
|
|
|
|
|
Re: Fetching Unix Folder Name and Timestamp in SQL/PLSQL [message #670127 is a reply to message #670122] |
Sat, 09 June 2018 00:30 |
John Watson
Messages: 8937 Registered: January 2010 Location: Global Village
|
Senior Member |
|
|
The hacker's way:orclx>
orclx> conn / as sysdba
Connected.
orclx> var ns varchar2(1024)
orclx> var dirname varchar2(1024)
orclx> exec :dirname:='c:\tmp'
PL/SQL procedure successfully completed.
orclx> exec sys.dbms_backup_restore.searchfiles(:dirname,:ns)
PL/SQL procedure successfully completed.
orclx> select fname_krbmsft from x$krbmsft where rownum < 10;
FNAME_KRBMSFT
--------------------------------------------------------------------------------------------------------------
C:\tmp\12.2_new_type
C:\tmp\access_log
C:\tmp\access_log-20180401
C:\tmp\airline_ticket.pdf
C:\tmp\apex_logons.txt
C:\tmp\apex_verify_out.html
C:\tmp\aprod_client_page_views.txt
C:\tmp\apxpatch.log
C:\tmp\ASH16742AL.pdf
9 rows selected.
orclx>
|
|
|
|
|
|
|
|
Re: Fetching Unix Folder Name and Timestamp in SQL/PLSQL [message #670134 is a reply to message #670131] |
Sat, 09 June 2018 04:10 |
John Watson
Messages: 8937 Registered: January 2010 Location: Global Village
|
Senior Member |
|
|
Quote:1. In the example, the read-write Oracle directory and the executable Oracle directory can be the ssame right? As in my case, both can be PROD_JOBS_DIR which points to /dev/product/jobs/PRODUCT_JOBS You can do this, but it is a security risk. For example, it makes it possible for a user to write a shell script to the directory and then run it. It will run with the OS privileges of the Oracle owner, so your user can escalate his privileges horrifically.
|
|
|
Re: Fetching Unix Folder Name and Timestamp in SQL/PLSQL [message #670181 is a reply to message #670122] |
Thu, 14 June 2018 19:18 |
|
nickz
Messages: 39 Registered: September 2013 Location: US
|
Member |
|
|
Thank you @BlackSwan @Solomon @Michel @John for all your help and guidance. The links and your directions really helped me complete this.
Here is what I did. I guess it would be helpful in case someone else stumbles upon this post in future with same issue/query.
1. CREATE DIRECTORY
in Oracle DB and grant EXECUTE permissions to XYZ_USER schema to be able to run scripts in Unix folder (from SYS). My Unix Shell Script will reside within /dev/product/jobs
CREATE DIRECTORY DIR_PRODUCT_JOBS AS '/dev/product/jobs';
/
GRANT EXECUTE ON DIRECTORY DIR_PRODUCT_JOBS TO XYZ_USER
/
2. CREATE UNIX SHELL SCRIPT
to display folder name (not files) and last modified time from the "/dev/product/jobs/PRODUCT_JOBS" folder in UNIX.
I created a Unix shell script "dir_list.sh" which when called ( "$ ./dir_list.sh" ) searches within the target folder specified in the script and displays the folder names along with its associated timestamp as a comma-separated records. It utilizes Perl scripting since GNU commands like stat, --full-time, --time-style, etc. doesn't work in my HP-UX B.11.11 OS. This shell script will later be used for the Oracle external table to execute in it's preprocessor directive.
Found some help here: https://unix.stackexchange.com/questions/449766/get-list-of-directories-and-timestamp-in-a-specific-format-in-hp-ux
#! /usr/bin/ksh
# dir_list.sh : Generate a comma separated directory list with last modified timestamp
#
# Navigate to the Target Directory
cd /dev/product/jobs/PRODUCT_JOBS || exit
#
# Execute Perl script to format the output
/usr/bin/perl -MPOSIX -MFcntl -MFile::stat -le '
setlocale(LC_TIME, "C");
for (<*>) {
$s = lstat $_ or die "$_: $!\n";
print "$_," . uc(strftime("%d-%b-%Y %I:%M:%S %p", localtime $s->mtime))
if S_ISDIR($s->mode)
}'
#
exit 0
3. CREATE EXTERNAL TABLE using PREPROCESSOR
While logged in as XYZ_USER in Oracle DB, I created the below External table which will run the shell script and dump the output in this table. I have set both my location file and the script file to be the same, since location file is of no use in this scenario, but is a mandatory clause for External Preprocessor table, hence can't exclude it.
CREATE TABLE T_JOBS_DIR_LIST
(
job_folder VARCHAR2 ( 100 )
, last_modified_ts VARCHAR2 ( 50 )
)
ORGANIZATION EXTERNAL
(TYPE ORACLE_LOADER
DEFAULT DIRECTORY DIR_PRODUCT_JOBS
ACCESS PARAMETERS (
RECORDS DELIMITED BY NEWLINE
NOLOGFILE
PREPROCESSOR DIR_PRODUCT_JOBS : 'dir_list.sh'
FIELDS
TERMINATED BY ','
)
LOCATION ( 'dir_list.sh' ))
REJECT LIMIT UNLIMITED
/
4. QUERY THE TABLE
to display all the folder names (excluding files) and their associated timestamps within the "/dev/product/jobs/PRODUCT_JOBS" directory. I don't even have to do any kind of data cleaning since I already have the data in the format I need, thanks to the Perl script in my shell script code. Any time I query the table, it always retrieves the latest data.
SELECT * FROM XYZ_USER.T_JOBS_DIR_LIST
ORDER BY TO_DATE(last_modified_ts) DESC;
Please let me know if I have stated anything incorrectly.
Thanks again.
[Updated on: Thu, 14 June 2018 19:21] Report message to a moderator
|
|
|
|
Re: Fetching Unix Folder Name and Timestamp in SQL/PLSQL [message #670186 is a reply to message #670181] |
Fri, 15 June 2018 06:50 |
Solomon Yakobson
Messages: 3275 Registered: January 2010 Location: Connecticut, USA
|
Senior Member |
|
|
Serious security hole. Pre-processor shell script must be in separate directory. Otherwise user selecting from external table must be granted all read, write and execute on directory object. As a result nothing prevents user from writing malicious shell script to directory using UTL_FILE and creating external table with that malicious shell script as pre-processor. Select form external table spawns OS process on database server that runs under OS user oracle (as OS user Oracle software was installed, to be precise) giving client all the access OS user oracle has. By creating pre-processor in separate directory you provide client read/write on external table directory and execute on pre-processor directory. This way client can write shell script to external table directory but can't execute it.
But much better solution is completely separate directory access. Create database user DIRECTORY_ACCESS which will be only database user granted access to Oracle directory objects and will own all external tables. Then create table/pipelined functions selecting from external tables and grant execute on such functions to database users.
SY.
|
|
|
|
|
|
|
|
|
|
Re: Fetching Unix Folder Name and Timestamp in SQL/PLSQL [message #670242 is a reply to message #670240] |
Wed, 20 June 2018 00:47 |
|
nickz
Messages: 39 Registered: September 2013 Location: US
|
Member |
|
|
Thanks for the options BlackSwan. Actually, I liked the idea of External Table with pre-processor since it allows for an on-demand real-time data fetch which is what I exactly wanted. No fuss with scheduled jobs, etc. I can't have that functionality with a regular Heap table.
For the first option you mentioned, TWO QUESTIONS:
1. How can I change the ownership of a directory to PUBLIC ? Currently, SYS is the Owner.
2. If I change the ownership to PUBLIC, then what are the impacts? I guess anyone will be able to READ< WRITE, EXECUTE, DROP the directory, right?
Otherwise, I guess at this point, my only option is to:
> create a role
> grant SELECT privilege on view and ext table to role
> grant READ privilege on the pre-processor directory to the role (I have NOLOGFILE enabled in ext table, so I guess only READ privilege will do)
> assign this role to the users who need access to the view from then on for easy maintenance
The main schema where the external table and view resides is XYZ_USER, as in my example. Currently, XYZ_USER has all READ, WRITE, EXECUTE privileges on the directory DIR_PRODUCT_JOBS. A QUESTION here. Do you think WRITE privilege is required by XYZ_USER ? Or only READ and EXECUTE will do, considering the below is how my external table is defined (NOLOGFILE) ?
CREATE TABLE T_JOBS_DIR_LIST
(
job_folder VARCHAR2 ( 100 )
, last_modified_ts VARCHAR2 ( 50 )
)
ORGANIZATION EXTERNAL
(TYPE ORACLE_LOADER
DEFAULT DIRECTORY DIR_PRODUCT_JOBS
ACCESS PARAMETERS (
RECORDS DELIMITED BY NEWLINE
NOLOGFILE
PREPROCESSOR DIR_PRODUCT_JOBS : 'dir_list.sh'
FIELDS
TERMINATED BY ','
)
LOCATION ( 'dir_list.sh' ))
REJECT LIMIT UNLIMITED
/
|
|
|
|
Re: Fetching Unix Folder Name and Timestamp in SQL/PLSQL [message #670265 is a reply to message #670242] |
Wed, 20 June 2018 08:03 |
Solomon Yakobson
Messages: 3275 Registered: January 2010 Location: Connecticut, USA
|
Senior Member |
|
|
As Michel said, CREATE DIRECTORY always creates directory object in SYS schema and is granting READ, WRITE, EXECUTE on directory object to user issuing CREATE DIRECTORY. Anyway, as I already mentioned secure way would be having DIRECTORY_MASTER user that is the only user with READ, WRITE, EXECUTE on directory. Create external table in DIRECTORY_MASTER schema. Create pipelined function in DIRECTORY_MASTER schema. Then grant execute on pipelined function to desired users.
SY.
|
|
|
|
Re: Fetching Unix Folder Name and Timestamp in SQL/PLSQL [message #670273 is a reply to message #670266] |
Wed, 20 June 2018 13:08 |
Solomon Yakobson
Messages: 3275 Registered: January 2010 Location: Connecticut, USA
|
Senior Member |
|
|
Below is example to read trace file directory:
Step 1. Login as user DIRECTORY_MASTER.
Step 2. Create trace file directory:
column trace_file_dir new_value trace_file_dir
select value trace_file_dir
from v$diag_info
where name like 'Diag Trace'
/
drop directory trace_file_dir
/
create or replace
directory trace_file_dir as '&trace_file_dir'
/
Step 3. Create external table to list trace file directory with ls -ltr.
a) Create PREP directory:
drop directory prep
/
create or replace
directory prep as '/prep'
/
b) Create shell script /prep/ls_ltr:
#!/bin/ksh
/bin/ls -ltr $1
c) Create external table:
create table trace_file_dir_list(
line varchar2(4000)
)
organization external(
type oracle_loader
default directory trace_file_dir
access parameters(
records delimited by newline
skip 1
nobadfile nologfile
preprocessor prep:ls_ltr
fields rtrim(line char(1000))
)
location('.')
)
reject limit unlimited
/
Step 4. Create pipelined function:
create or replace
function trace_file_dir_list_fn
return sys.odcivarchar2list
pipelined
is
cursor v_cur
is
select *
from trace_file_dir_list;
begin
for v_rec in v_cur loop
pipe row(v_rec.line);
end loop;
end;
/
Step 5. Create view:
create or replace
view trace_file_dir_list_vw
as
select *
from table(trace_file_dir_list_fn)
/
Step 6. Grant SELECT on the view to desired user, e.g. USER1:
grant select on trace_file_dir_list_vw to user1
/
Step 6. Login as USER1 and issue:
SQL> select *
2 from directory_master.trace_file_dir_list_vw
3 where rownum <= 3
4 /
COLUMN_VALUE
--------------------------------------------------------------------------------------
-rw-rw-r-x 1 oracle dba 158728 Mar 26 2015 alert_bi2d4.log.12987_032615.bz2.gz
-rw-rw-r-- 1 oracle dba 50153 Apr 17 2015 alert_bi2d4.log.28327_041715.bz2.gz
-rw-rw-r-- 1 oracle dba 639527 Apr 18 2015 alert_bi2d4.log.31269_041815.bz2.gz
SQL>
SY.
[Updated on: Wed, 20 June 2018 18:39] Report message to a moderator
|
|
|
|