How to revoke alter table permission from user in oracle?-CodePudding

I have a user who only has permission to create dblinks, create session, and manage Scheduler, I want that it will not be able to create any object, delete it or alter it ... I already managed to prevent delete or create new things but I cannot remove the permission to make alters.

try with the following command with another user who has SYSDBA privileges:

REVOKE ALTER ON USER.TEST_LUIS FROM USER;

But the response tells me that I cannot remove permissions that have not been granted.

Any ideas?

Thanks!

CodePudding user response：

You can't revoke alter privilege over objects from the user who owns them. You would need to remove the privilege to create them in the first place.

Example

sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Tue Sep 28 16:14:30 2021
Version 19.6.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.


Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.6.0.0.0

SQL> drop user test2 cascade ;

User dropped.

SQL> create user test2 identified by Oracle_1234 default tablespace users temporary tablespace temp_group account unlock profile default quota unlimited on users ;

User created.

SQL> grant create session , create table to test2 ;

Grant succeeded.

SQL> exit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.6.0.0.0

sqlplus test2/Oracle_1234

SQL*Plus: Release 19.0.0.0.0 - Production on Tue Sep 28 16:13:24 2021
Version 19.6.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.


Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.6.0.0.0

SQL> create table t1 ( c1 number ) ;

Table created.

SQL> alter table t1 add ( c2 number ) ;

Table altered.

SQL> exit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.6.0.0.0

sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Tue Sep 28 16:14:30 2021
Version 19.6.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.


Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.6.0.0.0

SQL> revoke alter on test2.t1 from test2 ;
revoke alter on test2.t1 from test2
*
ERROR at line 1:
ORA-01927: cannot REVOKE privileges you did not grant

In my example below, I can't revoke the privilege, because the user is the owner of the table.

I can revoke however the create table privilege

SQL> revoke create table from test2 ;

Revoke succeeded.

Alter objects you own is inherited by the create privilege of the same object type.