Database authentication using the current user

[synapsevampire]

CE 9
Oracle 8.X database (native connectivity)

I employ a single sign on using NT Authentication now by modifying the default in CSP.

What I would like to do is to be able to pass through this login/pw to the database from within CE so that the user never experiences a login prompt from within CE.

Has anyone done this?

I understand the URL methods, but I want standard eportfolio functionality, yet the database should use the login from the users machine, as I currently do for loggin into CE.

-k

 

[Turkbear]

Hi,
If you mean the user/pass for database used for the report, the username/password for it can be set when the report is published..It does not need to be the same as the user running the report ( unless there is some compelling reason that is needed)..

 

[synapsevampire]

I do mean the user/pw, but each users name/pw, not a generic.

We have security in the database which I wish to observe.

-k

 

[mdwyer]

Does this need to be on a report-by-report basis, are you essentially wanting to validate the user against the database in addition to NT and CE?

I'm using a piece of code to logon to an ODBC connection to an Oracle 9 database. This could be incorporated into a CE logon validation. I have not enabled an exception handler, so I'm not certain what error you would get on an invalid user, but I assume a try...catch construct will work.

Also, you will need to capture the user's password. I figure you know better than I how to get that from the NT authentication. If it was Enterprise authentication I'd capture it from the password field.

I picked up the technique used here from

http://listserv.sap.com/pipermail/sapdb.general/2002-November/010006.html.

The content of that page was a mailing list post, which may still be available.

The code snippet excludes the query routine because you would presumably only be validating the logon.

// Establish a database connection
var connSimple = Server.CreateObject("ADODB.Connection");
try{??
  connSimple.Open("DSN=LCWQ", "Scott", "Tiger"); // (ODBC Database, User, Password)
}catch ??
// Close the DB connection. Release memory
connSimple.Close;
connSimple = "";

 

[Turkbear]

Hi Synapse..
I think it may not be possible..The connection to Oracle
( Native, ODBC, whatever) is not configurable at 'run-time' unless you use custom code to establish the connection and 'bind' it to the report. By using code to set it, you may be able to find someplace in the EPortfolio pages to customise this, but I can't find one offhand. Here is some code from some V10 samples that seems to allow for the setting of a new user/password for given report IDs ( passed from some other place)..Don't know how adaptable it is for your situation..

<%
try
{
	var iStore = GetInfoStore();

	var strIDs = 0; // nothing has id 0

	var numIds = Request.Form.Item("ids").Count;
	
	if (numIds > 0)
	{
	    strIDs = Request.Form.Item("ids").Item(1);
	    if (numIds > 1)
	    {
	        for (var i=2; i <= numIds; i++)
	            strIDs = strIDs + ", " + Request.Form.Item("ids").Item(i);
	    }
	}            
	    
	str = "SELECT TOP " + numIds + " SI_ID, SI_PARENTID, SI_NAME, SI_PROCESSINFO.SI_DBNEEDLOGON, SI_PROCESSINFO.SI_LOGON_INFO FROM CI_INFOOBJECTS WHERE SI_INSTANCE=0 AND ";
	str = str + "SI_ID IN (" + strIDs + ") ORDER BY SI_NAME";

    try
    {
	    var rps = iStore.Query ( str );
	    var nReports = rps.Count;
	}
	catch(e)
	{
	    WriteErrorRetrieve(e.description);
	}        


	// SET THE LOGON INFO

	var strServer = Request.Form.Item("server");
	var strUser = Request.Form.Item("user");
	var strPwd = Request.Form.Item("pwd");

	if (nReports == Request.Form.Item("ids").Count)
	{
		nReports = rps.Count;
		for (i = 1; i <= nReports; i++)
		{
		    try
		    {
			    var obj = rps.Item(i);
			    var rpt = obj.PluginInterface("");
			    var logons = rpt.ReportLogons;
			    var nLogons = logons.Count;
			}
			catch(e)
			{
			    WriteErrorRetrieve(e.description);
			}        
			for (j = 1; j <= nLogons; j++)
			{
				var logon = logons.Item(j);
				if (logon.ServerName == strServer)
				{
					logon.UserName = strUser;
					logon.Password = strPwd;
				}
			}
		}

		var exCaught = false;
		try
		{
			iStore.Commit(rps);
		}
		catch(e)
		{
			exCaught = true;
			Response.Write("<span class='clsError'>"+L_ERROR+"</span><br>");
			Response.Write("<span class='clsErrReason'>" + e.description + "</span><P>");
			Response.Write("<a href='javascript:history.back()'>" + L_GO_BACK + "</A><P>");
			Response.Write("<a href='default.csp?framework=" + Request.QueryString.Item("framework") + "'>"+L_RETURN+"</A>");
		}

		if (!exCaught)
			Response.Redirect("default.csp?framework=" + Request.QueryString.Item("framework") + "&id=" + Request.Form.Item("parentid"));
			Response.End();
	}
	else
	{
		Response.Write("<span class='clsError'>"+L_ERROR+"</span><br>");
		Response.Write("<span class='clsErrReason'>" + L_FAILED_TO_RETRIEVE_REPORTS + "</span><P>");
		Response.Write("<a href='default.csp?framework=" + Request.QueryString.Item("framework") + "&id=" + Request.Form.Item("parentid") + "'>" + L_GO_BACK + "</A><P>");
		Response.Write("<a href='default.csp?framework=" + Request.QueryString.Item("framework") + "'>"+L_RETURN+"</A>");
	}
}
catch(e)
{
    WriteErrorGeneral(e.description);
}    	
%>

Maybe you can pass the Username/password ( from CMS ) to it..

 

[synapsevampire]

I suspect that a major rewqrite of eportfolio is in order to accomplish what I seek.

Thanks for the thoughts, and if you think of anything else, please let me know.

-k

 

[mdwyer]

Turkbear, you said the DB connection is not configurable at run-time, but isn't Synapse looking to establish the credentials at schedule-time? K, can you track the variables dbuser and dbpassword in schedule.csp and determine where to set these values in your code? The user and pw entry fields would also presumably be changed from type=text to type=hidden.

It doesn't sound like a major rewrite, unless I'm missing something.

- Mike

 

[synapsevampire]

Mike: I'm looking to use the credentials at View or Schedule time, basically anything in emportfolio would homor it.

-k

 

[Turkbear]

Hi,
One of the reasons ( aside from an ugly look-and-feel, IMHO)
we do not use EPortfolio is becaue it limits what we can do..
The nested Includes make modifying it a real chore..

We have developed a standard set of files for any new reporting application that we place in a web directory for the report developers to use( we create a new directory structure for each new set of reports).
They are mostly universal, regardless of the reports, but the developer does have to edit 2 of them to handle the drop-down lists of values the user is presented for each parameter in the report being run.
Other than that bit of coding ( and we have standardized many of our Parameter names and the functions used to produce the drop-down lists for them) the new set of reports can be ready for web access within an hour or so after their design completion and testing.( It really helps that all our enterprise-level reports use Oracle)

Once we got these standard pages developed ( lots of trial and error ) we were able to establish a standard look-and-feel for all of our enterprise reporting so users of any one of them will know how to use any other ones..

I do not know of a way to share these ( if desired) ..attachments are not part of this site and I really do not have time to send out to individuals..
Any ideas?

 

[synapsevampire]

As with Dave's generous offer to share his table list from reports utility, I suggest setting up a Yahoo Briefcase.

I think that it's 20MB, and free:

http://briefcase.yahoo.com/

And you can specificly invite users if you so desire.

I'd be very interested in looking at this code.

-k

 

[synapsevampire]

Just so you understand what I'm doing here, we have the Lawson security tables exported into a table, and then I join that table to all queries to honor the security model, yet not suffer the punishingly slow OLE DB Driver included.

Then those queries that require Lawson security could use something like the following theory:

select * from paemployee, myview where paemployee.department = myview.sec_locati

myview code:
select ltusersec.SEC_LOCATI
--(this is the department)
from lawson.ltusersec, lawson.listusermaptable
where
ltusersec.USERNAME = listusermaptable.LawsonID
-- (ltusersec.USERNAME is the LawsonID)
and
userlogin = (select user from dual)
-- This gets the current user logged into the database from Oracle

Kind of primitive, but it saves a LOT of time.

-k

 

[KingfisherINC]

I can understand how you would get the username but I can't imagin how you'd get the password from an NT SSO?

Kingfisher [CECP]

 

[Turkbear]

Hi,
Thanks for the info Synapse., I'll look into doing that in the next few days...I'll post when, and if, I manage to get it done..