• Skip to primary navigation
  • Skip to main content
  • Skip to primary sidebar
  • Skip to footer navigation

The Geek Diary

  • OS
    • Linux
    • CentOS/RHEL
    • Solaris
    • Oracle Linux
    • VCS
  • Interview Questions
  • Database
    • oracle
    • oracle 12c
    • ASM
    • mysql
    • MariaDB
  • DevOps
    • Docker
    • Shell Scripting
  • Big Data
    • Hadoop
    • Cloudera
    • Hortonworks HDP

How to limit access to oracle database so that only 1 user per schema is connected ( 1 Concurrent user per schema)

by admin

Question: How to limit the access to the database so that only one user per schema are connected (one concurrent user per schema)

1. Set in init.ora or spfile.ora the parameter:

resource_limit = true

and restart Database.

You can perform this without restarting Database by issuing as SYS: “alter system set resource_limit=true;”

2. Connect to Database as SYSTEM or SYS and create the following Profile:

create profile single_user limit sessions_per_user 1;

The other parameters should be adjusted too, but here we discuss only the session_per_user.

3. Create the user/schema where only one concurrent connection should be allowed and grant other needed privileges.

create user test1 identified by test1 profile single_user;
grant connect, resource to test1;

4. Test it by trying to connecting with two users “test1”:

Session 1:

connect test1/test1
Connected.

Session 2:

Connect test1/test1  
ORA-02391: exceeded simultaneous SESSIONS_PER_USER limit

Filed Under: oracle

Some more articles you might also be interested in …

  1. Stored Procedures and Functions in PL/SQL
  2. How To Automate The Opening Of Pluggable Databases After The CDB Starts Up in Oracle 12c
  3. How to Install/Uninstall/Upgrade Oracle SQLTXPLAIN (SQLT) Tool
  4. How to Drop/Truncate Multiple Partitions in Oracle 12C
  5. Using Rule Sets in Oracle Database Vault
  6. ORA-27125: unable to create shared memory segment; Error: 28: No space left on device
  7. Oracle Net New Features in Oracle Database 12c
  8. Upgrading to Oracle 12c using RMAN DUPLICATE with “NOOPEN” clause and “BACKUP LOCATION”
  9. Oracle Database: Profile Limits (Resource Parameter(s)) Are Not Enforced / Do Not Work
  10. Oracle RAC: How to modify private hostname, Private network IP & MTU

You May Also Like

Primary Sidebar

Recent Posts

  • vgextend Command Examples in Linux
  • setpci command – configure PCI device
  • db_load command – generate db database
  • bsdtar command – Read and write tape archive files

© 2022 · The Geek Diary

  • Archives
  • Contact Us
  • Copyright