/*
****************************************
calculate security level
if a user is null then we have to use
security level = defaullt value
if a user is linked to a profile then  we have to
use the creation user level
*/
CREATE function ols_fn_getAreaSecLevelByUser
( @_uri        nvarchar(255)
)
returns  integer
begin

    declare @linkCnt int;

    --- check if user is set
    if ( @_uri is null )
    begin
        -- have to use the config value
        return 4;
    end;

    set @linkCnt = ( select  count(*)
                        from ca_security_profile sp,
                            ca_link_dis_user_sec_profile usp,
                            ca_discovered_user u
                        where  u.uri = @_uri
                            and u.user_uuid = usp.user_uuid
                            and usp.security_profile_uuid = sp.security_profile_uuid
                    );


    -- test of the user is linked to any profile
    if(@linkCnt = 0)
    begin
        -- have to use the config value
        return 4;
    end

    -- user is linke to (at least one security profile)

    return 0;
end
GO
GRANT EXECUTE ON  [dbo].[ols_fn_getAreaSecLevelByUser] TO [ca_itrm_group]
GRANT EXECUTE ON  [dbo].[ols_fn_getAreaSecLevelByUser] TO [ca_itrm_group_ams]
GO