Скачать или смотреть Understanding Why a User with No Privileges Can ALTER TABLE in Oracle

Explore how user privileges work in Oracle and why a user with limited permissions can still alter tables they've created.
---
This video is based on the question https://stackoverflow.com/q/64516796/ asked by the user 'Timer' ( https://stackoverflow.com/u/13305519/ ) and on the answer https://stackoverflow.com/a/64522346/ provided by the user 'Littlefoot' ( https://stackoverflow.com/u/9097906/ ) 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: User with no privileges can alter table in oracle

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 Why a User with No Privileges Can ALTER TABLE in Oracle

In working with Oracle databases, understanding user privileges is crucial, especially when it comes to tasks like altering tables. You might encounter a puzzling situation where a user that seemingly has limited privileges is still able to modify existing tables. This can lead to confusion, particularly for those trying to assess or restrict user capabilities. Let’s dive into this issue to clarify what's happening and how privileges are enforced in Oracle.

The Problem Explained

Initially, let's clarify the issue at hand. A user, let's call him USER1, is attempting to conduct a test to validate user privileges. USER1 has only been granted a single privilege: the ability to execute programs. Despite this, the user encounters a situation where:

Creating a new table results in an "insufficient privileges" error.

Altering an already existing table is permitted without any issues.

This raises the question: Why is USER1 able to alter a table when they do not possess create table privileges?

Understanding Oracle User Privileges

To understand this anomaly, we need to break down how Oracle user privileges work. Here is a simplified outline:

Key Concepts of User Privileges

Ownership of Objects: In Oracle, the creator of a database object (such as a table) is considered the owner. Owners have certain rights over their objects that are not affected by the privileges granted to them.

Types of Privileges: Oracle uses various privileges that can be broadly categorized into two types:

System Privileges: Allow users to perform specific actions (e.g., CREATE TABLE, ALTER TABLE).

Object Privileges: Grant permissions to interact with certain objects (e.g., a specific table or view).

The Scenario with USER1

Let’s illustrate this with a scenario similar to USER1’s situation. Consider the following steps taken by a privileged user (like SYS) in creating a user and granting them limited privileges:

User Creation and Grants:

The admin creates a user (e.g., TIMER) and grants them the ability to create a session and create tables.

If these privileges were revoked later for TIMER, they would no longer be able to create new tables but could still alter existing tables they own.

Effect of Ownership: When the TIMER user creates a table, they become the owner. Any future alterations to this table (for example, adding a column) can be performed by the owner, irrespective of whether the user possesses system privileges to create new tables.

Practical Insights

To summarize the revelations from the case of USER1 and the similar example of TIMER:

Insufficient Privileges for Creation: USER1 receives an "insufficient privileges" error when trying to create a new table because they lack the CREATE TABLE privilege.

Ownership Allows Alteration: However, if they are the owner of a table created in the past, they retain the ability to alter that table as permitted by the ownership rights.

Recommended Actions

If you encounter this situation, here are a few steps you can take:

Verify User Privileges: Check which privileges have been granted to the user and also look into the ownership of the objects they are trying to alter.

Consult with a DBA: If there's still confusion about the permissions, it's advisable to discuss the situation with a Database Administrator. They can provide deeper insights into user roles and privileges.

Conclusion

In conclusion, navigating user privileges in Oracle can sometimes lead to unexpected outcomes, particularly concerning table alterations. The key takeaway here is understanding the relationship between object ownership and privilege grants. If a user owns an object, they typically have the ability to modify it, regardless