FGAC (Fine grained access control) and ColdFusion

Posted on 2009-05-06
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?
Question by:michael1174
  • 2
  • 2
LVL 40

Expert Comment

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.

-- FGA example // mrjoltcola

-- Use the EMP schema (SCOTT/TIGER) to demonstrate fine-grained auditing

-- Create SCOTT schema if you haven't yet


-- 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


   dbms_fga.drop_policy (





   dbms_fga.add_policy (




      audit_condition => NULL,  -- TRUE

      audit_column    => 'SAL',

      statement_types => 'SELECT,INSERT,UPDATE,DELETE'




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

LVL 36

Expert Comment

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 knows nothing about oracle user accounts

in the example above

update emp set sal = 30000 where ename = 'SCOTT';

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
LVL 40

Expert Comment

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.
LVL 36

Accepted Solution

SidFishes earned 500 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

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

fwiw, my acl system is table based

(user, pwd, 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

insert into foo (v1, v2) values(#form.v1#,#form.v2#)
<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">

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 article on my blog

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

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

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…
This article provides a case study on how our local youth baseball league deployed a new website, including the platform selection, implementation and benefits to the league.
The purpose of this video is to demonstrate how to update a WordPress Site’s version. WordPress releases new versions of its software frequently and it is important to update frequently in order to keep your site secure, and to get new WordPress…
The purpose of this video is to demonstrate how to integrate Mailchimp with WordPress, by placing a Mailchimp signup form on a WordPress Page or Post. This will be demonstrated using a Windows 8 PC. Mailchimp will be used. Log into your Mailchi…

932 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

9 Experts available now in Live!

Get 1:1 Help Now