How to Resolve ORA-00990: missing or invalid privilege

ORA-00990

There could be several error patterns of ORA-00990 when we tried to grant some privileges to a user.

1. GRANT CREATE INDEX
2. GRANT CREATE FUNCTION or GRANT CREATE PACKAGE
3. GRANT EXEC
4. GRANT QUOTA
5. GRANT TRUNCATE
6. Misspelled Privilege
7. Multiple Objects

ORA-00990 means that the privilege you specified is invalid, it could be misspelled or misunderstood. You should use valid privileges to grant to.

1. GRANT CREATE INDEX

SQL> grant create index to hr;
grant create index to hr
      *
ERROR at line 1:
ORA-00990: missing or invalid privilege

In this case, the privilege we specified is misunderstood, CREATE INDEX is not a valid system privileges.

Allow Owner to Create Index

To allow users to create their own indexes, they need only CREATE TABLE system privilege, a valid privilege to be granted.

SQL> grant create table to hr;

Grant succeeded.

Allow Others to Create Index

On the other side, to allow one to create index for other user's table, it needs a special object privilege on that table.

2. GRANT CREATE FUNCTION or GRANT CREATE PACKAGE

Some developers got ORA-01031: insufficient privileges when creating a function, so DBA tried to grant CREATE FUNCTION and CREATE PACKAGE to the user, but they both failed with ORA-00990.

SQL> grant create function to hr;
grant create function to hr
      *
ERROR at line 1:
ORA-00990: missing or invalid privilege


SQL> grant create package to hr;
grant create package to hr
      *
ERROR at line 1:
ORA-00990: missing or invalid privilege

In this case, the privileges we specified are misunderstood, CREATE FUNCTION and CREATE PACKAGE are not valid system privileges.

To allow users to create all kinds of stored procedures, you need only CREATE PROCEDURE, a valid privilege to be granted.

SQL> grant create procedure to hr;

Grant succeeded.

Please note that, although trigger is a kind of programming unit, CREATE TRIGGER is a different system privilege and it's valid.

3. GRANT EXEC

EXEC is a short form of EXECUTE in SQL*Plus, but it's not formally used in Oracle.

SQL> grant exec on sys.dbms_sql to public;
grant exec on sys.dbms_sql to public
      *
ERROR at line 1:
ORA-00990: missing or invalid privilege

In fact, EXEC is a not a valid privilege, EXECUTE is.

SQL> grant execute on sys.dbms_sql to public;

Grant succeeded.

4. GRANT QUOTA

Tried to limit the usage on specific tablespace, but it failed.

SQL> grant quota 100g on erpapp_tbs_01 to erpapp;
grant quota 100g on erpapp_tbs_01 to erpapp
      *
ERROR at line 1:
ORA-00990: missing or invalid privilege

In fact, there's no QUOTA privilege to be granted, we should use another way to grant quota on the tablespace to the user.

5. GRANT TRUNCATE

Tried to allow an user to TRUNCATE a table belongs to others.

SQL> grant truncate on oe.t1 to hr;
grant truncate on oe.t1 to hr
      *
ERROR at line 1:
ORA-00990: missing or invalid privilege

Although TRUNCATE is a valid SQL command, it's not a valid object privilege. If you want to allow one to truncate other user's table, you need system privilege DROP ANY TABLE.

SQL> grant drop any table to hr;

Grant succeeded.

6. Misspelled Privilege

Let's see a case of mistyping.

SQL> grant creat table to hr;
grant creat table to hr
      *
ERROR at line 1:
ORA-00990: missing or invalid privilege

Any misspelled privileges can produce ORA-00990. You should check every letter you typed if you thought the privilege was a valid one.

Here we correct the mistyped privilege to the right one.

SQL> grant create table to hr;

Grant succeeded.

If you use some GUI tool to work on the database, you should enable autocomplete and spell checking to reduce such error.

7. Multiple Objects

Granting privileges can be very efficient, we can grant multiple privileges to multiple grantees in a single statement, but we cannot grant privileges on multiple target objects. You can see more examples about how to grant privileges correctly.