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 ?
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.
Trying to figer out what is the problem I guess you execute something like
SQL> conn system/***@***
Connected.
SQL> grant alter table to scott;
grant alter table to scott
*
error in line 1:
ORA-00990: missing or invalid privilege
In accordance to Oracle documentation:
http://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_9013.htm#BABEFFEE
you can grant ALTER ANY TABLE (which is a powerful right) or grant ALTER privilege on particular table in another schema:
SQL> grant alter any table to scott;
Granted.
SQL> grant alter on hr.event to scott;
Granted.
Schema owner always has ALTER privilege over the objects it's owned:
http://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_3001.htm#CJAHHIBI
"Prerequisites
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."
SQL> conn scott/tiger@***
Connected.
SQL> select * from session_privs;
PRIVILEGE
----------------------------------------
CREATE SESSION
UNLIMITED TABLESPACE
CREATE TABLE
CREATE CLUSTER
CREATE SEQUENCE
CREATE PROCEDURE
CREATE TRIGGER
CREATE TYPE
CREATE OPERATOR
CREATE INDEXTYPE
SQL> create table t(x int);
Table created.
SQL> alter table t add (y int);
Table altered.
