Restrict Concurrent User And / Or Session From A Client?
Nov 5, 2012
We have an application with database Oracle 10g.
I want to add a new validation to restrict concurrent user and/or session from a client. (we have almost 60 client firms using the software to enter daily trasnactions). All users from all clients are connecting to the database using a common functional ID.
What I did was:
1) Add a column 'user_logged_in' in the master table for client and update it as Y when user from that client logged on to the system,
2) Insert the application logon details (we can figure out the client details from this) into a global temp table,
3) Create a logoff trigger to update the 'user_logged_in'flag in client master table by using values from global temp table when session logged off and
4) Restrict the users from same client if the flag is 'Y'
But the problem in this case is logoff trigger will not be executed in case if the session got killed or terminated abnormally.
There is a requirement in my database that I want to restrict the user from directly running queries on database from third party tools such as pl/sql developer and toad.
There is a utility in SQL product_user_profile through which this can be done but it is only restricted if you run the query through sql plus. If I want to restrict and (give suppose select,insert) to a user for directly running queries through PL/SQL.
I have an urgent requirement to kill an existing session if a new session starts for the same user. I have been reading lot of blogs and posts on the above topic, but could clearly tell me how to do it.
I thought of putting a process in 101 page when login button is pressed to catch this and kill the old session.
We are designing a three tiered system (client, application/web server, database server) that will allow clients through a web interface to select a text file from the operating system and load that file into a intermediate table (import database table). Many users will do this concurrently and data will load into a single table. The text files come in monthly for about 100 firms. No user is able to insert or update the data of another users data (there is a check out system). Their are about 30 to 40 users that will be using the system doing various functions but it is possible for 10 to 20 users to import data at one time. The files can have anywhere from 2000 to 25000 records at a record length of 398.I am concerned about having a good design strategy as well as decent performance.
Problems with each of the Oracle loaders.
1) External tables - Can not read data text files on the application server(which is where they want the text files to go) secondly you cannot create a instance of a external table. Multiple users will be using the external table to point to different text files and loading at the same time.
2) Sqlloader - is mainly a OS level tool and I am not sure how I could programatically point it to a different text file each time a user wants to load. The client will have to have the ability through code to point sqlloader to the correct file name.
I had a creative approach and was wondering if this would work. I would like to use external tables just like a connection pool. I would propose first a scheduled OS job to move files to the database server. I would create about 20 external tables with 20 different directory objects. Using a stored procedure for the user to call and pass in file name and audit info as needed. I would use a Load lock pool table (my invention) to load the name or a code for the external table in use. The procedure loads this code into my load lock pool table when a external table is in use and deletes the name when the load is completed. The procedure would check through a series of if statements whether a particular external table was in use. If in use (exist in load lock pool table) I would check the next available external table until a external table not in use is encountered. Now potentially 20 users at one time but not likely would be laoding into the same table at one time.My questions
1) Could Oracle handle this strategy? What do I need to consider performance wise with the possibility of so many users loading into a single table at one time?
2) Do any of you maybe have another strategy to do this?
Suppose there is customers table and the user (Scott) is having select,update,insert privelege on the customers table.
a.)From the form i.e GUI he is having all the access,we want that when he logs on with sql plus directly then he would only be able to run select query only.
b.)When we are using third party tools such as pl/sql developer tools and toad then we are not able to restrict the user by running only the select queries.The utility which was used previously was only used for SQL Plus.
Resolution:
a.)I am able to restrict the user when he logs on with sqlplus and he is able to run anly select queries. b.)How we can restrict the user so that in pl/sql he is able to issue only the select query.
Trigger the user when logs on session id is captured and user can be restricted in logging in to the plsql developer and toad applications
I have a user ( for example Scott) and he doesn't belongs to DBA group i want to restrict accessing data dictionaries like dba_% to this Scott user. how to restrict Data dictionaries to this user.
Is it possible that we restrict user at data level? For Example 'A' user can only query employeess of deptno 10 only. He can not query employees of others dept.
I am using Forms 6i,Oracle Apps... I have database block.... generally we can retrieve the all the records by press F11 and Ctrl F11.... but my requirement is i have to restrict the user that they can access the records based on the condition ...
For example....let we take emp table....I have to restrict the user that they can query dept no 10 employees only.
SQL Plus version Oracle8 Enterprise Edition Release 8.0.5.0.0 - Production PL/SQL Release 8.0.5.1.0 Production Forms Version : 6i Reports Version: 6i O/S : Microsoft Windows Xp professional Version 2002 Service Pack 3
With reference to the above version details here is the requirement. How do I restrict user on key next item trigger i.e I want the cursor to move to next field only when user presses "ENTER" button on the key board any other key like mouse should not allow user to move to the next field.
Need a code which prevents the User from entering more than 30 characters in a field. Although the variable can accept more than 30 characters. My requirement is to validate the field by restricting up to 30 characters.
We are using Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 and Oracle Discoverer 9i desktop & Administrator,We have configured a physical standby database. We have ask users to connect to standby thru discoverer thru a different tns entry, but user can also connect on primary db.
We want to restrict the specified users to connect on primary database thru discoverer.
i have master and detail form. In detail(tabular form) i want to restrict user to enter only 10 rows.if he tries to go for 11th row he shud get a message (Only 10 records can be created at a time).
select a.SETTINGVALUE db_owner from ABC a, DEF b where a.application_id = b.application_id
I want to confirm after executing this .sql that this variables v_number and db_owner should not have NULL value. I did searched a lot for SQL*PLUS commands. Only way i can check this is by writing anonymous PL/SQL block but i dont want to go for that option. How to restrict input of NULL values ?
Data block 1- master_blk : For date/customer of sale invoice Data block 2- detail_blk1 (detail of the master block - For products and qty) Data block 3- detail_blk2 (detail of DETAIL_BLK1 For entering serial numbers of products)
My requirement is that whatever quantity user enter in data block 2 against each product he must enter equal number of serial numbers of that product in data block 3.
For this I have created on item (cnt_iteml : to count product's serial numbers in block3 ) in data block 2, and on summary item (t_serial_no ) in block3.
Whenever user changes in quantity, cnt_iteml: item is populated with t_serial_no in block3 of that product by following trigger on quantity column.
Following trigger is written on block level at data block-3 to populate cnt_iteml with t_serial_no. PRE-RECORD
IF GET_BLOCK_PROPERTY('SERIAL_NUMBERS',STATUS) IN ('CHANGED') THEN :stock_transactions.cnt_itl:=:serial_numbers.t_serial_no; END IF;
Above triggers are fulfilling my requirement except following condition.
If user after entering serial numbers in block 3 and without saving goes back to block2 and try to navigate to another record he gets a message asking him to save changes by forms. At this time if user presses no then cnt_itl item is not been populated with t_serial_no item's value.
What I want in above condition is that if user was inserting new record cnt_it item should be populated with 0, so that he shouldn't be able to save this record. And If he was updating then cnt_itl item should be populated with actual no of records in database against that product.
I was asked if it was possible to restrict which users / or client IP's connect to my Oracle 11.2 database. I guess I could just shutdown the listener and have me and one other DBA connect to it via SSH / LOCALHOST but I was wondering if there was a more DBA specific way to restrict client connections to just two specific IP's over the Oracle listening port?
I have a user in my oracle database, I would like to know details such as, how many times and by using which tools same user has got logged for past one week.
How do I do that. I have sql which shows the current session either he is logged in, if so then which application or not logged in. But I required the information for past one week How do I get those details.
How can I find out a user session/connection details like ( his last connection time, his activity etc)
Here is the issue, a old dba_user_id who physically no longer exists is not removed from the database. At some time, a session is opened under his user_id and some activity runs and closes the session. So, checked scheduled jobs under him and disabled them. But not sure whether the scheduled jobs are creating a session under his user_id or some thing else.
I checked in v$session, but since I am not sure at what time his session opens, so I am not able to get his session details.
I can get "CPU used by this session' value from v$sesstat. That is the value in centiseconds used by each session. How to convert the value into a percentage of total CPU resource ?
Or there are some other ways to gain the value. i.e. Percentage of Total CPU usage for each Oracle session.
How do I get my list of user session info? I thought there was a user_session_parameter view or something? Basically, I did an ALTER SESSION ... and want to verify it was set correctly.
I am using TOAD to monitor user sessions. I have a user who uses nearly 80% of Overall resources in the mornings. He calls me daily and say that his work is done. But even then the TOAD shows that he is using same resources for some time.
My question is can i kill the session once he confirms his work is done OR do i need to wait till the redologs are cleared.
We have a package procedure called from SSRS , which returns a REFCURSOR to the SSRS.Sometimes it throws the error. The user Refresh the SSRS session and again run the query , the error does not happen
Following is the error number
ORA-01001: invalid cursor
We have not used any static cursor , so this is not happening because we have opened a cursor and not closed it.The Cursor used is REF CURSOR and the output parameter is declared as
--------------- p_ref_cursor OUT SYS_REFCURSOR ----------
The Procedure returns the Select query in the REF CURSOR.
,in 11g,is there a way I could limit the number of open application actions by some os user ?We have an application where users are executing the same thing while the last is not yet finished,so we have several same things runninng at the same time executed by the same user.
Can we restrict that somehow through the database or that needs to be done through application?
We have a Data Source with min_pool_size (10) and max_pool_size (20). A Data Source is by default a connection pool. By starting a transaction we are retrieving a connection from the pool (i.e., opening it to retrieve data, perform queries, inserts and updates). Our application server is JBoss. An application workflow uses many transactions to build a product. The same connection is not used by the application for the entire workflow; but, it uses and returns them to the connection pool. We do not use Java syntax like "rs.close():"... this is performed by iBATIS.
On the Linux side when we execute a "ps" command (ps -elf|grep -i ora) we see all the Oracle processes. A further refinement of that command (ps -elf|grep -i local=no)shows a list of the "waiting" connections in the connection pool. The DB may be queried with the following syntax:
SELECT schemaname, sid, serial# FROM gv$session where schemaname = 'APP_USER' order by SID;
A list of connected sid and serial numbers is returned, identifying which connections are in use. From here we are able to force the connection to trace by executing the following:
exec dbms_monitor.session_trace_enable(249, 6595, TRUE, FALSE); ! 249 and 6595 being SID and Serial# from query above
There should be no need to execute the inverse, since the connection is returned to the pool when the transaction is committed or rolled back.
We are trying to trace in order to use the Quest Benchmark Factory. Their instructions request the following syntax be applied to each session:
alter session set events '10046 trace name context forever, level 4';
and again, the inverse should not be necessary.
alter session set events '10046 trace name context off'
When it became too cumbersome to alter each session as it appeared, we issued an "alter system" to monitor (trace) everything. The trace files filled the disk, and four hours of testing was stopped two hours in. Doing a system level trace is probably not a good idea.
My first inclination was to create a post-logon trigger to set trace in the session; however, these connections, coming from an JBoss connection pool, do not logon each time, and I presume that they are not all the "same session".
We opened a support ticket with Quest last Friday and do not have an answer yet. This was the third ticket with them, the first to get Benchmark Factory installed (the original installer did not work). The second ticket was to setting up a shared directory on Linux with a folder on Windows, a setup configuration required by their tool. The third ticket to address this issue.
They needed to contact "the developers" to answer the last two questions. Their latest suggestion is to fix ticket two so we "won't need to trace" anything. How do I set trace in these connections?
the trigger shown below is working fine.But i want to get client machine name and client osuser name in the output of the same trigger shown below,how can i achieve this in the same trigger?
the output showing sessionid,date,db user name,host as local server machine name.but i want client machine name and client osuser names as u all know clients login to database from their machines.
