How To Fix ORA-01031: Insufficient Privileges - Ed Chen Logic (2023)

ORA-01031

ORA-01031: Insufficient privileges means the current user did not use the correct privileges to process the SQL statement.

Since this error can be seen in almost all types of SQL statements, sometimes you will never know what permission you are missing. So I'm doing my best to collect cases for you.

There are several ORA-01031 error patterns in this post. You can click any situation found.

1. select (query)
2. create table
3. create index
4. create view
5. Modify table (add constraint)
6. Change user
7. Change of password
8. Insert, update and delete
9. RUN IMMEDIATELY
10. DGMGRL
11. Change Pluggable Database Close
12. PDB-Clone

select (query)

Attempt to select someone else's table, ORA-01031: Insufficient permissions.

SQL> show users
USER is "HR"
SQL> select a gender other than oe.customers;
Select a different gender than oe.customers
*
ERROR in line 1:
ORA-01031: Insufficient permissions

Theoretically, if we can't see each other's table, we haveORA-00942: Table or view does not exist. But the error message tells us that we didn't use the right permission for it. Why? We'd better run some tests.

The first test is can we describe the definition of the table?

SQL> desc oe.clientes;
null name? writes
----------------------------------------- -------- - -----------------------
...
TYPE VARCHAR2(1)
...

Yes, we can see your metadata, but not the data.

So what object rights do we have now? Let's review them from a privileged user.

SQL> show users
USER is "SYSTEM"
SQL> select privilege from dba_tab_privs where owner = 'OE' and table name = 'CLIENTS' and beneficiary = 'HR' ordered by 1;

PRIVILEGE
-----------------------------------
REMOVE
INSERTION
TO UPDATE

ok we canINSERTION,TO UPDATEjREMOVE, but notSELECTPrivilege. That's very strange.

Solution to ORA-01031

To fix the insufficient privilege for the query, we need to grantSELECTuser privilege.

SQL> grant selection in oe.clientes to hr;

Grant was successful.

Then we consult the table again.

SQL> select a gender other than oe.customers;

GRAMM
-
METRO
F

Good, the problem is solved.

create table

ORA-01031 is very common when a new user wants to create a spreadsheet. Let's look at an example.

SQL> conn/as sysdba
Connected.
SQL> create user thomas, identified by thomas;

user created.

SQL> Grant Thomas create the session;

Grant was successful.

As you can see, a new userTomáscreated, but we only grantCREATE SESSIONto it so it can connect to the database. Let's see what happens when the new user wants to create a table.

C:\Users\edchen>sqlplus thomas/thomas@orcl
...
SQL> create table test1(id number, e_id number);
Create table test1 (ID number, e_ID number)
*
ERROR in line 1:
ORA-01031: Insufficient permissions

ORA-01031: Insufficient permissions are shown immediately, indicating thisTomáshe does not have the privileged right to do so.

Solution to ORA-01031

The solution is simple, just allowCREATE TABLEfor the user, a schema-based privilege, orCREATE ANY TABLE, a system-wide privilege.

SQL> conn/as sysdba
Connected.
SQL> Grant Thomas to create the table;

Grant was successful.

Then tell Thomas to try again.

SQL> create table test1(id number, e_id number);

table created.

And use itRUN IMMEDIATELYrunCREATE TABLEin a stored procedure you can checkORA-01031 in the RUN IMMEDIATELY section of this post.

create index

In the previous section we grantedCREATE TABLEaTomás, which gives Thomas the opportunityCREATE INDEXin your schematic.

Note that,CREATE INDEXis not a valid privilege, howeverBUILD EVERY INDEXes.

Let's look at an example and then guess what privilege we need.

Suppose Thomas wants to create an index forSH.CUSTOMERin Thomas' scheme, we concedeSELECTat this table (object privilege) instinctively.

SQL> Grant selection in sh.clientes to thomas;

Grant was successful.

Thomas then tries to create an index on that table.

SQL> crea el índice customer_id_gen_idx en sh.customers (cust_id, cust_gender);
Create index oe.customer_id_gen_idx and sh.customers(cust_id, cust_gender)
*
ERROR in line 1:
ORA-01031: Insufficient permissions

Solution to ORA-01031

That's becauseSELECTAt this table it is not enough, it must also be grantedINDEXon this table for the user who is an authorization object.

SQL> Grant index on sh.customers to thomas;

Grant was successful.

Try again.

SQL> crea el índice customer_id_gen_idx en sh.customers (cust_id, cust_gender);

index created.

Although in reality the case is rarely possibleCreate an index on another user's tablein our scheme.

create view

If you read the previous section, then you know that you must grantCREATE VIEWto the user claiming ORA-01031.

SQL> Grant Thomas build view;

Grant was successful.

If the user still received ORA-01031 after the grantCREATE VIEWfor him it must be a deeper problem. That's why this section is a bit longer.

Inherit Role Permission

Some permissions inherited from the role may not work in some situations, especially when accessing intermediate object types such as views or store procedures.

Here is a case that can reproduce the bug.

We assign roleRESOURCEaTomás. Then we grant the system privilege.CHOOSE ANY TABLEto paperRESOURCE.

SQL> Grant resources to Thomas;

Grant was successful.

SQL> grant the resource to select any table;

Grant was successful.

Then we can expect the userTomásinherits system privileges fromRESOURCE. Namely,TomásYou can choose any other table.

Let's do the first test. CarryTomásto select another user's tableSH.SALE.

SQL> select count(*) from sh.sales;

TELL(*)
----------
918843

Well, however, it is behaving as we expectedTomáshas no object rights onSH.SALE.

Let's do the second test. CarryTomásto create a view based on another user's spreadsheetSH.SALE.

SQL> create view sh_sales_v as select * from sh.sales;
Create the sh_sales_v view as select * from sh.sales
*
ERROR in line 1:
ORA-01031: Insufficient permissions

What happened?TomáshatCREATE VIEWand inheritCHOOSE ANY TABLEsinceRESOURCEYou shouldn't have any problems.

The result implies that the role's permissions do not reach the underlying objects through intermediate objects such as views.

Solution to ORA-01031

The solution to this problem is to giveSELECTon the table directly to the user.

First, explicitly grant permission to the object to fix the problem.

SQL> Grant selection in sh.sales to thomas;

Grant was successful.

Then tell Thomas to recreate the view.

SQL> create view sh_sales_v as select * from sh.sales;

view created.

Well, no problem.

Note that when creating a synonym inSH.SALES, it will succeed regardless of whether the explicit object privilege is granted directly or not.

Change table (ADD CONSTRAINT)

In the previous section we grantedCREATE TABLEaTomás. By implication, you also have the right toCHANGE TABLEat the schema level. So the cause of ORA-01031 inCHANGE TABLEIt's not as obvious as we thought.

Note that,CHANGE TABLEIt's not a privilege, howeverCHANGE EVERY TABLEis. That's why there is no such thingCONCEDER AMENDS TABLE AExpression.

Let's look at an example. User Thomas wants to add a constraint to refer to another user's data, so we'll grantSELECTat this table forTomásout of instinct

SQL> grant selection on hr.employees to thomas;

Then we make the connection.

SQL> alter table test1 add constraint fk_eid foreign key (e_id) references hr.employees (employee_id);
Modify table test1 add constraint fk_eid foreign key (e_id) references hr.employees (employee_id)
*
ERROR in line 1:
ORA-01031: Insufficient permissions

We have ORA-01031.

Solution to ORA-01031

The right privilege to refer to the data of others is notSELECT, areREFERENCES.

we should admitREFERENCESon the table for the user either throughHOURor privileged users.

SQL> Grant references in hr.employees to Thomas;

Grant was successful.

Now Thomas can finish his work.

SQL> alter table test1 add constraint fk_eid foreign key (e_id) references hr.employees (employee_id);

table changed.

Change user

Try toAdd a quota in the tablespaceitself, but lacks some privileges.

SQL> show users
USER is "HR"
SQL> change unlimited user-hours quota for users;
Change unlimited user hours quota for users
*
ERROR in line 1:
ORA-01031: Insufficient permissions

The right privilege in this case isCHANGE USER.

SQL> show users
USER is "SYSTEM"
SQL> grant user change to hr;

Grant was successful.

So let's do it again.

SQL> show users
USER is "HR"
SQL> change unlimited user-hours quota for users;

Modified user.

SQL> change user account lockout oe;

Modified user.

As you can see withCHANGE USERthe user can not only assign a certain quota to himself, but also change the status of others.

Change of password

An attempt was made to change someone else's password using the SQL*Plus commandClave, but the user has inappropriate permissions.

SQL> show users
USER is "HR"
SQL> Passwort oe
Change password for o
New password:
Enter new password again:
ERROR:
ORA-01031: Insufficient permissions

Password unchanged

SinceClaveThe command is actually aCHANGE USERStatement that the correct right to change the password of others isCHANGE USER.

Insert, update and delete

You may have the right to choose the table of others.

SQL> conn sch/sch
Connected.
SQL> select * from hr.t1;

I WOULD
----------
1
2
3

However, you may not have the right to modify the table. For example,INSERT INsome data like this:

Insert SQL> into values ​​hr.t1(4);
insert into values ​​hr.t1(4)
*
ERROR in line 1:
ORA-01031: Insufficient permissions

That's because you're missing itINSERTION,TO UPDATEÖREMOVEChange permission on this table, which is usually owned by others.

Solution to ORA-01031

The right privilege is clearINSERTION,TO UPDATEÖREMOVEat object level. You can ask the DBA or the owner of the object to grant you permission.

SQL> connection time/time
Connected.
SQL> grant insert, update, delete in hr.t1 to sh;

Grant was successful.

As we can see, the grantor grants 3 object permissionsINSERTION,TO UPDATEjREMOVEimmediately on the table for the dealer.

In some cases you might consider granting the user all possible object permissions, for example:

SQL> pass everything in hr.t1 to sh;

Grant was successful.

In the statementALLis a keyword meaning all possible privileges for the given object. Of course, it does not only belong to a tableSELECT, but alsoINSERTION,TO UPDATEjREMOVE.

As a result, we can insert some rows.

SQL> conn sch/sch
Connected.
Insert SQL> into values ​​hr.t1(4);

1 row created.

SQL> confirmation;

Full commitment.

That means not onlySELECT, but alsoINSERTION,TO UPDATEÖREMOVEYou must have privilege to manipulate tables owned by other users.

Let's see how I reproduce ORA-01031 for statementsRUN IMMEDIATELYfor the following example.

Inherit Role Permission

In the previous section, I assigned the roleRESOURCEaTomás. now I admitCREATE ANY DIRECTORYjEXIT A DIRECTORYto paperRESOURCE.

SQL> Grant Create Arbitrary Directory, Release Arbitrary Directory;

Grant was successful.

So we can expect the userTomásYou can also perform such operations by inheriting all permissions fromRESOURCE.

Things look good when we use themTomásto create or delete directories.

SQL> create directory tmp_path as '/u02/tmp';

directory created.

SQL> delete tmp_path directory;

directory below.

SQL> create directory tmp_path as '/u02/tmp';

directory created

Now Thomas wants to create directories in stored procedures, also known as PL/SQL named blocks or programming units.

First, DBA must grantCREATE PROCEDUREhim before Thomas does anything.

SQL> Grant Thomas the build procedure;

Grant was successful.

So Thomas creates a procedure like this:

SQL> method to create or replace drop_create_tmp_dir is
Start
immediately run 'drop directory tmp_path';
run immediately 'Create tmp_path directory or replace as ''/u02/tmp''';
exit drop_create_tmp_dir;
/
2 3 4 5 6
procedure created.

It seems there is no problem. But when we ran the stored procedure (named PL/SQL) we got ORA-01031 on line 3.

SQL> exec drop_create_tmp_dir;
START drop_create_tmp_dir; FINAL;

*
ERROR in line 1:
ORA-01031: Insufficient permissions
ORA-06512: in "THOMAS.DROP_CREATE_TMP_DIR", Zeile 3
ORA-06512: on line 1

fundamental reason

This is because theSystem privileges inherited from the role cannot be used in named stored procedures with setter privileges.

Solutions to ORA-01031

Now we have several options, the first is to grant the user all necessary permissions directly, the second is to use the caller right and the last is to use anonymous PL/SQL blocks.

1. Direct Grant to User

TomásYou should get the DBA system privilege directly and not inherit it from the role.

SQL> Grant Create any directory, drop any directory to thomas;

Grant was successful.

The backTomás, we can run it again.

SQL> exec drop_create_tmp_dir;

PL / SQL-procedure completed successfully.

The best part is that we don't have to recompile the procedure.

2. Using the summoner right

Another way to resolve ORA-01031 for instructions inRUN IMMEDIATELYis to use the caller's right to define the procedure.

We will revoke the approval of SYS.

SQL> revoke, create any directory, remove any directory from Thomas;

Revocation successful.

Then wecreated the procedure withAUTHID USER_CURRENTclause.

SQL> create or replace procedure drop_create_tmp_dirautid current_useres
Start
immediately run 'drop directory tmp_path';
run immediately 'Create tmp_path directory or replace as ''/u02/tmp''';
exit drop_create_tmp_dir;
/
2 3 4 5 6
procedure created.

Try running the procedure withTomás.

SQL> exec drop_create_tmp_dir;

PL / SQL-procedure completed successfully.

The right of the caller allows us to use the privileges of the role.

3. Block anonymous PL/SQL

What we mean by this is that role permissions cannot intrude into NAMED stored procedures. That is, you can use role permissions on anonymous PL/SQL blocks. For example, we can rewrite the stored procedure in an anonymous PL/SQL as follows:

Start
immediately run 'drop directory tmp_path';
run immediately 'Create tmp_path directory or replace as ''/u02/tmp''';
Finale;
/

You can save it and use it like a regular SQL script file.

For the same reasonCREATE TABLEinRUN IMMEDIATELYit can also cast ORA-01031.

DGMGRL

DGMGRL allows the user to easily query the status of all participating nodes via local authentication, but this can fail.Switch to a standby databaseÖconvert to standby snapshot.

DGMGRL switchover

Let's look at a change in 11g, it fails when connecting DGMGRL with local authentication.

[oracle@primary01 ~]$ dgmgrl /
...
DGMGRL> switch to standby
Make the change NOW, please wait...
The operation requires shutting down the primdb2 instance in the primdb database
Shutting down instance "primdb2"...
ORA-01031: Insufficient permissions

Warning: You are no longer connected to ORACLE.

Perform the following steps and reissue the SWITCHOVER command:
Close instance 'primdb2' of database 'primdb'

DGMGRL>

But if you connect DGMGRL to the database password, the change will be successful.

[oracle@primary01 ~]$ dgmgrl sys/contraseña@primdb1
...
DGMGRL> switch to standby
Make the change NOW, please wait...
The operation requires shutting down the primdb2 instance in the primdb database
Shutting down instance "primdb2"...
closed database.
Unload database.
Shutting down the ORACLE instance.
A new main database "standb" is opened...
The operation requires shutting down instance primdb1 in database primdb
Shutting down instance "primdb1"...
ORA-01109: Database not open

Unload database.
Shutting down the ORACLE instance.
The operation requires starting the instance "primdb1" in the database "primdb"
Starting instance "primdb1"...
ORACLE instance started.
Mounted database.
The change was successful, the new headmaster is on "standb"
DGMGRL>

Turn into

The same error occurred during a conversion.

[oracle@primary01 ~]$ dgmgrl /
...
DGMGRL> CONVERT STANDBY DATABASE TO STANDBY INSTANTLY;
Converting the "standb" database to a snapshot standby database, please wait...
The operation requires shutting down the "standb2" instance in the "standb" database
Shutting down the "standb2" instance...
ORA-01031: Insufficient permissions

Warning: You are no longer connected to ORACLE.

Follow these steps and run the CONVERT command again:
Close instance "standb2" of database "standb".

Solution to ORA-01031

You must use database authentication to convert a standby database.

[oracle@primary01 ~]$ dgmgrlsys/contraseña@primdb1
...
DGMGRL> CONVERT STANDBY DATABASE TO STANDBY INSTANTLY;
Converting the "standb" database to a snapshot standby database, please wait...
The operation requires shutting down the "standb2" instance in the "standb" database
Shutting down the "standb2" instance...
ORA-01109: Database not open

Unload database.
Shutting down the ORACLE instance.
We continue with the conversion of the "standb" database...
Database "standb" successfully converted
...

For the same reasonThe broker fails to start the new standby database during a switch and generates ORA-01017 due to OS authentication.

Change Pluggable Database Close

An error occurred when a normal user attempted to close a Pluggable Database (PDB).

SQL> conn hr/contraseña@orclpdb
Connected.
SQL> show users
USER is "HR"
change SQL> shutdown pluggable database;
Close Modify Pluggable Database
*
ERROR in line 1:
ORA-01031: Insufficient permissions

To fix ORA-01031 we took two steps to allow the user to close a PDB.

1. Grant the user SYSDBA

Please make sure you are logged in asSYSTEMand they are in the correct container.

SQL> show users
USER is "SYS"
Show SQL> con_namev
WITH NAME
------------------------------
ORCLPDB

So we concedeSYSDBAuser privilege.

SQL> otorgar sysdba a hr;

Grant was successful.

2. Connect as SYSDBA

The user must useSYSDBAPrivilege to connect to the PDB.

SQL> conn hr/password@orclpdb als sysdba
Connected.
change SQL> shutdown pluggable database;

Changed pluggable database.

In reality, the ordinary user has become oneSYSTEMwho of course has the opportunity to maintain the database.

PDB-Clone

if you tryClone a remote PDB via database link, you can see ORA-17628 and ORA-01031 at this time. I talked about it in this post.