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 31255772. 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 allowed. 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.
Copyright (c) 2000, 2023, Oracle Corporation and/or its affiliates. All rights reserved.