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