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

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s