Re: SQL Security & User-Level Access

From: Ian O'Betz (webmaster_at_(NoSpam)premier-ed.com)
Date: 03/04/05 

Date: Fri, 4 Mar 2005 12:42:55 -0700

Because the users are going to be accessing the SQL Server through an .ade
file using runtime, I am forced to use SQL Server Authentication (If I
understand everything correctly). I don't necessarily need to restrict
access to specific columns of data, but I am going to need to break down
security access levels.

I'd like the users to be able to login to the SQL Server database using
their username and password in the Members table. And based on roles, their
access level is determined. I'm not sure how difficult that is going to be,
but it would work well in the application. 

--
Ian O'Betz
Clear Results
www.clearresults.net
"Keith Kratochvil" <sqlguy.back2u@comcast.net> wrote in message
news:OKP$XYOIFHA.2564@tk2msftngp13.phx.gbl...
> It sounds like you have two security related items to consider.  First of
> all, SQL Server security is the overall security mechanism that is used
> which allows a user (login) to do something.  If they don't have rights to
> perform an action they don't have rights.
>
> Secondly, it sounds like you have user (member) level security as well.
> Specific members can do some things (like create, read, update, or delete
> data) and others can only read specific data.
>
> How are you going to handle the SQL Server authentication?  SQL Server
> authentication or integrated security (based on the network user)?  You
> could do either.  Depending on which option you choose you could solve the
> "who can do what" and "who can see what" problems in different ways.  You
> could handle the entire security issue within your application by coding
the
> business logic into the MS Access gui.  You could also allow or deny
> specific things to each SQL Server login/user.
>
> It is possible to enforce security down to the column level if you are
> allowing direct table access.  If you use stored procedures to retrieve
data
> you could only retrieve the data that the user is authorized to see.  You
> could also return all rows and columns to the application gui but only
show
> the specific data that the user is authorized to see.  You would use a
> different solution for each option.
>
> --
> Keith
>
>
> "Ian O'Betz" <webmaster@(NoSpam)premier-ed.com> wrote in message
> news:OOY1JFOIFHA.156@TK2MSFTNGP10.phx.gbl...
> > I've been asked to create an online database for my organization with
> Access
> > as the front end. I'm pretty good with Access and moderate with SQL. In
> the
> > database, I was told that different users need to have access to certain
> > tables and columsn in those tables. Some users need to be able to create
> new
> > users to have access. So my admin user needs to grant "grant" permission
> to
> > another user.
> >
> > The users, however, are stored in a table in the database called
Members.
> > Certain members in the Members table need to have access to the
database.
> >
> > My question(s): Where is a good place to start when designing something
> like
> > this? Is there some kind of add-in I can import and use or is there some
> > other direction I should be headed? Any direction is much appreciated.
> >
> > --
> > Ian O'Betz
> > Clear Results
> > www.clearresults.net
> >
> >
> >
>

Relevant Pages

  • Re: System Administrator Implied Permissions
    ... > sa login, it assigns it the System Administrator fixed ... > Now, given this, why does SQL Server ... in each database is always a member of the public and db_owner roles. ... Other sysadmin role members have the exact same ...
    (microsoft.public.sqlserver.security)
  • Re: Cryptography in SQL Server 2000
    ... SQL Server 2000 Out-of-the-Box database security solution. ... database protection and privacy software is an industry-proven ...
    (microsoft.public.sqlserver.security)
  • Re: SQL or Access DB
    ... i am aware of the security modell of SQL server (we do use SQL server ... an end user,,, there is not reall alternative as a worgroup Access database ... MSDE and SQL express are all free ...
    (microsoft.public.dotnet.languages.vb)
  • Re: Cryptography in SQL Server 2000
    ... A company is vulnerable when its security ... > database encryption solution with protected key-management software ... > tested by the SQL Server Test Lab. ...
    (microsoft.public.sqlserver.security)
  • Re: Trusted connections??
    ... implement role or user based security at the SQL Server. ... If the ASP.Net app controls what the user can request of the database then I ... I implement user authentication at the application and the application ...
    (microsoft.public.dotnet.framework.aspnet)