Tim


>Why should you *what* ? Implement security in your database ? It isn't a
>matter of *should*, it's a matter of *can* if you need to. Just because you
>don't need it now does not necessarily mean that you never will, or that no
>other EDB customer will.

I'm coming at this from a totally selfish viewpoint I don't care about other ElevateSoft customers <vbg>. I'm just trying to find out what it will do for ME NOW.

<<How else would you handle security for ODBC or .NET data provider access ?>>

Encryption, engine signature changing the administrative user/password and not having any other users?

Roy Lambert

Bill


As I said in my post to Tim if concerned (I'm fortunate my stuff isn't that sensitive) I'd prefer to lock it down so that another app or software couldn't get at it at all.

However, are you saying that ALL you would provide is the database level stuff or do you also have a more granular approach as well and if so how do you manage/integrate the two?


Roy Lambert

A couple of things:

First, my application does not have *every* report an end-user may
want/eventually need.  If the only way to the data is through my
program and my client needs a report right now, how are we to get them
that report if I have encrypted the data and bound access to the data
to my application?  If I move security to the database then there is
no need to encrypt the data and my users can use tools to run SQL
statements (maybe they need to call me to know what SQL statement to
enter) against the data and get the reports they need right now.   So,
witht he data unencrypted, how do we protect the data from the same
tool that allowed them to enter a SELECT statement from say entering a
DELETE statement?  

Also, while you may encrypt your data and think it is secure, how
would you stop Joe Blow from using a SQL tool and deleting every row
of data in your database?  Without database level securities, you
cannot stop them.

Next, how granular are you talking?  I am not sure the answer will
matter since *any* security you need on the database could and IMHO
should be imposed at the database level.  This saves me time and money
too.  How?  Because I don't have to implement the securities in every
tool that I write for the database.  I generally don't include
everything in a single application.  For example, if an index is
corrupt I need to rebuild it.  I do this from a separate utility
application and I don't want to have to deal with all the security
issues concerning the database in that utliity as well as in the main
application - it's bloat.  

I do have "application" specific securities built into my
applications.  For example, on an hospital application, not all users
are allowed to perform a "purge" records command.  In this instance, a
purge is really just moving medical records from the shelves in the
medical record department to boxes for long term storage.  Nothing is
deleted and only a locater column is updated.  Yes, there is a
database level security on this action that prevents that column from
being manipulated by users without the appropriate rights, but my
application disabled the entire command option so they cannot even get
to that part of the app.  Still - database security is implemented at
the database level.



>Bill
>
>
>As I said in my post to Tim if concerned (I'm fortunate my stuff isn't that sensitive) I'd prefer to lock it down so that another app or software couldn't get at it at all.
>
>However, are you saying that ALL you would provide is the database level stuff or do you also have a more granular approach as well and if so how do you manage/integrate the two?
>
>
>Roy Lambert

Bill


Thanks for the detailed reply. My version of granularity could well include the following:

1. add new companies
2. alter the name of an existing company
3. add new contacts
4. alter the name of an existing contact
5. send out mailshots
6. send out eshots
7. send out emails in someone elses name
8. create a project with more than x records
9. run report x

In the app I'm converting for TMS and ElevateDB there are about 100 some of which refer to a single table, some to several and some to none. Some of the most sensitive (eg send eshots) only require read access.

From what you and Tim are saying it looks as though a two tier approach is going to be required Tier 1 database, Tier 2 application.

Roy Lambert

It all depends on the sensitivity of your data.  There are some
databases that need little database level securities.  You were asking
why we use Roles.  We use them to help implement database securities.

In your list, 1 through 4 could be handled completely by the database.
If the user (either directly or through Role inheritence) doesn't have
the right to insert a row, they cannot add a new company or contact.
The same goes for altering the row - they need rights.  The
application level would just prevent then access to a screen/dialog so
as to not frustrate them when they keep trying to add or edit without
the proper rights.

Since I don't know what's involved in 5 - 8 it is hard to say for sure
but this looks like an application specific job versus a database
issued.  I assume you are accessing the database to perform some sort
of merge and if that is the case, nothing is changing in the database
so as long as the user has Select rights on the tables/views they
should be OK.

Pretty much the same thing applies to number 9, since the data is
being read and not likely to change due to a report, then simple read
Select rights are all the users need.

5 - 9 would probably just need application securities - disable the
commands if the user doesn't have the rights to them.

>Bill
>
>
>Thanks for the detailed reply. My version of granularity could well include the following:
>
>1. add new companies
>2. alter the name of an existing company
>3. add new contacts
>4. alter the name of an existing contact
>5. send out mailshots
>6. send out eshots
>7. send out emails in someone elses name
>8. create a project with more than x records
>9. run report x
>
>In the app I'm converting for TMS and ElevateDB there are about 100 some of which refer to a single table, some to several and some to none. Some of the most sensitive (eg send eshots) only require read access.
>
>From what you and Tim are saying it looks as though a two tier approach is going to be required Tier 1 database, Tier 2 application.
>
>Roy Lambert

Bill


>In your list, 1 through 4 could be handled completely by the database.

Not with ElevateDB in its current state. 2 & 4 require Column level security (its only the company/contact name they can't alter not the rest of the data) not database or table.

>Since I don't know what's involved in 5 - 8

5 & 6 could be create permission

7 is altering data, possibly in a new record before its posted so has to be application

8 is the number of records in a table, will require read access to 3 tables, write to 1 BUT there are several ways to add records to a project and if the limit is say 100 then in none of those ways is the user allowed to add records to a project so that the number is 101 or more.

9 A user may be allowed to run a report from a table, but not another report eg list the companies invoices have been sent to but not list the invoice values.

Its usually possible to find ways to use things if you want. What concerns me is how messy this will become if I try to implement a duplex security system. I'm also not certain I'll need the database layer. I can well understand in some environments and with some system it would be an essential especially where access to the data is more important than how the data is accessed.

Roy Lambert

Roy,

<< Not with ElevateDB in its current state. 2 & 4 require Column level
security (its only the company/contact name they can't alter not the rest of
the data) not database or table. >>

A little trick:

Just define an updateable (can generate a sensitive result set) view that
selects the columns that they can alter as-is, and selects the company and
contact name using something like:

SUBSTRING(Company FROM 1 FOR LENGTH(Company)) AS Company

and that will make the Company column, etc. read-only.  Then, only give the
user(s) or role(s) rights to insert, update, etc. into this view, and not
the base table.  It works very nicely without needing column-level
privileges.

--
Tim Young
Elevate Software
www.elevatesoft.com

Tim

Intriguing trick, and one I'll keep an eye on in case I find a use for it. I'll give it a miss for now until I figure out how many of these will be needed and the impact on UI design.

Roy Lambert