-1

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;
Randomuser1204281
  • 15
  • 5
  • Whats your problem then? You want to create role which has a privilege that can create objects on any tablespaces right? Are you asking about `grant UNLIMITED TABLESPACE to computer`? – atokpas Jan 31 '17 at 16:01
  • @JSapkota thank you very much for your help. But with this permission I can create objects in the SYSTEM tablespace, right? – Randomuser1204281 Jan 31 '17 at 16:17
  • 2
    Yes, however, creating user objects on system tablespace is not recommended. Search on Oracle documentation about this system privilege. – atokpas Jan 31 '17 at 16:25

1 Answers1

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.

Jucan
  • 421
  • 4
  • 8