Oracle alter table insufficient privileges

Question

I have just installed oracle 12c and then I am trying to grant user various rights.

I am logged in as system and I had given rights for create user which worked. However, while granting rights for alter table it gave me error

ORA-00990: missing or invalid privilege

Researching on this problem brought me to another post on SO. The Comments on this post indicated that it is because I am not logged in as GLOBAL user. However I don't know how to log in as GLOBAL user.

Do I have to create one ?

Is there any alternative solution ?

Answer 1

There is no ALTER TABLE privilege. The valid privileges are listed in the documentation.

If you have CREATE TABLE then you can create and alter your own table. To alter the definition of a table in another schema you'd need the ALTER ANY TABLE privilege.

Curiously this page does refer to ALTER TABLE:

For example, to create a trigger on a table, the user requires both the ALTER TABLE object privilege for the table and the CREATE TRIGGER system privilege.

The ALTER TABLE command prerequisites also say:

The table must be in your own schema, or you must have ALTER object privilege on the table, or you must have ALTER ANY TABLE system privilege.

In this context it's a bit clearer; 'ALTER object privilege' means that you've been directly granted ALTER on the table by its owner, rather than via the ALTER ANY TABLE system privilege, as in:

create table t42(id number);
grant alter on t42 to user2;

Then user2 would be able to alter table t42 ..., or create a trigger on it (for example), but not any other tables.