Forgot your password?    
+ Reply to Thread
Results 1 to 2 of 2

Thread: Oracle Database User Management.

  1. #1
    Oracle Administrator
    Join Date
    Dec 2011
    Posts
    92

    Oracle Database User Management.

    USER CREATION AND DELETION.

    SQL> create user tom identified by jerry
    2 default tablespace users
    3 temporary tablespace temp
    4 quota 20m on users;

    User created.

    Above command creates a user tom with password jerry.
    The tablespace in which tom will store his data is "USERS".
    The tablespace used for storing temporary segments will be "TEMP".
    And amount of space which tom can use on "USERS" tablespace is 20M.

    SQL> drop user tom cascade;


    SQL> alter user scott identified by tiger account unlock;

    User altered.

    The above command unlocks the "SCOTT" user with password "TIGER".


    SQL> select username, account_status, default_tablespace,
    temporary_tablespace, profile from dba_users
    where username = 'TOM'

    Code:
    USERNAME ACCOUNT_STATUS DEFAULT_TABLESPACE TEMPORARY_TABLESPACE PROFILE  
    -------- -------------- ------------------ -------------------- ---------
    TOM      OPEN           USERS              TEMP                 DEFAULT

    The above query shows the account information related to "TOM".


    ALTERING TABLEPSACE QUOTA

    SQL> select * from dba_ts_quotas where username = 'TOM';

    Code:
    TABLESPACE_NAME USERNAME BYTES MAX_BYTES BLOCKS MAX_BLOCKS DRO
    --------------- -------- ----- --------- ------ ---------- ---
    USERS           TOM      0     20971520  0      2560       NO



    SQL> alter user tom quota 30m on users;

    User altered.

    SQL> select * from dba_ts_quotas where username = 'TOM';

    T
    Code:
    ABLESPACE_NAME USERNAME BYTES MAX_BYTES BLOCKS MAX_BLOCKS DRO
    --------------- -------- ----- --------- ------ ---------- ---
    USERS           TOM      0     31457280  0      3840       NO
    GRANTING AND REVOKING PRIVILEGES.


    SQL> grant create table to tom;

    Grant succeeded.

    SQL> grant create session to tom;

    Grant succeeded.

    SQL> grant create any table, create tablespace to tom;

    Grant succeeded.

    SQL> revoke create any table from tom;

    Revoke succeeded.

    SQL> revoke create tablespace from tom;

    Revoke succeeded.

    SQL> grant select, insert, update, delete on scott.emp to tom;

    Grant succeeded.

    SQL> revoke update,delete on scott.emp from tom;

    Revoke succeeded.


    ROLES

    SQL> grant create any table,
    2 alter any table,
    3 drop any table,
    4 select any table,
    5 update any table,
    6 delete any table
    7 to manager;



    SQL> grant create any index,
    2 alter any index,
    3 drop any index
    4 to manager;

    Grant succeeded.

    SQL> grant alter session,
    2 restricted session
    3 to manager;

    Grant succeeded.

    SQL> grant create tablespace,
    2 alter tablespace,
    3 drop tablespace,
    4 unlimited tablespace
    5 to manager;

    Grant succeeded.

    SQL>create role manager;
    SQL> grant select, insert, update, delete on scott.dept to manager;

    Grant succeeded.

    SQL> grant manager to tom;

    Grant succeeded.


    SQL> select * from dba_sys_privs where grantee = 'TOM';
    Code:
    GRANTEE                        PRIVILEGE                                ADM
    ------------------------------ ---------------------------------------- ---
    TOM                            CREATE TABLE                             NO
    TOM                            CREATE SESSION                           NO
    SQL> select * from dba_sys_privs where grantee = 'MANAGER'

    Code:
    GRANTEE                        PRIVILEGE                                ADM
    ------------------------------ ---------------------------------------- ---
    MANAGER                        DELETE ANY TABLE                         NO
    MANAGER                        CREATE ANY TABLE                         NO
    MANAGER                        DROP TABLESPACE                          NO
    MANAGER                        ALTER TABLESPACE                         NO
    MANAGER                        ALTER ANY INDEX                          NO
    MANAGER                        DROP ANY TABLE                           NO
    MANAGER                        DROP ANY INDEX                           NO
    MANAGER                        UPDATE ANY TABLE                         NO
    MANAGER                        ALTER SESSION                            NO
    MANAGER                        SELECT ANY TABLE                         NO
    MANAGER                        RESTRICTED SESSION                       NO
    MANAGER                        CREATE ANY INDEX                         NO
    MANAGER                        ALTER ANY TABLE                          NO
    MANAGER                        UNLIMITED TABLESPACE                     NO
    MANAGER                        CREATE TABLESPACE                        NO
    
    15 rows selected.


    SQL> select grantee, owner, table_name, grantor, privilege from dba_tab_privs
    2 where grantee = 'TOM';
    Code:
    GRANTEE  OWNER  TABLE_NAME   GRANTOR    PRIVILEGE
    -------- ------ ------------ ---------  ---------  
    TOM      SCOTT  EMP          SCOTT      SELECT
    
    TOM      SCOTT  EMP          SCOTT      INSERT
    SQL> select grantee, owner, table_name, grantor, privilege from dba_tab_privs
    where grantee = 'MANAGER'

    Code:
    GRANTEE  OWNER  TABLE_NAME   GRANTOR    PRIVILEGE
    -------- ------ ------------ ---------  --------- 
    MANAGER  SCOTT  DEPT         SCOTT      UPDATE
    
    MANAGER  SCOTT  DEPT         SCOTT      SELECT
    
    MANAGER  SCOTT  DEPT         SCOTT      INSERT
    
    MANAGER  SCOTT  DEPT         SCOTT      DELETE

    SQL> select * from dba_roles where role = 'MANAGER';

    Code:
    ROLE                           PASSWORD AUTHENTICAT
    ------------------------------ -------- -----------
    MANAGER                        NO       NONE

    SQL> select * from dba_role_privs where grantee = 'TOM';

    Code:
    GRANTEE                        GRANTED_ROLE                   ADM DEF
    ------------------------------ ------------------------------ --- ---
    TOM                            MANAGER                        NO  YES

    SQL> select * from role_sys_privs where role = 'MANAGER';

    Code:
    ROLE                           PRIVILEGE                                ADM
    ------------------------------ ---------------------------------------- ---
    MANAGER                        DROP TABLESPACE                          NO
    MANAGER                        CREATE ANY TABLE                         NO
    MANAGER                        DELETE ANY TABLE                         NO
    MANAGER                        ALTER TABLESPACE                         NO
    MANAGER                        DROP ANY TABLE                           NO
    MANAGER                        ALTER ANY INDEX                          NO
    MANAGER                        UPDATE ANY TABLE                         NO
    MANAGER                        DROP ANY INDEX                           NO
    MANAGER                        ALTER SESSION                            NO
    MANAGER                        RESTRICTED SESSION                       NO
    MANAGER                        SELECT ANY TABLE                         NO
    MANAGER                        CREATE TABLESPACE                        NO
    MANAGER                        UNLIMITED TABLESPACE                     NO
    MANAGER                        ALTER ANY TABLE                          NO
    MANAGER                        CREATE ANY INDEX                         NO
    
    15 rows selected.



    SQL> select * from role_tab_privs where role = 'MANAGER';

    Code:
    ROLE     OWNER   TABLE_NAME   COLUMN_NAME   PRIVILEGE   GRA
    -------- ------- ------------ ------------- ----------- ---
    MANAGER  SCOTT   DEPT                       DELETE       NO
    
    MANAGER  SCOTT   DEPT                       UPDATE       NO
    
    MANAGER  SCOTT   DEPT                       SELECT       NO
    
    MANAGER  SCOTT   DEPT                       INSERT       NO
    SQL> revoke manager from tom;

    Revoke succeeded.



    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;


    Profile created.

    SQL> alter user tom profile developer;

    User altered.


    SQL> select * from dba_profiles where profile = 'DEVELOPER';

    Code:
    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
    
    16 rows selected.
    SQL> drop profile developer cascade;

    Profile dropped.
    Last edited by ajaychandi; 02-02-2012 at 12:46 AM.

  2. #2
    Oracle DBA
    Join Date
    Mar 2013
    Location
    [India]->[Kuwait]->[India]
    Posts
    11
    User and Profile Information in Data Dictionary Views
    The following data dictionary views contain information about database users and profiles:


    View Description
    DBA_USERS Describes all users of the database
    ALL_USERS Lists users visible to the current user, but does not describe them
    USER_USERS Describes only the current user
    DBA_TS_QUOTASUSER_TS_QUOTAS Describes tablespace quotas for users
    USER_PASSWORD_LIMITS Describes the password profile parameters that are assigned to the user
    USER_RESOURCE_LIMITS Displays the resource limits for the current user
    DBA_PROFILES Displays all profiles and their limits
    RESOURCE_COST Lists the cost for each resource
    V$SESSION Lists session information for each current session, includes user name
    V$SESSTAT Lists user session statistics
    V$STATNAME Displays decoded statistic names for the statistics shown in the V$SESSTAT view
    PROXY_USERS Describes users who can assume the identity of other users


    The following sections present some examples of using these views, and assume a database in which the following statements have been executed:


    CREATE PROFILE clerk LIMIT
    SESSIONS_PER_USER 1
    IDLE_TIME 30
    CONNECT_TIME 600;


    CREATE USER jfee
    IDENTIFIED BY wildcat
    DEFAULT TABLESPACE users
    TEMPORARY TABLESPACE temp_ts
    QUOTA 500K ON users
    PROFILE clerk;


    CREATE USER dcranney
    IDENTIFIED BY bedrock
    DEFAULT TABLESPACE users
    TEMPORARY TABLESPACE temp_ts
    QUOTA unlimited ON users;


    CREATE USER userscott
    IDENTIFIED BY scott1;


    Listing All Profiles and Assigned Limits
    The following query lists all profiles in the database and associated settings for each limit in each profile:


    SELECT * From DBA_PROFILES
    ORDER BY PROFILE;

    --
    PROFILE RESOURCE_NAME RESOURCE LIMIT
    ----------------- --------------- ---------- --------------
    CLERK COMPOSITE_LIMIT KERNEL DEFAULT
    CLERK FAILED_LOGIN_ATTEMPTS PASSWORD DEFAULT
    CLERK PASSWORD_LIFE_TIME PASSWORD DEFAULT
    CLERK PASSWORD_REUSE_TIME PASSWORD DEFAULT
    CLERK PASSWORD_REUSE_MAX PASSWORD DEFAULT
    CLERK PASSWORD_VERIFY_FUNCTION PASSWORD DEFAULT
    CLERK PASSWORD_LOCK_TIME PASSWORD DEFAULT
    CLERK PASSWORD_GRACE_TIME PASSWORD DEFAULT
    CLERK PRIVATE_SGA KERNEL DEFAULT
    CLERK CONNECT_TIME KERNEL 600
    CLERK IDLE_TIME KERNEL 30
    CLERK LOGICAL_READS_PER_CALL KERNEL DEFAULT
    CLERK LOGICAL_READS_PER_SESSION KERNEL DEFAULT
    CLERK CPU_PER_CALL KERNEL DEFAULT
    CLERK CPU_PER_SESSION KERNEL DEFAULT
    CLERK SESSIONS_PER_USER KERNEL 1
    DEFAULT COMPOSITE_LIMIT KERNEL UNLIMITED
    DEFAULT PRIVATE_SGA KERNEL UNLIMITED
    DEFAULT SESSIONS_PER_USER KERNEL UNLIMITED
    DEFAULT CPU_PER_CALL KERNEL UNLIMITED
    DEFAULT LOGICAL_READS_PER_CALL KERNEL UNLIMITED
    DEFAULT CONNECT_TIME KERNEL UNLIMITED
    DEFAULT IDLE_TIME KERNEL UNLIMITED
    DEFAULT LOGICAL_READS_PER_SESSION KERNEL UNLIMITED
    DEFAULT CPU_PER_SESSION KERNEL UNLIMITED
    DEFAULT FAILED_LOGIN_ATTEMPTS PASSWORD UNLIMITED
    DEFAULT PASSWORD_LIFE_TIME PASSWORD UNLIMITED
    DEFAULT PASSWORD_REUSE_MAX PASSWORD UNLIMITED
    DEFAULT PASSWORD_LOCK_TIME PASSWORD UNLIMITED
    DEFAULT PASSWORD_GRACE_TIME PASSWORD UNLIMITED
    DEFAULT PASSWORD_VERIFY_FUNCTION PASSWORD UNLIMITED
    DEFAULT PASSWORD_REUSE_TIME PASSWORD UNLIMITED

    Viewing Memory Use for Each User Session

    SELECT USERNAME, VALUE || 'bytes' "Current UGA memory"
    FROM V$SESSION sess, V$SESSTAT stat, V$STATNAME name
    WHERE sess.SID = stat.SID
    AND stat.STATISTIC# = name.STATISTIC#
    AND name.NAME = 'session uga memory';

    USERNAME Current UGA memory
    ------------------------------ ---------------------------------------------
    18636bytes
    17464bytes
    19180bytes
    18364bytes
    39384bytes
    35292bytes
    17696bytes
    15868bytes
    USERSCOTT 42244bytes
    SYS 98196bytes
    SYSTEM 30648bytes



+ Reply to Thread

Similar Threads

  1. Oracle Database Online Redo Log Files Management.
    By ajaychandi in forum Core Database Administration and Monitoring
    Replies: 0
    Last Post: 01-13-2012, 10:40 PM
  2. Oracle Database Temporary Tablespace Management.
    By ajaychandi in forum Core Database Administration and Monitoring
    Replies: 0
    Last Post: 01-12-2012, 11:12 PM
  3. Oracle Database Tablespace Management.
    By ajaychandi in forum Core Database Administration and Monitoring
    Replies: 1
    Last Post: 01-12-2012, 04:59 PM
  4. Replies: 0
    Last Post: 01-10-2012, 06:22 PM
  5. Replies: 1
    Last Post: 01-01-2012, 03:50 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

DBA Lounge (P) Ltd. deals in Oracle Technologies on Consulting, Resourcing, Corporate Training


Online and corporate training available on Oracle Database 11g, Oracle 11g Real Application Cluster (RAC), Oracle Applications 11i/R12, Oracle Fusion Middleware 11g, Oracle Identity Management-OIM, Oracle Internet Directory 11g-OID, Oracle Business Intelligence Enterprise Edition-OBIEE, Oracle Golden Gate, Oracle Access Management-OAM, Oracle Internet Directory-ODS, Oracle Identity Analytics Architecture-OIA Statistics