ORA-01031: insufficient privileges

General Discussion
1

Leveraging Oracle’s schema only user (18c+) and heres the LB properties file :

url=jdbc:oracle:thin:least_priv_user/@0.0.0.0:1521/XEPDB1
username: liquibase_admin
password: liquibase_admin123

Pre-Steps ( outside of the liquibase )
grant create session to liquibase_admin;
alter user least_priv_user grant connect through liquibase_admin;

Question : Upon doing an liquibase update

  1. why does this work only if GRANT ALL PRIVILEGES to liquibase_admin;
  2. db change logs tables gets created in both schemas - Not sure why

If a GRANT ALL PRIVILEGES to liquibase_admin privileges is not given :

The below error occurs :

Unexpected error running Liquibase: ORA-01031: insufficient privileges
[Failed SQL: (1031) CREATE TABLE LIQUIBASE_ADMIN.DATABASECHANGELOGLOCK (ID INTEGER NOT NULL, LOCKED NUMBER(1) NOT NULL, LOCKGRANTED TIMESTAMP, LOCKEDBY VARCHAR2(255), CONSTRAINT PK_DATABASECHANGELOGLOCK PRIMARY KEY (ID))]

  1. If the lb_admin is only given create TABLE privileges then the below error occurs when processing changelog :
    Reason: liquibase.exception.DatabaseException: ORA-01031: insufficient privileges
    …
2

The user that Liquibase is using to connect to the database is expected to have privileges to CREATE, ALTER, etc all object types. You need to expand your set of privileges.

3

Thanks , but even the below doesnt help either
grant create view …
grant create table …
grant alter session …
grant create cluster …
grant create session …
grant create synonym …
grant create trigger …
grant create sequence …
grant create procedure …
grant create database link …

4

I see you are running Liquibase as “lb_admin”, but your schema in the sql is “LIQUIBASE_ADMIN”. In order to create a table in another schema lb_admin would need the “CREATE ANY TABLE” privilege.

5

Thanks , it is liquibase_admin, I had only updated it up on this forum post . I edited the original post. The errors still occurs.

6

Yes updated grants with “create any …” privileges but it does not seem to help.

7

Why do you have “least_priv_user” in your jdbc string?

8

The least privileged user in that jdbc string is the user used to have proxy connection to the liquibase_admin.
Heres more info on that type of accounts :

9

All of my Liquibase connections use proxy connections. I’m not familiar with the technique you are trying, I would recommend you try this:

  1. remove least_priv_user from the jdbc string
  2. change username to: least_priv_user[liquibase_admin]

That is how all of my proxy connections are done with Liquibase.

10

Hey , thanks it helped though it was more than adjustment to URL.

It was a combination of : alter grant connect statement , adjustment to the jdbc url & the additional grants that the DDL needed ( for constraints )

So it worked.

thanks
:grinning:

1 Like