Home » Open Source » Programming Interfaces » alter session set "_ORACLE_SCRIPT"=true -> insufficient privileges (Oracle 12.2)
alter session set "_ORACLE_SCRIPT"=true -> insufficient privileges [message #675306] Thu, 21 March 2019 13:17 Go to next message
a100
Messages: 34
Registered: March 2019
Member
Hello,
forgive my ignorance - I am total noob with Oracle ...

I have just created Oracle 12.2 in AWS for dev/testing purposes and connected to it using the admin account.
I am trying to run the following:
alter session set "_ORACLE_SCRIPT"=TRUE
create user ...
but gets the following error - does anybody know why ?
Reason:
SQL Error [2097] [42000]: ORA-02097: parameter cannot be modified because specified value is invalid
ORA-01031: insufficient privileges
Re: alter session set "_ORACLE_SCRIPT"=true -> insufficient privileges [message #675307 is a reply to message #675306] Thu, 21 March 2019 13:51 Go to previous messageGo to next message
Michel Cadot
Messages: 68716
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator

Why do you want to do it?

"_" parameter should NEVER be used unless with Oracle support advice.
And above these (hidden) parameter should NEVER be used by a noob.

In this case, it is clear what the parameter means: ONLY set it to run Oracle script and ONLY through Oracle software or with Oracle assistance.

Re: alter session set "_ORACLE_SCRIPT"=true -> insufficient privileges [message #675308 is a reply to message #675307] Thu, 21 March 2019 15:05 Go to previous messageGo to next message
a100
Messages: 34
Registered: March 2019
Member
I couldn't create any account on 12.2 and after googling I found this solution.
I need to have a consistent way (Python script) to create database accounts for both Oracle 11.* and 12.*.
Is there any another way ?
Re: alter session set "_ORACLE_SCRIPT"=true -> insufficient privileges [message #675309 is a reply to message #675308] Thu, 21 March 2019 15:14 Go to previous messageGo to next message
Michel Cadot
Messages: 68716
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator

As everyone can do it, I should say do it correctly and you will succeed. Smile
In other word: "I couldn't create any account" does not help in any way to know what you are doing wrong.
Most likely you are connect to CDB (root container) and not to a PDB (pluggable database) as you should.

Use SQL*Plus and copy and paste your session, the WHOLE session.
Before, Please read How to use [code] tags and make your code easier to read.

Re: alter session set "_ORACLE_SCRIPT"=true -> insufficient privileges [message #675310 is a reply to message #675308] Thu, 21 March 2019 15:28 Go to previous messageGo to next message
John Watson
Messages: 8960
Registered: January 2010
Location: Global Village
Senior Member
a100 wrote on Thu, 21 March 2019 20:05
I couldn't create any account on 12.2 and after googling I found this solution.
I need to have a consistent way (Python script) to create database accounts for both Oracle 11.* and 12.*.
Is there any another way ?
You cannot have a "consistent" way because your 12.x database is a multitenant database, which did not exist in 11.x.
Re: alter session set "_ORACLE_SCRIPT"=true -> insufficient privileges [message #675311 is a reply to message #675308] Thu, 21 March 2019 15:39 Go to previous messageGo to next message
Michel Cadot
Messages: 68716
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator

Quote:
I need to have a consistent way (Python script) to create database accounts for both Oracle 11.* and 12.*.
There is no difference if you connect to the PDB.
Of course, you have to do it using "remote" connection.
So the ONLY thing you have to change is your connect string.
Which you already do as you can't connect with the same string in your 11g and 12c databases.

Re: alter session set "_ORACLE_SCRIPT"=true -> insufficient privileges [message #675312 is a reply to message #675310] Thu, 21 March 2019 15:42 Go to previous messageGo to next message
a100
Messages: 34
Registered: March 2019
Member
John Watson wrote on Thu, 21 March 2019 15:28
You cannot have a "consistent" way because your 12.x database is a multitenant database, which did not exist in 11.x.
I need to create users with the following permissions:
'CREATE USER user1 IDENTIFIED BY "pass" DEFAULT TABLESPACE users QUOTA unlimited ON users'
'GRANT CREATE SESSION TO user1'
'GRANT CREATE TABLE TO user1'
'GRANT CREATE VIEW TO user1'
'GRANT CREATE SEQUENCE TO user1'

Is it for Oracle 12 or 11 ? And can I switch 12c to be in 11c-compatible mode ?
Re: alter session set "_ORACLE_SCRIPT"=true -> insufficient privileges [message #675313 is a reply to message #675311] Thu, 21 March 2019 15:52 Go to previous messageGo to next message
a100
Messages: 34
Registered: March 2019
Member
Michel Cadot wrote on Thu, 21 March 2019 15:39

There is no difference if you connect to the PDB.
Of course, you have to do it using "remote" connection.
So the ONLY thing you have to change is your connect string.
Which you already do as you can't connect with the same string in your 11g and 12c databases.
How do I enforce connection to PDB ?
I use actually cx_Oracle (Python) to connect to Oracle.
Not sure if the connection string is consistent Wink for both sql*plus and cx_Oracle ...
Re: alter session set "_ORACLE_SCRIPT"=true -> insufficient privileges [message #675314 is a reply to message #675312] Thu, 21 March 2019 15:52 Go to previous messageGo to next message
Michel Cadot
Messages: 68716
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator

I told you what to do: you just have to correctly connect to the PDB.

Quote:
And can I switch 12c to be in 11c-compatible mode ?

12c is 11g-compatible.

Re: alter session set "_ORACLE_SCRIPT"=true -> insufficient privileges [message #675315 is a reply to message #675313] Thu, 21 March 2019 15:53 Go to previous messageGo to next message
Michel Cadot
Messages: 68716
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator

Quote:
How do I enforce connection to PDB ?
I use actually cx_Oracle (Python) to connect to Oracle.
Not sure if the connection string is consistent Wink for both sql*plus and cx_Oracle ...

Show the code.

Re: alter session set "_ORACLE_SCRIPT"=true -> insufficient privileges [message #675316 is a reply to message #675315] Thu, 21 March 2019 15:57 Go to previous messageGo to next message
a100
Messages: 34
Registered: March 2019
Member
import cx_Oracle
..
dsn = cx_Oracle.makedsn('my.server.with.fqdn',1521,'MYDB')
Re: alter session set "_ORACLE_SCRIPT"=true -> insufficient privileges [message #675320 is a reply to message #675316] Fri, 22 March 2019 01:57 Go to previous messageGo to next message
Michel Cadot
Messages: 68716
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator

Have a look at this example to connect to Oracle with a service name using cx_Oracle (example 7).

[Updated on: Fri, 22 March 2019 02:00]

Report message to a moderator

Re: alter session set "_ORACLE_SCRIPT"=true -> insufficient privileges [message #675324 is a reply to message #675320] Fri, 22 March 2019 03:52 Go to previous messageGo to next message
a100
Messages: 34
Registered: March 2019
Member
        if dsn and sid and not service_name:
            dsn = cx_Oracle.makedsn(dsn, conn.port, sid)
            conn = cx_Oracle.connect(conn.login, conn.password, dsn=dsn)
        elif dsn and service_name and not sid:
            dsn = cx_Oracle.makedsn(dsn, conn.port, service_name=service_name)
            conn = cx_Oracle.connect(conn.login, conn.password, dsn=dsn)
        else:
            conn = cx_Oracle.connect(conn.login, conn.password, conn.host)
This code presents many ways to connect to Oracle. Which one is the connection to PDB ?
Re: alter session set "_ORACLE_SCRIPT"=true -> insufficient privileges [message #675326 is a reply to message #675324] Fri, 22 March 2019 03:58 Go to previous messageGo to next message
John Watson
Messages: 8960
Registered: January 2010
Location: Global Village
Senior Member
You need to provide the service name.
Re: alter session set "_ORACLE_SCRIPT"=true -> insufficient privileges [message #675327 is a reply to message #675324] Fri, 22 March 2019 04:06 Go to previous messageGo to next message
Michel Cadot
Messages: 68716
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator

Maybe the one with service name as I mentioned...

Oops! John already said it.

[Updated on: Fri, 22 March 2019 04:07]

Report message to a moderator

Re: alter session set "_ORACLE_SCRIPT"=true -> insufficient privileges [message #675335 is a reply to message #675327] Fri, 22 March 2019 05:28 Go to previous messageGo to next message
a100
Messages: 34
Registered: March 2019
Member
From the code:
        :param service_name: the db_unique_name of the database that you are connecting to (CONNECT_DATA part of TNS)
        You can set these parameters in the extra fields of your connection
        as in ``{ "dsn":"some.host.address" , "service_name":"some.service.name" }``

I am not using any tnsnames.ora - how do I get service name from the existing DB ? Any select to retrieve it ?

[Updated on: Fri, 22 March 2019 05:29]

Report message to a moderator

Re: alter session set "_ORACLE_SCRIPT"=true -> insufficient privileges [message #675337 is a reply to message #675335] Fri, 22 March 2019 05:47 Go to previous messageGo to next message
a100
Messages: 34
Registered: March 2019
Member
By the way - do I need to create a new PDB or is it (default ?) available just after the DB has been created ?
Re: alter session set "_ORACLE_SCRIPT"=true -> insufficient privileges [message #675338 is a reply to message #675337] Fri, 22 March 2019 05:52 Go to previous messageGo to next message
John Watson
Messages: 8960
Registered: January 2010
Location: Global Village
Senior Member
When you created the database, if you used the DBCA you were prompted whether or not to create one or more PDBs and if so what the name(s) would be. You were there, I wasn't.
Re: alter session set "_ORACLE_SCRIPT"=true -> insufficient privileges [message #675339 is a reply to message #675338] Fri, 22 March 2019 05:59 Go to previous messageGo to next message
a100
Messages: 34
Registered: March 2019
Member
What would be a sql select to retrieve if there are any PDBs ?
Re: alter session set "_ORACLE_SCRIPT"=true -> insufficient privileges [message #675340 is a reply to message #675339] Fri, 22 March 2019 06:02 Go to previous messageGo to next message
John Watson
Messages: 8960
Registered: January 2010
Location: Global Village
Senior Member
Are you telling me that you do not remember what you did, and cannot be bothered to look at the log files? Hmmmm. If I were your DBA, I would be saying that you need to put a bit more thought into things. In the meantime, you could

select name,open_mode from v$containers;

and do some reading about basic DB admin:

https://docs.oracle.com/en/database/oracle/oracle-database/18/admqs/index.html

Enjoy....
Re: alter session set "_ORACLE_SCRIPT"=true -> insufficient privileges [message #675343 is a reply to message #675340] Fri, 22 March 2019 06:12 Go to previous messageGo to next message
a100
Messages: 34
Registered: March 2019
Member
Hi John,
I used AWS GUI to create this DB and I really don't remember I was asked any PDB-related question. I would say I am 100% sure I wasn't but ... you know.
The `PDB` word would trigger some bell in my mind but when you and Michel asked me about it I was lost ...
Thank you for the select:
ORCL	READ WRITE
I remember I was asked for this "ORCL" but it was kind of default name for DB and I didn't know about any PDB ...

Believe me if we had Oracle DBA I wouldn't ask you those questions - our software just supports Oracle as one of the databases but we don't really use it in production.

[Updated on: Fri, 22 March 2019 06:12]

Report message to a moderator

Re: alter session set "_ORACLE_SCRIPT"=true -> insufficient privileges [message #675344 is a reply to message #675343] Fri, 22 March 2019 06:18 Go to previous messageGo to next message
John Watson
Messages: 8960
Registered: January 2010
Location: Global Village
Senior Member
I deduce from this that you are using RDS. If you had said all that at the beginning you would have saved a lot of time.

You had better start again: show what the problem is that you are trying to solve, not the attempted solution. Do not fall into the XY problem:

https://en.wikipedia.org/wiki/XY_problem
Re: alter session set "_ORACLE_SCRIPT"=true -> insufficient privileges [message #675345 is a reply to message #675344] Fri, 22 March 2019 06:34 Go to previous messageGo to next message
a100
Messages: 34
Registered: March 2019
Member
John,
yes I used RDS in this particular case. But I also have another Oracle 12 DB in Azure and another Oracle 11 in both AWS/Azure.
I just wanted to create automatically accounts and grant some privileges (as a prereq for tests) using my cx_Oracle script and found that the same method didn't work for both 11 and 12.
Started googling and found some workaround with alter session.
That is the whole story.

From what I read above it looks like there is a consistent way (SQL) to create those accounts in both 11 and 12 on condition I connect to PDB.
To do that I need to add "service_name" to cx_oracle connection.
Do I understand correctly ?
Re: alter session set "_ORACLE_SCRIPT"=true -> insufficient privileges [message #675346 is a reply to message #675345] Fri, 22 March 2019 07:02 Go to previous messageGo to next message
John Watson
Messages: 8960
Registered: January 2010
Location: Global Village
Senior Member
Quote:
I just wanted to create automatically accounts and grant some privileges (as a prereq for tests) using my cx_Oracle script and found that the same method didn't work for both 11 and 12.
yes, the XY problem! What is this script, and what happens when you run it?
Re: alter session set "_ORACLE_SCRIPT"=true -> insufficient privileges [message #675347 is a reply to message #675346] Fri, 22 March 2019 07:39 Go to previous messageGo to next message
a100
Messages: 34
Registered: March 2019
Member
import cx_Oracle
...     
dsn = cx_Oracle.makedsn(args['--server'], args['--port'], args['--dbname'], service_name=args['--dbname'])
print('dsn: {}'.format(dsn))
...
connection = cx_Oracle.connect(args['--dbadmin'], args['--dbadminpass'], dsn)
...
It does work for AWS (RDS) and doesn't for Oracle in Azure:
dsn: (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=myhost)(PORT=1521))(CONNECT_DATA=(SID=ORCLCDB)(SERVICE_NAME=ORCLCDB)))
Traceback (most recent call last):
  File "/db.py", line 105, in <module>
    main(args)
  File "/db.py", line 68, in main
    connection = cx_Oracle.connect(args['--dbadmin'], args['--dbadminpass'], dsn)
cx_Oracle.DatabaseError: ORA-12514: TNS:listener does not currently know of service requested in connect descriptor
Re: alter session set "_ORACLE_SCRIPT"=true -> insufficient privileges [message #675348 is a reply to message #675347] Fri, 22 March 2019 08:05 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
post results from OS command below run on Azure DB Server

lsnrctl service
Re: alter session set "_ORACLE_SCRIPT"=true -> insufficient privileges [message #675349 is a reply to message #675348] Fri, 22 March 2019 08:10 Go to previous messageGo to next message
a100
Messages: 34
Registered: March 2019
Member
This is Oracle running in Docker container (the official Oracle docker image):
$ lsnrctl service

LSNRCTL for Linux: Version 12.2.0.1.0 - Production on 22-MAR-2019 13:06:51

Copyright (c) 1991, 2016, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=0.0.0.0)(PORT=1521)))
Services Summary...
Service "83e7c9a565ff015be0530400000a92a7.localdomain" has 1 instance(s).
  Instance "ORCLCDB", status READY, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:47317 refused:0 state:ready
         LOCAL SERVER
Service "ORCLCDB.localdomain" has 1 instance(s).
  Instance "ORCLCDB", status READY, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:47317 refused:0 state:ready
         LOCAL SERVER
Service "ORCLCDBXDB.localdomain" has 1 instance(s).
  Instance "ORCLCDB", status READY, has 1 handler(s) for this service...
    Handler(s):
      "D000" established:0 refused:0 current:0 max:1022 state:ready
         DISPATCHER <machine: myhost, pid: 141>
         (ADDRESS=(PROTOCOL=tcp)(HOST=myhost.fqdn)(PORT=45017))
Service "orclpdb1.localdomain" has 1 instance(s).
  Instance "ORCLCDB", status READY, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:47317 refused:0 state:ready
         LOCAL SERVER
The command completed successfully
Re: alter session set "_ORACLE_SCRIPT"=true -> insufficient privileges [message #675351 is a reply to message #675349] Fri, 22 March 2019 10:18 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
Please notice the actual "Service" names that are enclosed in double quote marks.

In order for any client to connect via SQL*Net, you must use the complete Service Name that the listener knows about; i.e. "ORCLCDB.localdomain"
Re: alter session set "_ORACLE_SCRIPT"=true -> insufficient privileges [message #675353 is a reply to message #675349] Fri, 22 March 2019 10:43 Go to previous messageGo to next message
John Watson
Messages: 8960
Registered: January 2010
Location: Global Village
Senior Member
The naming convention is clear, you should be connecting to the service orclpdb1.localdomain which will be your pluggable container.
Re: alter session set "_ORACLE_SCRIPT"=true -> insufficient privileges [message #675354 is a reply to message #675353] Fri, 22 March 2019 10:56 Go to previous messageGo to next message
a100
Messages: 34
Registered: March 2019
Member
When I used:
SERVICE_NAME=ORCLCDB.localdomain
I got:
CREATE USER user IDENTIFIED BY "pass" DEFAULT TABLESPACE users QUOTA unlimited ON users

Error: ORA-65096: invalid common user or role name

When I used:
SERVICE_NAME=orclpdb1.localdomain
I got:
cx_Oracle.DatabaseError: ORA-01017: invalid username/password; logon denied
It looks like I don't have any PDB ?
Re: alter session set "_ORACLE_SCRIPT"=true -> insufficient privileges [message #675356 is a reply to message #675354] Fri, 22 March 2019 11:30 Go to previous message
John Watson
Messages: 8960
Registered: January 2010
Location: Global Village
Senior Member
I've already told you what to do. You do have to provide the correct password, though. What username and password are you using?

[Updated on: Fri, 22 March 2019 11:31]

Report message to a moderator

Previous Topic: SQL query returning wrong values
Next Topic: Looking for more information about a bug
Goto Forum:
  


Current Time: Sat Nov 23 03:28:38 CST 2024