Governance, Risk, and Compliance

Change Control: PERL Directory and File Permission (statistics) recursively

2010-02-12T11:57:00.001-08:00

Change Control: PERL Directory and File Permission (statistics) recursively

*******************************************************************************************
Programming Language: Perl
Calling Method: ./filePermissions.pl <dir Offset> <depth> <dir> or <file>
Example-1-: ./filePermissions.pl "/tmp" "n" "dir"
where starting dir is "/tmp" for "n" (depth=all levels) and list the directories recursively
Example-2-: ./filePermissions.pl "/usr" "1" ""
where starting dir is "/usr" for "1" level of depth and lists all files and directories

Recommendations:
===================
Run this program periodically. Grab the output, version it, BASELINE it, and archive it.
Subsequent time, the output of this report for the same criteria must be compared with the BASELINE. Report any anomalies.

Fields Reported: ($dev,$ino,$mode,$nlink,$uid,$gid,$rdev,$size,$atime,$mtime,$ctime,$blksize,$blocks)
==========================================================================================================

dev:ID of device containing file. If this value and following has no meaning on the platform, it will contain undefined value.
ino: inode number.
mode: Unix mode for file.
nlink: Number of hard links.
uid:User ID of owner.
gid: Group ID of owner.
rdev: Device ID (if special file).
size:Total size, in bytes.
atime: Time of last access as DateTime object.
mtime:Time of last modification as DateTime object.
ctime: Time of last status change as DateTime object.
blksize: Block size for filesystem I/O.
blocks: Number of blocks allocated.

Custom:
==========
Feel free to modify to suite your requirements.
The MD5 checksum has been disabled from this script because of performance issues.
You may want to add, edit or remove field names that are being reported.

*********************************** Code Begins ****************************************

P=`for L in \`echo /usr/bin/perl /usr/local/perl5/bin/perl /usr/local/bin/perl /usr/local/bin/perl5 /bin/perl\`
do
if [ -f $L ]; then
V=\`$L -V | grep 'Summary' | awk '{ print \$4 }' \`
if [ "$V" = "perl5" ]; then
echo $L
exit
fi
fi
done`;$P -e '
use Getopt::Long;

my $verbose=0;
my @excludefiles;

$md5Found = 0;
#BEGIN{
# $md5Found = ;
# eval {require Digest::MD5;};
# if ($@) {
# $md5Found = 0;
# };
#}

$path = shift;
$depth = shift;
if ($depth eq "") {
$depth = "n";
}

$filter = shift;
if ($filter eq "") {
$filter = "all";
}

@excludefiles = split(",", $exclude);

#for my $path (@ARGV) {
$path =~ s{//}{/}; # remove multiple slashes
my $fullpath= makeFullPath($path, "", "");

if (-d $path) {
processDirectory($path, "", $md5Found, $depth);
} else {
print("$path not found\n");
exit(-1);
}
processFile($fullpath, $md5Found);
#}

exit(0);

sub processDirectory {
my ($root, $path, $md5Found, $currDepth)= @_;
$currDepth-- if ($currDepth ne "n");

my $files= getFilelist(makeFullPath($root, $path));

for my $file (@$files) {
next if ($file eq "." || $file eq "..");
my $fullpath= makeFullPath($root, $path, $file);
my $relative= makeFullPath($path, $file);

if (!isInIgnoreList($relative)) {
if ((-f $fullpath) && ($filter ne "dir")) {
processFile($fullpath, $md5Found);
}
elsif (-d $fullpath) {
processFile($fullpath, 0);
if (($currDepth eq "n") || ($currDepth > 0))
{
processDirectory($root, $relative, $md5Found, $currDepth);
}
}
}
}
}

sub makeFullPath {
my ($full, @parts)= @_;

$full ||="";
for my $path (@parts) {
next if (!defined $path);
$path =~ s{^/}{}; # remove leading slash
$full =~ s{/?$}{/$path}; # remove trailing slash, append path
}

return $full;
}

sub getFilelist {
my ($path)= @_;

die "$!: reading $path\n" unless opendir(DIR, $path);

my @files= readdir DIR;
closedir DIR;

return \@files;
}

sub processFile {
my ($filename, $md5Found)= @_;

$hex = -1;
if ($md5Found) {
open(FILE, $filename) or return;
binmode(FILE);
$hex = Digest::MD5->new->addfile(*FILE)->hexdigest;
}

($dev,$ino,$mode,$nlink,$uid,$gid,$rdev,$size,$atime,$mtime,$ctime,$blksize,$blocks) = stat($filename);

$uname = getpwuid($uid);
$gname = getgrgid($gid);

printf("\"$filename\",\"$dev\",\"$ino\",\"%04o\",\"$nlink\",\"$uid\",\"$gid\",\"$rdev\",\"$size\",\"%s\",\"%s\",\"%s\",\"$gname\",\"$uname\",\"$hex\"\n",
($mode & 07777),
get_real_time($atime),get_real_time($mtime),get_real_time($ctime));
}

sub isInIgnoreList {
my $fullfilename= shift;

for my $pattern (@excludefiles) {
if (matches($fullfilename, glob2pat("/$pattern"))) {
return 1;
}
}
return 0;
}

sub matches {
my ($filename, $pattern)= @_;

return ($filename =~ m{$pattern}i);
}

sub glob2pat {
my $globstr = shift;
my %patmap = (
"." => "\.",
"*" => ".*",
"?" => ".",
"[" => "[",
"]" => "]",
);
$globstr =~ s{(.)} { $patmap{$1} || "\Q$1" }ge;
return $globstr;
}

sub get_real_time ($) {
my($sec,$min,$hour,$mday,$mon,$year,$wday,$yday,$isdst) = gmtime $_[0];
$year += 1900;
$mon += 1;
return "$year\-$mon\-$mday $hour:$min:$sec";
}

sub get_user($) {
($username) = split(":", `getent passwd $_[0]`);
return($username);
}

sub get_group($) {
($groupname) = split(":", `getent group $_[0]`);
return($groupname);
}' "$1" "$2" "$3"

*********************************** Code Ends ****************************************

Oracle EBS: jdapi - API to read Oracle Forms

2010-02-12T11:07:00.000-08:00

Oracle EBS: jdapi - API to read Oracle Forms

The JDAPI from Oracle would allow us to build a complete change control around Oracle Forms by programatically editing FMB files.

If any customer requires this functionality, the capability can be achieve by JDAPI.

Reference: http://www.oracle.com/technology/products/forms/files/10gR2/1012jdapiDoc.zip
Reference valid as of 12-Feb-2010

Oracle sqlplus: Restrict TRUNCATE, CONNECT, INSERT, UPDATE and DELETE capabilities

2010-02-12T10:38:00.000-08:00

Oracle sqlplus: Restrict TRUNCATE, CONNECT, INSERT, UPDATE and DELETE capabilities

****************************** Code Begins **************************************************

REM
rem Script Description: This script is designed to restrict the TRUNCATE, CONNECT, INSERT, UPDATE and DELETE capabilities
rem of end-users from within SQL*Plus. It was implemented to perform weekly maintenance of
rem the product user profile table.
rem
rem Output file: prodprof.sql
rem
rem Prepared By: Oracle Resource Stop
rem
rem Usage Information: SQLPLUS SYS/pswd
rem @prodprofmaint.sql
rem

set pages 10000
set heading off
set lines 200
set feedback off

spool prodprof.sql

rem
rem The following adds any new users to the PRODUCT_USER_PROFILE table using the standard access restrictions.
rem

select 'insert into product_user_profile(product,userid,attribute,char_value) values ('||
'''SQL*Plus'','''||username||''',''INSERT'',''DISABLED'''||');',
'insert into product_user_profile(product,userid,attribute,char_value) values ('||
'''SQL*Plus'','''||username||''',''UPDATE'',''DISABLED'''||');',
'insert into product_user_profile(product,userid,attribute,char_value) values ('||
'''SQL*Plus'','''||username||''',''DELETE'',''DISABLED'''||');',
'insert into product_user_profile(product,userid,attribute,char_value) values ('||
'''SQL*Plus'','''||username||''',''CONNECT'',''DISABLED'''||');',
'insert into product_user_profile(product,userid,attribute,char_value) values ('||
'''SQL'','''||username||''',''TRUNCATE'',''DISABLED'''||');'
from dba_users a
where not exists
(select * from product_user_profile
where userid = a.username);
spool off

@prodprof

rem
rem The following removes and deleted users from the PRODUCT_USER_PROFILE table.
rem

delete from product_user_profile a
where not exists
(select * from dba_users
where username = a.userid);

commit;

set pages 1000
set heading on
set lines 80
set feedback on

****************************** Code Ends **************************************************

ORACLE EBS: SQL to get the profile option values and key columns at all levels.

2010-02-12T10:34:00.001-08:00

ORACLE EBS: SQL to get the profile option values and key columns at all levels.

/*
Description: This script will generate a list of all profile values in an Oracle Apps 11i env.
It lists the profiles by Level (Site,Application,Responsibility,User) with relevant fields
and provides the ability to search on any of the listed fields.

Running this SQL periodically and doing a different between the output provides the changes to profile option values..

Warning: This SQL runs for a long time and could generate about 170K rows when run against Large databases.

*/

SELECT DECODE(level_id,10001,'Site',10002,'Application',10003,'Responsibility',10004,'USER') "Profile_Level",

DECODE(level_id,10001,NULL,10002,fa.application_name,10003,fr.responsibility_name,10004,fu.user_name) "Non_Site_Description"
, fu.user_name
, fpov.user_profile_option_name
, fpov.profile_option_name
, fpova.profile_option_value
, fpova.creation_date
, fpova.last_update_date
, fpov.HIERARCHY_TYPE
, fpov.START_DATE_ACTIVE
, fpov.END_DATE_ACTIVE
, fpov.WRITE_ALLOWED_FLAG
, fpov.READ_ALLOWED_FLAG
, fpov.USER_CHANGEABLE_FLAG
, fpov.USER_VISIBLE_FLAG
, fpov.SITE_ENABLED_FLAG
, fpov.SITE_UPDATE_ALLOWED_FLAG
, fpov.APP_ENABLED_FLAG
, fpov.APP_UPDATE_ALLOWED_FLAG
, fpov.RESP_ENABLED_FLAG
, fpov.RESP_UPDATE_ALLOWED_FLAG
, fpov.USER_ENABLED_FLAG
, fpov.USER_UPDATE_ALLOWED_FLAG
, fpov.SERVER_ENABLED_FLAG
, fpov.SERVER_UPDATE_ALLOWED_FLAG
, fpov.ORG_ENABLED_FLAG
, fpov.ORG_UPDATE_ALLOWED_FLAG
FROM fnd_profile_options_vl fpov
, fnd_profile_option_values fpova
, fnd_application_tl fa -- table inclusion when looking at application joins
, fnd_responsibility_tl fr -- table inclusion when looking at responsibility joins
, fnd_user fu -- table inclusion when looking at user joins
, fnd_user fu2
WHERE fpov.application_id = fpova.application_id
AND fpov.profile_option_id = fpova.profile_option_id
AND fpov.start_date_active <= SYSDATE
AND NVL(fpov.end_date_active,SYSDATE) >= SYSDATE
AND (fpov.site_enabled_flag = 'Y' OR fpov.app_enabled_flag = 'Y' OR fpov.resp_enabled_flag = 'Y' OR fpov.user_enabled_flag = 'Y')
AND fpova.level_value = fa.application_id (+) -- join for application values
AND fpova.level_value = fr.responsibility_id (+) -- join for responsibility values
AND fpova.level_value = fu.user_id (+) -- join for user values
AND fpova.last_updated_by = fu2.user_id (+) -- join for update by user values
ORDER BY "User Profile Option Name", "Profile Level";