application login , but user security

Hi everybody...please don't flame me for my ignorance.
We are in the process of migrating from mainframe application using
vsam files to MICROFOCUS and SQL server
Here is the senario we are at now :
In mainframe, the application might be able to read and write to the
file, but the write access is controled by the RACF (security software
like active directory). In other words, even though USER A can execute
the application, he cannot write to it, because the security software
doesn't allow USER A to write to the file.
In the new environment, our online application are replaced by a
product called MICROFOCUS -ES_MTO. ES-MTO connects to sqlserver via an
application userid (lets say APPL1). USERA logins in to the ES-MTO
using login id USERA, but then ES-MTO connects to sqlserver using
APPL1. APPL1 has read/write authority on the tables. USERA should be
able to execute the application, so he can read the table, but
shouldn't be able to write to it.
The application is however a read/write application.
I hope I was clear enough on my sceneraio.
What I am hoping to find out is, how can I still use sqlserver to check
permission using the real user login id , when the application uses the
application userid to connect ?
Am I making sense ?
Any help or input is greatly appreciated .
ThanksSQL Server security context is determined by the login used to connect to
SQL Server or an application role enabled after the connection is made. If
ES_MTO uses a single login, you won't be able to implement a SQL Server
security model that allows you to control access based on an individual
user's identity unless your application code can conditionally enable an
application role. I know nothing about ES_MTO so I can't comment on whether
or not that approach is feasible. You can read about application roles in
the SQL Server Books Online
Hope this helps.
Dan Guzman
SQL Server MVP
"sql rookie" <anytasks@.gmail.com> wrote in message
news:1114703232.008696.232480@.g14g2000cwa.googlegroups.com...
> Hi everybody...please don't flame me for my ignorance.
> We are in the process of migrating from mainframe application using
> vsam files to MICROFOCUS and SQL server
> Here is the senario we are at now :
> In mainframe, the application might be able to read and write to the
> file, but the write access is controled by the RACF (security software
> like active directory). In other words, even though USER A can execute
> the application, he cannot write to it, because the security software
> doesn't allow USER A to write to the file.
> In the new environment, our online application are replaced by a
> product called MICROFOCUS -ES_MTO. ES-MTO connects to sqlserver via an
> application userid (lets say APPL1). USERA logins in to the ES-MTO
> using login id USERA, but then ES-MTO connects to sqlserver using
> APPL1. APPL1 has read/write authority on the tables. USERA should be
> able to execute the application, so he can read the table, but
> shouldn't be able to write to it.
> The application is however a read/write application.
> I hope I was clear enough on my sceneraio.
> What I am hoping to find out is, how can I still use sqlserver to check
> permission using the real user login id , when the application uses the
> application userid to connect ?
> Am I making sense ?
> Any help or input is greatly appreciated .
> Thanks
>|||sql rookie wrote:
What I am hoping to find out is, how can I still use sqlserver to check
permission using the real user login id , when the application uses the
application userid to connect ?
Am I making sense ?
My response:
I am unsure what you mean by using 'sqlserver to check permission using the
real user login id'?
If you setup the security on the MicroFocus environment using SQLServer
security (uid and password). This userid can be granted R/W access to the
tables while the real user id will not be granted them. You can then contro
l
access to writing/updating the tables via the application. If you want/need
to have the user be able to read the information outside of the application
(Access linked tables, Excel Queries, etc) you can grant the 'real user id'
read access to the tables/views.
Does this cover your question'
Mike
--
Mike Mattix
CP Kelco, Inc
Okmulgee, OK
"sql rookie" wrote:

> Hi everybody...please don't flame me for my ignorance.
> We are in the process of migrating from mainframe application using
> vsam files to MICROFOCUS and SQL server
> Here is the senario we are at now :
> In mainframe, the application might be able to read and write to the
> file, but the write access is controled by the RACF (security software
> like active directory). In other words, even though USER A can execute
> the application, he cannot write to it, because the security software
> doesn't allow USER A to write to the file.
> In the new environment, our online application are replaced by a
> product called MICROFOCUS -ES_MTO. ES-MTO connects to sqlserver via an
> application userid (lets say APPL1). USERA logins in to the ES-MTO
> using login id USERA, but then ES-MTO connects to sqlserver using
> APPL1. APPL1 has read/write authority on the tables. USERA should be
> able to execute the application, so he can read the table, but
> shouldn't be able to write to it.
> The application is however a read/write application.
> I hope I was clear enough on my sceneraio.
> What I am hoping to find out is, how can I still use sqlserver to check
> permission using the real user login id , when the application uses the
> application userid to connect ?
> Am I making sense ?
> Any help or input is greatly appreciated .
> Thanks
>