Oracle Database User Management
CREATE AND DROP USER:
create
user
shaan identified by moon
default
tablespace rtbs
temporary
tablespace temp
quota
30m on rtbs;
Above
command creates a user shaan with password moon. Consider the tablespace you
have in which shaan will store his data is "RTBS". The tablespace
used for storing temporary segments will be "TEMP" and the amount of
space which the user shaan can use on "RTBS" tablespace is 30M.
SQL> DROP USER SHAAN CASCADE;
SQL> DROP USER SHAAN CASCADE;
ALTERING/UNLOCKING ACCOUNT:
SQL>
alter
user
shaan identified by moon account unlock;
The above command alters or unlocks the "SHAAN" user with password "moon".
SQL> select username, account_status, default_tablespace,
The above command alters or unlocks the "SHAAN" user with password "moon".
SQL> select username, account_status, default_tablespace,
temporary_tablespace, profile
from
dba_users
where username = 'HRMS';
USERNAME ACCOUNT_STATUS DEFAULT_TABLESPACE TEMPORARY_TABLESPACE PROFILE
USERNAME ACCOUNT_STATUS DEFAULT_TABLESPACE TEMPORARY_TABLESPACE PROFILE
------
-------------- ------------------ -------------------- ---------
HRMS OPEN MUJ_HRMS_DBF TEMP DEFAULT
The above query shows the account information related to
"HRMS".
ALTERING TABLEPSACE
QUOTA:
SQL> select * from dba_ts_quotas where username = 'HRMS';
SQL> select * from dba_ts_quotas where username = 'HRMS';
TABLESPACE_NAME
USERNAME BYTES MAX_BYTES BLOCKS MAX_BLOCKS
---------------
-------- ----- --------- ------ ----------
MUJ_HRMS_DBF HRMS
198901760 -1 24280
-1
SQL> alter user shaan quota 40m on RTBS;
GRANTING AND REVOKING
PRIVILEGES:
SQL> GRANT create table to SHAAN;
SQL> GRANT create session to SHAAN;
SQL> GRANT create any table, create tablespace to SHAAN;
SQL> REVOKE create any table from SHAAN;
SQL> REVOKE create tablespace from SHAAN;
SQL> GRANT select, insert, update, delete on HRSM.PAY_PAYMENT_MASTER to HRMS;
SQL> REVOKE update,delete on HRMS.PAY_PAYMENT_MASTER from HRMS;
SQL> GRANT create table to SHAAN;
SQL> GRANT create session to SHAAN;
SQL> GRANT create any table, create tablespace to SHAAN;
SQL> REVOKE create any table from SHAAN;
SQL> REVOKE create tablespace from SHAAN;
SQL> GRANT select, insert, update, delete on HRSM.PAY_PAYMENT_MASTER to HRMS;
SQL> REVOKE update,delete on HRMS.PAY_PAYMENT_MASTER from HRMS;
ROLES:
SQL>create role MY_ROLE;
SQL>create role MY_ROLE;
SQL>
GRANT
create
any
table,
alter
any
table,
drop
any
table,
select
any
table,
update
any
table,
delete
any
table
to
MY_ROLE;
SQL>
GRANT
create
any
index,
alter any index, drop any index to MY_ROLE
SQL> GRANT alter session, restricted session to MY_ROLE;
SQL> GRANT alter session, restricted session to MY_ROLE;
SQL>
GRANT
create
tablespace, alter tablespace, drop tablespace, unlimited tablespace to MY_ROLE;
SQL> GRANT select, insert, update, delete on HRMS.PAY_PAYMENT_MASTER to MY_ROLE;
SQL> GRANT select, insert, update, delete on HRMS.PAY_PAYMENT_MASTER to MY_ROLE;
SQL> GRANT MY_ROLE to SHAAN;
SQL> select * from dba_sys_privs where grantee = 'SHAAN';
SQL> select * from dba_sys_privs where grantee = 'SHAAN';
GRANTEE PRIVILEGE ADM
----------------------
-------------------- ---
SHAAN CREATE TABLE NO
SHAAN CREATE SESSION NO
SQL> select * from dba_sys_privs where grantee = 'MY_ROLE'
GRANTEE PRIVILEGE ADM
----------------------
--------------------------- ---
MY_ROLE DELETE ANY TABLE NO
MY_ROLE CREATE ANY TABLE NO
MY_ROLE DROP TABLESPACE NO
MY_ROLE ALTER TABLESPACE NO
MY_ROLE ALTER ANY INDEX NO
MY_ROLE DROP ANY TABLE NO
MY_ROLE DROP ANY INDEX NO
MY_ROLE UPDATE ANY TABLE NO
MY_ROLE ALTER SESSION NO
MY_ROLE SELECT ANY TABLE NO
MY_ROLE RESTRICTED SESSION NO
MY_ROLE CREATE ANY INDEX NO
MY_ROLE ALTER ANY TABLE NO
MY_ROLE UNLIMITED
TABLESPACE NO
MY_ROLE CREATE TABLESPACE NO
SQL>
select
grantee, owner, table_name, grantor, privilege from dba_tab_privs
where
grantee = 'SHAAN';
GRANTEE
OWNER TABLE_NAME GRANTOR
PRIVILEGE
-------- ------ ------------ --------- ---------
HRMS HRMS PAY_PAYMENT_MASTER HRMS SELECT
HRMS HRMS PAY_PAYMENT_MASTER HRMS INSERT
SQL>
select
grantee, owner, table_name, grantor, privilege from dba_tab_privs
where grantee = 'MY_ROLE';
GRANTEE OWNER
TABLE_NAME GRANTOR PRIVILEGE
--------
------ ------------ --------- ---------
MY_ROLE HRMS PAY_PAYMENT_MASTER
HRMS UPDATE
MY_ROLE HRMS PAY_PAYMENT_MASTER
HRMS SELECT
MY_ROLE HRMS PAY_PAYMENT_MASTER
HRMS INSERT
MY_ROLE HRMS PAY_PAYMENT_MASTER
HRMS DELETE
SQL> select * from dba_roles where role = 'MY_ROLE';
ROLE PASSWORD AUTHENTICAT
---------------------
-------- -----------
MY_ROLE NO NONE
SQL> select * from dba_role_privs where grantee = 'SHAAN';
GRANTEE GRANTED_ROLE
ADM DEF
------------------------
--------------- --- ---
SHAAN MY_ROLE NO
YES
SQL> select * from role_sys_privs where role = 'MY_ROLE';
ROLE PRIVILEGE ADM
---------------------
------------------------------- ---
MY_ROLE DROP TABLESPACE NO
MY_ROLE CREATE ANY TABLE NO
MY_ROLE DELETE ANY TABLE NO
MY_ROLE ALTER TABLESPACE NO
MY_ROLE DROP ANY TABLE NO
MY_ROLE ALTER ANY INDEX NO
MY_ROLE UPDATE ANY TABLE NO
MY_ROLE DROP ANY INDEX NO
MY_ROLE ALTER SESSION NO
MY_ROLE RESTRICTED SESSION NO
MY_ROLE SELECT ANY TABLE NO
MY_ROLE CREATE TABLESPACE NO
MY_ROLE UNLIMITED TABLESPACE NO
MY_ROLE ALTER ANY TABLE NO
MY_ROLE CREATE ANY INDEX NO
SQL> select * from role_tab_privs where role = 'MY_ROLE';
ROLE
OWNER TABLE_NAME COLUMN_NAME
PRIVILEGE GRA
-------- ------- ------------ -------------
----------- ---
MY_ROLE HRMS PAY_PAYMENT_MASTER DELETE NO
MY_ROLE HRMS PAY_PAYMENT_MASTER UPDATE NO
MY_ROLE HRMS PAY_PAYMENT_MASTER SELECT NO
MY_ROLE HRMS PAY_PAYMENT_MASTER INSERT NO
SQL> revoke MY_ROLE from SHAAN;
PROFILES:
SQL> create profile DEVELOPER limit
SQL> create profile DEVELOPER limit
failed_login_attempts 3
password_lock_time unlimited
password_life_time 30
password_reuse_time 30
password_grace_time 5
idle_time 30;
SQL> alter user SHAAN profile developer;
SQL> select * from dba_profiles where profile = 'DEVELOPER';
SQL> select * from dba_profiles where profile = 'DEVELOPER';
PROFILE
RESOURCE_NAME
RESOURCE LIMIT
----------- -------------- -------- -----
DEVELOPER
COMPOSITE_LIMIT
KERNEL DEFAULT
DEVELOPER
SESSIONS_PER_USER
KERNEL DEFAULT
DEVELOPER
CPU_PER_SESSION
KERNEL DEFAULT
DEVELOPER
CPU_PER_CALL KERNEL DEFAULT
DEVELOPER
LOGICAL_READS_PER_SESSION
KERNEL DEFAULT
DEVELOPER
LOGICAL_READS_PER_CALL
KERNEL DEFAULT
DEVELOPER
IDLE_TIME
KERNEL 30
DEVELOPER
CONNECT_TIME KERNEL
DEFAULT
DEVELOPER
PRIVATE_SGA
KERNEL DEFAULT
DEVELOPER
FAILED_LOGIN_ATTEMPTS
PASSWORD 3
DEVELOPER
PASSWORD_LIFE_TIME
PASSWORD 30
DEVELOPER
PASSWORD_REUSE_TIME
PASSWORD 30
DEVELOPER
PASSWORD_REUSE_MAX
PASSWORD DEFAULT
DEVELOPER
PASSWORD_VERIFY_FUNCTION
PASSWORD DEFAULT
DEVELOPER
PASSWORD_LOCK_TIME
PASSWORD UNLIMITED
DEVELOPER
PASSWORD_GRACE_TIME
PASSWORD 5
SQL> drop profile developer cascade;