Dynamic DSN

179 views
Skip to first unread message

Sid Wing

unread,
Apr 9, 2015, 3:14:49 PM4/9/15
to lu...@googlegroups.com
I am looking for a way to setup a DSN for an application to use - but I need to assign the information (database type, db name, credentials, port, etc) from an external source (info from another registry).  I can pull in and parse the info from the other registry - I just need to know how (if there is a way) to create a DSN from inside my application code (like in the Application.cfc's onApplicationStart method).

Seth Johnson

unread,
Apr 10, 2015, 1:00:54 AM4/10/15
to lu...@googlegroups.com
Hi Sid,

Mark Drew blogged about that feature in Railo.
http://blog.getrailo.com/post.cfm/railo-4-1-explicit-datasources-in-application-cfc

If you want to change the DSN on the fly, you should be able to do something like this:

<cfset var connstring = "jdbc:postgresql://#getKeys.link#:5432/#getKeys.dbname#?ssl=true&sslfactory=org.postgresql.ssl.NonValidatingFactory">
  
  <cfadmin action="updateDatasource"
   type="web"
   password="xxxxxx"
   classname="org.postgresql.Driver"
   dsn="#connstring#"
name="mydsn"
newname="mydsn"
   port="5432"
   dbusername="#decrypted_username#"
   dbpassword="#decrypted_password#"
allowed_select="true"
   allowed_insert="false"
   allowed_update="false"
   allowed_delete="false"
   allowed_alter="false"
   allowed_drop="false"
   allowed_revoke="false"
   allowed_create="false"
   allowed_grant="false">

Seth

On Thu, Apr 9, 2015 at 3:14 PM, Sid Wing <sid....@gmail.com> wrote:
I am looking for a way to setup a DSN for an application to use - but I need to assign the information (database type, db name, credentials, port, etc) from an external source (info from another registry).  I can pull in and parse the info from the other registry - I just need to know how (if there is a way) to create a DSN from inside my application code (like in the Application.cfc's onApplicationStart method).

--
You received this message because you are subscribed to the Google Groups "Lucee" group.
To unsubscribe from this group and stop receiving emails from it, send an email to lucee+un...@googlegroups.com.
To post to this group, send email to lu...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/lucee/b4ae5e0f-6256-4b39-b403-34466f1ae0f2%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

Chris Blackwell

unread,
Apr 10, 2015, 5:52:15 AM4/10/15
to lu...@googlegroups.com
You can set the datasource directly in your Application.cfc

// Application.cfc
component {

  this.name = "myapp"
  this.datasources = {
    "mydsn":  {
"class": "org.gjt.mm.mysql.Driver"
,"connectionString": "jdbc:mysql://127.0.0.1:3306/test"
,"username": "user"
,"password": "password"
    }

    //  add as many as you like here
  }

  // set the default datasource used for queries or orm
  this.datasource = "mydsn";

}

Dan Kraus

unread,
Apr 10, 2015, 10:06:05 AM4/10/15
to lu...@googlegroups.com
That is my preferred method too. You can then wrap all that up in environment detection and your app becomes more portable so you can deploy and not fiddle around with Admin settings first before it starts working.

Sid Wing

unread,
Apr 10, 2015, 10:37:54 AM4/10/15
to lu...@googlegroups.com
What would the classname value be for an Oracle DB? org.Oracle.Driver?  I'm not finding a place to find a list of classname values for that CFAdmin tag...

--
You received this message because you are subscribed to a topic in the Google Groups "Lucee" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/lucee/XeOQJV8kSSA/unsubscribe.
To unsubscribe from this group and all its topics, send an email to lucee+un...@googlegroups.com.

To post to this group, send email to lu...@googlegroups.com.

For more options, visit https://groups.google.com/d/optout.



--
Sid Wing
"We are dreamers, shapers, singers, and makers. We study the mysteries of laser and circuit, crystal and scanner, holographic demons and invocations of equations. These are the tools we employ, and we know many things." - Elric

Chris Blackwell

unread,
Apr 10, 2015, 10:47:56 AM4/10/15
to lu...@googlegroups.com
you probably want

classname="oracle.jdbc.OracleDriver"
dsn="jdbc:oracle:thin:<user>/<password>@<database>"

I would also caution against setting insert/update/delete/etc = false when you create the datasource, for two reasons.
1) this places an overhead on Lucee, that it must parse every sql statement and try and determine if it is allowed, this could be a real performance hit
2) Lucee's parsing is not fool-proof and you should not assume that these settings make your connection truly readonly.

implement the correct security settings on the database server, if you want readonly, create a readonly user.

Chris


Sid Wing

unread,
Apr 10, 2015, 11:19:02 AM4/10/15
to lu...@googlegroups.com
Chris - 

Can I omit all the "allowed_" attributes and simply let the DB Server handle permissions then?

That would give me something like this:

<cfset var connstring = "jdbc:oracle:thin:<user>/<password>@<database>">
<cfadmin action="updateDatasource"
  type="web"
          password="xxxxxx"
          classname="oracle.jdbc.OracleDriver"
 dsn="#connstring#"
name="mydsn"
newname="mydsn"
  port="5432"
  dbusername="#decrypted_username#"
  dbpassword="#decrypted_password#">


Would that be correct?


For more options, visit https://groups.google.com/d/optout.

Chris Blackwell

unread,
Apr 10, 2015, 11:25:33 AM4/10/15
to lu...@googlegroups.com
absolutely, yes you can
you need to replace <user>/<password>@<database>  with your values.
I'm not familiar with oracle, so i'm not sure about the exact format of the connection string.
theres an example that shows a connection string like "jdbc:oracle:thin:@prodHost:1521:ORCL";

Michael van Leest

unread,
Apr 11, 2015, 4:43:21 AM4/11/15
to lu...@googlegroups.com
@Sid These settings will be removed from the admin as database security should be handled by the DB server.
So yes, you can omit the allowed parameters or just set them all to true.



For more options, visit https://groups.google.com/d/optout.



--
Michael van Leest

Sid Wing

unread,
Apr 11, 2015, 8:59:05 AM4/11/15
to lu...@googlegroups.com
Michael - 

Thanks for the heads up!  I always thought that it was best to let the DB server handle as much of the weight as possible (as that is what they are designed for).
Reply all
Reply to author
Forward
0 new messages