The only effect that "with sharing" has is to enforce object sharing rules; that is, if you use "with sharing" and query an object that has private record visibility, any Apex SOQL will only return results for objects shared to them; if you do not use "with sharing", the Apex SOQL will return results for all objects.
As VNE_Hess stated, the user's license restrictions are always enforced no matter what; you can't directly get around them when the code is executing as that user.
It's also worth mentioning that although Apex never enforces field-level and CRUD security, most of the standard VisualForce rendering components do enforce FLS/CRUD, so even though your system-context Apex was able to retrieve (and modify, etc) a field that the user doesn't have profile visibility to, if you pass it to VF via say an inputfield component, the user won't see it.
The Sites Guest User is a special profile that is heavily restricted from making updates to core CRM objects. From memory I think it's read-create only to the core objects except for Articles. Salesforce imposes this restriction AFAICT to require customers to upgrade to more expensive Partner or Customer Portal licenses to get this level of access.
If you're developing AppExchange apps that deal with the standard objects, it's very important to understand that to pass security review, your Apex code must manually enforce FLS and CRUD at every step of the way. This is a HUGE pain in the rear if your app does a lot of these updates, and results in very error-prone code because the ESAPI toolkit that Salesforce provides has a much more error-prone API than the core SOQL/DML interface.
Salesforce would save its app developer community literally thousands of hours of work per year by implementing a "with FLSAndCrud" type keyword, but architecturally that seems unlikely any time soon.
