|
Since the date is stored with high precision (i would guess as a function of the exact time the entry was made, like Now()), a general date query is likely to have many misses (the hh:mm:ss parts may get defaulted to 00 and cause a candidate to fail the equality match). I would use a range query instead, (i.e Between "XXX - 12H" and 'XXX + 12h', or present a selection list of candidate dates queried from the database. The string format of the date is also dependant on the locale setting of the computer running you app, so using the general date format is risky dd/mm/yy works for some locales, others expect mm/dd/yy. I would format the date using th 'odbc canonical' format which is locale immune:
"XXXX" = "{ts 'yyyy-MM-dd HH:mm:ss'}".
Even better, use a parameterized query and pass the date value(s) as a parameter of type OLEDATE.
You should also be aware that Access stores the dates as local time relative to the zero date , so changing the time zone can cause the dates to appear differently in different time zones...
Absolute faith corrupts as absolutely as absolute power
Eric Hoffer
All that is necessary for the triumph of evil is that good men do nothing.
Edmund Burke
|
|
|
|
|
Hey all,
I'm working with a software package that relies on the security of the server to protect the database, meaning the database is entirely in plain english, and there is no double checking or check sum features to verify the data.
The server is accessable by all domain users(go figgure?) and therefore so is the database. The problem I am having is certain users have figgured this out and will go in to the db to change their access level and passwords of others (just for the sake of being malicious)
I have contacted the developers of the package and they basically said, "Sorry, mate, can't help you, we didn't design the system to be used in a hostile environment like yours". So I have no help from them, but still have the problem.
I need a way to track the transactions happening on the access_level column of the users table.
I've tried using the sql profiler, but i only know how to trace current transactions not previous tranactions so if the server gets rebooted, or someone logs into the console they see that its running and shut it off.
Basically, is there anyway to write something that will store the transactions happening on that column? or is there anyway to store the username column that was modified? so if i were user1 and i went in to the db and modified my account access level there would be a log or record of that transaction somewhere else that would store that users name.
I will plan to write any neccessary app in C#, and I would prefer to have the app run on my pc, not the server.
i've thought about just running an app that queries the db every so often (3-5 mins) to see the number of users in each access level, then returns the usernames for each admin access level(the level they are maliciously accessing) but that seems like a awful lot of work on the db, specially since its allready running slow due to the number of hits and request the server allready deals with. (which i can't really change since the server is run and operated by a different team, I just run the software package, there is a server group and a db group)
Is there a better option???
Dim Beautiful As String
Beautiful = "ignorant"
Label1.Text = "The world is full of " & Beautiful & " people."
Why is common sense such an un-common comodity?
|
|
|
|
|
What is the underlying database engine? If it is sql server you could add an update trigger on the users table and audit trail the changes to another (new) table. Better yet, the trigger could also reject access level changes by other than specific users, and perhps temporarily disable their access completely...
Absolute faith corrupts as absolutely as absolute power
Eric Hoffer
All that is necessary for the triumph of evil is that good men do nothing.
Edmund Burke
|
|
|
|
|
yes it is sql server, and i will have to do some research on these triggers you speak of, any direct links, good books, ideas you can share?
Dim Beautiful As String
Beautiful = "ignorant"
Label1.Text = "The world is full of " & Beautiful & " people."
Why is common sense such an un-common comodity?
|
|
|
|
|
The most complete documentation would be in SQL Sever Books On Line, which can be downloaded here.[^]
Absolute faith corrupts as absolutely as absolute power
Eric Hoffer
All that is necessary for the triumph of evil is that good men do nothing.
Edmund Burke
|
|
|
|
|
Cool, I'll check it out, thanks!
|
|
|
|
|
ok, so i've done some reading on triggers, and now i've got some questions!!!
do i want to do this with an instead of, or after statement?
or would it be just best for me to use this: CREATE trigger Nnew_updates on people for insert, update as
if i use this one, will i need to search thru the deleted table to find it, then rollback, or just use the instead of and compare the requested change with the trigger and not let it do it at all? (can i even do that with the instead of)
I found some DB2 info that uses BEFORE, too bad sqlSever does't use that, it'd be nice.
Course, the info that i found showing sql doesn't use it was from 2000, and it said that Microsoft was contemplating the use of BEFORE, do you konw if they have implemented the use of it in sp3 by chance?
My next question then is regardless of what method I go with what should my trigger statement look like? for instance I'm already looking at using what i pasted earlier and i've got the format down, but what should my begin statement look like?
I need to beable to compare the old access_level to the new access_level. so will i need to compare the deleted table with the people table? or is there an easier way to just not allow changes to the access_level column?
Dim Beautiful As String
Beautiful = "ignorant"
Label1.Text = "The world is full of " & Beautiful & " people."
Why is common sense such an un-common comodity?
|
|
|
|
|
I would use separate triggers for insert and update. The Deleted pseudoTable will not exist in the case of an insert, so the handling will likely need to be different. In both cases the new data can be accessed in the "Inserted" psuedoTable.
The for insert, for update triggers would seem nore sensible here than an after or instead of...
Could be as simple as "if Inserted.Access_level <> Deleted.Access_level then rollback transaction"
For inserts, I suspect you will want to check the user that is inserting the record (not the user being added)
- SELECT user_name() will return this, but this might be probematic if the application is always logged in as sysadmin - dbo - (it shouldn't be, but that is another matter).
Yuo should likely disallow changes to more than on record at a time (rollback if @@rowCount > 1) as this would seem inappropriate in any case, and allows succeeding checks to assume only one row (the current one) in the psuedo tables.
Absolute faith corrupts as absolutely as absolute power
Eric Hoffer
All that is necessary for the triumph of evil is that good men do nothing.
Edmund Burke
|
|
|
|
|
Well, my thinking is that I'm going to be better off by not restricting it to users as there IS one user who has admin access to the software but does not need the access to change others access(i was forced to give him access). so in other words i need to restrict the access to the access_level column entirely, and IF i need to update someones level(only if my boss tells me to) then i disable the trigger to complete what I would need to do. plus the domain levels are not set to change the db level so they can log in with sa level access.
and i think i would need to block any new entries as admin as there is nothing keeping someone from entering a new username and creating it as an admin from the db.(not sure if that was inline or out-of-line with what you were saying about the inserts.)
in your last statement, not sure i fully understand, are you saying i need to block more the possiblity of someone using a query to insert multiple accounts so as to trick the trigger into only stopping the first entry?
and i sould be able to run both the insert and update checks in the same trigger right? by just separating the functions of each rather than running them as a single statement?
Dim Beautiful As String
Beautiful = "ignorant"
Label1.Text = "The world is full of " & Beautiful & " people."
Why is common sense such an un-common comodity?
|
|
|
|
|
wetrivrrat wrote:
and i sould be able to run both the insert and update checks in the same trigger right? by just separating the functions of each rather than running them as a single statement?
You could, but the code might be much simpler if you did them as separate triggers, as they will fire independantly (the 'for update' won't fire the 'for insert' unless the update trigger itself inserts new records).
wetrivrrat wrote:
and i think i would need to block any new entries as admin as there is nothing keeping someone from entering a new username and creating it as an admin from the db.(
That makes the for insert trigger simple: just rollback transaction
wetrivrrat wrote:
in your last statement, not sure i fully understand, are you saying i need to block more the possiblity of someone using a query to insert multiple accounts so as to trick the trigger into only stopping the first entry?
I was more concered about an update that changed the access_level for any username 'like A%" forinstance, or all usernames ....
Absolute faith corrupts as absolutely as absolute power
Eric Hoffer
All that is necessary for the triumph of evil is that good men do nothing.
Edmund Burke
|
|
|
|
|
Which is better?
sqlDataReader[i]
sqlDataReader["columnName"]
sqlDataReader.GetValue(i)
where i is index of column
|
|
|
|
|
i prefer using the column name, for the simple reason that it is more readable than using a column index...
moreover, it would avoid some debug mistakes...
TOXCCT >>> GEII power
[toxcct][VisualCalc]
|
|
|
|
|
sqlDataReader["columnName"]
is generally used....
coz giving number as index may create prob if the sequence of columns is changed
Annpurna Tiwari
IIPL Company
Nagpur
India
|
|
|
|
|
Hi, i just wanna download DB2/ORacle editor in my system having the OS windows 2000.How can i download this freely in my machine.Plzz help me.
Thanks
BhuvanaSathish
|
|
|
|
|
oracle databases are free unless you use the DL versions for sell and some reasons explained on the website....
did you have a look at their site ?
TOXCCT >>> GEII power
[toxcct][VisualCalc]
|
|
|
|
|
I have SQL Server 2000 installed on my laptop, as well as Visual Studio .NET 2003. I am going through a tutorial in which it asks me to go to the Northwind database and right-click on Views and select New View. When I right-click on Views, the only context menu items I get are Refresh and Properties. Does anyone know why I do not get the "New View..." menu item in Server Explorer within Visual Studio .NET? Thanks in advance.
Dan
|
|
|
|
|
I'd suspect that you don't get all the options you would get in Enterprise Manager from within the IDE.
Christian Graus - Microsoft MVP - C++
|
|
|
|
|
Check your are db owner on the security panel for the database. You may not have rights to create a view.
|
|
|
|
|
Upon further investigation, it turns out that Visual Studio.NET Professional visual data tools (via Server Explorer) do not work with SQL Server 2000, but only with the SQL Server Desktop Engine (MSDE). This is actually on the features page for Visual Studio, but who looks at that, right? 
So, here's what I did... I left SQL Server 2000 on my laptop, and installed the MSDE (they can reside together). Some good instructions for doing this are here. After doing this, you can use the Server Explorer to create a connection to the MSDE and create tables, views, etc.
Another problem I ran into was the tutorial that I was going through had examples using the Northwind sample database, which comes with SQL Server, but not with MSDE. I used the Data Transformation Services (Import/Export Wizard) to copy the Northwind database from SQL Server 2000 to my MSDE.
|
|
|
|
|
I need to get the relationships that exist between the tables in a database. The database structure is unknown to me , i.e at runtime the program connects to any database be it Access or SqlServer. From that database i get the info. related to the tables such as the columns that exist in it, their datatypes,etc but not the table relationship to other tables. For SQL databse i have used this approach - I have used SQLDMO to get the table names. Then generating a select statement e.g: "SELECT * FROM Tablename" and then using DataAdapter.FillSchema method i get the structure of the table. But i also need the realtionships that exist between the tables. Could any one help me??! (Using C#)
|
|
|
|
|
I assume you have access to enterprise manager.
Look for any database diagrams. If there are none add the tables you need into a new database diagram and the foreign key relationships will show themselves.
|
|
|
|
|
Usually you need to access the metadata tables of the database to get this information.
There's no standardized way to get this information. For example, Access stores tables in a table MSysObjects and relationships in a table MSysRelationships, but SQLServer will have a different system.
Regards,
mav
|
|
|
|
|
You can query foreign key relationships from the INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS view. You could also get the schema from the INFORMATION_SCHEMA views. Look up INFORMATION_SCHEMA in SQL Server Books Online.
|
|
|
|
|
I have a matter with Ms.Access. I don't know how to pass the value from one form to another form. Also, I want to set this value to the text-box of the destination form.
For example:
I'd like to transfer the value '5' inside the code VBA of the form 'form1' to the form 'form2'. After moving successfully, I want to show this value in a text-box of the destination form. Please point me to a way of solutions.
Thanks so much...
|
|
|
|
|
Suppose you have two forms and they are Form1,Form2. Form2 is your destination form. Where you want to pass a value 5 to Form2's textbox. The following code should work.
Dim form2 as New Form2
form2.TextBox1.Text = "5"
Tutul
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
