SQL Alert: password

Showing posts with label password. Show all posts

Thursday, March 8, 2012

Access to sql on windows for mysql application

Hi
I hope someone here can advise or at least point me to a good site to
pursue this.
Have installed a password protection system (freeware) successfully on
a unix/php/mysql server and looking forward to developing it into a
full blown database application. I is written in php and use a mysql
database to hold user id's and passwords. There is a small amount of
code on each protected page on the site that checks the session.
I now need to set it up on a windows server system which uses IIS and
sql, mysql is not an option. It appears that ODBC could be the answer
but I don't want to fight my way through the mire of finding and fixes
issues one by one only to get trapped near the end and find it was not
possible all along.
Does anyone have any experience of the and am I on the right track.
Ideally the code will work with minimal change once set up. Would that
be possible and what would I have to set up on the server?
Thanks JohnYou'll need to look at the code and see if the SQL is really
portable. If it's a system written for a mysql backend then
it may not work. The SQL languages have different
implementations on the different database platforms. Without
knowing the app or the code, I don't know that anyone up
here could give you a definitive answer.
-Sue
On Wed, 09 Aug 2006 11:32:10 +0100, John
<yjdyhugo@.d8hujsrfu> wrote:

>Hi
>I hope someone here can advise or at least point me to a good site to
>pursue this.
>Have installed a password protection system (freeware) successfully on
>a unix/php/mysql server and looking forward to developing it into a
>full blown database application. I is written in php and use a mysql
>database to hold user id's and passwords. There is a small amount of
>code on each protected page on the site that checks the session.
>I now need to set it up on a windows server system which uses IIS and
>sql, mysql is not an option. It appears that ODBC could be the answer
>but I don't want to fight my way through the mire of finding and fixes
>issues one by one only to get trapped near the end and find it was not
>possible all along.
>Does anyone have any experience of the and am I on the right track.
>Ideally the code will work with minimal change once set up. Would that
>be possible and what would I have to set up on the server?
>Thanks John

Saturday, February 25, 2012

Access rights to two mssql dbs via password protected role

I dont know how to arrange situation when application enduser needs to access data in two databases of mssql server concurently in those circumstances that access rights to the data should be restricted by password protected role (whose password is not known to the end user).

Detailed description of problem:

So far there was an application, that manipulated its data, saved in mssql server's database. End user authenticates to application by his (mssql server's) login name and password. The application authenticates the user by connecting to the database with the given name/password credentials, and then the application sets application role with hardcoded name/password. Thus application role sets the access rights for consequent end user's requests, delivered via application to the database server.

The goal is that end user cannot manipulate application database data when connects to the database by other means (e. g. via SQL server Manager), because he does not know the application role's password.

Now suppose that there are two applications (A1, A2), both using the same model for access restrictions. Each of them has its own database (A1DB, A2DB) and its own application role (A1R residing in A1DB, A2R residing in A2DB). End user (login) X can manipulate A1DB data when connects via A1, and A2DB data when connects via A2, and NO data when connects by other means.

Finally suppose that some subset of A2 data (let's say one table) is useful to see also via A1 application. There is no problem to add to A1DB view, that shows data from A2DB table together with A1DB tables. But when the user is connected via A1, he cannot see the data, because query on A1 view fails (user has not access rights on A2 data).

The access rights for A1 enduser cannot be set by no means i know because:

1) I cannot set the rights via public (guest) access because in that case they will be accessible to any users connected by any third party products, which is supposed to be security hole.

2) I cannot set the rights via dbuser or dbrole privileges, because they will not work when connected via A1 application (setting the app role suppresses the db privileges)

3) I cannot set the rights via application role because two application roles cannot be set concurrently.

4) I cannot abandon using application roles mechanism and use database roles mechanism, because db roles cannot be protected by independent password (not known to the enduser).

Please can anybody review my problem and either find the mistake in my approach, or propose other solution? So far I suppose the problem is my ignorance, because I am not great mssql expert.

I hope I can explain why approles will not work on your particular scenario. Approles are principals defined within a database, and they have absolutely no presence on the server or on other databases. Even if the 2 approles are named the same and have the same passwords, because they are defined in different databases they are not the same principals. The fact that after establishing an approle it is possible to go to a different DB as “guest” account (given that guest access is allowed) is a consequence of the previous SQL Server security model and that we have to maintain it for backwards compatibility reasons.

One potential solution for your problem would be to use the new impersonation mechanisms in SQL Server 2005. For example, you can create a module (i.e. a SP or a multistatement function) marked with EXECUTE AS (disable the login mapped to this user to prevent direct connections) with enough permission to allow that the impersonated context on A1DB can access the data on A2DB. Because you will be accessing cross-database data, you will need to either sign your module (recommended) or set the TRUSTWORTHY bit option ON on A1DB (source DB).

I would recommend the following references to understand this new impersonation model better:

· EXECUTE AS Clause http://msdn2.microsoft.com/en-us/library/ms188354.aspx

· Using EXECUTE AS in Modules http://msdn2.microsoft.com/en-us/library/ms178106.aspx

· Understanding Context Switching http://msdn2.microsoft.com/en-us/library/ms191296.aspx

· Understanding Execution Context http://msdn2.microsoft.com/en-us/library/ms187096.aspx

I also recommend reading Laurentiu’s blog as well as my own blog

· Laurentiu Cristofor’s blog http://blogs.msdn.com/lcris/

· Raul Garcia’s blog http://blogs.msdn.com/raulga/

I hope this information will be useful. Let us know if you have further questions.

-Raul Garcia

SDE/T

SQL Server Engine

|||

Thanks to Mr. Garcia

Thank you very much for your answer. It seems you have solved my problem. In fact the focus of my problem was not in (not)understanding of role mechanism in mssql server. The problem was, that i was not able to convince my managers, that the concept of roles in mssql server does not allow simple setting of access rigthts to objects in two databases via password protected roles. They did not believed me, because were used to platforms, on which it is not problem at all. After reviewing your explanation it seems, that my managers will give me more time for solving our inter-application-communication problem via more complicated way - the new impersonating mechanism.