Oracle DB user privileges

Configuring the server
1

I can setup keycloak with oracle database when i give the database user all privileges
GRANT ALL PRIVILEGES to user

But I want to restrict it. Does anyone has an idea wich minimal privileges a oracle user must have to work with keycloak?

I’m not so familar with sqlplus and oracle. maybe someone can provide me the whole command to do that?

many thanks and kind regards

2

Hi,

This is what I’m running. There are grants to session, table, and recyclebin. Also, the KC user owns the tablespace.

-Carl

CREATE USER KEYCLOAK IDENTIFIED BY somepasswd

GRANT CREATE SESSION TO KEYCLOAK;

GRANT CREATE TABLE TO KEYCLOAK;

CREATE TABLESPACE KEYCLOAK_DATA
    DATAFILE 'D:\app\ORADATA\ORCL\KEYCLOAK.DATA' SIZE 500K REUSE
    AUTOEXTEND ON NEXT 500K MAXSIZE 250M;

ALTER USER KEYCLOAK QUOTA 100M ON KEYCLOAK_DATA

ALTER USER KEYCLOAK QUOTA 100M ON USERS;

ALTER USER KEYCLOAK DEFAULT TABLESPACE KEYCLOAK_DATA;

GRANT SELECT ON SYS.DBA_RECYCLEBIN TO KEYCLOAK;
3

Hi Carl

Thank you so much. I think the tablespace will be created automatically when keycloak ist starting.

When i except that part with tablespace then I run into that error:

Failed to insert first record to the lock table. Maybe other transaction inserted in the meantime. Retrying…

i did that:
GRANT CREATE SESSION TO KEYCLOAK;
GRANT CREATE TABLE TO KEYCLOAK;
GRANT SELECT ON SYS.DBA_RECYCLEBIN TO KEYCLOAK;

is there a way to grant insert, delete etc. without a prepared tablespace?

BR

4

It’s probably attempting to use a default tablespace rather than creating a new one on-the-fly.

What’s the problem you’re having with setting up a dedicated tablespace?

5

Hi Carl

My issue with the tablespace is because my oracle DB is a pluggable db in a oracle cloud infrastructure PaaS container db system. I’m not sure what I have to fill out in DATAFILE instead of your example “D:\app\ORADATA\ORCL\KEYCLOAK.DATA”

For other applications as example i have that script. Should I use that here, too?
CREATE TABLESPACE “XY”
LOGGING
DATAFILE
SIZE 100M
AUTOEXTEND ON NEXT 64M
EXTENT MANAGEMENT LOCAL
SEGMENT SPACE MANAGEMENT AUTO;

CREATE USER XY PROFILE "DEFAULT"
IDENTIFIED BY password
DEFAULT TABLESPACE "XY"
TEMPORARY TABLESPACE "TEMP"
ACCOUNT UNLOCK;

GRANT CONNECT, RESOURCE, SELECT_CATALOG_ROLE, CREATE VIEW TO “XY”;
GRANT READ, WRITE ON DIRECTORY DATA_PUMP_DIR TO “XY”;
ALTER USER “XY” QUOTA UNLIMITED ON “XY”;

6

Ah. I’m not sure unfortunately. I went the tablespace-per-user route because I wasn’t sure I could control where Keycloak would create its DB objects.

7

Ok, that works for me I think, thank you for your input.

SQL> CREATE TABLESPACE “KEYCLOAK” LOGGING DATAFILE SIZE 100M AUTOEXTEND ON NEXT 64M EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;
SQL> CREATE USER KEYCLOAK PROFILE “DEFAULT” identified by “password” DEFAULT TABLESPACE “KEYCLOAK” TEMPORARY TABLESPACE “TEMP” ACCOUNT UNLOCK;
SQL> GRANT CREATE SESSION TO KEYCLOAK;
SQL> GRANT CREATE TABLE TO KEYCLOAK;
SQL> GRANT CONNECT, RESOURCE, SELECT_CATALOG_ROLE, CREATE VIEW TO “KEYCLOAK”;
SQL> GRANT READ, WRITE ON DIRECTORY DATA_PUMP_DIR TO “KEYCLOAK”;
SQL> GRANT SELECT ON SYS.DBA_RECYCLEBIN TO KEYCLOAK;
SQL> ALTER USER “KEYCLOAK” QUOTA UNLIMITED ON “KEYCLOAK”;
SQL> ALTER TABLESPACE TEMP ADD TEMPFILE;

8

Hello Carl,

Kindly is there a way to override the USERS tablespace? even though when created the schema i have specified a table space but when the keycloak started
i had the error belwo

Caused by: liquibase.exception.DatabaseException: ORA-01950: no privileges on tablespace ‘USERS’

BR,
Odai