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

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.

Answer 2

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.