Thanks for the question, Surajit.

Asked: March 23, 2018 - 2:16 pm UTC

Last updated: February 06, 2019 - 12:41 am UTC

Version: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

Viewed 1000+ times

You Asked

We have a database consists of 14 users and approx 60 people are using it through PLSQL developer. I would like to restrict 5 session each OSuser in PLSQL developer so that weblogic application can have sufficient surplus session. Please guide how can I implement this.

Thanks in advance

and Connor said...

I'll suggest something different:

Do NOT have your weblogic application share the same user accounts as people logging in. That sounds like a really really bad idea.

Here's a simple starting point:

1) Have an account that owns all of your objects (tables, views, etc etc). Let's call that "SCHEMA_OBJ"
2) Have an account that can do insert, update, delete, select on everything in SCHEMA_OBJ (and execute privileges on procedures etc). Let's call that "SCHEMA_APP". That is the schema that your weblogic application will connect as
3) Have an account for *each* person (so you can audit them, track them etc). Give them read-only access to the tables, views in SCHEMA_OBJ

That's a basic scheme to get you going.

Rating

(3 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

I have a similar situation

Chuy Sanchez, January 30, 2019 - 11:18 pm UTC

After my "SCHEMA_APP" account puts the data in my "SCHEMA_OBJ" I have a set of analysts and modelers that need the capability to write to a common "SCHEMA_OBJ_2" in order to leverage their own ad-hoc work.

Some of these users have the habit of initiating several sessions which puts extra load into the DB.

I want to be able to limit the number of sessions each OSUSER initiates on "SCHEMA_OBJ_2".

I have read in other forum that you can accomplish this by creating a role with this limitation and assign it to these users. Is that the best alternative?

Thanks,

February 01, 2019 - 3:10 pm UTC

create profile MAX_TWO limit sessions_per_user 2;

alter user SCHEMA_OBJ_2 profile MAX_TWO;

I have a similar situation (2)

A reader, February 01, 2019 - 4:09 pm UTC

That wouldn't work on my case. Let's say I have 10 OSUSER that need to run scripts on SCHEMA_OBJ_2. What I want if for these 10 OSUSER to fire at most 2 sessions at a time (20 total sessions on SCHEMA_OBJ_2 evenly distributed).

Today each OSUSER can fire multiple sessions, which is a problem.

February 06, 2019 - 12:41 am UTC

Well, its not really a limit on database users but OS users. In that case, you could use a login trigger. Some pseudo code below

create or replace
trigger check_logins
after logon on SCHEMA_OBJ_2.schema
declare
  c int;
begin
    select count(*) into c from sys.gv_$session
    where  osuser = sys_context('userenv','os_user');
  
    if c > ... then
      raise_application_error(-20000,'Whoa there... you are being greedy');
    end if;
end;
/

share and share alike

Racer I., February 04, 2019 - 7:57 am UTC

Hi,

@A reader :
I don't know if this will work for your requirement but you can try and see :

https://blogs.oracle.com/oraclemagazine/on-becoming-others-limits-and-restoration

alter user b grant connect through a [with role roleX];

connect a[b]/a

Then test if a 2-connection-restriction on "a1" to "a10" is honored for each or if they inherit a 20-limit on b.

regards,

Breadcrumb

Question and Answer

You Asked

and Connor said...

Rating

Comments

I have a similar situation

I have a similar situation (2)

share and share alike

More to Explore

PL/SQL demos

PL/SQL docs

Restrict user session for each osuser

Breadcrumb

Question and Answer

You Asked

and Connor said...

Rating

Comments

I have a similar situation

I have a similar situation (2)

share and share alike

More to Explore

PL/SQL demos

PL/SQL docs