Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

FGAC (Fine grained access control) and ColdFusion

Posted on 2009-05-06
4
Medium Priority
?
628 Views
Last Modified: 2013-12-16
Can someone tell me how to go about implementing FGAC in a web environment that uses ColdFusion, and oracle 10g?
0
Comment
Question by:michael1174
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
4 Comments
 
LVL 40

Expert Comment

by:mrjoltcola
ID: 24317534
Nothing to do with Coldfusion, all Oracle.

You configure Oracle with policies using the DBMS_FGA package, to select which tables or schemas or users to audit, and which statement types to audit, and to what detail.

Full sample below, based on the SCOTT/TIGER (EMP) schema.

And finally, a tried and true article.

http://www.oracle.com/technology/oramag/webcolumns/2003/techarticles/nanda_fga.html

-- FGA example // mrjoltcola
-- Use the EMP schema (SCOTT/TIGER) to demonstrate fine-grained auditing
-- Create SCOTT schema if you haven't yet
(@{ORACLE_HOME}/rdbms/admin/utlsampl.sql)
 
-- Check database to see if both AUDITING and/or FGA support is setup
 
show parameter audit
 
/*
Should see:
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
audit_file_dest                      string      C:\ORACLE\ADMIN\DEV\ADUMP
audit_sys_operations                 boolean     FALSE
audit_trail                          string      NONE
*/
 
-- We don't have to have standard auditing on, but to use standard auditing, but set AUDIT_TRAIL above
-- to db_extended so we get fine grained audit plus SQL_BIND, (bind vars) and SQL_TEXT auditing
--  which is important for this purpose. Then restart database
 
ALTER SYSTEM SET audit_trail=db_extended SCOPE=SPFILE;
 
shutdown immediate
startup open
 
-- To use FGA, we use FGA policies
-- FGA
-- Add a SALARY policy to the EMP table
 
-- Run as SYS or another user with execute on package dbms_fga
-- Default action is to audit SELECT but with 10g we can add DML actions by adding to statement_types parameter
-- Setup a policy to audit access of Employee Salaries
 
begin
   dbms_fga.drop_policy (
      object_schema=>'SCOTT',
      object_name=>'EMP',
      policy_name=>'EMP_ACCESS'
  );
 
   dbms_fga.add_policy (
      object_schema=>'SCOTT',
      object_name=>'EMP',
      policy_name=>'EMP_ACCESS',
      audit_condition => NULL,  -- TRUE
      audit_column    => 'SAL',
      statement_types => 'SELECT,INSERT,UPDATE,DELETE'
);
end;
/
 
connect scott/tiger
 
-- Scott selects salaries and then gives self a raise
 
select * from emp;
update emp set sal = 30000 where ename = 'SCOTT';
 
-- DBA connects and checks audit trail
connect / as sysdba
 
-- Find who ran what against EMP table
select timestamp, db_user, sql_text from dba_fga_audit_trail where object_name = 'EMP';
 
/* You will see...
 
01-APR-09          SCOTT              select * from emp
01-APR-09          SCOTT              update emp set sal = 30000 where ename = 'SCOTT'
*/

Open in new window

0
 
LVL 36

Expert Comment

by:SidFishes
ID: 24318315
actually it has everything to do with coldfusion if CF is accessing the db...

coldfusion accesses the db via jdbc and the user is the coldfusion service...it knows nothing about oracle user accounts

in the example above

<cfquery...>
update emp set sal = 30000 where ename = 'SCOTT';
</cfquery


all transactions in the audit log would point to the cfservice and give you no info about scott


you would need to build something in cf using either ldap or most usually a login/acl interface
0
 
LVL 40

Expert Comment

by:mrjoltcola
ID: 24318536
I did not mean to put anyone in Coldfusion zone on the defensive, I apparently cannot read and thought the OP said "FGA" (auditing) as I was rushing out to late lunch. This is an Oracle feature. I apologize if my comment seemed rude and also apologize that I answered the wrong question.

>>all transactions in the audit log would point to the cfservice and give you no info about scott

That depends on the context, but doesn't change either of our points, FGA is not an ACL mechanism, and I should not have submitted it as such. Even if he had asked about FGA, I now see the OP probably is discussing a "web" model, with virtual users (a USERS table as opposed to an Oracle user) which are all coming in through a common schema user / connection pool, so you would also be correct in that case, FGA would also not be the solution even *if* michael had asked for FGA instead of FGACL. :(

So, SidFishes is correct, you need something other than Oracle schema users.

We implement FGAC (or we call it FGACL) with a USERS table, and a Roles Based Authentication schema of approx 4 tables. ROLES -> PRIVILEGES -> RESOURCES. That can also be stored in LDAP. But since my experience is with doing this in JEE and .NET environments, I will now shutup and step out of this question as I have adequately stuck my foot in my mouth, sorry for cluttering the thread.

Good luck.
0
 
LVL 36

Accepted Solution

by:
SidFishes earned 2000 total points
ID: 24318945
no offense taken here

some easy things you can do for dealing with user rights/acl in CF

globally allow/disallow
 SELECT           Create           GRANT INSERT       DROP       REVOKE UPDATE       ALTER       Stored Procedures DELETE

cfadmin >datasources >datasource > advanced settings

in 99.9% of the time you should only have the following checked
 SELECT,INSERT, UPDATE, Stored Procedures DELETE

(a web user should never be allowed the other rights)

fwiw, my acl system is table based

tbluser
(user, pwd, acl)
tblmenu
(Menuid,MenuDesc,acl)  

menu options are presented based on acl level

then each page checks session.acl to prevent url manipulation to unauthorized pages

You can log all activity using the cflog. I tend to use cftry/catch blocks around all but select to log errors. You could also log all successful transactions as well as selects if you like

<cftry>
<cfquery...>
insert into foo (v1, v2) values(#form.v1#,#form.v2#)
</cfquery>
<cfset result="ok">
<cflog file="DBErrors" text="#result#:User|#session.user#:data|#form.v1#,#form.v2#">
<cfcatch type="any">
      <cfset result = "#cfcatch.type# - #cfcatch.message# - #cfcatch.detail#">
      <cflog file="DBErrors" text="#result#">
      <cfset result = "Error">
</cfcatch>
</cftry>


It's also a good idea to set up cf as a network user and specifically allow/restrict access to network resources. While this may not be necessary, one of the benefits is the ability to restrict db rights based on a network user (provided your db uses the equivelent of "trusted connection" in mssql)

If you're on a windows system, the steps for doing this are in the http://sidfishes.wordpress.com/category/main-courses/coldfusion/ article on my blog

that's just the start...acl systems can be quite complex
0

Featured Post

Looking for a new Web Host?

Lunarpages' assortment of hosting products and solutions ensure a perfect fit for anyone looking to get their vision or products to market. Our award winning customer support and 30-day money back guarantee show the pride we take in being the industry's premier MSP.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

CFGRID Custom Functionality Series -  Part 1 Hi Guys, I was once asked how it is possible to to add a hyperlink in the cfgrid and open the window to show the data. Now this is quite simple, I have to use the EXT JS library for this and I achiev…
Sometimes databases have MILLIONS of records and we need a way to quickly query that table to return the results me need. Sure you could use CFQUERY but it takes too long when there are millions of records. That is why SOLR was invented. Please …
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.
The purpose of this video is to demonstrate how to integrate Mailchimp with Facebook. This will be demonstrated using a Windows 8 PC. Mailchimp and Facebook will be used. Log into your Mailchimp account. : Click on your name. Go to Account Setti…

730 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question