ORA-06512: at “CTXSYS.TEXTINDEXMETHODS”, line 366

while creating a domain index as a SYS user for a table…..the following error was thrown:

ERROR at line 1:

ORA-29855: error occurred in the execution of ODCIINDEXCREATE routine

ORA-20000: Oracle Text error:

DRG-50857: oracle error in drvxtab.create_index_tables

ORA-01031: insufficient privileges

ORA-06512: at “CTXSYS.DRUE”, line 160

ORA-06512: at “CTXSYS.TEXTINDEXMETHODS”, line 366

 

Solution:

Grant create table to the schema account user of the table.

USE GRANT READ instead of GRANT SELECT in Oracle 12c

Normally when we want to grant an oracle database account access to read records form certain tables, we use the SQL command (GRANT SELECT), however this is found to be not the best security practice. And, new security feature has been introduced in Oracle 12c which is GRANT READ.

To illustrate more,

I have created a dummy account named “dummy_test” with the following basic privileges:

dummy account

And created a dummy table with random values called “DUMMY_RECORDS”, and executed the below SQL statement to grant the user access to read records from the table:

SQL> grant select on DUMMY_RECORDS to dummy_test ;

Now….the interesting part is the following…..i will be able to exclusively LOCK the table !!!

either by executing the following:

SQL> lock table DUMMY_RECORDS in exclusive mode;

OR

SQL> select * from DUMMY_RECORDS for update;

exclusive lock.jpg

Now, let us revoke (GRANT SELECT) and use (GRANT READ) on the table

grant read 1

grant read 2

as shown above, after logging with the account we were not able to exclusively  lock the table and ORA-01031 was thrown.

Important Remarks:

  • this security feature is only available in 12c release.

 

  • some applications could frequently use (select* from table for update) frequently so you need to test the consequences of using the GRANT READ permission.

 

  • the purpose of this security feature is that it will prevent the hacker who stole the credentials of the account to lock the table which will block transactions and impact the running the application ! (denial of service)

 

 

 

 

ORA-01031: insufficient privileges while creating MATERIALIZED VIEW

One of the things i have found by coincidence in Oracle 11g is when you try to create a materialized view by a non-schema user (even if he has a ‘DBA‘ privilege), you will face the following error:

ERROR at line 1: ORA-01031: insufficient privileges

 

although the user is powerful but he could not create the materialized view under any schema (in the following example the schema name is “sico“).

SQL> CREATE MATERIALIZED VIEW sico.test_mv AS SELECT * FROM dual;
CREATE MATERIALIZED VIEW sico.test_mv AS SELECT * FROM dual
*
ERROR at line 1:
ORA-01031: insufficient privileges

 

you can create a materialized view using the schema owner after granting him the “create table” privilege.

 

check it and see !!!

I personally faced this in Oracle 11gR2.