Information générale
Forum:
Microsoft SQL Server
Titre:
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');
Suivant
Répondre
Voir le fil de ce thread
Voir le fil de ce thread à partir de ce message seulement
Voir tous les messages de ce thread
Voir tous les messages de ce thread à partir de ce message seulement