-- Removes an user from the CMS user list
  -- Parameters:
  --   p_u_id: user id to remove
  -- raise NO_DATA_FOUND exception if the user doesn t exists
  procedure removeUser(p_u_id number) is
  begin
    delete from cms_user_group where u_id=p_u_id;
    delete from cms_users where u_id=p_u_id;
  end removeUser;

  -- Adds a given group to the user
  -- Parameters:
  --   p_u_id: user id
  --   p_g_id: group id
  -- raise Primary key violation exception if the user is already in this group
  procedure addGroup(p_u_id number, p_g_id number) is
  begin
    insert into cms_user_group values(p_u_id,p_g_id);
  end addGroup;

  -- Removes a given group to the user
  -- Parameters:
  --   p_u_id: user id
  --   p_g_id: group id
  -- raise NO_DATA_FOUND exception if the user is not in group
  procedure removeGroup(p_u_id number, p_g_id number) is
  begin
    delete from cms_user_group where u_id=p_u_id and g_id=p_g_id;
  end removeGroup;

  -- Updates an user password from the CMS user list
  -- Parameters:
  --   p_loginName: user login name
  --   p_old_pass: previous password
  --   p_new_pass: password to be assigned
  -- raise NO_DATA_FOUND exception if the user doesn t 
  -- exists or old_pass did not match
  procedure setPassword(p_loginName varchar2, 
                        p_old_pass varchar2, p_new_pass varchar2) is
  begin
     update cms_users set password=p_new_pass 
            where loginName=p_loginName and password=p_old_pass;
  end setPassword;

  -- Validates a given username and password against the cms_users tables
  -- Parameters:
  --   p_loginName: username to be verified
  --   p_pass: password
  -- Raise NOT_DATA_FOUND: if user/pass do not match
  procedure checkPassword(p_loginName varchar2, p_pass varchar2) is
    v_u_id number;
  begin
    select u_id into v_u_id from cms_users 
          where loginName=p_loginName and password=p_pass;
  end checkPassword;

  -- Gets User Id for a given loginName
  -- Parameters:
  --   p_loginName: username to be verified
  -- Returns:
  --   u_id: user id
  -- Raise NOT_DATA_FOUND: if loginName do not exists
  function getId(p_loginName varchar2) return number is
    v_u_id number;
  begin
    select u_id into v_u_id from cms_users 
          where loginName=p_loginName;
    return v_u_id;
  end getId;