Screening for potential prohibitions is not a solitary task and occurs at multiple steps in the process.
First, and foremost, the applicant is required to respond to whether certain situations apply, in item 16 in the current application, which are related to whether or not the applicant might be subject to a prohibition. These are broad enough that if there is any applicable prohibition, the applicant MUST disclose the relevant circumstances. That is, the person who conducts the primary check for prohibitions is the applicant himself or herself.
And note: failure to get this one right is perhaps the most likely and common ground for alleging misrepresentation. That could be during processing when a finding of misrepresentation would result in denying the application, a further five-year prohibition, and potential criminal charges. OR it could be after taking the oath, which would grounds for revoking citizenship, a further ten-year prohibition, and potential criminal charges. In the last decade or so, and looking forward, this is perhaps targeted almost as commonly as residency/presence fraud. It is the most likely to be discovered and prosecuted misrepresentation because there is typically a judicial record or other official proceeding record which documents the misrepresentation.
Secondly, and importantly, the applicant is almost always asked about these same circumstances again, and required to sign an affirmation of his or her declarations regarding, during an interview. Same potential consequences loom. I am not sure of current local office practices, but at least some applicants are asked about this again and again required to sign a written affirmation of no prohibitions, at the time they appear for the oath ceremony.
Additionally, applicants who have been in some other country for 183 or more days during the four years prior to applying are required to submit a police certificate, to in effect prove they have no prohibitions arising from criminal charges in that country. Applicants may otherwise also be required to submit a police certificate later in the processing of their application.
Then, as you note, RCMP does a criminal history background check. This is undoubtedly more extensive and thorough than a name-record check.
IRCC also periodically does a GCMS record check, which would flag an applicant if there is a FOSS alert potentially related to certain prohibitions (reported PR RO breach for example) and would usually flag the applicant if there is a criminal name-record hit (at least in Canada or the U.S.). This check is supposed to be done any time any action is taken on the application, so it is at least done two or three times during the processing of the application, last time not too long before the oath is scheduled, a final clearance one might say.
Then, as you note, CSIS (which has functions well beyond the scope of a "spy" agency) also does some background checking, largely focused on whether there are any security related issues or prohibitions. The scope of this is not known, but obviously for some it can be very extensive and involve referrals to authorities in other countries.
Along the way, if IRCC comes across any information suggesting there is a possibility of a prohibition, particularly any undeclared prohibition, it can refer inquiries or even official investigations to either CBSA or the RCMP. These referrals may have to be supported by particular grounds, but those grounds do not have to rise to the level that would support a search or seizure warrant for example.
And I am undoubtedly omitting some.