User Management in Oracle

Data Dictionary table which is used to view the list of users available in database.

SQL> SELECT username FROM dba_users;

USERNAME

------------------------------

SYS

SYSTEM

SCOTT

TSMSYS

4 rows selected.

Creating User

SQL> CREATE USER murugappan

  2  IDENTIFIED BY murugappan;

User created.

SQL> SELECT username FROM dba_users;

USERNAME

------------------------------

SYS

SYSTEM

MURUGAPPAN

SCOTT

TSMSYS

5 rows selected.

SQL> conn murugappan/murugappan

ERROR:

ORA-01045: user MURUGAPPAN lacks CREATE SESSION privilege; logon denied

Warning: You are no longer connected to ORACLE.

SQL>

Oracle System Privileges

System privileges allow the user to perform system level activities. This might include such things as being able to actually connect to the system, or it might include the ability to do things like create objects in schemas other than your own schema. In the next sections we will discuss the grant command, which you use to grant system privileges. Also the revoke command is used to revoke privileges from users.

Granting Oracle System Level Privileges

The grant command is used to grant system level privileges. System level privileges are those privileges that you need to actually do something on the system. For example you grant system level privileges to a user so that they can:

* Connect to the database (create session)

* Create objects (create table, create index)

* Perform DBA activities, like backup the database (SYSDBA, SYSOPER)

                                                  

                           

                         

SQL> conn / as sysdba;

Connected.

SQL> show user;

USER is "SYS"

SQL> GRANT create session, create table TO murugappan;

Grant succeeded.

SQL> conn murugappan/murugappan

Connected.

SQL> SHOW user;

USER is "MURUGAPPAN"

SQL> CREATE TABLE stud_rec ( sid NUMBER(3), sname VARCHAR2(30));

CREATE TABLE stud_rec ( sid NUMBER(3), sname VARCHAR2(30))

*

ERROR at line 1:

ORA-01950: no privileges on tablespace 'USERS'

Updating Password with Assigning Quota on Users tablespace

SQL> conn / as sysdba;

Connected.

SQL> ALTER USER murugappan

  2  IDENTIFIED BY murugan123   --Reset Password

  3  DEFAULT TABLESPACE users

  4  TEMPORARY TABLESPACE TEMP

  5  QUOTA 3M ON users;

User altered.

SQL> conn murugappan/murugan123

Connected.

SQL>  CREATE TABLE stud_rec ( sid NUMBER(3), sname VARCHAR2(30));

Table created.

SQL> DESC stud_rec;

 Name                                      Null?    Type

----------------------------------------- -------- -------------------

 SID                                                NUMBER(3)

 SNAME                                              VARCHAR2(30)

If you create any object automatically it will get created in the default tablespace that assigned to the particular user.

i.e. Above table stud_rec get created in the users tablespace.

If you want to create object in some other tablespace you have to use optional parameter TABLESPACE in the table creation code as follows.

To view list of tablespace available in the database

SQL> SELECT tablespace_name FROM dba_tablespaces;

TABLESPACE_NAME

------------------------------

SYSTEM

UNDOTBS1

SYSAUX

TEMP

USERS

Assigning Quota on SYSAUX tablespace.

SQL> conn / as sysdba

Connected.

SQL> ALTER USER murugappan

  2  QUOTA 3M ON SYSAUX;

User altered.

SQL> conn murugappan/murugan123

Connected.

SQL> CREATE TABLE log_info(emp_id NUMBER(3), total_hours NUMBER(2)) TABLESPACE SYSAUX;

Table created.

stud_rec table has been created in default tablespace (USERS) & log_info table has been create in SYSAUX tablespace.

To view all the information about the tables you can use USER_TABLES data dictionary.

SQL> SELECT table_name, Tablespace_name FROM user_tables;

TABLE_NAME                     TABLESPACE_NAME

------------------------------ ------------------------------

STUD_REC                       USERS

LOG_INFO                       SYSAUX

You can assign a user either individual quotas for a specific amount of disk space in each tablespace or an unlimited amount of disk space in all tablespaces. Specific quotas prevent a user's objects from consuming too much space in the database.

SQL> ALTER USER murugappan

  2  QUOTA UNLIMITED ON SYSAUX;

User altered.

To view quota assigned for all the tablespace and users:

SQL> SELECT tablespace_name, username, bytes, max_bytes, blocks

2   from dba_ts_quotas;

*If MAX_BYTES column value is -1 means that user have unlimited access on that particular tablespace.

Change Oracle passwords, expire, and lock unnecessary users

SQL> show user

USER is "MURUGAPPAN"

SQL> conn / as sysdba

Connected.

If you create or alter the user with PASSWORD EXPIRE option means it will allow the user to change their password.

  

SQL> ALTER USER murugappan

  2  IDENTIFIED BY new_pass

  3  PASSWORD EXPIRE;

User altered.

SQL> conn murugappan/new_pass

ERROR:

ORA-28001: the password has expired

Changing password for murugappan

New password: ora123

Retype new password: ora123

Password changed

Connected.

SQL> disc

Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production

With the Partitioning, OLAP and Data Mining options

SQL> conn murugappan/new_pass

ERROR:

ORA-01017: invalid username/password; logon denied

SQL> conn murugappan/ora123

Connected.

Lock unnecessary users

SQL> conn / as sysdba

Connected.

SQL> ALTER USER murugappan ACCOUNT LOCK;

User altered.

SQL> SELECT username, account_status FROM dba_users

  2  WHERE username = 'MURUGAPPAN';

USERNAME                       ACCOUNT_STATUS

------------------------------ --------------------------------

MURUGAPPAN                     LOCKED

SQL> conn murugappan/ora123;

ERROR:

ORA-28000: the account is locked

Warning: You are no longer connected to ORACLE.

SQL> conn / as sysdba;

Connected.

SQL> ALTER USER murugappan ACCOUNT UNLOCK;

User altered.

SQL> SELECT username, account_status FROM dba_users

  2  WHERE username = 'MURUGAPPAN';

USERNAME                       ACCOUNT_STATUS

------------------------------ --------------------------------

MURUGAPPAN                     OPEN

SQL> conn murugappan/ora123;

Connected.

Unlock the users with new password

SQL> ALTER USER murugappan ACCOUNT UNLOCK

   2 IDENTIFIED BY oracle123;

User altered.

Privileges

·         SYSTEM Privileges

·         OBJECT Privileges       

·        ROLE

System Privileges: (already discussed)

                        Create Session

                        Create Table

Object Privileges:

                        A schema object privilege is a privilege or right to perform a particular action on a specific schema object:

Table	Sequence	View
Procedure	Function	package

SQL> show user;

USER is "SYS"

SQL>

SQL> CREATE TABLE obj_priv ( col1  NUMBER(2));

Table created.

SQL> BEGIN

  2  insert into obj_priv values(1);

  3  insert into obj_priv values(2);

  4  insert into obj_priv values(3);

  5  end;

  6  /

PL/SQL procedure successfully completed.

SQL> select * from obj_priv;

      COL1

----------

         1

         2

         3

SQL> conn murugappan/ora123;

Connected.

SQL> select * from sys.obj_priv;

select * from sys.obj_priv

                  *

ERROR at line 1:

ORA-00942: table or view does not exist

SQL> conn / as sysdba;

Connected.

SQL>  GRANT SELECT, INSERT, UPDATE ON obj_priv TO murugappan;

Grant succeeded.

SQL> conn murugappan/ora123;

Connected.

SQL> select * from sys.obj_priv;

      COL1

----------

         1

         2

         3

Role:

                       

 

SQL> conn / as sysdba

Connected.

SQL> create user u1 identified by u1;

User created.

SQL> create user u2 identified by u2;

User created.

SQL> create user u3 identified by u3;

User created.

SQL> GRANT create session, create table TO u1;

Grant succeeded.

SQL> CREATE ROLE basic_prive;

Role created.

SQL> GRANT create session, create table TO basic_prive;

Grant succeeded.

SQL> GRANT basic_prive TO u2,u3;

Grant succeeded.

Default role

·         DBA

·         connect

·         imp_full_database

·         exp_full_database

--Thanks for reading