Solved

FGAC (Fine grained access control) and ColdFusion

Posted on 2009-05-06
4
597 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
  • 2
  • 2
4 Comments
 
LVL 40

Expert Comment

by:mrjoltcola
Comment Utility
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
Comment Utility
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
Comment Utility
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 500 total points
Comment Utility
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

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.
The purpose of this video is to demonstrate how to prevent comment spam on a WordPress Website. This will be demonstrated using a Windows 8 PC. Plugin Akismet will be used. Go to your WordPress login page. This will look like the following: myw…

744 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

Need Help in Real-Time?

Connect with top rated Experts

8 Experts available now in Live!

Get 1:1 Help Now