fbpx

Oracle Users Management Examples


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

UpScale Analytics is one of the largest platforms in the world for learning SQL by doing, consisting over 300 SQL exercises at different levels (including solutions), by topics, across over 100 different datasets. More…