Скачать или смотреть Resolving CREATE USER Privilege Issues in Oracle Database

Learn how to successfully grant `CREATE USER` privileges in Oracle Database with the `CONTAINER=ALL` clause for user management.
---
This video is based on the question https://stackoverflow.com/q/64325135/ asked by the user 'predefined_' ( https://stackoverflow.com/u/13614340/ ) and on the answer https://stackoverflow.com/a/64325462/ provided by the user 'predefined_' ( https://stackoverflow.com/u/13614340/ ) at 'Stack Overflow' website. Thanks to these great users and Stackexchange community for their contributions.

Visit these links for original content and any more details, such as alternate solutions, latest updates/developments on topic, comments, revision history etc. For example, the original title of the Question was: Oracle CREATE USER privilege

Also, Content (except music) licensed under CC BY-SA https://meta.stackexchange.com/help/l...
The original Question post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/... ) license, and the original Answer post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/... ) license.

If anything seems off to you, please feel free to write me at vlogize [AT] gmail [DOT] com.
---
Understanding the Oracle CREATE USER Privilege Issue

Good evening, fellow Oracle Database users! Today, we are tackling a common challenge faced when managing user permissions in Oracle, particularly the CREATE USER privilege. Have you ever encountered the following error when trying to create a user?

[[See Video to Reveal this Text or Code Snippet]]

This error occurs even after granting the necessary privileges, and it can be quite frustrating. In this guide, we will explore the reasons behind this error and how to resolve it effectively.

The Problem: Insufficient Privileges

You may have followed the steps to grant a user the ability to create, alter, and drop users in your Oracle Database, only to find that you are still unable to perform these actions. Here is a brief outline of the steps typically taken before encountering the error:

Create a User: The initial step typically involves creating a user in the database.

Grant Privileges: Following this, you grant various privileges, including CREATE USER, ALTER USER, and DROP USER.

Example Steps Taken:

Here’s a summary of how the privileges were appropriately granted to the user in question:

[[See Video to Reveal this Text or Code Snippet]]

Despite these detailed grants, attempts to create a new user resulted in the ORA-01031 error.

Investigation Findings

Upon checking the USER_SYS_PRIVS table, it was revealed that the COM column showed a NO for the critical privileges—CREATE USER, ALTER USER, and DROP USER. This indicated that while the privileges were granted, they were not effectively applicable due to how they were set.

The Solution: Adding Container Clause

So, what’s the fix? The answer lies in including the CONTAINER=ALL clause when granting system privileges. Here’s how the GRANT statement should be adjusted:

[[See Video to Reveal this Text or Code Snippet]]

Explanation of CONTAINER=ALL

System Privileges: The privileges like CREATE USER are considered system privileges. To propagate these privileges from a system user to another user correctly, it is essential to specify CONTAINER=ALL.

Successful Creation: Once the grants are made with the container clause included, you should now be able to create a new user successfully without any errors.

Conclusion and Additional Notes

In conclusion, remember that when dealing with system privileges in Oracle Database, especially under multi-tenant architecture, always check if you need to specify CONTAINER=ALL. This small addition can save you from many headaches related to user management.

I hope this guide proves to be beneficial for anyone facing similar issues in Oracle Database. Happy coding!