|
Hi everyone,
I was wondering how you all went about connecting to your databases from a security point of view. Some people say to use the SA password and embed security in your app with internal usernames/passwords, while others use Windows Security.
I think the SA password idea sounds simplier, and has the advantage of being able to limit what data the user gets to (eg: which clients information they can see) HOWEVER it doesn't stop someone using the password with MS Access to bypass your app and get the data.
So I was thinking the integrated security is best, and like any security, the use of groups would be best. My issue then becomes how to use those groups in SQL / VB.NET to enforce security, especially how would I enforce record level security (eg: which clients information they can see)
Thoughts everyone ??
|
|
|
|
|
Mark Cabbage wrote: Some people say to use the SA password
I've heard from numerous others that you should never use the sa password in your app. Create a user account for your app, granting it only the necessary permissions that it requires to get it's job done.
"Real programmers just throw a bunch of 1s and 0s at the computer to see what sticks" - Pete O'Hanlon
|
|
|
|
|
Thanks for the reply Paul, however that doesn't solve the overall decision of using self-contained security accounts defined inside the account, or using Active Directory security.
|
|
|
|
|
SQL Server's security model is pretty complicated.
The credentials you supply to the server map to a login. This login can be authenticated using SQL Server, by supplying a username and password, or can be authenticated by Windows (either through local accounts or domain accounts, if the server is a member of an NT or Active Directory domain). You can grant both Windows user accounts and Windows security groups a login. You do not supply a username or password for Windows Authentication - it uses the Windows credentials of the user running the client application, and the groups that they are members of. To use different credentials you must run the application as a different user.
By default, SQL Server authentication is disabled. The default configuration has an sa login which is SQL Server-authenticated, and allows the Windows security group BUILTIN\Administrators to log in as well.
Logins can be members of one or more server roles. These are fixed roles which have special permissions to manage or modify the server. All logins are members of the public server role - this cannot be removed. The only permission this role has in SQL Server 2005 is VIEW ANY DATABASE which means that the login can see all the databases on the system - without this, it can only see master, tempdb, and any databases it owns. sa and BUILTIN\Administrators are members of the sysadmin server role, which has full control over the database server. This is why you should never use sa in application code - it's simply far too privileged.
The set of server roles is fixed and, apart from public in SQL Server 2005, their permissions cannot be changed. In SQL Server 2005, many of the permissions can be granted individually to logins, while in SQL Server 2000 these permissions can only be granted through membership of the appropriate server role.
Just being granted a login to the server does not give any access to a database. The database must have a mapped user for the login. Again, the server roles do allow this to be overridden and in effect, members of the sysadmin server role have full control over every object in every database. Individual database users can be granted or denied permission to execute particular SQL statements on particular objects, and can also be granted permission to grant that permission to someone else.
Again, there are database roles which perform the function of groups of users. Again, there are predefined roles and everyone is a member of the public role. The login that owns the database is mapped to the dbo user and is a member of the db_owner role. Often a user is given the same name as the corresponding login.
There are also application roles which are independent of the user login. You change to an application role by running the sp_setapprole procedure. After this, the permissions are taken only from the specified application role, rather than using the user's permissions.
Permissions can be either GRANTed or DENYed. A login is allowed to do something if at least one of the roles that the login or user is a member of, or the login or user itself, is GRANTed the permission, and none of the above is DENYed. A DENY trumps any GRANTs. DENY is generally used only to exclude particular users that are members of a larger group which you want to GRANT permission to.
Getting down to the most commonly secured items, each of the basic query/DML statements SELECT, INSERT, UPDATE and DELETE are controllable for table and view objects, and SELECT and UPDATE for individual columns in a table. Users can be GRANTed permission to EXEC a stored procedure. The default is that the owner of an object has full control over it (users in a database can be granted permission to create their own objects, which in SQL Server 2000 results in a schema named by the creating user, while in SQL Server 2005 schemas are separate objects, so if granted permission another user can add new objects to the dbo schema) and the public role has no permissions at all (not denied, just not granted). The various other fixed database roles have specific permissions to all objects (e.g. db_datareader has SELECT permission).
What I commonly do is grant the public role the minimum permissions necessary to perform the application's tasks, generally firewalling the actual tables behind stored procedures and only granting the public database role permissions to EXEC those procedures. I then create a login for the application which is only a member of the public fixed server role, and map that to a user in the database that is also only a member of the public database role.
SQL Server does not have record-level permissions. You will need to do this at an application level (presumably through a stored procedure which the application can EXEC and don't grant permissions to SELECT from the table(s) it references). It may be possible to do this using some part of the Windows or SQL Server authentication model, but it's hard to discover Windows group or SQL Server role membership from within a stored procedure.
|
|
|
|
|
Thanks so much for taking the time to elaborate on the topic.
Mark
|
|
|
|
|
Hi,
I'm having trouble trying to fully populate a full-text index table in SQL 2000 Server. It worked fine with the news table I've created but it failed to populate any other tables I had.
When I go to the fulltext catalog and click on one of the catalogs and view the properties, the item count is 0 even when I click on full population and it said successfully populated.
The problem is that it always return empty for any other table but it worked fine for the news table.
|
|
|
|
|
I'm a little confused, did you define a full text index on the other tables and did you run a full population?
|
|
|
|
|
Hello
VS 2005
I am saving a database table as a xml file. So that a client could use this offline and make changes to the table. Then go online and synchronize them with the main database.
I set the identity to decrement. That would solve the problem of having duplicate rows.
However, I am not sure what I have done wrong, as everytime I have added a new row. All the rows have -1 for the incidentID (PK) column. So I am getting all -1. I am not sure why it doesn't decrement so that I would get -1, -2 etc.
Can you see any mistakes in my code, as to why I am getting -1s.
Here is the code I am using.
<br />
'Read the xsd file<br />
dsIncidents.ReadXmlSchema(incidentXSD) 'Do I always need to read in the xml schema file<br />
'Read in the xml file<br />
dsIncidents.ReadXml(incidentXML, XmlReadMode.DiffGram)<br />
<br />
Dim dr As DataRow<br />
'Set the auto increment columns<br />
dsIncidents.Tables(0).Columns("IncidentID").AutoIncrementSeed = -1<br />
dsIncidents.Tables(0).Columns("IncidentID").AutoIncrementStep = -1<br />
dsIncidents.Tables(0).Columns("IncidentID").AutoIncrement = True<br />
<br />
dr = dsIncidents.Tables(0).NewRow()<br />
<br />
dr("Company") = Me.cboCustomer.Text<br />
dr("Contact") = Me.cboContact.Text<br />
dr("PhoneNo") = Me.txtPhone.Text<br />
dr("Priority") = Me.cboPrority.Text<br />
dr("Subject") = Me.txtSubject.Text<br />
dr("AssignedTo") = Me.cboUsers.Text<br />
<br />
dsIncidents.Tables(0).Rows.Add(dr) 'Add the new row<br />
<br />
dsIncidents.WriteXmlSchema(incidentXSD) 'Do I always need to write the xsd schema when i have added a new row<br />
dsIncidents.WriteXml(incidentXML, XmlWriteMode.DiffGram)<br />
Many thanks for your help,
Steve
|
|
|
|
|
Never tried this before but, are you setting the AutoIncrement properties each time you add a row? If so, this may reset it back to -1.
You may want to look at Guid(vb/c#) / UNIQUEIDENTIFIER(SQL) for your primary keys. This will allow you to create primary keys locally and synchronize updates with the server. This I have done before and was able to sync insert/updates/deletes without any problems since foreign keys don't need to change.
|
|
|
|
|
Thanks for the reply.
I think you may have shined some light on the problem.
I read in the xml files and create a new dataset and set the autoIncrement properties. So each time a user wants to add a new row it will do the same thing again. So I will be setting the autoIncrement properties each time.
The add form opens as a showDialog each time a user wants to add a new row. Is it possible to have a global dataset where it can be set only once?
However, there is still a problem I noticed if you look at the code snippet below, I set the autoIncrement once, then add 3 rows. But row 1 always gives a NULL for the incidentID PK.
example, why am I always getting a null for the first row?
Row 1 NULL
row 2 0
row 3 -1
etc.
<br />
Dim dr As DataRow<br />
'Set the auto increment columns<br />
dsIncidents.Tables(0).Columns("IncidentID").AutoIncrementSeed = 0<br />
dsIncidents.Tables(0).Columns("IncidentID").AutoIncrementStep = -1<br />
dsIncidents.Tables(0).Columns("IncidentID").AutoIncrement = True<br />
<br />
dr = dsIncidents.Tables(0).NewRow()<br />
dr("Company") = Me.cboCustomer.Text<br />
dr("Contact") = Me.cboContact.Text<br />
dr("PhoneNo") = Me.txtPhone.Text<br />
dr("Priority") = Me.cboPrority.Text<br />
dr("Subject") = Me.txtSubject.Text<br />
dr("AssignedTo") = Me.cboUsers.Text<br />
dsIncidents.Tables(0).Rows.Add(dr) 'Add the new row 1<br />
<br />
dr = dsIncidents.Tables(0).NewRow()<br />
dr("Company") = Me.cboCustomer.Text<br />
dr("Contact") = Me.cboContact.Text<br />
dr("PhoneNo") = Me.txtPhone.Text<br />
dr("Priority") = Me.cboPrority.Text<br />
dr("Subject") = Me.txtSubject.Text<br />
dr("AssignedTo") = Me.cboUsers.Text<br />
dsIncidents.Tables(0).Rows.Add(dr) 'Add the new row 2<br />
<br />
dr = dsIncidents.Tables(0).NewRow()<br />
dr("Company") = Me.cboCustomer.Text<br />
dr("Contact") = Me.cboContact.Text<br />
dr("PhoneNo") = Me.txtPhone.Text<br />
dr("Priority") = Me.cboPrority.Text<br />
dr("Subject") = Me.txtSubject.Text<br />
dr("AssignedTo") = Me.cboUsers.Text<br />
dsIncidents.Tables(0).Rows.Add(dr) 'Add the new row 3<br />
Many thanks for any extra help with this problem.
Steve
|
|
|
|
|
Check the AllowDBNull value on the column. It should be set to FALSE. Just guessing....
|
|
|
|
|
Hello,
Thanks for your continued support.
I have set the allowDBNull:
<br />
dsIncidents.Tables(0).Columns("IncidentID").AllowDBNull = False<br />
I get an exception error when I added the new row: "Column 'IncidentID' does not allow nulls"
When I take out the above line and run the program. On careful inspection the incident column is 'system.DBNull', when I check the other rows the incident column is what I would expect it to be, 0, -1, -2, etc.
Any other ideas would be most helpful,
Many thanks for your help,
Steve
|
|
|
|
|
Hello,
Problem solved, not sure what I did but seems to be working now. I guess that is programming.
Thanks for the advice and help,
Steve
|
|
|
|
|
Hi Dear All,
Can anybody help me? I am new to the web services in .net. So requesting for the free downloadable e-book for the webservices. Or whitepapres or any material that demonstrates the all the stuffs of the Web Services, from strach..
Thanks!!!
Warm Regards
Sushilkumar Jadhav
Cell +91 98509 87349
|
|
|
|
|
You seem to be confused. This forum is for SQL/ADO/ADO.NET. It seems to be missing the bit that says web services.
Deja View - the feeling that you've seen this post before.
|
|
|
|
|
Hi All
Dont get me wrong i buy books,but hey my problem is out of scope, but i can solve it, and have already solved it many times. i need a site where i can download E-books for free in the Following Subject.
ADO.NET 1.1 or 2.0
VB.net 2003 or 2005
Thanks
Vuyiswa Maseko,
Sorrow is Better than Laughter, it may Sudden your Face, but It sharpens your Understanding
VB.NET/SQL7/2000/2005
http://vuyiswamb.007ihost.com
http://Ecadre.007ihost.com
|
|
|
|
|
Try here[^].
Deja View - the feeling that you've seen this post before.
|
|
|
|
|
hi
i did not see the Thread before. anyway thank you very much
Vuyiswa Maseko,
Sorrow is Better than Laughter, it may Sudden your Face, but It sharpens your Understanding
VB.NET/SQL7/2000/2005
http://vuyiswamb.007ihost.com
http://Ecadre.007ihost.com
|
|
|
|
|
Hello Everyone,
What is the difference between join and nested queries.......
and which is executed fast
-----------------------------------
|
|
|
|
|
The Knowledge wrote: What is the difference between join and nested queries.......
and which is executed fast
A join, ummm... joins two tables together.
SELECT * FROM A INNER JOIN B ON A.PK = B.FK
A nested query (aka sub-queries) runs one query inside another.
SELECT * FROM (SELECT * FROM A) -- Okay this is a bit of a useless example
I wouldn't say that either is faster than the other. Although, from what I can see, it is easier to make a mess of nested queries which will affect performance negatively. Typically a nested query will be used with a join anyway.
SELECT * FROM A<br />
INNER JOIN (SELECT * FROM B) AS nestedB ON A.PK = nestedB.FK
Okay, that's another pointless example, but it does demonstrate the basic syntax. Often, the sub-query will be used with some sort of group by clause.
|
|
|
|
|
Hi,
it is very hard to find a general answer on your question and it really depends on the situation and database schema behind (indexes at most). If you are trying to join two tables with, let's say more than 30 millions of records it will take forever, but if you have a good filtering on the second one and than using that in subquery it will fly. On other way, in a lot of situations, for example, SQL Server will treat your subquery as separate one and perform a separate optimization plan which costs time. There are two different types of subqueries, self-contained (run independently of the outer query) and correlated ones (have references to outer query columns) and they also behave differently. The same is for joins. SQL Server now has a couple of join algorithms used by query optimizer (loop join, merge join and hash join) in different situation.
To get a real answer in a particular situation, is possible by just examine execution plan for both queries and than decide which one to use.
In addition to this, I would always consider using CTE (Common table Expressions) (at least on SQL Server 2005) as one of the most effective ways of subquerying.
Hope this helps a little bit.
Best regards,
DLM@bypsoft.com
|
|
|
|
|
can we use arrays in a sored procedure
if not, is there any alternative to store the values somewhere dynamically from where we will be able to use the values.
|
|
|
|
|
nicetohaveyou wrote: store the values somewhere
Will a temp table suffice ? (With respect to SqlServer)
|
|
|
|
|
can we use a temp table dynamically inside a T-SQL statement
|
|
|
|
|
You can, and you can also use a table variable. This is, effectively, an in memory temporary table.
Deja View - the feeling that you've seen this post before.
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
