Allow a role to create objects in tablespaces

Question

I am trying to assign to the role "computer" the permission to create objects in any less tablespace in the SYSTEM tablespace.

Thank you very much and I hope I have explained correctly.

I have tried the following query:

GRANT CREATE ON ANY TABLESPACE TO USER COMPUTER;

Answer 1

This is what you're looking for

GRANT UNLIMITED TABLESPACE TO computer 

Some more info:

Granting Users the UNLIMITED TABLESPACE System Privilege

To permit a user to use an unlimited amount of any tablespace in the database, grant the user the UNLIMITED TABLESPACE system privilege. This overrides all explicit tablespace quotas for the user. If you later revoke the privilege, then explicit quotas again take effect. You can grant this privilege only to users, not to roles.

Before granting the UNLIMITED TABLESPACE system privilege, you must consider the consequences of doing so.

Advantage:

You can grant a user unlimited access to all tablespaces of a database with one statement.

Disadvantages:

• The privilege overrides all explicit tablespace quotas for the user.
• You cannot selectively revoke tablespace access from a user with the UNLIMITED TABLESPACE privilege. You can grant selective or restricted access only after revoking the privilege.

This should be of more help regarding the security management for users/roles.