-1

I created a user in SQL using,

CREATE USER opt IDENTIFIED BY pass;

User got created. Then I try to grant certain privileges to the user,

    SQL> grant unlimited tablespace ,create session to opt;

    Grant succeeded.
    SQL> grant alter session to opt;
 
    Grant succeeded.

I connect to OPT user and then an alter session, but I am getting insufficient privileges error,

SQL> alter session set some_variable=0;
ERROR:
ORA-01031: insufficient privileges

I am new to this, please tell me where I am wrong. Thanks in advance. :)

Edit: After I read Roberto's answer, I tried adding

SQL> alter session set optimizer_mode=first_rows ;

Session altered.

This is working fine. But when I do

SQL> alter session set sql_trace=true ;
ERROR:
ORA-02097: parameter cannot be modified because specified value is invalid
ORA-20000: Missing database property for object store credentials

and

SQL> alter session set optimizer_dynamic_sampling=0;
ERROR:
ORA-01031: insufficient privileges
fthomas
  • 7
  • 1
  • 4

1 Answers1

2

If your user is granted with the create session privilege, most of the alter session statements are possible, except the ones which require specifically the alter session privilege.

Demo

SQL> create user test6 identified by Oracle_1234 ;

User created.

SQL> grant create session to test6 ;

Grant succeeded.

SQL> exit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.6.0.0.0

We now connect with the new user

$ sqlplus test6/Oracle_1234

SQL*Plus: Release 19.0.0.0.0 - Production on Thu Oct 7 14:03:57 2021
Version 19.6.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.


Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.6.0.0.0

SQL> alter session enable parallel dml ;

Session altered.

SQL> alter session enable parallel query ;

Session altered.

SQL> alter session set optimizer_mode=first_rows ;

Session altered.

SQL> alter session set sql_trace= true ;
ERROR:
ORA-01031: insufficient privileges

As the documentation states, in the following cases you would need to grant the privilege directly

  1. To enable and disable the SQL trace facility, you must have ALTER SESSION system privilege.

  2. To enable or disable resumable space allocation, you must have the RESUMABLE system privilege.

  3. You do not need any privileges to perform the other operations of this statement unless otherwise indicated.

If we grant the privilege, the enable of trace will work

SQL> grant alter session to test6 ;

Grant succeeded.

Then we connect again with the user

sqlplus test6/Oracle_1234

SQL*Plus: Release 19.0.0.0.0 - Production on Thu Oct 7 14:11:22 2021
Version 19.6.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.

Last Successful login time: Thu Oct 07 2021 14:11:03 +02:00

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.6.0.0.0

SQL> alter session set sql_trace=true ;

Session altered.

UPDATE

If you are using MULTITENANT, you need to create the user in the right pluggable database.

CONN / AS SYSDBA -- you connect to the container root
-- Switch container while connected to a common user.
ALTER SESSION SET CONTAINER = yourpdb;
-- Create the local user using the CONTAINER clause.
CREATE USER xxxx IDENTIFIED BY xxxxx CONTAINER=CURRENT;
GRANT CREATE SESSION TO xxxx CONTAINER=CURRENT;

You can connect directly to the pdb ( pluggable database )

CONN system/password@pdb1

-- Create the local user using the default CONTAINER setting.

CREATE USER xxxxxxxx IDENTIFIED BY xxxxxxxxxx;
GRANT CREATE SESSION TO xxxxxx;

Then, connect with the user to the pluggable database

conn xxxx/password@pdb1 

alter session set optimizer_dynamic_sampling=0 ;
Roberto Hernandez
  • 8,231
  • 3
  • 14
  • 43
  • thanks, Roberto for your reply. I am trying to write an sql file. So Initially I login as the admin, create OPT user and grant him both create session and alter session. Then, I connect to OPT and try to alter session. So when i do that, when I try to set optimizer_mode=first_rows as you have mentioned, It is working fine. but when I try to set optimizer_dynamic_sampling=0 it throws the insufficient privileges error. I am a bit confused why this is happening since I have granted Alter session privilege also to OPT Thanks again. – fthomas Oct 07 '21 at 12:59
  • @fthomas, if you are login as admin, you are using multitenant option, am I right ? Please, post the whole code in the question, how you connect, who is running the script to do the grants, and the error you get when you connect with the other user. Any alter session for the optimizer will work as long as the user has the create session privilege – Roberto Hernandez Oct 07 '21 at 13:22
  • I am calling SQL file from another file along with some parameters, please find the relevant parts of the code SQL> connect &admusr/&admpwd@&pdb1 Connected. SQL> --CREATE USER opt IDENTIFIED BY pass; SQL> grant unlimited tablespace ,create session ,advisor to opt; Grant succeeded. SQL> grant alter session to opt; Grant succeeded. SQL> conn opt/pass@&pdb1; Connected. SQL> alter session set optimizer_mode=first_rows ; Session altered. SQL> alter session set optimizer_dynamic_sampling=0; ERROR: ORA-01031: insufficient privileges – fthomas Oct 07 '21 at 14:07