FGAC (Fine grained access control) and ColdFusion

Can someone tell me how to go about implementing FGAC in a web environment that uses ColdFusion, and oracle 10g?
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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

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

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
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.
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 http://sidfishes.wordpress.com/category/main-courses/coldfusion/ article on my blog

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Web Development Software

From novice to tech pro — start learning today.