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;
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,
temporary_tablespace, profile from dba_users
where username = ‘HRMS’;
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’;
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;
ROLES:
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 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 MY_ROLE to 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’;Code:
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
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’;
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;
