Em có viết 1 số Proc trong SQL có tham số để trả về 1 bảng danh sách.
Vậy làm thế nào để em có thể lấy được nó về ạ. Em chỉ thấy table khi connect đến Slq server thôi ạ.
Và nếu có thì điều này có bảo mật không ạ. File excel này còn được gửi đi cho những người khác nữa ạ.
Em cảm ơn!
Em có viết 1 số Proc trong SQL có tham số để trả về 1 bảng danh sách.
Vậy làm thế nào để em có thể lấy được nó về ạ. Em chỉ thấy table khi connect đến Slq server thôi ạ.
Và nếu có thì điều này có bảo mật không ạ. File excel này còn được gửi đi cho những người khác nữa ạ.
Em cảm ơn!
Đây là code mình đang sử dụng cho file của mình, bạn có thể xử lý tiếp rs như câu truy vấn thông thường.
Mã:
Command.ActiveConnection = cn
Command.CommandTimeout = 60
Command.CommandType = adCmdText
Command.CommandText = "sl_ListHocSinh '""& Biến tương ứng với Biến @ma trong SP &""'"
Set rs = Command.Execute
Nếu bạn có quyền trong database thì có thể tạo một 1 user chỉ có quyền thực thi các câu lệnh SP thôi, sau đó bỏ user đó vào câu truy vấn kết nối đến database, đây là cách bảo mật dữ liệu hiện tại của mình, người dùng có thể biết code VBA, nhưng dữ liệu trên database thì sẽ không xem được hết.
Đây là code mình đang sử dụng cho file của mình, bạn có thể xử lý tiếp rs như câu truy vấn thông thường.
Mã:
Command.ActiveConnection = cn
Command.CommandTimeout = 60
Command.CommandType = adCmdText
Command.CommandText = "sl_ListHocSinh '""& Biến tương ứng với Biến @ma trong SP &""'"
Set rs = Command.Execute
Nếu bạn có quyền trong database thì có thể tạo một 1 user chỉ có quyền thực thi các câu lệnh SP thôi, sau đó bỏ user đó vào câu truy vấn kết nối đến database, đây là cách bảo mật dữ liệu hiện tại của mình, người dùng có thể biết code VBA, nhưng dữ liệu trên database thì sẽ không xem được hết.
CommandType là adCmdStoredProc
CommandText là tên của SP (stored procedure)
Sau đó phải refresh Parameters, và set từng Parameter.
Đối với các kết nối vung văng (cho phép ADO kết nối như vậy có thể coi là vung văng, không đáng tin cậy), mục đích chính của SP là giới hạn quyền truy cập và chống SQL Injection.
SQL Injection là một kỹ thuật dùng tham số sửa đổi câu lệnh CommandText để phá hoại CSDL. SP chỉ nhận tham số qua parameters cho nên người phá hoại không thể sửa đổi câu lệnh SQL bên trong SP.
Đây là code mình đang sử dụng cho file của mình, bạn có thể xử lý tiếp rs như câu truy vấn thông thường.
Mã:
Command.ActiveConnection = cn
Command.CommandTimeout = 60
Command.CommandType = adCmdText
Command.CommandText = "sl_ListHocSinh '""& Biến tương ứng với Biến @ma trong SP &""'"
Set rs = Command.Execute
Nếu bạn có quyền trong database thì có thể tạo một 1 user chỉ có quyền thực thi các câu lệnh SP thôi, sau đó bỏ user đó vào câu truy vấn kết nối đến database, đây là cách bảo mật dữ liệu hiện tại của mình, người dùng có thể biết code VBA, nhưng dữ liệu trên database thì sẽ không xem được hết.
Cảm ơn bạn nhé, để mình thử xem ạ.
Hiện tại server đó là mình quản lý bạn à. Vì ứng dụng viết trên Access dùng nội bộ và cả qua Internet, Vì mình cứ phải lấy dữ liệu qua access sau đó tải ra file excel sau đó thực hiện copy các data thô vào 1 file excel mà đã xây dựng sẵn để refrest là dữ liệu sẽ có như đã xây dựng,
nên mình muốn là khi nào mở file excel ra thì refresh tại excel luôn không bị mất nhiều thao tác
CommandType là adCmdStoredProc
CommandText là tên của SP (stored procedure)
Sau đó phải refresh Parameters, và set từng Parameter.
Đối với các kết nối vung văng (cho phép ADO kết nối như vậy có thể coi là vung văng, không đáng tin cậy), mục đích chính của SP là giới hạn quyền truy cập và chống SQL Injection.
SQL Injection là một kỹ thuật dùng tham số sửa đổi câu lệnh CommandText để phá hoại CSDL. SP chỉ nhận tham số qua parameters cho nên người phá hoại không thể sửa đổi câu lệnh SQL bên trong SP.
Nếu tôi trả lời thẳng câu này là tôi nói dóc.
Muốn biết "ổn" (an ninh) hay không thì phải biết thiết kế của Server. Chỉ có người Admin của Server mới trả lời được.
Nếu CSDL chứa trong file Excel là căn hộ nhỏ thì SQL Server Database là một cơ quan làm việc.
Cho phép một user kết nối là bạn đã giao một chùm chìa khoá cơ quan. Số chìa khoá tuỳ theo số quyền của user, nhưng chùm nào cũng phải có cái chìa mở cổng chính.
Vì vậy cơ quan luon phải cảnh giác 2 điều sau:
1. user lạm dụng quyền
2. kẻ xấu ăn cắp chùm chìa khoá từ user (hoặc là bạn bè, bám theo) để vào cơ quan. Điểm này quan trọng hơn điểm trên, vì kẻ xấu này rất nhiều khả năng là giỏi hơn bạn.
Những nơi tôi làm việc, SQL Server được bảo vệ như thành trì. Một user phải có văn bản trưởng phòng yêu cầu Admin cho phép kết nối và văn bản nêu rõ phạm vi truy cập. Nếu user không phải là developer thì Adimm buộc phải qua một buổi huấn luyện về sử dụng. Admin thường xuyên phân tích cái log để kiểm soát những hoạt động của users.
Nếu tôi trả lời thẳng câu này là tôi nói dóc.
Muốn biết "ổn" (an ninh) hay không thì phải biết thiết kế của Server. Chỉ có người Admin của Server mới trả lời được.
Nếu CSDL chứa trong file Excel là căn hộ nhỏ thì SQL Server Database là một cơ quan làm việc.
Cho phép một user kết nối là bạn đã giao một chùm chìa khoá cơ quan. Số chìa khoá tuỳ theo số quyền của user, nhưng chùm nào cũng phải có cái chìa mở cổng chính.
Vì vậy cơ quan luon phải cảnh giác 2 điều sau:
1. user lạm dụng quyền
2. kẻ xấu ăn cắp chùm chìa khoá từ user (hoặc là bạn bè, bám theo) để vào cơ quan. Điểm này quan trọng hơn điểm trên, vì kẻ xấu này rất nhiều khả năng là giỏi hơn bạn.
Những nơi tôi làm việc, SQL Server được bảo vệ như thành trì. Một user phải có văn bản trưởng phòng yêu cầu Admin cho phép kết nối và văn bản nêu rõ phạm vi truy cập. Nếu user không phải là developer thì Adimm buộc phải qua một buổi huấn luyện về sử dụng. Admin thường xuyên phân tích cái log để kiểm soát những hoạt động của users.
Vâng. Em cảm ơn anh.
Hiện tại SQL SV em đang dùng là em cài đặt luôn trên 1 PC mà em làm việc. Về vấn đề bảo mật thì đúng là em không hiểu gì nhiều và thực chất cũng không có nhiều kiến thức để tìm hiểu và làm cho nó an toàn. Ban đầu chỉ là xây dựng 1 ứng dụng nhỏ tiện mình quản lý, càng sau càng nâng cấp để người khác dùng được và dùng qua được cả internet, để đến được từ bước xấy dựng code và cách kết nối đến SQL SV cũng là 1 trạng đường dài để tìm hiểu. Về vấn đề an toàn thì e chỉ có thể backup data hàng ngày để đề phòng vì e nghĩ dữ liệu này chỉ quan trọng với e và 1 số đồng nghiệp vì nếu mất thì không có gì để sử dụng tiếp theo. Còn với người khác thì chắc không có ý nghĩa
Các Form nhập liệu đầu vào em cũng sử dụng parameter và xử lý các ký tự đặc biết để tránh người dùng vô tình hoặc gì thôi ạ.
Vậy anh có thể cho em hỏi. Nếu khi sử dụng qua internet và các file excel hoặc front end được kết nối đến server mà chỉ toàn người trong cty sử dụng (đa phần k biết gì về data) thì những người mà k có ứng dụng e xây dựng kết nối đến SQL server thì có thể phá hoại được CSDL của em không ạ?
Vâng. Em cảm ơn anh.
Hiện tại SQL SV em đang dùng là em cài đặt luôn trên 1 PC mà em làm việc. Về vấn đề bảo mật thì đúng là em không hiểu gì nhiều và thực chất cũng không có nhiều kiến thức để tìm hiểu và làm cho nó an toàn. ...
Nếu nó chỉ trên một PC thì vấn đề bảo mật và an toàn không quan trọng.
Chỉ quan trọng ở chỗ cần backup thường xuyên. Nếu backup qua mạng được thì càng tốt.
Nếu bị attacked thì bạn có thể recover và reconfigure dễ dàng.
Vậy anh có thể cho em hỏi. Nếu khi sử dụng qua internet và các file excel hoặc front end được kết nối đến server mà chỉ toàn người trong cty sử dụng (đa phần k biết gì về data) thì những người mà k có ứng dụng e xây dựng kết nối đến SQL server thì có thể phá hoại được CSDL của em không ạ?
File font end là Excel hay Access điều phải có cái hàm để kết nối tới SQL Server mà hàm này tất nhiên phải chứa thông tin username và pass đăng nhập SQL Server. Khi có 2 thông tin này thì người ta có thể dùng lệnh "Select.." để liệt kê các Table có trong SSV và lấy dữ liệu về xem.
Để khoá không cho xem code thì Access chỉ cần chuyển sang .accde là không xem được, còn Excel thì bạn phải phải kiếm tool, hàm để khoá xem VBA code, mã hoá phần thông tin đăng nhập này,
CommandType là adCmdStoredProc
CommandText là tên của SP (stored procedure)
Sau đó phải refresh Parameters, và set từng Parameter.
Đối với các kết nối vung văng (cho phép ADO kết nối như vậy có thể coi là vung văng, không đáng tin cậy), mục đích chính của SP là giới hạn quyền truy cập và chống SQL Injection.
Em đã thử kiểu khai báo như trên và set Parameter thì cho thời gian trả về kết quả lâu hơn so với việc truy vấn theo text (cùng 1 SP), nên em chọn các tham số kia. Ở VBA em sẽ tạo các hàm để kiểm duyệt các biến của user nhập vào sao cho đúng với kiểu biến nạp vào SP, nếu user nhập vào biến không hợp lệ thì sẽ không thực thi câu lệnh , như thế sẽ nhanh hơn và hiệu quả hơn.
Em đã thử kiểu khai báo như trên và set Parameter thì cho thời gian trả về kết quả lâu hơn so với việc truy vấn theo text (cùng 1 SP), nên em chọn các tham số kia. Ở VBA em sẽ tạo các hàm để kiểm duyệt các biến của user nhập vào sao cho đúng với kiểu biến nạp vào SP, nếu user nhập vào biến không hợp lệ thì sẽ không thực thi câu lệnh , như thế sẽ nhanh hơn và hiệu quả hơn.
"lâu" đối với tôi không phải là một yếu tố. "lâu" ở một hệ thống này vẫn có thể "nhanh" ở hệ thống khác.
Sử dụng adCmdStoredProc là cách chính thống để gọi SP
Dùng text là cách đi vòng. Vì nó là full text cho nên không đủ an toàn.
Tuy nhiên, như toi đã nói ở bài #10, nếu nó chỉ là hệ thống cá nhân thì chả cần an toàn. Và trừ phi cái SP rất lớn, xài dynamic SQL luôn cho khoẻ.
"lâu" đối với tôi không phải là một yếu tố. "lâu" ở một hệ thống này vẫn có thể "nhanh" ở hệ thống khác.
Sử dụng adCmdStoredProc là cách chính thống để gọi SP
Dùng text là cách đi vòng. Vì nó là full text cho nên không đủ an toàn.
Lâu đối với thầy không phải là một yếu tố, nhưng với em là một yếu tố, đôi khi đối với user cũng là một yếu tố để quyết định có sử dụng sản phẩm đó không.
Em đồng ý với thầy sử dụng adCmdStoredProc là cách chính thống để gọi SP, nhưng đôi khi con đường chính lại về nhà chậm hơn so với mấy con đường vòng, thầy có thể cho em biết được việc sử dụng full text không đủ an toàn ở điểm nào không, người dùng (không có ý định phá hoại) có thể làm được những gì? người dùng (có ý định phá hoại) có thể làm được những gì?
Em thiết kế database cho công ty khoảng 20 người sử dụng, nên việc bảo mật em cũng quan tâm. Hệ thống cá nhân thì chả cần an toàn nhưng em nghĩ nên tìm hiểu và sử dụng SP thay vì dynamic SQL, vì nếu câu lệnh đó được sử dụng nhiều nơi với nhiều điều kiện khác nhau trong file excel thì mỗi lần muốn chỉnh sửa lại tốn nhiều thời gian hơn so với sửa trên SP (Trước đây em đã viết kiểu như vậy và việc chỉnh sửa khá mất thời gian nên em mới tìm hiểu đến SP), chưa kể đến những ưu điểm của SP so với truy vấn thông thường.
... nếu câu lệnh đó được sử dụng nhiều nơi với nhiều điều kiện khác nhau trong file excel thì mỗi lần muốn chỉnh sửa lại tốn nhiều thời gian hơn so với sửa trên SP ...
SP chỉ dùng cho các truy vấn đã được thành hình. Một khi đã được dùng vào code rồi thì rất ít khi người ta chỉnh sửa nó. Các hệ thống lớn đều có cache lệnh sử lý của SP.
Tuy theo lý thuyết, chỉnh SP là tất cả mọi code gọi nó đều xài được nhưng trên thực tế, bạn chỉ chỉnh sửa được cách làm việc bên trong thôi, cái giao diện (interface: tức là parameters và result set) thì bạn phải giữ. Nếu nó thay đổi tínhn chất hoặc cấu trúc kết quả hoặc giao diện thì mọi code gọi nó cũng phải chỉnh sửa như thường (thêm bớt parameters, thêm bớt số cột trả về...)
Trên thực tế, khi chỉnh sửa một SP, người ta đặt luôn một cái tên mới và hẹn ngày decommission cái cũ.
Ví dụ: Bạn có một cái SP nhiều người dùng. Mỗi lần bạn sửa nó bạn phải báo cho tất cả những người dùng nó. Code của tôi gọi SP của bạn. Bạn sửa SP, quên báo cho tôi biết thì sao? Code tôi có khả năng lấy kết quả sai (đúng với bạn, nhưng sai với tôi) mà tôi không hề hay.
Em ví dụ như vầy, trong file Excel có nhiều chỗ truy vấn tới bảng danh sách học sinh (tùy vào mục đích của người dùng khác nhau) với các câu lệnh như sau:
SELECT MaHocSinh,TenHocSinh FROM DanhSachHocSinh -- Không có biến để nạp
SELECT MaHocSinh,TenHocSinh FROM DanhSachHocSinh WHERE GioiTinh=N'"& @BienNvarChar &"'
SELECT MaHocSinh,TenHocSinh,GioiTinh FROM DanhSachHocSinh WHERE Tuoi>='"& @BienInt &"'
SELECT MaHocSinh,TenHocSinh,GioiTinh,Lop FROM DanhSachHocSinh WHERE HienDien='"& @BienBit &"'
Vậy em sẽ viết câu SP như sau:
CREATE PROCEDURE DanhSachHocSinh _SELECT @option TINYINT, @BienNvarChar NVARCHAR(5) =NULL, @BienInt INT=NULL, @BienBit BIT=NULL
IF @option ='1'
SELECT MaHocSinh,TenHocSinh FROM DanhSachHocSinh
ELSE IF @option ='2'
SELECT MaHocSinh,TenHocSinh FROM DanhSachHocSinh WHERE GioiTinh=@BienNvarChar
ELSE IF @option ='3'
SELECT MaHocSinh,TenHocSinh,GioiTinh FROM DanhSachHocSinh WHERE Tuoi>=@BienInt
ELSE IF @option ='4'
SELECT MaHocSinh,TenHocSinh,GioiTinh,Lop FROM DanhSachHocSinh WHERE HienDien=@BienBit
Và tùy vào người dùng khác nhau mà 4 câu truy vấn ban đầu em sẽ thay đổi như sau:
DanhSachHocSinh _SELECT '1'
DanhSachHocSinh _SELECT '2',N'"& @BienNvarChar &"'
DanhSachHocSinh _SELECT '3',NULL,'"& @BienInt &"'
DanhSachHocSinh _SELECT '4',NULL,NULL,'"& @BienBit &"'
Vậy một cái SP nhiều người dùng. Mỗi khi người dùng nào đó báo lỗi hoặc muốn thay đổi kết quả trả về,em chỉ cần sửa nội dung trong @option tương ứng, nếu kết quả trả về thay đổi giao diện của người dùng đó( mà đó cũng do yêu cầu từ người dùng đó),em chỉ cần chỉnh sửa code và giao diện của người đó, em không cần phải báo cho người dùng khác biết. Và với câu truy vấn cho bảng DanhSachHocSinh đó, có phát sinh nhu cầu thêm từ người dùng, nếu truy vấn đã có thì em gọi lại, nếu kết quả trả về mới thì em chỉ cần tạo thêm @option khác. Đây là cách em đang thiết kế cho database và file của mình.
Nếu nó thay đổi tính chất hoặc cấu trúc kết quả hoặc giao diện thì mọi code gọi nó cũng phải chỉnh sửa như thường (thêm bớt parameters, thêm bớt số cột trả về...)
Em bắt chước các sub trong vba với các biến có thể để optional và áp dụng cho câu SP với biến để giá trị NULL như trên của mình, như thế sẽ linh động cho việc sử dụng SP.
Vậy em sẽ viết câu SP như sau:
CREATE PROCEDURE DanhSachHocSinh _SELECT @option TINYINT, @BienNvarChar NVARCHAR(5) =NULL, @BienInt INT=NULL, @BienBit BIT=NULL
IF @option ='1'
SELECT MaHocSinh,TenHocSinh FROM DanhSachHocSinh
ELSE IF @option ='2'
SELECT MaHocSinh,TenHocSinh FROM DanhSachHocSinh WHERE GioiTinh=@BienNvarChar
ELSE IF @option ='3'
SELECT MaHocSinh,TenHocSinh,GioiTinh FROM DanhSachHocSinh WHERE Tuoi>=@BienInt
ELSE IF @option ='4'
SELECT MaHocSinh,TenHocSinh,GioiTinh,Lop FROM DanhSachHocSinh WHERE HienDien=@BienBit
CREATE PROCEDURE TimKiemDSHocSinh
@GioiTinh CHAR(5)
@Tuoi INT
@HienDien BIT
WITH RECOMPILE
AS
SELECT A.*
FROM DanhSachHocSinh A
WHERE (@GioiTinh IS NULL OR A.GioiTinh = @GioiTinh)
AND (@Tuoi IS NULL OR A.Tuoi >= @Tuoi)
AND (@HienDien IS NULL OR A.HienDien = @HienDien)
Sau này muốn thêm điều kiện cũng thêm 1 dòng đơn giản hơn.
Còn về viêc dùng CommandType là adCmdStoreProc hay adCmdText nhanh chậm cũng là do cách thiết kế Store proc. Nếu Stored proc dùng SQl động sẽ nhanh hơn dùng SQL tĩnh.
Cái ví dụ trên là tôi dùng SQL tĩnh nên có thể khi chạy lần đầu sẽ nhanh, mấy lần sau sẽ châm, qua máy khác cũng vậy. Đó là do kiểu Stored Proc này tạo một cái Execution plan khi lần đầu chạy và lưu vào sp cache, những lần chạy sau nó lấy cái execution plan này ra chạy nhưng có thể không phù hợp với file dữ liệu lần sau nên tốc độ sẽ chậm. Để khắc phục tôi thêm "WITH RECOMPILE" để mỗi lần chạy là mỗi lần tạo Execution plan mới, tốc độ chăc chắn nhanh hơn nhưng hệ thống lại mất thêm thời gian tạo execution plan.
CREATE PROCEDURE TimKiemDSHocSinh
@GioiTinh CHAR(5)
@Tuoi INT
@HienDien BIT
WITH RECOMPILE
AS
SELECT A.*
FROM DanhSachHocSinh A
WHERE (@GioiTinh IS NULL OR A.GioiTinh = @GioiTinh)
AND (@Tuoi IS NULL OR A.Tuoi >= @Tuoi)
AND (@HienDien IS NULL OR A.HienDien = @HienDien)
Sau này muốn thêm điều kiện cũng thêm 1 dòng đơn giản hơn.
Còn về viêc dùng CommandType là adCmdStoreProc hay adCmdText nhanh chậm cũng là do cách thiết kế Store proc. Nếu Stored proc dùng SQl động sẽ nhanh hơn dùng SQL tĩnh.
Cái ví dụ trên là tôi dùng SQL tĩnh nên có thể khi chạy lần đầu sẽ nhanh, mấy lần sau sẽ châm, qua máy khác cũng vậy. Đó là do kiểu Stored Proc này tạo một cái Execution plan khi lần đầu chạy và lưu vào sp cache, những lần chạy sau nó lấy cái execution plan này ra chạy nhưng có thể không phù hợp với file dữ liệu lần sau nên tốc độ sẽ chậm. Để khắc phục tôi thêm "WITH RECOMPILE" để mỗi lần chạy là mỗi lần tạo Execution plan mới, tốc độ chăc chắn nhanh hơn nhưng hệ thống lại mất thêm thời gian tạo execution plan.
Mỗi khi thực thi câu lệnh mà thấy tộc độ lâu mình cũng thử nhiều phương pháp khác đề tìm nguyên nhân, và cũng sử dụng WITH RECOMPILE để chạy, có câu cải thiện tốc dộ hơn, nhưng mình so sánh ở đây là giữa 2 cách viết câu truy vấn thì việc set parameter cho nó lại khiến tốc độ chậm hơn (bạn có thể test thử xem, mình nhớ là nó chậm ở code VBA, lúc debug các dòng set parameter mình nhớ nó chậm,hình như chứ không phải là chậm lúc thực thi ), và mình cảm thấy việc set parameter cho nó cũng chỉ để kiểm tra tính đúng đắn của dữ liệu nạp vào, mình có thể tạo các hàm kiểm duyệt biến từ VBA để làm việc đó nên sử dụng adCmdText. Câu truy vấn của bạn mình có đọc đâu đó rồi, để mình tìm hiểu xem, nhưng câu truy vấn của bạn có bị giới hạn đối với nhu cầu người dùng về các field khác nhau không? Như ví dụ của mình chỉ trả về các field mà người dùng cần chứ không Select * , và mình cũng hạn chế select * vì nó làm chậm .
