WL#14073: Disable DROP/RENAME USER for SQL DEFINER users in procedures, functions, views, triggers and events.

Affects: Server-8.0   —   Status: Complete

When user account is dropped or renamed the stored programs and views he's a SQL DEFINER of get orphaned. 
And when a new user account with the same name is created the orphaned stored programs and views get adopted by the new user account.
This might cause significant usability problems (they won't work as expected) and can also be considered a security issue: allow account with just the right to create stored programs to make another more privileged account created later to execute "packaged code" in these stored programs. 

This worklog aims at fixing this by making DROP USER and RENAME fail if there are stored programs, views or scheduled tasks that have the user as a definer. 
1. DROP/RENAME USER sql should report error if this user account is 
   referenced in any of the stored programs (procedures, functions, triggers, 
   events) and views as a definer account.
   1.1 If connected user has SET_USER_ID privilege then we allow DROP/RENAME USER 
       sql to succeed and report a warning.

2. To adopt orphaned objects ( refer HLS to know what is orphaned objects) 
   user needs to create missing definer account. This CREATE USER operation
   should report error.
   2.1 If connected user has SET_USER_ID privilege then we allow CREATE USER sql 
       to succeed and report a warning.

3. To adopt orphaned objects, DBA or admin is expected to drop the orphaned 
   object, create the definer account, and then recreate the orphaned object.

4. CREATE/DROP/RENAME USER sql in parallel to creation of any stored program or
   view should be atomic.

Non FR:
Dump and restore of logical backup has no effect.
Upgrade operations should not have any effect with this WL.
Creation of stored programs like procedures, functions, triggers, events or 
views can be specified with a definer clause which specifies the user account
to be used when checking access privileges at execution time. When this user 
account is dropped or renamed, the corresponding stored programs or view will
become an orphaned object and will fail to execute. This WL aims to restrict 
such operations on user accounts.

During creation of stored program or views, existence of definer user account is
not validated. This behaviour can cause a security threat described in this bug

To overcome this security threat this WL provides 2 solutions.
1. End user having CREATE USER privilege trying to create a user with 
authorisation ID which matches the stored programs definer account will not be 
Thus user is expected to do following in same order:
   a. Drop the stored program or view.
   b. Create the user account and grant needed privileges.
   c. Create the dropped stored program or view.
However if user has SET_USER_ID privilege then CREATE USER sql will pass and a 
warning will be reported.

2. DROP/RENAME USER sql statement will be restricted in case user account is 
referenced as a definer account in any of the stored programs or views. If user 
has SET_USER_ID privilege, this operation will succeed by reporting a warning.

Note: In OCI customer root account does not have SUPER or SET_USER_ID privilege.
This customer root cannot create stored programs or views with definer set to a
different account other than himself.

During upgrade if there are any orphaned objects, then upgrade will proceed, 
however when user comes to know that execution of orphaned object fails and 
tries to create missing user account, this will fail(provided user does not have 
SET_USER_ID privilege), expecting user to drop the orphaned object, create the 
definer user account and recreate the orphaned object.

If there exists a user account with some extra unwanted privileges and DBA 
creates a stored program or view with such existing user accounts as a definer 
account, then its DBA responsibility and server will not check for any security 
threat in such a case.

When syncing replication slave server against master, and if on slave there 
exists user accounts matching the definer accounts of stored programs then,
such stored program creation will succeed on slave.

When restoring a logical backup file, if there exists a user accounts matching 
the definer accounts of stored programs from backup file, then creation of those
objects will pass. If such a definer account does not exist, and stored program
execution fails, then user is expected to drop stored program, create the user 
account and recreate the stored program.