-- USERS


-- Note, generally users are not created under CDB databases,
-- If you wish to create a user under a CDB use the following syntax

create user C##scott identified by scott;


-- USERS


-- View all users / schemas 

SELECT username FROM dba_users;

-- Create a new user
--------------------
-- Options 

-- * USER (mandatory)
---------------------
--   User's username 

-- * IDENTIFIED BY (mandatory)
------------------------------
--   * BY password - local user. indicates that the user must specify password to log on to the  
--     database, local user always requires a password to log in to oracle, no matter what.

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 shimon;

CREATE USER shimon
IDENTIFIED BY pass;

DROP USER shimon;

CREATE USER shimon
IDENTIFIED BY shimon
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 = 'SHIMON';

-- * 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 shimon/shimon

conn / as sysdba
GRANT CREATE SESSION TO shimon;

-- Connect second attempt :
Conn shimon/shimon

SELECT * FROM session_privs;

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

conn / as sysdba
grant create table to shimon;

conn shimon/shimon

SELECT * FROM session_privs;

create table test(y number);

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

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

conn / as sysdba
 
grant create any table to shimon;

conn shimon/shimon

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 shimon with admin option ;

conn shimon/shimon

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 shimon;

conn shimon/shimon

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 ('SHIMON','HR','SH')
ORDER BY grantee ;

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

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

conn shimon/shimon

update hr.employees set salary=200;

conn hr/hr

GRANT UPDATE ON employees TO shimon WITH GRANT OPTION;

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

conn shimon/shimon

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 shimon;

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 shimon 

drop user shimon ;

create user SHIMON identified by shimon; 
 
grant create session to shimon ; 

grant CREATE VIEW to shimon ; 

grant UPDATE on HR.EMPLOYEES to shimon ;

-- 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 shimon 

GRANT basic TO shimon;

conn shimon/shimon

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 = 'SHIMON';

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

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

-- 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 shimon DEFAULT ROLE ALL;

ALTER USER shimon DEFAULT ROLE NONE;

ALTER USER shimon DEFAULT ROLE ALL EXCEPT basic;

conn shimon/shimon

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 shimon/shimon

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.

-----------------------
-- * COMPOSITE_LIMIT - 
----------------------

-- 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 SHIMON without a password:
ALTER USER shimon GRANT CONNECT THROUGH hr;

conn hr[shimon]/hr

show user

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

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