In Oracle 23c we can now grant privileges on a schema to a user. Now, in previous versions of Oracle, you never wanted to grant with the “ANY” clause; however, now we can just apply that to a schema. In the bellow example, we’re granting select any table, and create any table on the HR schema to usr1,
SQL> GRANT SELECT ANY TABLE ON SCHEMA HR TO usr1;
Grant succeeded.
SQL> grant create any table on schema hr to usr1;
Grant succeeded.
This goes a long way to helping us manage privileges and also help enforce the principle of least privilege.
SQL> conn usr1/secret
Connected.
SQL> create table hr.t (x number);
Table HR.T created.
SQL> drop table hr.t;
Error starting at line : 1 in command –
drop table hr.t
Error report –
ORA-01031: insufficient privileges
- 00000 – “insufficient privileges”
*Cause: An attempt was made to perform a database operation without
the necessary privileges.
*Action: Ask your database administrator or designated security
administrator to grant you the necessary privileges
But you’ll notice, because drop table was not granted, the user usr1 can not drop the table he/she just created.