|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
First time asking an SQL question...I've just invented a fabulous way of barring students from accessing the Internet at the school of which I'm the network administrator. Question is, can it be done? Essentially, if - in a classroom environment - the teacher has decided beforehand the students in the class do not need to access the Internet for that class time, the teacher should be able to revoke *any student ID* from Internet access *that logs on at any of those class room PCs*. Sounds harsh? Having Internet access where none is needed is a GIGANTIC distraction for kids who SHOULD be doing something else entirely. More fierce supervision - probably, but that's not my call, and my solution is much more elegant, and final. The classroom PCs run Windows 98 and Windows XP, depending on the CPU capability. Students access the Internet via ISA Server 2004 - and only if they are members of "Internet Access". So far, I have been able to have these PCs update which user ID is logging in to which machine, to a central SQL database. So thus far:- SQL Server has a database called "WSDB" in WSDB there are tables called "Room1", Room2", "Room3", "Room4" and "Room5". In "Room1" (for example) there are 28 fields - R101, R102, R103.... R128. Meaning Room 1 PC 01, Room 1, PC 02, etc. In the R101 field (for example), when a student logs in, his/her login ID is stored against R101 - "ST1041", meaning the R1 table, field R101 has ST1041 user ID logging in to it. So, if the teacher taking Class 1, in Room 1 has decided NO student ID can access the Internet for that Class, that student ID should be removed from the Active Directory Internet access security group. Therefore, that student ID cannot access the Internet through our ISA Server 2004, because only members of the Internet Access security group can get through! Hooray! So essentially I need this:- Teacher decides beforehand Room 1 Period 1 No Internet Access required (and selects "No" in a database). Student logs in on R104 Period 1. My little program updates WSDB database/R1 table/R104 field with the student login info. Trigger is tripped - R104 field in table R1 in database WSDB is updated. SQL trigger checks whether R1 Period 1 Internet access denied. If yes (denied), remove student login ID from Internet Access security group. At end of Class 1, automatically (based on time-of-day) restore all student accounts to Internet Access security group. Can any of this stuff be done within SQL Server 2000? (As in, execute a query/trigger/whatever?) This is my first ever go at playing with SQL Server beyond creating a database - muddling around I could probably get something to go... Could the above scenario be got to work? Mark <Comment>
This shouldn´t be done in tables, you should consider having one Table for the Rooms which is called "Rooms", there you could add for rows with a description of the rooms..yadayada </Comment> In "Room1" (for example) there are 28 fields - R101, R102, R103.... R128. <Comment> Guess you mean columns not fields, you should go for one table called Computers referenced to the Table "Rooms". You should redesign your database as soon as do this, this will help you in the future. believe me ;-) </Comment> Redesign your database, then we can look further to your requirements. Show quote "Mark B" <m***@mosaiccomputers.com.au> schrieb im Newsbeitrag news:eZzuo0tWFHA.3280@TK2MSFTNGP09.phx.gbl... > Hi guys, > > I've just invented a fabulous way of barring students from accessing the > Internet at the school of which I'm the network administrator. > > Question is, can it be done? > > Essentially, if - in a classroom environment - the teacher has decided > beforehand the students in the class do not need to access the Internet > for that class time, the teacher should be able to revoke *any student ID* > from Internet access *that logs on at any of those class room PCs*. Sounds > harsh? Having Internet access where none is needed is a GIGANTIC > distraction for kids who SHOULD be doing something else entirely. More > fierce supervision - probably, but that's not my call, and my solution is > much more elegant, and final. > > The classroom PCs run Windows 98 and Windows XP, depending on the CPU > capability. Students access the Internet via ISA Server 2004 - and only if > they are members of "Internet Access". > > So far, I have been able to have these PCs update which user ID is logging > in to which machine, to a central SQL database. > > So thus far:- > > SQL Server has a database called "WSDB" > > in WSDB there are tables called "Room1", Room2", "Room3", "Room4" and > "Room5". > > In "Room1" (for example) there are 28 fields - R101, R102, R103.... R128. > > Meaning Room 1 PC 01, Room 1, PC 02, etc. > > In the R101 field (for example), when a student logs in, his/her login ID > is stored against R101 - "ST1041", meaning the R1 table, field R101 has > ST1041 user ID logging in to it. > > So, if the teacher taking Class 1, in Room 1 has decided NO student ID can > access the Internet for that Class, that student ID should be removed from > the Active Directory Internet access security group. Therefore, that > student ID cannot access the Internet through our ISA Server 2004, because > only members of the Internet Access security group can get through! > Hooray! > > So essentially I need this:- > > Teacher decides beforehand Room 1 Period 1 No Internet Access required > (and selects "No" in a database). > > Student logs in on R104 Period 1. > > My little program updates WSDB database/R1 table/R104 field with the > student login info. > > Trigger is tripped - R104 field in table R1 in database WSDB is updated. > > SQL trigger checks whether R1 Period 1 Internet access denied. > > If yes (denied), remove student login ID from Internet Access security > group. > > At end of Class 1, automatically (based on time-of-day) restore all > student accounts to Internet Access security group. > > Can any of this stuff be done within SQL Server 2000? (As in, execute a > query/trigger/whatever?) > > This is my first ever go at playing with SQL Server beyond creating a > database - muddling around I could probably get something to go... > > Could the above scenario be got to work? > > Mark > > I suspect it would be possible to do such a thing... perhaps invoking
xp_cmdshell then using one of the NET commands to do the AD thing.. You might consider instead of removing the students from the Internet Access permission group, create another group which is the DENY Internet Access group... Always leave them in the Access group, but temporarily place them in the deny group, and delete them from the Deny group when class is over... That way if you ever get messed up somehow, you can simply delete everyone from the Deny group and start again... (Leaving the Access group safe and unchanged.) There is also a way to setup an AD linked server (which I haven't done), or may use something in WMI to make this easier... But I do not use either, so perhaps someone else would have a suggestion there... At first I was thinking why not just do the work when the teacher says Deny for the entire class, but that would deny a student for the internet even if he was NOT in class.... So in your plan, a student would only be denied the he attended class, but if the class was skipped (perhaps to complete some other project) , they would not be denied... - good idea... -- Show quoteWayne Snyder MCDBA, SQL Server MVP Mariner, Charlotte, NC (Please respond only to the newsgroup.) I support the Professional Association for SQL Server ( PASS) and it's community of SQL Professionals. "Mark B" <m***@mosaiccomputers.com.au> wrote in message news:eZzuo0tWFHA.3280@TK2MSFTNGP09.phx.gbl... > Hi guys, > > I've just invented a fabulous way of barring students from accessing the > Internet at the school of which I'm the network administrator. > > Question is, can it be done? > > Essentially, if - in a classroom environment - the teacher has decided > beforehand the students in the class do not need to access the Internet for > that class time, the teacher should be able to revoke *any student ID* from > Internet access *that logs on at any of those class room PCs*. Sounds harsh? > Having Internet access where none is needed is a GIGANTIC distraction for > kids who SHOULD be doing something else entirely. More fierce supervision - > probably, but that's not my call, and my solution is much more elegant, and > final. > > The classroom PCs run Windows 98 and Windows XP, depending on the CPU > capability. Students access the Internet via ISA Server 2004 - and only if > they are members of "Internet Access". > > So far, I have been able to have these PCs update which user ID is logging > in to which machine, to a central SQL database. > > So thus far:- > > SQL Server has a database called "WSDB" > > in WSDB there are tables called "Room1", Room2", "Room3", "Room4" and > "Room5". > > In "Room1" (for example) there are 28 fields - R101, R102, R103.... R128. > > Meaning Room 1 PC 01, Room 1, PC 02, etc. > > In the R101 field (for example), when a student logs in, his/her login ID is > stored against R101 - "ST1041", meaning the R1 table, field R101 has ST1041 > user ID logging in to it. > > So, if the teacher taking Class 1, in Room 1 has decided NO student ID can > access the Internet for that Class, that student ID should be removed from > the Active Directory Internet access security group. Therefore, that student > ID cannot access the Internet through our ISA Server 2004, because only > members of the Internet Access security group can get through! Hooray! > > So essentially I need this:- > > Teacher decides beforehand Room 1 Period 1 No Internet Access required (and > selects "No" in a database). > > Student logs in on R104 Period 1. > > My little program updates WSDB database/R1 table/R104 field with the student > login info. > > Trigger is tripped - R104 field in table R1 in database WSDB is updated. > > SQL trigger checks whether R1 Period 1 Internet access denied. > > If yes (denied), remove student login ID from Internet Access security > group. > > At end of Class 1, automatically (based on time-of-day) restore all student > accounts to Internet Access security group. > > Can any of this stuff be done within SQL Server 2000? (As in, execute a > query/trigger/whatever?) > > This is my first ever go at playing with SQL Server beyond creating a > database - muddling around I could probably get something to go... > > Could the above scenario be got to work? > > Mark > > > in WSDB there are tables called "Room1", Room2", "Room3", "Room4" and Do they teach database design at your school? Seriously, this is a very> "Room5". > In "Room1" (for example) there are 28 fields - R101, R102, R103.... R128. poor desgn. In fact it would make a good textbook example of how NOT to do it. > Can any of this stuff be done within SQL Server 2000? (As in, execute You can't execute a trigger on login. You would need to create your owna > query/trigger/whatever?) process to do that. -- David Portas SQL Server MVP -- I pitty the help desk guy who is trying to figure out why some students are
sporatically having trouble accessing the internet. It would be easier for the teacher to simply pull the network cable from the classroom's router before class starts and then plug it back in after the class ends. The network admin should be able to implement a convenient on / off switch for teachers to use. Show quote "Mark B" <m***@mosaiccomputers.com.au> wrote in message news:eZzuo0tWFHA.3280@TK2MSFTNGP09.phx.gbl... > Hi guys, > > I've just invented a fabulous way of barring students from accessing the > Internet at the school of which I'm the network administrator. > > Question is, can it be done? > > Essentially, if - in a classroom environment - the teacher has decided > beforehand the students in the class do not need to access the Internet for > that class time, the teacher should be able to revoke *any student ID* from > Internet access *that logs on at any of those class room PCs*. Sounds harsh? > Having Internet access where none is needed is a GIGANTIC distraction for > kids who SHOULD be doing something else entirely. More fierce supervision - > probably, but that's not my call, and my solution is much more elegant, and > final. > > The classroom PCs run Windows 98 and Windows XP, depending on the CPU > capability. Students access the Internet via ISA Server 2004 - and only if > they are members of "Internet Access". > > So far, I have been able to have these PCs update which user ID is logging > in to which machine, to a central SQL database. > > So thus far:- > > SQL Server has a database called "WSDB" > > in WSDB there are tables called "Room1", Room2", "Room3", "Room4" and > "Room5". > > In "Room1" (for example) there are 28 fields - R101, R102, R103.... R128. > > Meaning Room 1 PC 01, Room 1, PC 02, etc. > > In the R101 field (for example), when a student logs in, his/her login ID is > stored against R101 - "ST1041", meaning the R1 table, field R101 has ST1041 > user ID logging in to it. > > So, if the teacher taking Class 1, in Room 1 has decided NO student ID can > access the Internet for that Class, that student ID should be removed from > the Active Directory Internet access security group. Therefore, that student > ID cannot access the Internet through our ISA Server 2004, because only > members of the Internet Access security group can get through! Hooray! > > So essentially I need this:- > > Teacher decides beforehand Room 1 Period 1 No Internet Access required (and > selects "No" in a database). > > Student logs in on R104 Period 1. > > My little program updates WSDB database/R1 table/R104 field with the student > login info. > > Trigger is tripped - R104 field in table R1 in database WSDB is updated. > > SQL trigger checks whether R1 Period 1 Internet access denied. > > If yes (denied), remove student login ID from Internet Access security > group. > > At end of Class 1, automatically (based on time-of-day) restore all student > accounts to Internet Access security group. > > Can any of this stuff be done within SQL Server 2000? (As in, execute a > query/trigger/whatever?) > > This is my first ever go at playing with SQL Server beyond creating a > database - muddling around I could probably get something to go... > > Could the above scenario be got to work? > > Mark > > Hi,
Sorry guys, I got my terminologies confused - yes, it is one database with 5 tables, and in each table there are 28 columns. Those columns are labelled R101, R102, R103, etc. I'd love to have a router, but because there is already an ISA Server, a firewall router and an upstream ISP Proxy, there's just more places a packet could get lost. (Not to mention the cost - the public school finances can't justify it). That deny_internet group's a damn good idea. I might implement that in any case. I think my database design is OK. It's manageable, and simple.I hope the respondent was saying it was not good because I was confusing my terminologies. The database is pretty simple in that it only records what student login ID logs in on what computer (using an external program I have written). Therefore, when the appropriate computer ID column is updated with the student's login ID, that sets off a trigger. I figure I could then use that trigger to check whether Internet Access should be denied and if yes, remove that ID from the Internet Access security group. So. Database = WSDB Table = R1 Columns = R101 R102 R103 R104 R104 R106 R107 .... R128 Should a trigger be applied to the table? Or can a trigger be applied to each column? Frogive my newbie questions. You're right - I should get a book, but the community is much faster (and more accurate!) Mark This database design is what is known as "denormalized", and it's not good.
Let's say you have up to R128 right now. What happens when they want to add another room? Now you have to change your schema to accomodate it. Let's say they like it so much they decide to add the whole second floor (R201 to R228). Do you really want to keep changing your schema and re-writing the front-end program so it can handle all the new rooms? How are you going to get the list of rooms that are currently "Active" and those that are "Inactive"? Normalization gives you flexibity, like this: CREATE TABLE Rooms ( Room_Number VARCHAR(4) PRIMARY KEY CLUSTERED, Status CHAR(1) ) GO INSERT INTO Rooms (Room_Number, Status) VALUES ('R101', 'A') INSERT INTO Rooms (Room_Number, Status) VALUES ('R102', 'A') INSERT INTO Rooms (Room_Number, Status) VALUES ('R103', 'I') INSERT INTO Rooms (Room_Number, Status) VALUES ('R104', 'A') GO In this instance, Rooms R101, R102 and R104 are set to 'A' for Active, Room R103 is set to 'I' for Inactive. Of course you can set up your own codes, etc. This can be easily expanded to include separate buildings, etc., and you can add as many rooms as you can find 4-digit identifiers for. Show quote "Mark B" <m***@mosaiccomputers.com.au> wrote in message news:e69b2czWFHA.3076@TK2MSFTNGP12.phx.gbl... > Hi, > > Sorry guys, I got my terminologies confused - yes, it is one database with > 5 tables, and in each table there are 28 columns. Those columns are > labelled R101, R102, R103, etc. > > I'd love to have a router, but because there is already an ISA Server, a > firewall router and an upstream ISP Proxy, there's just more places a > packet could get lost. (Not to mention the cost - the public school > finances can't justify it). > > That deny_internet group's a damn good idea. I might implement that in any > case. > > I think my database design is OK. It's manageable, and simple.I hope the > respondent was saying it was not good because I was confusing my > terminologies. > > The database is pretty simple in that it only records what student login > ID logs in on what computer (using an external program I have written). > > Therefore, when the appropriate computer ID column is updated with the > student's login ID, that sets off a trigger. > > I figure I could then use that trigger to check whether Internet Access > should be denied and if yes, remove that ID from the Internet Access > security group. > > So. > > Database = WSDB > > Table = R1 > > Columns = R101 R102 R103 R104 R104 R106 R107 .... R128 > > Should a trigger be applied to the table? Or can a trigger be applied to > each column? Frogive my newbie questions. You're right - I should get a > book, but the community is much faster (and more accurate!) > > Mark >  |
|||||||||||||||||||||||
Other interesting topics