How to create and manage users and roles in an Oracle database
Create users with CREATE USER, assign privileges via GRANT, and manage access using roles; for example, create user john with quota and unlock account, grant CREATE SESSION and CREATE TABLE privileges, create role app_developer, assign privileges to the role, grant role to user, and use ALTER or DROP to modify or remove users and roles as needed.
Setting up and managing users and roles in an Oracle database involves creating accounts, defining privileges, and organizing access through roles for better security and administration. Here’s how to do it properly.
Create a User
To create a new user in Oracle, use the CREATE USER statement. You must have DBA privileges or the CREATE USER system privilege.
Basic syntax:
CREATE USER username IDENTIFIED BY password[ DEFAULT TABLESPACE tablespace_name ]
[ TEMPORARY TABLESPACE temp_tablespace_name ]
[ QUOTA { size_clause | UNLIMITED } ON tablespace_name ]
[ ACCOUNT { LOCK | UNLOCK } ];
Example:
CREATE USER john IDENTIFIED BY securepassDEFAULT TABLESPACE users
TEMPORARY TABLESPACE temp
QUOTA 100M ON users
ACCOUNT UNLOCK;
This creates a user named "john" with a password, assigns default and temporary tablespaces, limits storage quota, and leaves the account unlocked.
Grant System and Object Privileges
New users have no privileges by default. Use GRANT to give them access.
- To allow login and basic session creation:
GRANT CREATE SESSION TO john; - To let the user create tables:
GRANT CREATE TABLE TO john; - To grant unlimited tablespace (use cautiously):
GRANT UNLIMITED TABLESPACE TO john; - To give access to a specific object (e.g., a table owned by another user):
GRANT SELECT ON hr.employees TO john;
Create and Manage Roles
Roles simplify privilege management. Instead of granting privileges to each user, assign them to a role and grant the role to users.
Create a role:
CREATE ROLE app_developer;Grant privileges to the role:
GRANT CREATE SESSION, CREATE TABLE, CREATE VIEW TO app_developer;Assign the role to a user:
GRANT app_developer TO john;You can also set a role as default so it's active upon login:
ALTER USER john DEFAULT ROLE app_developer;Modify and Drop Users and Roles
Change a user’s password or quota:
ALTER USER john IDENTIFIED BY newpassword;ALTER USER john QUOTA 200M ON users;
Lock or unlock an account:
ALTER USER john ACCOUNT LOCK;ALTER USER john ACCOUNT UNLOCK;
Delete a user (removes all schema objects if CASCADE is used):
DROP USER john CASCADE;Delete a role:
DROP ROLE app_developer;Note: Dropping a role revokes it from all users automatically.
Managing users and roles effectively helps maintain security and streamline permissions across your Oracle environment. Use roles for groups with similar responsibilities and always follow the principle of least privilege.
Basically just stick to creating users with proper quotas, using roles to bundle permissions, and cleaning up unused accounts when needed.
The above is the detailed content of How to create and manage users and roles in an Oracle database. For more information, please follow other related articles on the PHP Chinese website!
Hot AI Tools
Undress AI Tool
Undress images for free
AI Clothes Remover
Online AI tool for removing clothes from photos.
Undresser.AI Undress
AI-powered app for creating realistic nude photos
ArtGPT
AI image generator for creative art from text prompts.
Stock Market GPT
AI powered investment research for smarter decisions
Hot Article
Popular tool
Notepad++7.3.1
Easy-to-use and free code editor
SublimeText3 Chinese version
Chinese version, very easy to use
Zend Studio 13.0.1
Powerful PHP integrated development environment
Dreamweaver CS6
Visual web development tools
SublimeText3 Mac version
God-level code editing software (SublimeText3)
Hot Topics
20522
7
13634
4
How to use Oracle APEX to build a low-code app? (Rapid Development)
Mar 13, 2026 am 12:48 AM
OracleAPEXislow-glue,notno-code:itskipsinfrastructurebutrequiresSQL,PL/SQL,anddeclarativelogic;ApplicationProcesseshandleserver-sidevalidationandsideeffects,DynamicActionsmanageclient-sideinteractivity;InteractiveGridneedskey-preservedsourcesforediti
How to patch Oracle Grid Infrastructure? (System Maintenance)
Mar 10, 2026 am 01:00 AM
Three things must be confirmed before applying the GI patch: 1. The opatchlsinventory-detail output of each node is consistent; 2. OCR and VoteDisk are online and crsctlcheckcluster-all and ocrcheck both return SUCCESS; 3. $GRID_HOME/crs/install/rootcrs.sh-prepatch has been successfully executed.
How to manage Flashback Data Archive_Flashback Data Archive table space allocation
Mar 28, 2026 pm 04:06 PM
The reason why the FlashbackDataArchive table space is full is that the hidden history table (SYS_FBA_HIST_XXXXXX) occupies the table space where the main table is located and does not go through ASSM cleaning; you need to use ALTERFLASHBACKARCHIVE...MODIFYTABLESPACE to migrate to the local management automatic segment space table space, and manually clean up the orphan history table.
How to implement Transparent Data Encryption (TDE) in Oracle? (Data Security)
Mar 13, 2026 am 12:14 AM
OracleTDE must first enable and open the encrypted wallet (Wallet), otherwise ORA-28365 will be reported when executing ALTERTABLESPACE...ENCRYPTION; Wallet needs to be created, opened and managed through the ADMINISTERKEYMANAGEMENT command, and the path must be explicitly configured in sqlnet.ora and permissions must be ensured.
How to troubleshoot the Oracle Listener startup? (Network Services)
Mar 10, 2026 am 12:58 AM
Oraclelistenerstartupfailuresstemfromsilentlistener.oraparsingerrors,hostnameresolutionissues,orpermissionproblems—notbinariesorports;validatesyntaxwithreload,checkownership,verifyactualconfigpath,testDNS,useexplicitIPs,confirmADR_BASE,enabletracingp
How to grant flashback permission_GRANT FLASHBACK ON and FLASHBACK ANY TABLE
Apr 03, 2026 pm 11:54 PM
FLASHBACK permissions must be explicitly granted: GRANTFLASHBACKONschema.tableTOuser for a single table, and GRANTFLASHBACKANYTABLETOuser for all tables; basic permissions such as SELECT and ALTER and row movement enablement are also required.
How to use JSON data types in Oracle Database? (NoSQL Features)
Mar 08, 2026 am 01:03 AM
In Oracle's JSON scenario, you should select VARCHAR2 (4000CHAR) plus ISJSON constraints (small documents) or BLOB plus ISJSON constraints (large documents), and disable CLOB; ISJSON is a column-level constraint syntax, not a function call; the JSON_VALUE path must be a string literal; JSON_EXISTS needs to be speeded up with the JSON_VALUE function index.
How to grant SYSDBA permissions_sysdba management of password files and OS authentication
Apr 03, 2026 am 08:54 AM
Ordinary users can be authorized through GRANTSYSDBATOusername; provided that the database enables password file authentication (REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE) and has logged in with SYS; there is no need to restart after authorization, but the connection needs to explicitly specify assysdba, and the user credentials must exist in the V$PWFILE_USERS view.
