July 3, 2006 at 5:54 pm
Hello All,
We currently operate a number of web and sql servers and to date, I have been the only one who controls them. Finally, we have some others coming in but I have a concern of how to lock the machines down properly to ensure that data or scripting is not accessed or modified by unauthorized persons.
The web servers connect to the DB servers on their own username and password as dbowners.
Administrators/dba's connect to the DB servers as sysadmins on their own login/password.
What I need to do is implement sufficient security to allow the web application to work unhindered but lock the other dba/admins down to only their access, such as only being able to execute/edit certain stored procedures and/or run or not run certain queries.
Is this at all possible with SQL2000? Does anyone have a similar scenario and could suggest a workable solution?
Thanks
July 4, 2006 at 2:00 am
Neil
Sysadmins can do anything they like on the server, no matter what. If that isn't what you want, you'll need to go for a lower server role, such as Security Administrators. Otherwise, don't give them a server role at all but give them the access they need to each individual database. You can use database roles to simplify the administration of this. I would have thought that a DBA would need to be a sysadmin, but I suppose that depends on the structure of your team.
Are you sure that your web application needs db owner access? Unless your application is so badly designed that it creates and deletes objects on the fly, you will probably find that it's sufficient to give it access to the tables and views and execute permission on the stored procedures. Or, if all queries are done through stored procedures, you can revoke access to the underlying tables and views.
Have you considered using Windows authentication for extra security?
John
July 10, 2006 at 7:45 am
Hello John,
Thank you for the detailed reply. Our DBA's are not permitted to have full access to the system overall, they are assigned a small section within either a separate database or objects within the database as their work is not permitted to extend beyond that.
The web application certainly does not need this level of access, rather it was only setup like this initially and somehow carried across (for far too long than I would have preferred). Not being a security expert in sql server, I was unsure as to the ramifications of security restrictions imposed on users. All the web servers access only by stored procedure so essentially could we create a 'web user' to have access to the databases and only exec stored procedures? If so, does this need to be set on each procedure or can it be set on the database overall?
Yes, we have considered windows auth, but again not 100% knowing it's impact preferred to stay in a separated environment.
Thanks again.
Neil.
July 10, 2006 at 8:08 am
Neil
Create a login, say WebUser, give it access to the database, put it into a role, say WebRole, and grant WebRole execute permission on each store procedure. You do have to do it on each proc, but if you run the script below, it will generate the SQL that will do it for you. Remember to TEST thoroughly before this goes anywhere near your production environment.
John
USE WebDB
SELECT 'GRANT EXECUTE ON ' + ROUTINE_NAME + ' TO WebRole'
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_TYPE = 'PROCEDURE' --Remove if you want to grant permission to functions also
July 10, 2006 at 5:53 pm
Hello John,
Thank you very much for your assistance. I have already begun to configure the applications as such and all is look good.
Thanks
Neil.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply