Log In Confusion (newbie)

Greetings Gentlemen, this is my first post - I'm sure it will be one of many!

I'm a fairly experienced programmer, VB and ASP being my languages of choice. However, due to the cost of SQL Server, I have only recently been given an opportunity to use it - through work...

I have to say that I'm REALLY impressed with it - using it seems mainly intuituve - you have have a basic understanding of databases, then, it seems, that one can lever basic functionality out of SQL Server with the minimum of fuss and effort... My collegue, next to me, is using Oracle for another project, and he is constantly expressing his envy at SQL Servers rich GUI interface! Anyway, any more of this and you'll be thinking I work for Microsoft!

I am designing a web site, the back end of which will be SQL Server based. I am slightly worried about exposing the connection information to the database in the DSN connect string - at the moment, I am developing with the 'sa' login, which, I think is bad news... I need to use a login that is restricted to the web sites database, rather than giving access to the entire SQL Server.

So, I opened up the tree for my database, (I'm using SQL Server 2000) and opened logins, and created a new login. I made sure it used SQL Authentication, and I gave it default access to my database. In the 'Roles' tab, I made sure it was NOT a member of any server roles, since I don't want people administering the server through this login. All databases (like northwind etc) were de-selected, except of course for my database. I also made sure that the only permissions it had were databasereader and databasewriter.

After doing this, I logged off, and logged back on again (I have set it up to use SQL Server Authentication), and logged in using my newly created login.

I was expecting to be taken straight to my database only, but I could see ALL of the databases.

Further, when I open up the northwind database, and select a table (return all rows) it asks me for a password (fair enough), but if I enter the login I created for my OTHER database, it returns all the rows! Surely I don't have the correct permissions to do this?

I am missing something out?

Any comments would be appreciated.By default you are member of public group( which includes obviously any new logins ), which has Select ( read ) access to all system tables & views in all databases. This is normal.

By the way, I would STRONGLY recommend you add yourself to the db_owners role in the database as it gives you automatically full access to all tables, views etc in that database ONLY, and by the way you dont have to add yourslef into any read/write roles even though as db_owner your permissions are not explicitly displayed...

Yes, you can see all the databases - see my comment about public group. This is normal when using EM or Query Analyser.

Also, even though you are in db_owners, to ensure trouble free developing, ensure ALL objects created are owned by DBO. This makes sure no broken ownership chains exists. This also avoids the DBA hassling you for this....it also means if you have to transfer databases later on, as dbo exists by default on every database, it works straight up immediatley after transfer.

Hope this helps & post back if problems.

J.|||Thanks for your reply, jbleen.

I must admit, it sounds a little confusing!

Do you think you could guide me through adding a user, and 'locking' it down so that it can only access ONE database?

The reason for this is that the connect string to the database will have to be passed to the server box as part of an ASP script - therefore publicly advertising the usrname and password if anyone gets the source-code.

If the web application logs in with sa permissions, it's BAD! A hacker could then use ADO etc to create a command set that simply DROPS all databases! Ouch!!

In the meatime, I will print out your notes and have a good play with it! I'm really enjoying it, in a geeky, nerdy dont-let-my-mates-know kind of way! :D

Mark.|||Hi

Glad you are learning. Yep, using sa for login is an executable ( shot at dawn ) sort of thing. Dont do it - ever.

Easiest way to stop access by Public group is to not grant it permissions in the first place. If you have Public with access to system tables - not user created tables - etc ( which exists by default in every database when its created ) you can explicitly deny access to these. This way, only the user/s you define in the database can access that databse. Also make sure there no users have sneaked into any db_XXXXX database roles ( good housekeeping ).

Post back if probs.

J.|||Hi mark wills

Actually what is happening with your security issue is that when you access a database which you don't have rights to but the GUEST account exists then by default you have access.

If you look into the model database you should see a user account called guest, this is a built in user account. When you create a new database, SQL Server takes a copy of model as a starting point, therefore the account guest gets copied into your new database. As well guest is a member of public, all new accounts are, like jbleen100 had stated and by default public has read access again like jbleen100 had stated. The quickest and easiest action to do is remove guest from your database and you should also remove guest from the model database. By doing so you will eliminate this open access to all new databases.|||Good point Mark

My apologies...we already automatically remove the guest account from every new db by default, but if you were new to SQL you may not have picked that up.

Cheers

J.|||Thanks guys - you rock!

My next question(s) arise from creating an APPLICATION login/role. Theses are apparantly exactly what I need for my web application - I could then just use the sa login when working within the SQL Server console, and my web app could use an Application login, (with SELECT, INSERT, UPDATE, and DELETE permissions - but ONLY for my chosen database!) I should prpbably make that a seperate post, but I will check the online help first (very good online help in the Enterprise edition!)

Phew! It's confusing all this security stuff - the actual database stuff doesn't seem too bad, although of course datawharehousing etc is probably a subject in itself... but this security thing... :confused:

Thanks again guys...

Mark.sql