General information
Forum:
Microsoft SQL Server
Title:
Row Level Security or VPD on SQL Server
Oracle has a feature called Row Level Security..
When you can tag a where clause onto each table for different users and limit the access. I.E. So that different users can see different records (hide records from different users or roles). I am wondering if such a feature exists on SQL Server 2000. If so what's it called.
For reference here is the oracle code:
--Create COntext namespace
CREATE OR REPLACE CONTEXT QuestPDM USING SET_APP_ROLE;
REM Procedure to Set Application Roles Called iN login Trigger...
CREATE OR REPLACE PROCEDURE SET_APP_ROLE(
p_username in varchar2 default sys_context('userenv','session_user'))
AS
BEGIN
--set context username
dbms_session.set_context('QuestPDM','UserName',p_username);
--find role
for x in (select granted_role from user_role_privs) loop
--set context for each role
dbms_session.set_context('QuestPDM','RoleName',x.granted_role);
end loop;
END;
--Make Automatic with Logon trigger
CREATE OR REPLACE TRIGGER QUESTPDM_LOGON
AFTER LOGON ON DATABASE
BEGIN
SET_APP_ROLE;
END;
--use this to test when logged in with the user...
select * from session_context;
CREATE OR REPLACE FUNCTION PREDICATE_ARTICLE
(p_schema in varchar2, p_object in varchar2) return varchar2
AS
l_arti_pred varchar2(100);
BEGIN
IF (sys_context('QuestPDM','RoleName')='PDMLVMH')
THEN
l_arti_pred:='artno='||''''||'TIS'||'''';
ELSIF (sys_context('QuestPDM','RoleName')='PDMKENZO')
THEN
l_arti_pred:='';
ELSE
l_arti_pred:='';
END IF;
RETURN l_arti_pred;
END;
EXECUTE DBMS_RLS.ADD_POLICY ('LVMH', 'ARTICLE', 'ARTICLE_POLICY', 'LVMH', 'PREDICATE_ARTICLE', 'update,select,delete,insert');
Next
Reply
View the map of this thread
View the map of this thread starting from this message only
View all messages of this thread
View all messages of this thread starting from this message only