How to add security to data sources from SQL 2000

[mike2004]

How to add security to data sources from SQL 2000

I have been doing ColdFusion programming for over 5 years and now I hired an
assistant for myself. So far I didn?t have any security on my data sources but
now I need to add user rights to these so if my assistant make a mistake
nothing would happen to the tables. Currently I have about 3 data sources. I
did some research on creating users under Microsoft SQL 2000 and I did create a
user account under SQL but since my data sources are connecting via sa account,
this user still have full power on all the tables. Maybe there?s more than just
creating a user account under SQL and permitting read only access to the tables
under the permission tab! Or maybe I need to create new data sources with
different configurations or maybe use ColdFusion Sandbox!

This is where I am confused right now and don?t know what would be the way to
protect my assistant against damaging my tables!

I?m going to explain my situation with an example here:

Let say Mike is a Project Manager / Database Administrator and he?s the only
one in the network who needs to have full access to all the tables/data
sources. And Juta is the Programmer who should only have Read-Only access to
all of the tables with the exception of Update/Insert access on 1 or 2 tables.

I would really appreciate if someone can help me to figure what I need to do
to accomplish this.

Thanks,

Mike R

[paross1]

The amount of security associated with the user account that you use for
accessing your database through your data source will need to allow the minimum
necessary actions (UPDATE, DELETE, INSERT, SELECT, etc.) on the database
objects used by your application. You have chosen SA, which is wide open, but
you could create a user account with a much more restricted access. Are you
more worried about what can be done via ColdFusion, or are you worried about
your assistant accessing the database directly through Enterprise Manager? If
it is the latter, then you should set up an account for this person with the
appropriate privileges to the minimum number of objects. SQL Server Books
Online has a good section on Managing Security, and I recommend that you read
it, as it should answer most of your questions. This is really a SQL Server
issue, not a ColdFusion one.

Phil

[mike2004]

thx for the reply. i thought so that i have go with sql side. i read the online
book from sql and i'm even more confused. i don't understand where i have to go
and set this permission for my users knowing my datasources are using sa
account. i created a user under sql with limited permission and still that user
have full access to everything due to the way data sources are connecting to
these tables (via sa account).

maybe i need to create views, or some sort of rules to over write sa permssion
if the user account exists on the sql enterprise.

any idea what i have to do? please advice.

thanks,

mike r

[paross1]

In Enterprise Manager for your database, you should be able to manage user's
privileges in Security-->Logins, or even create a role for this user and others
with a similiar profile under Security-->Roles. Of course, the user has to log
on to the database with this account for the privileges to be in effect. If
they still use SA, then they will have unlimited access.

Phil

[mike2004]

thx again.

i think i know where i'm confused:

when i enter the rds password on dreamweaver for my coldfusion data sources,
no matter who the logged-in user is i'll see all the possible data sources that
are out there. saying that if i even create a custom data source for my
programmer (user) still she can see all the other data sources so she can
simply query one of the other data sources with full permission!

is there any way to have control over what data sources to show under
dreamweaver or how can workaround this?

please advice.

[paross1]

Well, that is an RDS issue, not a SQL Server one. You see the databases with
the same privileges as how your data sources are configured on your ColdFusion
server. I hope that this is a development server, as you shouldn't be using RDS
on a production system because of security issues.

Phil

[mike2004]

how can i not use rds and still make communication with my databases? i think i'm missing something here.

[paross1]

I'm guessing that you need RDS for Dreamweaver to communicate with your
ColdFusion server, etc., but ColdFusion doesn't use RDS to connect to the
database, just for you to be able to "talk" to CF with Dreamweaver, right? We
have RDS enabled in our develoment environment so that we can use CF Studio
(yes, I know, an antique), but the same aplies to Homesite or DW, I would
imagine. We do not have RDS enabled on our production CF server. so if I happen
to use CF Studio to edit any templates on the production system, I can't run
them (or look at any databases, etc. ) and would need to use Explorer to run
the template, and Enterprise Manager (or SQL Plus, etc.) to "look" at the
database. However, you really shouldn't make a habit of modifying production CF
templates without testing them in development anyway, so it really shouldn't be
an issue, unless you don't have development database instances, etc., which
then could be a problem.

Phil

[mike2004]

but aren't data sources running from coldfusion server via rds login account?
do i have any power over what databases to be shown with a specifiec type of
permission under coldfusion databases tab after entering my rds?

[paross1]

I think that you are confusing Remote Development Services, which allows
connectivity with a remote ColdFusion server, and Remote Data Services, which
is something different entirely. The RDS password that you are entering in
Dreamweaver is for Remote Development Services and has nothing to do with your
data sources.

Phil

[mike2004]

so why my data sources load once i enter the rds? please note that these data sources are setup through coldfusion data source tab.

[paross1]

See [url]http://livedocs.macromedia.com/coldfusion/7/htmldocs/00000053.htm[/url]

If you use Macromedia Dreamweaver MX or Macromedia HomeSite+ to develop
your applications, you can access a remote ColdFusion MX 7 server using HTTP.
However, you must configure Remote Development Services (RDS) in your
integrated development environment (IDE), and RDS must be enabled in ColdFusion
MX 7. Using RDS, IDE users can securely access remote files and data sources,
build SQL queries from these data sources, and debug CFML code.

Note: The ColdFusion Report Builder uses RDS for the Query Builder and for
charting support.

However, for security reasons, Macromedia recommends that you disable RDS
on a production server. To disable it, you must disable the RDSServlet
mapping.

You actually configure your data sources through the ColdFusion Administrator,
but RDS lets you link to them.

[mike2004]

so for me to not allow my assistant to have full power over my datasources i
shouln't open rds on her macromedia dreamweaver mx and instead i should have
her to create her own datasources with her own sql username and password? am i
understanding correct?

[paross1]

No, you aren't understanding it entirely correctly, but I don't know how else
to explain it to you. If your assistand doesn't have RDS enabled, then she
won't be able to connect to the ColdFusion server at all, at least using your
Dreamweaver development environment. That is why you should have a separate
development ColdFusion AND database so that you can have RDS enabled on
development, and NOT production. She would have full power on your development
database, but normally, who cares? You should be able to restore a dev db from
production, right?

Phil

[mike2004]

lol, sorry Phil if I gave you a hard time on understanding this but i have a
good news for you. i think i finally got it.

so the best way to handle my situation is to separate my development
environment and production environment. currently i only have one environment.

so here's what i think i have to do next to come up with a new develeopment
environment:

1) go to my assistant computer and take off RDS password so she can't have
access to my production environment data sources and other important stuffs.

2) install coldfusion locally on her computer

3) then i will have to create local sql database for her similar to what i
have on my production enviroment. (maybe it's better to just install sql server
2000 on her machine).

4) then enable rds on her machine which is only dealing with her local
database and coldfusion and doesn't know any thing about my production
enviroment.

5) once she's done with her coding and once i know the program is working
properly then at that time i should move it to the production enviroment.

am i good to go?

[paross1]

I think that you got it! Actually, it is a little easier than you described.
You probably could just create another database on the same SQL Server as a
development version, whichalso makes it easy to "refresh", since you could
take a recent backup of the production database and "restore" it once in a
while as the dev database. On her local version of ColdFusion, just create the
datasources that access the dev database only, so that would be the only
database that she could affect.

Phil :grin;

[mike2004]

Great. thanks for your time. you helped me a lot. have a wonderful weekend.