Oracle User, Role and Profile Management

A user in Oracle represents an account that can log into the database and execute SQL queries. Each user has its own schema where objects like tables, indexes, views, and stored procedures can be created.

Aspect	User	Schema
Definition	An Oracle account for database access.	A collection of database objects owned by a user.
Purpose	Controls access to the database and its operations.	Stores database objects (tables, views, procedures).
Authentication	Requires a username and password to log in.	No direct authentication (accessed through the user).
Relation	A user owns exactly one schema.	A schema is tied to exactly one user.

 Each Database User Account has:

• A Unique Username
• An Authentication Method
• A default tablespace
• A temporary tablespace
• A user profile (password restriction)
• An initial Consumer Group – oracle resource manager
• An account status – open, logged or expired

Default Admin User Accounts:

• SYS         (MASTER OF ALL USERS-ALL PRIVILEGES-STARTUP-SHUTDOWN-BACKUP-RECOVERY-DBUPGRADE)
• SYSTEM                (similar to sys- con’t perform backup,recovery and upgrade)
• DBSNMP              (used by Oracle Enterprise Manager tool (GUI)to monitor and manage the db)-tablespace,backup – all db operations)
• SYSMAN               (Oracle Enterprise Manager-perform dba task tablespace,backup)

select username,account_status from dba_users;

create user muthu identified by muthu123;

create user muthu identified by muthu123 password expire;

create user muthu profile default identified by muthu123 default tablespace tbs1 temporary tablespace temptbs1 account unlock;

grant create session to muthu ;

grant connect, resource to muthu ;

alter user muthu identified by muthu123;    --password reset

alter user muthu account lock;

alter user muthu account unlock;

alter user muthu quota 5m on tbs1;

drop user muthu cascade;

Privileges:

• System privilege
• Object privilege

System privilege:

Example: create table, create tablespace, create view, create session, unlimited tablespace.

Any – means permissions to all users.

System Privilege	Description
CREATE SESSION	Allows a user to connect to the database.
ALTER SESSION	Allows a user to alter session-level settings.
CREATE TABLE	Allows a user to create tables in their own schema.
CREATE ANY TABLE	Allows a user to create tables in any schema.
ALTER ANY TABLE	Allows a user to alter tables in any schema.
DROP ANY TABLE	Allows a user to drop tables in any schema.
SELECT ANY TABLE	Allows a user to select data from any table in any schema.
INSERT ANY TABLE	Allows a user to insert data into any table in any schema.
UPDATE ANY TABLE	Allows a user to update data in any table in any schema.
DELETE ANY TABLE	Allows a user to delete data from any table in any schema.
CREATE VIEW	Allows a user to create views in their own schema.
CREATE ANY VIEW	Allows a user to create views in any schema.
ALTER ANY VIEW	Allows a user to alter views in any schema.
DROP ANY VIEW	Allows a user to drop views in any schema.
CREATE PROCEDURE	Allows a user to create procedures in their own schema.
CREATE ANY PROCEDURE	Allows a user to create procedures in any schema.
ALTER ANY PROCEDURE	Allows a user to alter procedures in any schema.
DROP ANY PROCEDURE	Allows a user to drop procedures in any schema.
EXECUTE ANY PROCEDURE	Allows a user to execute procedures in any schema.
CREATE TRIGGER	Allows a user to create triggers in their own schema.
CREATE ANY TRIGGER	Allows a user to create triggers in any schema.
ALTER ANY TRIGGER	Allows a user to alter triggers in any schema.
DROP ANY TRIGGER	Allows a user to drop triggers in any schema.
CREATE USER	Allows a user to create new database users.
ALTER USER	Allows a user to alter existing users.
DROP USER	Allows a user to drop existing users.
GRANT ANY ROLE	Allows a user to grant roles to other users or roles.
REVOKE ANY ROLE	Allows a user to revoke roles from other users or roles.
CREATE ROLE	Allows a user to create roles.
ALTER ROLE	Allows a user to alter roles.
DROP ROLE	Allows a user to drop roles.
CREATE TABLESPACE	Allows a user to create new tablespaces.
ALTER TABLESPACE	Allows a user to alter existing tablespaces.
DROP TABLESPACE	Allows a user to drop tablespaces.
CREATE PROFILE	Allows a user to create new profiles.
ALTER PROFILE	Allows a user to alter existing profiles.
DROP PROFILE	Allows a user to drop profiles.
CREATE SYNONYM	Allows a user to create synonyms in their own schema.
CREATE ANY SYNONYM	Allows a user to create synonyms in any schema.
ALTER ANY SYNONYM	Allows a user to alter synonyms in any schema.
DROP ANY SYNONYM	Allows a user to drop synonyms in any schema.
CREATE MATERIALIZED VIEW	Allows a user to create materialized views in their own schema.
CREATE ANY MATERIALIZED VIEW	Allows a user to create materialized views in any schema.
ALTER ANY MATERIALIZED VIEW	Allows a user to alter materialized views in any schema.
DROP ANY MATERIALIZED VIEW	Allows a user to drop materialized views in any schema.
CREATE DATABASE LINK	Allows a user to create database links.
ALTER DATABASE LINK	Allows a user to alter database links.
DROP DATABASE LINK	Allows a user to drop database links.
CREATE VIEW	Allows a user to create views in their own schema.
CREATE ANY VIEW	Allows a user to create views in any schema.
ALTER ANY VIEW	Allows a user to alter views in any schema.
DROP ANY VIEW	Allows a user to drop views in any schema.
EXECUTE ANY PROCEDURE	Allows a user to execute any procedure in any schema.

Syntax:

grant <system_privilege> to <user_name>;

grant <system_privilege> to <user_name> with admin option;

revoke <system_privilege> from <user_name>;  --revoke only for user1 not applied other users

Object Privilege

Example: select, update, delete, alter, execute

Object Privilege	Description
SELECT	Allows a user to query (select) data from a table or view.
INSERT	Allows a user to insert data into a table or view.
UPDATE	Allows a user to update existing data in a table or view.
DELETE	Allows a user to delete data from a table or view.
REFERENCES	Allows a user to create a foreign key constraint that references a table's columns.
ALTER	Allows a user to alter the structure of a table or view (e.g., add or drop columns).
DROP	Allows a user to drop a table or view (i.e., delete the table or view).
EXECUTE	Allows a user to execute a stored procedure or function.
TRUNCATE	Allows a user to truncate (remove all rows from) a table.
CREATE	Allows a user to create new tables, views, or other objects.
ALTER ANY	Allows a user to alter any object of the specified type (e.g., ALTER ANY TABLE).
DROP ANY	Allows a user to drop any object of the specified type (e.g., DROP ANY TABLE).
INDEX	Allows a user to create an index on a table.
GRANT	Allows a user to grant specific object privileges to other users or roles.
DEBUG	Allows a user to debug a procedure or function.

Syntax:

grant <object_privilege> on <object> to <user_name>;

grant <object_privilege> on <object> to <user_name> with grant option;

grant <object_privilege> on <object> from <user_name>; //revoke the all user permission with cascade

Role:

A role in Oracle is a named group of privileges that can be assigned to users. This makes it easier to manage permissions, especially when dealing with many users.

create role role_name;

create role role_name identified by rol_name123;

drop role role_name;

grant Role_name to user_name;

revoke role_name from user_name;

Profile:

A profile is a set of resource limits and password management settings that can be applied to users. This helps in enforcing policies like password expiration, failed login attempts, and session resource usage.

1. Resource Parameters:

• SESSIONS_PER_USER: Maximum number of concurrent sessions per user.
• CPU_PER_SESSION: CPU time allowed per session (in hundredths of seconds).
• CPU_PER_CALL: CPU time allowed per SQL call.
• LOGICAL_READS_PER_SESSION: Maximum number of logical reads for a session.
• LOGICAL_READS_PER_CALL: Maximum number of logical reads per SQL call.
• CONNECT_TIME: Maximum time allowed for a user session (in minutes).
• IDLE_TIME: Maximum time allowed for an idle session (in minutes).
• PRIVATE_SGA Specify the amount of private space a session can allocate in the shared pool of the system global area.
• COMPOSITE_LIMIT  Specify the total resource cost for a session, expressed in service units. Oracle Database calculates the total service units as a weighted sum of CPU_PER_SESSION, CONNECT_TIME, LOGICAL_READS_PER_SESSION, and PRIVATE_SGA.

 

2. Password Management Parameters:

• PASSWORD_LIFE_TIME: Number of days a password is valid before it must be changed.
• PASSWORD_GRACE_TIME: Number of days before a password expires when the user is warned to change it.
• PASSWORD_REUSE_TIME: Number of days before a previously used password can be reused.
• PASSWORD_REUSE_MAX: Maximum number of times a user can reuse the same password.
• PASSWORD_VERIFY_FUNCTION: A PL/SQL function to enforce custom password complexity.
• FAILED_LOGIN_ATTEMPTS: Maximum number of failed login attempts before the account is locked.
• PASSWORD_LOCK_TIME: Number of days the account remains locked after reaching the maximum failed login attempts.
• PASSWORD_VERIFY_FUNCTION in Oracle is a PL/SQL function used to enforce password complexity and security rules. This function allows administrators to define custom password rules, such as password length, content (letters, numbers, special characters), and other security policies.

 

Example:

CREATE PROFILE secure_profile
LIMIT
SESSIONS_PER_USER 3  -- Maximum 3 concurrent sessions
CPU_PER_SESSION 3000  --3000 hundredths of a second CPU per session
CONNECT_TIME 60       -- 1-hour session limit
IDLE_TIME 10       -- 10 minutes idle time before session ends
FAILED_LOGIN_ATTEMPTS 5  --Maximum of 5 failed login attempts
PASSWORD_LIFE_TIME 90    -- Password expires in 90 days
PASSWORD_GRACE_TIME 10   --User warned 10 days before expiration
PASSWORD_REUSE_TIME 365  --Cannot reuse password for 1 year
PASSWORD_REUSE_MAX 3     -- Password can be reused after 3 changes
PASSWORD_VERIFY_FUNCTION ora12c_verify_function;  --Password complexity check function

Find the users default profile:

select username, profile from dba_users;
select username, profile from dba_users where username='MUTHU';
select * from dba_profiles;
select * from dba_profiles where profile='secure_profile'; OR 'DEFAULT'

Create user and assign the profile:

create user raju identified by raju321 profile secure_profile;

Assign the profile for existing user:

alter user siva profile secure_profile;

Alter the Profile:

alter profile secure_profile limit session_per_user 10;

Drop the Profile:

drop profile secure_profile cascade;