Skip to content

How to Resolve ORA-00990: missing or invalid privilege

  • by

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. Misspelled Privilege
  5. Multiple Objects

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

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

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

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.

On the other side, to allow a user 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. 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.

5. 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.

Leave a Reply

Your email address will not be published.