Oracle Users Management

PROBLEM:
What are the basics of Oracle users management?

1. Users 
• username 
• identified by 
• default tablespace 
• temporary tablespace 
• password expire option 
• profile 
• account 
2. Priviliges 
• system privileges
• object privileges
3. Roles 
4. Quotas  
5. Profiles 

SOLUTION:

----------------------------------------------------------
-- USERS
----------------------------------------------------------

-- View all users / schemas

SELECT username FROM dba_users;

-- Create a new user
CREATE USER kipi IDENTIFIED BY sumsum ;

--   * EXTERNALLY Clause - External user will not require any login password as local user does.
--     Such a user will be authenticated by operating System.
--     Means: If you can log in to operating system, you can use oracle too.

show parameter os_authent_prefix

select UPPER(sys_context('userenv','os_user')) from dual;

CREATE USER "OPS$BASDBINS\ADMINISTRATOR" IDENTIFIED EXTERNALLY;

GRANT CREATE SESSION TO "OPS$BASDBINS\ADMINISTRATOR";

conn /

-- * GLOBALLY Clause - GLobal user will also not require any login password as local user does.
--   Such a user will be authenticated by a directory service.
--   like OID (Oracle Internet Directory).
-- * GLOBALLY AS extname, where the username and password will be passed to
--   the extname service for logon validation

-- * DEFAULT TABLESPACE
-----------------------
-- * Specify the default tablespace for objects that the user creates.
--   If you omit this clause, then the user's objects are stored in the database default tablespace. If no default tablespace has been specified for the database, then the user's objects are
--   stored in the SYSTEM tablespace 
-- * DEFAULT TABLESPACE option does not grant any quota to the user.

-- * QUOTA [size] ON [tablespace]
---------------------------------
--   specify the maximum amount of space the user can allocate in the tablespace
--   UNLIMITED lets the user allocate space in the tablespace without bound.

-- * TEMPORARY TABLESPACE
-------------------------
--   Specify the tablespace or tablespace group for the user's temporary segments.
--   If you omit this clause, then the user's temporary segments are stored in the database default temporary tablespace or, if none has been specified, in the SYSTEM tablespace.

-- * PASSWORD EXPIRE
--------------------
--   Specify PASSWORD EXPIRE if you want the user's password to expire.
--   This setting forces the user or the DBA to change the password before the user
--   can log in to the database

-- * PROFILE [profile name]
---------------------------
--   Specify the profile you want to assign to the user.
--   The profile limits the amount of database resources the user can use.
--   If you omit this clause, then Oracle Database assigns the DEFAULT profile to the user.

-- * ACCOUNT Clause
-------------------
--   Specify ACCOUNT LOCK to lock the user's account and disable access.
--   Specify ACCOUNT UNLOCK to unlock the user's account and enable access to the account.

CREATE USER debra;

CREATE USER debra
IDENTIFIED BY pass;

DROP USER debra;

CREATE USER debra
IDENTIFIED BY debra
DEFAULT TABLESPACE users
TEMPORARY TABLESPACE temp
QUOTA 10m ON users
QUOTA UNLIMITED ON example
PROFILE DEFAULT
ACCOUNT LOCK
PASSWORD EXPIRE;

SELECT username, DEFAULT_TABLESPACE, profile
FROM dba_users
ORDER BY username;

-- Quotas
SELECT * FROM dba_ts_quotas
WHERE USERNAME = 'debra';

-- * UNLIMITED TABLESPACE privilege - which is a result of assigning the RESOURCE role.
--   In that case user will not appear in dba_ts_quotas.

-- Via OEM:
-- Server => (Security) => Users

-------------------------------------------------------
-- Passwords
-------------------------------------------------------

-- Password column is null

SELECT username, password FROM dba_users WHERE username = 'HR';

SELECT NAME, PASSWORD FROM SYS.USER$ WHERE name = 'HR';

ALTER USER hr IDENTIFIED BY my_pass

conn hr/my_pass

-- Change whatever i want...

conn / as sysdba

ALTER USER hr IDENTIFIED BY VALUES ' '

-- Hashing vs Encription
------------------------
-- * There is a huge difference between encryption and hashing.
-- * Encrypton is a reversible proces, while hashing is not.
-- * With encryption, you can allways get the original value from the encrypted value,
--   provided that you have propper encryption key and an alghorytm.
-- * But with hashing process it is different. You can (generally speaking) never get
--   the original value from the hashed value, even if you know the hashing algorythm.
--   It is mathematicaly one-way process.

-------------------------------------------------------
-- PRIVILEGES
-------------------------------------------------------

-- * System privileges
--   Allow a user to perform certain database actions, such as create a table,
--   or create an index, or even connect to the instance
-- * Object privileges
--   allow a user to manipulate objects, as by reading data through a view,
--   executing a stored procedure, or changing data in a table

-- Connect first attempt
Conn debra/debra

conn / as sysdba
GRANT CREATE SESSION TO debra;

-- Connect second attempt :
Conn debra/debra

SELECT * FROM session_privs;

-- Try to create a table:
create table test(y number);

conn / as sysdba
grant create table to debra;

conn debra/debra

SELECT * FROM session_privs;

create table test(y number);

-- GRANT ANY
------------

-- Try to create a table on another schema:
[debra] create table sh.newtab(y number);

conn / as sysdba

grant create any table to debra;

conn debra/debra

create table sh.newtab(y number);

create table sys.newtab(y number);

conn / as sysdba

show parameter O7_DICTIONARY_ACCESSIBILITY

------------------------------------------------
-- WITH ADMIN OPTION - (System - No cascade)
------------------------------------------------

-- GRANT system privilege  [ANY] TO username [WITH ADMIN OPTION]
-- REVOKE system privilege [ANY] FROM username

conn / as sysdba

grant create any table to debra with admin option ;

conn debra/debra

grant create any table to hr ;

grant create any table to hr with admin option ;

-- is it possible to grant user hr CREATE TABLE privilege ?

grant create table to hr ;

conn hr/hr

create table sh.test2(y number) ;

conn / as sysdba

revoke create any table from debra;

conn debra/debra

create table sh.test3(y number) ;

conn hr/hr

create table sh.test3(y number) ;

-- we can monitor different privileges using dba_sys_privs
-- we cannot monitor the hierarchy

SELECT *
FROM dba_sys_privs
WHERE grantee in ('debra','HR','SH')
ORDER BY grantee ;

-------------------------------------------------------
-- OBJECT PRIVILEGES (CASCADE)
-------------------------------------------------------

-- GRANT | REVOKE object privilege ON object TO username [WITH GRANT OPTION]

conn debra/debra

update hr.employees set salary=200;

conn hr/hr

GRANT UPDATE ON employees TO debra WITH GRANT OPTION;

GRANT UPDATE (salary) ON employees TO debra WITH GRANT OPTION;

conn debra/debra

update hr.employees set salary=200;

roll

GRANT UPDATE ON hr.employees to SH;
-- or
GRANT UPDATE ON hr.employees to SH WITH GRANT OPTION ;

Grant succeeded.

conn sh/sh

update hr.employees set salary=400;

roll

conn / as sysdba

col owner for a10
col table_name for a15
col grantor for a15
col privilege for a15

SELECT GRANTOR, GRANTEE, TABLE_NAME, PRIVILEGE,GRANTABLE
FROM  dba_tab_privs
WHERE table_name='EMPLOYEES'
AND GRANTEE <> 'OE'

conn hr/hr (or conn / as sysdba)

REVOKE UPDATE ON hr.employees FROM debra;

SELECT GRANTOR, GRANTEE, TABLE_NAME, PRIVILEGE,GRANTABLE
FROM  dba_tab_privs
WHERE table_name='EMPLOYEES'
AND GRANTEE <> 'OE'

-- Granting all object privileges to a user using a quick script

conn hr/hr

SELECT 'GRANT ALL ON ' || table_name || ' TO ' || '&username' FROM user_tables

-------------------------------------------------------
-- ROLES
-------------------------------------------------------

-- recreate debra

drop user debra ;

create user debra identified by debra;

grant create session to debra ;

grant CREATE VIEW to debra ;

grant UPDATE on HR.EMPLOYEES to debra ;

-- create role basic;

drop role basic ;

create role basic;

grant CREATE TABLE to basic;

grant RESOURCE to basic ;

grant DROP ANY TABLE to basic ;

grant UPDATE on HR.DEPARTMENTS to basic ;

-- Grant role to debra

GRANT basic TO debra;

conn debra/debra

select * from session_privs ;

conn / as sysdba

Helpful Views
-------------------------------

-- list of object privs for every user
SELECT GRANTEE, TABLE_NAME, PRIVILEGE
FROM dba_tab_privs
WHERE GRANTEE = 'debra';

-- list of system privs for every user
SELECT * FROM dba_sys_privs
WHERE grantee = 'debra'; 

-- list of roles for every user
SELECT GRANTEE, GRANTED_ROLE
FROM dba_role_privs
WHERE GRANTEE = 'debra'

-- list of roles for every role
SELECT ROLE, GRANTED_ROLE
FROM ROLE_ROLE_PRIVS
WHERE ROLE = 'BASIC';

-- list of sys privs for every role
SELECT *
FROM dba_sys_privs
WHERE grantee = 'RESOURCE';

-- list of object privs for every role
SELECT GRANTEE, TABLE_NAME, PRIVILEGE
FROM dba_tab_privs
WHERE GRANTEE = 'BASIC'

-- or we could just use OEM or external scripts such as
-- user_privs.sql
-- source : http://www.petefinnigan.com/tools.htm

-- DEFAULT / NON-DEFAULT ROLES
------------------------------

ALTER USER debra DEFAULT ROLE ALL;

ALTER USER debra DEFAULT ROLE NONE;

ALTER USER debra DEFAULT ROLE ALL EXCEPT basic;

conn debra/debra

SELECT username, granted_role, default_role
FROM   user_role_privs ;

select * from session_privs ;

SET ROLE basic;

select * from session_privs ;
                               
-- PASSWORD PROTECTED ROLES:
----------------------------

conn / as sysdba

ALTER ROLE basic IDENTIFIED BY mypass;

conn debra/debra

select * from session_privs ;

SET ROLE basic IDENTIFIED BY mypass;

select * from session_privs ;

ALTER ROLE basic NOT IDENTIFIED ;
                                                                  

---------------------------------------------------------------------------------
-- PROFILES
---------------------------------------------------------------------------------

-- Via OEM:
-- Server => Security => Profiles

PROFILES
--------
-- * A profile is an Oracle object that allows you to set both password management and
--   resource limits.
-- * In every Oracle database a single profile is created when you create the
--   database. This profile, called DEFAULT, places no limits on password and account
--   lockout, or on resource utilization.
-- * You can change the settings of the DEFAULT profile to conform to your requirements,
--   and they will then be applied to all users in thedatabase assigned the DEFAULT profile.
--   A database administrator may create additional profiles dealing with password or
--   account lockout issues, resource management settings, or both.
-- * Once created, a profile can be assigned to a user account as it is created, or it can be
--   assigned to the user with the ALTER USER command.
-- * Any settings in the profile will then apply to the user the next time he/she connects to the
--   database.
-- * A user may have only one profile active at one time, so you need to ensure that the
--   settings within the profile match the requirements of each user.

-- * PERFORMANCE
----------------
-- * CPU_PER_SESSION -
--   The total CPU time, measured in hundredths of a second, that a user
--   is allowed to consume during a session.
--   Once the limit is reached, the user’s session is terminated with
--   an Oracle server error message.
--   To reset this limit, the user needs to disconnect from the instance
--   and connect again.

-- * CPU_PER_CALL -
--   The total CPU time, measured in hundredths of a second, that a user
--   is allowed to consume for a single SQL statement.
--   Once the limit is reached, the SQL statement is aborted and the transaction it is
--   a part of is rolled back.
--   The user’s session remains connected.
--   The limit is reset on every call to the database.

-- * CONNECT_TIME -
--   The maximum amount of time, specified in minutes, that a user may remain
--   connected to the instance.

-- * IDLE_TIME -
--   The maximum amount of time, specified in minutes,
--   that a user’ssession may remain connected to the instance while not performing
--   any database activity.

-- * SESSIONS_PER_USER -
--   The maximum number of concurrent sessions that a user may have at one time.

-- * LOGICAL_READS_PER_SESSION -
--   The number of blocks (both physical—from disk—and logical—
--   from the database buffer cache) that the user is allowed to read
--   during their session.
--   Once the number of blocks specified by this parameter are read, the user will need to
--   start another session in order to access data in the database.

-- * LOGICAL_READS_PER_CALL -
--   The number of blocks (both physical—from disk—and logical—from
--   the database buffer cache) that the user is allowed to read when
--   executing a SQL statement.
--   Once the number of blocks specified by this parameter are read,
--   the SQL statement will be terminated and any transaction that it is a part
--   of will be rolled back.

-- * PRIVATE_SGA -
--   In a Multi-Threaded Server (MTS) environment, this parameter
--   specifies the maximum number of bytes that a user’s session can
--   occupy in the SGA.
--   If you are not connected to the database instance with an MTS connection,
--   this parameter is ignored.

-- Change the costs
-------------------

-- * Example

ALTER RESOURCE COST
CPU_PER_SESSION 100
CONNECT_TIME      1;

-- * The weights establish this cost formula for a session:
--   cost = (100 * CPU_PER_SESSION) + (1 * CONNECT_TIME)
-- * If you do not assign a weight to a resource, then the weight defaults to 0, and use of the
--   resource subsequently does not contribute to the cost.
--   The weights you assign apply to all subsequent sessions in the database.

-- The composite limit
-----------------------

--   Specifies a numerical value that is the weighed average of four resource limits:
--   1.CPU_PER_SESSION
--   2.CONNECT_TIME
--   3.LOGICAL_READS_PER_SESSION
--   4.PRIVATE_SGA
--   Setting COMPOSITE_LIMIT will allow Oracle to monitor all four
--   of these parameter values; when the combination of all exceeds the
--   value specified by COMPOSITE_LIMIT, the user’s session will be
--   terminated.

-- * Example :

ALTER RESOURCE COST
CPU_PER_SESSION 10
LOGICAL_READS_PER_SESSION 2
PRIVATE_SGA 6
CONNECT_TIME 1;

-- * When the session hits the composite limit, the session is terminated.

ALTER PROFILE app_developr
COMPOSITE_LIMIT 500;

-- * PASSWORD
-------------
-- * PASSWORD_LIFE_TIME
--   The number of days that will pass before the user is prompted to change his password.

-- * PASSWORD_GRACE_TIME
--   The number of days following a successful login after the expiration of PASSWORD_LIFE_TIME.
--   The grace time starts after the login, even if the password has already expired.

-- * PASSWORD_REUSE_MAX
--   Specify the number of password changes required before the current password can be reused.
--   If you set PASSWORD_REUSE_MAX to an integer value, then you must set
--   PASSWORD_REUSE_TIME to UNLIMITED.

-- * PASSWORD_REUSE_TIME
--   The time in days that must elapse before apassword can be reused.
--   If you set PASSWORD_REUSE_TIME to an integer value, then you must set
--   PASSWORD_REUSE_MAX to UNLIMITED.

-- * These two parameters must be set in conjunction with each other.
-- * For these parameter to have any effect, you must specify an integer for both of them.
--   Possible via sql*plus only.

ALTER PROFILE "DEFAULT" LIMIT
PASSWORD_REUSE_MAX 4
PASSWORD_REUSE_TIME 1/1440

-- * PASSWORD_VERIFY_FUNCTION
--   A PL/SQL function that will perform complexity checks (or any other checks
--   that may be needed) whenever a password is changed.

-- * FAILED_LOGIN_ATTEMPTS
--   After this number of consecutive connect requests with an incorrect password,
--   the account will be locked.
--   This will prevent hackers from trying numerous common passwords in an attempt
--   to guess the correct one.
--   The counter is reset after a successful connection.

-- * PASSWORD_LOCK_TIME
--   The number of days for which the account will be locked if the FAILED_LOGIN_ATTEMPTS
--   limit is reached. If days are unsuitable as units, use appropriate arithmetic.
--   For instance, one day has one thousand four hundred and forty minutes,
--   so setting the PASSWORD_LOCK_TIME to (30/1440) is equivalent to thirty minutes
--   enough to foil a hacker who is throwing a dictionary at your database, without inconveniencing --   forgetful users too much.

-- Current profiles - by default - all is unlimited:
col limit for a15
set lines 200
SELECT *
FROM dba_profiles
ORDER BY profile, RESOURCE_TYPE;

-- Users profiles
SELECT username , profile
FROM dba_users ;

-- Create a new profile:
CREATE PROFILE dev_prof
LIMIT
SESSIONS_PER_USER 2
FAILED_LOGIN_ATTEMPTS 5;

-- All the rest - depend on the Default values (Unlimited):
SELECT *
FROM dba_profiles
WHERE profile= 'DEV_PROF'
ORDER BY profile, RESOURCE_TYPE;

ALTER USER hr PROFILE DEFAULT;

ALTER PROFILE "DEFAULT"
LIMIT FAILED_LOGIN_ATTEMPTS 3 ;

conn hr/hr

conn hr/badpass

conn hr/badpass

conn hr/badpass

conn / as sysdba

ALTER USER hr ACCOUNT UNLOCK;

ALTER USER hr ACCOUNT UNLOCK IDENTIFIED BY hr;

ALTER USER sh ACCOUNT UNLOCK IDENTIFIED BY sh PASSWORD EXPIRE;

-- Complexity Function:

get ?\rdbms\admin\utlpwdmg.sql

ALTER PROFILE dev_prof
LIMIT
PASSWORD_VERIFY_FUNCTION my_func

ALTER USER hr PROFILE dev_prof;

-- Enabling resource limits
show parameter resource_limit

-- ALTER SYSTEM SET RESOURCE_LIMIT=TRUE;                                                            
-- Change a default profile for all users
SELECT 'ALTER USER '||username||' PROFILE dev_prof;' FROM dba_users

-- for more complex management
-- Via OEM => Server => Resource Manager

-------------------------------------------------------
-- PROXY USERS
-------------------------------------------------------
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::p11_question_id:21575905259251

-- Enable HR to connect to debra without a password:
ALTER USER debra GRANT CONNECT THROUGH hr;

conn hr[debra]/hr

show user

-- Can access HR?
SELECT * FROM hr.departments;

-- Try to connect to HR from debra:
conn debra[hr]/debra

If you find this solution useful, you are welcome to press one of the ads in this page.. Thanks!