Home » RDBMS Server » Security » Max string size for regular users (18)
Max string size for regular users [message #689636] Wed, 06 March 2024 16:34 Go to next message
Darth Waiter
Messages: 78
Registered: October 2020
Member
When I try to run the below query under a regular user account it fails because the table does not exist.

SELECT value FROM sys.v$parameter WHERE name = 'max_string_size'
Do regular users have to try to use long varchar2 columns and fail, to establish that the setting is not on, or is there another method?
Re: Max string size for regular users [message #689637 is a reply to message #689636] Thu, 07 March 2024 01:04 Go to previous messageGo to next message
Michel Cadot
Messages: 68664
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Grant SELECT on the view sys.v_$parameter to your user.

[Updated on: Thu, 07 March 2024 01:12]

Report message to a moderator

Re: Max string size for regular users [message #689638 is a reply to message #689637] Thu, 07 March 2024 05:55 Go to previous messageGo to next message
Darth Waiter
Messages: 78
Registered: October 2020
Member
My software has to run under arbitrary user accounts chosen by the end user. More often than not they would choose regular user accounts nor would they even be aware of such things. I have no control of what they do or do not.

I find to be very odd Oracle's decision to hide server configuration from regular users and have them resort to less-than-graceful means such as try create a table and trap error 910. It would have been the same as in MS SQL one could not query @@ variables unless sa.
Re: Max string size for regular users [message #689639 is a reply to message #689638] Thu, 07 March 2024 06:13 Go to previous messageGo to next message
Michel Cadot
Messages: 68664
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Security security.

Why end user would create tables on the fly?
Tables are part of the application code.
Would regular user modify the application code? Do yo allow them to modify the source code?

Re: Max string size for regular users [message #689640 is a reply to message #689639] Thu, 07 March 2024 07:16 Go to previous messageGo to next message
Darth Waiter
Messages: 78
Registered: October 2020
Member
Even if I accept that users should not create tables, which is subjective, they still declare variables, do they not?

And what is Oralce trying to hide? nls_language?
If knowing max length is such a frightful security breach, then these parameters should not have been lumped into one pile with risky ones.
Re: Max string size for regular users [message #689642 is a reply to message #689640] Thu, 07 March 2024 08:42 Go to previous messageGo to next message
Michel Cadot
Messages: 68664
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
they still declare variables, do they not?

An end user can declare variables in its application? No.
A developer can.
A developer can read and modify all system variables? No.
An administrator can.


Quote:
And what is Oralce trying to hide? nls_language?

NLS_LANGUAGE is a client variable, user can read (using NLS_SESSION_PARAMETERS view) and overwrite (using ALTER SESSION) NLS variables but can't declare or create new ones.


Quote:
If knowing max length is such a frightful security breach, then these parameters should not have been lumped into one pile with risky ones.

All system parameters are protected in the same way.
If an application designer needs extended length then he asks for it for his database as a prerequisite for the application as he will for server memory, CPU, Oracle version... Extended length will be activated by the DBA responsible of the database where the application will be deployed.
A developer for this application won't have to check if extended length is there or not, he just have to assume it is there otherwise it means his boss does not give him the correct platform to do his job.
An application user won't have to even know there is such thing that extended length.
An application user uses the application and read/modify data through it, he even has not to know what are the columns and their datatype.

Anyway, as I said, it is open to you to grant access to system variables if it is needed for your application.
Grants are part of the application as its source code is.
By default, Oracle protects system variables, you want to loosen that, Oracle provides you the way to do it.
What do you want more?

Re: Max string size for regular users [message #689644 is a reply to message #689642] Thu, 07 March 2024 09:53 Go to previous messageGo to next message
cookiemonster
Messages: 13925
Registered: September 2008
Location: Rainy Manchester
Senior Member
You could always Grant SELECT on the view sys.v_$parameter to public if you really need the ability to let anyone access it.
You could create a view on top v_$parameter that just selects the parameters your users need and grant select on that to public.
Re: Max string size for regular users [message #689645 is a reply to message #689644] Thu, 07 March 2024 10:18 Go to previous messageGo to next message
Darth Waiter
Messages: 78
Registered: October 2020
Member
No, I cannot grant anything because my software runs on arbitrary Oracle instances that I do not own, under arbitrary user accounts.
The only way is to try to create a table with a single column that is longer than 2000 characters and trap error 910, which violates one of the fundamental principles of software development: do not use exception handling as flow control.
Re: Max string size for regular users [message #689646 is a reply to message #689645] Thu, 07 March 2024 10:51 Go to previous messageGo to next message
Michel Cadot
Messages: 68664
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
No, I cannot grant anything because my software runs on arbitrary Oracle instances that I do not own, under arbitrary user accounts.
Your software has prerequisites to run on a database, so you ask your client for these prerequisites otherwise it couldn't provide the service it is built for.
If your client does not agree with these prerequisites then he doesn't buy your software or accept to use it in degraded mode.
And why if your client refuses to grant your user CREATE TABLE privilege or quota on tablespace?

In the end, it seems your software tries to break security and act as a hacker (you need a privilege your client does not grant by default and you don't want to ask).

There are many legal ways to achieve what you want (cookiemonster mentioned some), all of them require you ask your client to grant some privileges, possibly create some objects, and all of them require your client voluntarily accepts it.

Re: Max string size for regular users [message #689647 is a reply to message #689646] Thu, 07 March 2024 10:57 Go to previous messageGo to next message
Darth Waiter
Messages: 78
Registered: October 2020
Member
Seriously? Knowing maximum width of varchar2 fields is a security breach in Oracle?
Re: Max string size for regular users [message #689648 is a reply to message #689647] Thu, 07 March 2024 11:11 Go to previous messageGo to next message
Michel Cadot
Messages: 68664
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Who knows? Anyway ALL (not-hidden) system parameters are in this view and this view is not accessible to anyone by default.

Why do you need that? Why do your user need to create table? What is your software?
Why don't you want to ask the database owner the resources you need?

[Updated on: Thu, 07 March 2024 11:11]

Report message to a moderator

Re: Max string size for regular users [message #689649 is a reply to message #689648] Fri, 08 March 2024 06:39 Go to previous messageGo to next message
cookiemonster
Messages: 13925
Registered: September 2008
Location: Rainy Manchester
Senior Member
Bottom line - it's the way it is because it's a system parameter and 99.999999999% (add more decimal 9s to taste) of the time normal users don't need to know what the system parameters are set to.
You've got a deeply non-standard setup and are complaining that oracle didn't treat this system parameter differently to all the others because it's making your life inconvenient.
If you really aren't in a position to implement either work-around I suggested then you are left with 2 choices.
1. The users work it out the hard way.
2. The users ask whoever set up the particular instance in question what it's set to. It's an instance wide parameter. Someone set that instance up and should either know / be able to find out what it's set and communicate that fact to whoever needs to know.
Re: Max string size for regular users [message #689650 is a reply to message #689649] Fri, 08 March 2024 08:26 Go to previous message
Darth Waiter
Messages: 78
Registered: October 2020
Member
I have the solution: error 910 is the king.
Previous Topic: Grant DBA role using procedure doesn't work
Next Topic: SYS account is locked (merged)
Goto Forum:
  


Current Time: Tue Jun 25 07:39:14 CDT 2024