Skip to main content
A
Anonymous
June 21, 2006
Answered

How to add security to data sources from SQL 2000

  • June 21, 2006
  • 6 replies
  • 1254 views
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
This topic has been closed for replies.
Correct answer paross1
I'm guessing that you are using 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 development environment so that we can use CF Studio (yes, I know, an antique), but the same applies 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 with database security. Taking shortcuts by not having separate development and production environments can bite you.

Phil

6 replies

A
Anonymous
June 22, 2006
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?

P
paross1
Participating Frequently
June 23, 2006
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 copy, which also 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
A
Anonymous
June 23, 2006
Great. thanks for your time. you helped me a lot. have a wonderful weekend.
A
Anonymous
June 22, 2006
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?
P
paross1
Participating Frequently
June 22, 2006
No, you aren't understanding it entirely correctly, but I don't know how else to explain it to you. If your assistant 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
A
Anonymous
June 22, 2006
so why my data sources load once i enter the rds? please note that these data sources are setup through coldfusion data source tab.
P
paross1
Participating Frequently
June 22, 2006
See Disabling Remote Development Services

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 (and I guess through Dreamweaver too, although I have never used it myself), but RDS lets you work with them. Bottom line, if you allow your developer access to ColdFusion via RDS, then they will have all of the privileges that are allowed to the data source for the account that is configured in the ColdFusion administrator for that database.

Phil
A
Anonymous
June 22, 2006
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?
P
paross1
Participating Frequently
June 22, 2006
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
A
Anonymous
June 22, 2006
how can i not use rds and still make communication with my databases? i think i'm missing something here.
P
paross1Correct answer
Participating Frequently
June 22, 2006
I'm guessing that you are using 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 development environment so that we can use CF Studio (yes, I know, an antique), but the same applies 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 with database security. Taking shortcuts by not having separate development and production environments can bite you.

Phil
P
paross1
Participating Frequently
June 22, 2006
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 or, more properly, a role, which has much more restricted privileges for an application level instead of an administrator. 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
A
Anonymous
June 22, 2006
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
P
paross1
Participating Frequently
June 22, 2006
In Enterprise Manager for your database, you should be able to manage user's privileges in Security-->Logins. 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.

If you look at tables for your particular database, then pick a table and right click selecting properties, then permissions, you can see what users have which privileges on the object. Also under your database, take a look at Users and Roles. if you look at the properties for a Role, then permissions, you can see what permissions are applicable for each object, etc.

Phil
Terms & ConditionsAccessibility statement
Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices