Problem with "WITH CHECK OPTION"
-
We have a peculiar problem with views. We created a view with check option. We tried to update a record, by which the new record will not come in the scope of view. Because we created the view with "WITH CHECK OPTION" we are under assumption that the record will not get updated using the view. But the view is allowing us to update the record using the view. The view definition contains some subqueries. Here is an example
Create view v1 as
select * from employee where employee_id in
(select employee_id from employee where lastname in (select lastname from users where userid='user'))
or employee_id in
(select employee_id from employee where firstname in (select firstname from users where userid='user'))
or employee_id in
(select employee_id from employee where middlename in (select middlename from users where userid='user'))
WITH CHECK OPTION
The employee table contains a employee called "VictoriaADerham"
(LastName MiddleName FirstName) which we can see using the view.
update V1 set middlename='B', lastname = 'Eerham', firstname ='Xictoria'
WHERE employee_id ='CE6200DB-EA28-4A9B-A00E-76DA6BAE0B88'
The above statement should not update the table, but it is updating. Any ideas on this, why it is updating?
-
I'm not absolutely clear from the example if this is what you need. I'm assuming userid in users links to employee_id in employee, in which case creating a view something like the following will cause the update you specify to fail:
Create view v1 as
select * from employee e where lastname in
(select lastname from users where userid=e.employee_id)
or firstname in
(select firstname from users where userid=e.employee_id)
or middlename in
(select middlename from users where userid=e.employee_id)
WITH CHECK OPTIONIf my assumption's wrong, can you clarify the purpose of the userid field? Maybe provide example data for both users & employee tables.
Cheers,
mia
Life moves pretty fast. If you don't stop and look around once in a while, you could miss it.
Cheers,
mia
Life moves pretty fast. If you don't stop and look around once in a while, you could miss it. -
The actual view definition is
CREATE view V1 as
SELECT
EMPLOYEEDETAILS.EMPLOYEEDETAILS_ID,EMPLOYEEDETAILS.FIRSTNAME,EMPLOYEEDETAILS.MIDDLENAME,EMPLOYEEDETAILS.LASTNAME,EMPLOYEEDETAILS.ADDRESS1,EMPLOYEEDETAILS.AGE,EMPLOYEEDETAILS.COMPANYOFFICE,EMPLOYEEDETAILS.DATEOFBIRTH,EMPLOYEEDETAILS.GRADE,EMPLOYEEDETAILS.HEALTHASSESSMENT,EMPLOYEEDETAILS.LOCATION,EMPLOYEEDETAILS.REPORTSTO
FROM EMPLOYEEDETAILS WHERE
EMPLOYEEDETAILS.EMPLOYEEDETAILS_ID IN
(
SELECT EMPLOYEEDETAILS.EMPLOYEEDETAILS_ID FROM EMPLOYEEDETAILS
WHERE EMPLOYEEDETAILS.MIDDLENAME IN
(select EMPLOYEEDETAILS.MIDDLENAME
from users
join EMPLOYEEDETAILS on users.associatedrecordid = EMPLOYEEDETAILS.EMPLOYEEDETAILS_ID
where USERS.UserID = 'USER')
)
or EMPLOYEEDETAILS.EMPLOYEEDETAILS_ID IN
(
SELECT EMPLOYEEDETAILS.EMPLOYEEDETAILS_ID FROM EMPLOYEEDETAILS
WHERE EMPLOYEEDETAILS.FIRSTNAME IN
(select EMPLOYEEDETAILS.FIRSTNAME
from users
join EMPLOYEEDETAILS on users.associatedrecordid = EMPLOYEEDETAILS.EMPLOYEEDETAILS_ID
where USERS.UserID = 'USER')
)
or EMPLOYEEDETAILS.EMPLOYEEDETAILS_ID IN
(
SELECT EMPLOYEEDETAILS.EMPLOYEEDETAILS_ID FROM EMPLOYEEDETAILS
WHERE EMPLOYEEDETAILS.LASTNAME IN
(select EMPLOYEEDETAILS.LASTNAME
from users
join EMPLOYEEDETAILS on users.associatedrecordid = EMPLOYEEDETAILS.EMPLOYEEDETAILS_ID
where USERS.UserID = 'USER')
) WITH CHECK OPTION
The users table contains userid, associatedrecordid. The associatedrecordid is linked with employeedetails_id of employeedetails.
We are developing security module and we are dynamically generating the sql, so the above sql may not be tuned properly, but this satisfies the result. The above view will allow the user to see only the records pertain to "USER". We have a function which gives the USERNAME, later we will replace userid="FunctionName".
-
I presume you mean to select from USERS in the innermost nested select statement (as indicated in your first example)?
i.e.
select USERS.FIRSTNAME
from users
join EMPLOYEEDETAILS on users.associatedrecordid = EMPLOYEEDETAILS.EMPLOYEEDETAILS_ID
where USERS.UserID = 'USER'and not
select EMPLOYEEDETAILS.FIRSTNAME
from users
join EMPLOYEEDETAILS on users.associatedrecordid = EMPLOYEEDETAILS.EMPLOYEEDETAILS_ID
where USERS.UserID = 'USER'Otherwise, the record won't disappear from the view when updated to 'Xictoria B Eerham'.
I don't know why with check option isn't working here, but I do know that if I remove the unnecessary extra nested select from the equation as follows
...
WHERE
EMPLOYEEDETAILS.MIDDLENAME IN
(select USERS.MIDDLENAME
from users
join EMPLOYEEDETAILS on users.associatedrecordid = EMPLOYEEDETAILS.EMPLOYEEDETAILS_ID
where USERS.UserID = 'USER')
or EMPLOYEEDETAILS.FIRSTNAME IN
(select USERS.FIRSTNAME
from users
join EMPLOYEEDETAILS on users.associatedrecordid = EMPLOYEEDETAILS.EMPLOYEEDETAILS_ID
where USERS.UserID = 'USER')
...I then get an error message when attempting to create the view - View 'V1' is not updatable because either it was created WITH CHECK OPTION or it spans a view created WITH CHECK OPTION and the target table is referenced multiple times in the resulting query.
Perhaps the clue to your problem lies in this error message? Maybe WITH CHECK OPTION doesn't like the nested select involving a table that is part of the view??
Cheers,
mia
Life moves pretty fast. If you don't stop and look around once in a while, you could miss it.
Cheers,
mia
Life moves pretty fast. If you don't stop and look around once in a while, you could miss it. -
The USERS table is having only userid and associatedrecordid. Userid is a loginname. associatedrecordid is uniqueidentifier data type. Not only this, we have another problem. That is if I issue the following command
select * from v1 where employeedetails_id ='CE6200DB-EA28-4A9B-A00E-76DA6BAE0B88'
It will return 0 results. But If you say select * from v1 it retrieves all the records including 'CE6200DB-EA28-4A9B-A00E-76DA6BAE0B88'. Otherthan "equals to" operator other operators are working fine. The above view is developed to see the details of other employees whose firstname, middlename, lastname similar to the login user. We have a function which gives the login id. In the above view we hardcoded the login id as "USER".
-
I still don't know why 'with check option' isn't working. The only way I can get the with check option to work with your query is by doing it the following way instead:
create view V1 as
select
e.EMPLOYEEDETAILS_ID
,e.FIRSTNAME FIRSTNAME
,e.MIDDLENAME MIDDLENAME
,e.LASTNAME LASTNAME
from EMPLOYEEDETAILS e
where e.MIDDLENAME IN
(select users.MIDDLENAME
from users
where users.associatedrecordid = e.EMPLOYEEDETAILS_ID
and USERS.UserID = 'USER'
)
or e.FIRSTNAME in
(select users.FIRSTNAME
from users
where users.associatedrecordid = e.EMPLOYEEDETAILS_ID
and USERS.UserID = 'USER'
)
or e.LASTNAME in
(select users.LASTNAME
from users
where users.associatedrecordid = e.EMPLOYEEDETAILS_ID
and USERS.UserID = 'USER'
)
with check optionI know you are generating the code automatically, but you may have to reconsider whether the auto-generated code is what you need if you can't find a way to get your view to operate correctly using 'with check option'.
As to the 'equals' comparison using the uniqueidentifier datatype I'm not familiar with that datatype. The comparison works OK for me. Perhaps start another thread for that question??
Sorry I can't be of more help!
Cheers,
mia
Life moves pretty fast. If you don't stop and look around once in a while, you could miss it.
Cheers,
mia
Life moves pretty fast. If you don't stop and look around once in a while, you could miss it. -
just my 2ct
Could you elaborate why your update statement would not qualify to be done using this view ?
If the update works, I presume you'll still find these data through this view, right ? If yes: So (its correct
) ? if No: contact pss.Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
Interestingly enough, if you change the reference in the innermost subquery from EMPLOYEEDETAILS.<Name> to USERS.<Name> (which is what I think ramakrishna100 intends from the first posting), the update succeeds, but the row subsequently disappears from the view. Something seems to prevent the 'with check option' from intercepting the update. Haven't a clue what though! In the absence of an explanation or way to fix that query, I'd suggest a less-nested version as that seems to work fine.
Cheers,
mia
Life moves pretty fast. If you don't stop and look around once in a while, you could miss it.
Cheers,
mia
Life moves pretty fast. If you don't stop and look around once in a while, you could miss it. -
Is it possible that another row with middlename equal to 'B' is allowing the check option to be met.
One suggestion would be to try EXIST rather than IN.
) ? if No: contact pss.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply