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.
The Guest "User license" is designed for public users who access your Site.com or Force.com sites. If Communities is enabled, these users will also have access to public pages in your communities. Site visitors have access to any information made available in an active public site. For each Guest "User license", you can develop one site for your organization.
Note: When I've put "User license" in quotes above, I'm referring to the Guest site type. It's still a User license type, but doesn't require an additional User license.
For Site.com, Developer, Enterprise, Unlimited, and Performance Editions each come with unlimited Guest User licenses.
Force.com sites, Enterprise, Unlimited, and Performance Editions come with 25 Guest "User licenses". Developer Edition comes with one Guest "User license".
Some portals will still require an additional license even for Guests because of the objects you're going to expose or display on the site. If all you're doing is creating a "normal" web site, then you could use a free Guest "User license". However if you were creating a web site that utilized any standard objects from Salesforce that required another license of some kind to view them, then you'd need to have the appropriate License specified for your portal.
One example would be the new Chatter Free Licenses that expose Chatter to visitors through a portal. That would require a Chatter Free License. Another example would be the HVCP (High Volume Customer Portal) License. You can expose Cases and other standard objects with that license. There are other examples I could give, but I hope that gives you the gist of the idea.
For more on this, see User Licenses Overview.
EDITED 6-01-19
I ran across some additional information today that may be helpful and important for members to know about these profiles/licenses.
First, is a link to old documentation on Changing the Guest User Profile from the Site.com Workbook (July 4, 2014). Note that this is a reference to Force.com Sites. To read the full instructions download the PDF from the 2nd link.
Next is a KB article about Guest Users not being able to access Knowledge articles in Communities and how to grant that permission to them. The solutions in that article involve both sharing settings on pages and records, plus settings on profiles that depend on whether the Community uses a VisualForce plus Tabs Template (references are to the Sites Guest User License and Profile) or is a Lightning Community (references to the Guest License and Profile).
On October 3. 1918, according to KB 000273124 Security vulnerability impact on Salesforce Sites & Communities both of these profiles were updated as follows.
Implemented a software fix to block guest user access to specific URL paths on Sites and Communities that guest users weren't meant to access. Because of that update, a guest user will now be directed to an authorized login page or receive an “insufficient access” error message.
For Sites or Communities created after October 3, the Salesforce Technology team updated the process for site and community creation to ensure new guest user profiles default to more limited permissions.
For Sites or Communities created before October 3, 2018, to provide an additional layer of protection, the Salesforce Technology team proactively revoked the standard guest user permissions that were granted by default when the Sites or Communities were created. Those permissions were revoked on October 5, 2018 when the critical update “Restrict Record Access for Guest Users” was pushed to all customers, thus adding an extra layer of data safety for customers. (This action also applies to the custom guest profiles of our legacy Site.com product.)
Because we revoked those permissions, some customers may experience some functional issues within their Sites or Communities. We recommend you review and acknowledge the critical update “Restrict Record Access for Guest Users,” then review your guest users’ access rights via the Public Access Settings on the Site Detail Page or the Guest User Profile settings for your communities in Community Builder. In particular, we recommend reviewing each guest user profile’s “Standard Object Permissions” to ensure access is not granted in error. While some admins may have set these permissions on purpose, we want them to understand the risk associated with using these settings.
We also advise that you read the Public Access settings for Salesforce Sites article regarding the use of private external user sharing for the objects you are granting at least read access to. To improve visibility into object permissions for guest users, we added guest user information to Portal Health Check reports.
While we are continuing to investigate the potential impact on our customers, we do not have evidence that this vulnerability resulted in any malicious activity.
We apologize for any inconvenience you have experienced as a result of this issue.
As a final note, also of interest is KB 000212470 Enable 'Person Accounts' for 'Guest User Profile' in Site.com.
It would appear that depending on what you've read as a reference, it ultimately depends on how your site/community was implemented as to whether one or both of these licenses and profiles applies to it.
Best Answer
It appears you may not have fully understood what was being conveyed in the post you've cited in your post. A Sites user license does not allow access to standard objects. The post you're referring to was speaking of using a "mirror" object and a trigger to update Opportunity via sites. That page was also discussing the different types of sharing models to use on the controller and what that meant in terms of user permissions.
Without the proper licenses, your Sites users will not be able to make updates directly to the Opportunity object. You'll need to set up a partner portal/community if you want to be able update Opportunity without using a mirrored object and triggers to do it indirectly.