Oracle Database User Access Control

 

Controlling User Access

•        Two administrative user accounts are automatically created when Oracle Database is installed:

•        SYS (default password: CHANGE_ON_INSTALL)

•        SYS

•        When you create an Oracle database, the use SYS is automatically created and granted the DBA role.

•        Don’t do anything using SYS user. Read the following for details.

 NB: All the base tables and views are stored in the database in the SYS schema. No user will be a changer and handle anything by SYS user. The base tables and views are very critical for the operation of the oracle database. To maintain the integrity of the data dictionary, No one will disturb the SYS schema. No one should create any table using the schema of the SYS user.

•  SYSTEM (default password: MANAGER)

•        User

•        SYS (Default) also treated as superuser, have all control to oracle database

•        SYSTEM (Default) is very powerful and has dba power

•        RASHID (Additional user should be created as DBA privilege from system user)

•        About DBA Role

•        Predefined DBA role is automatically created when oracle is installed. This role contains most database privileges. Therefore, the DBA role should be granted only to the actual database administrator.

•        Login Database

•        Strat->Programe Files->Oracle database 11g express edition->Run SQL Command Line>sqlplus

•                    Or

•        Startàrun->cmd->sqlplus

•        Sqlplus

•        Shutdown immediate

•        startup

•        Schema: Collection of objects such as tables, views and sequences

•        Role- Role is named group of related privileges. The role is the logical user.

•        System Privileges: More than 80 privileges are available and offered by DBA

•        Examples are

•        create session,

•         create table,

•         create sequence,

•         create view,

•         create procedure

 

User	Difference
SYS	•        Sql>CONN sys as sysdba •        Sql>SHOW USER; •        Sql>select * from tab;
SYSTEM	•        Sql>CONNECT system/system@napd; •        Sql>SHOW USER; •        Sql>select * from tab;
RASHID	•        Sql>CONNECT rashid/tiger@napd •        Sql>SHOW USER; •        Sql>select * from tab;

 

• User Creation-

 Sql>CREATE USER RASHID IDENTIFIED BY rashid;

Sql>GRANT DBA TO rashid;

Sql>DROP USER joti;

Sql>CREATE USER joti IDENTIFIED BY joti;

Sql>GRANT CONNECT,RESOURCE TO joti;

Sql>DROP USER rashid CASCADE

 •        Changing Password

Sql> ALTER USER scott IDENTIFIED by lion;

 •        Object Privileges

right to perform a particular action on a specific table

 •        Object Privileges are

–       ALTER, DELTE,

–       EXECUTE,

–       INDEX,

–       INSERT,

–       REFERENCES,

–       SELECT,

–       UPDATE

 Grant Query Privileges

sql> GRANT select

              ON emp

         TO joti, sarmin, basar;

Grant Update Privileges

Sql> GRANT update (dname, loc)

         ON dept

         TO scott, manager;

Grant to all user

Sql>Grant select

                ON dept

         TO PUBLIC;

Case Study

A Business Process Re-engineering (BPR) company suggested to ‘AAA’  Company (Electrical Billing Company) to establish a new MIS department for that organization. Suggested MIS formation is shown in the following table

 

Post	No of Post
GM(ICT)	1
DGM(ICT)	2
Manager(ICT)	3
DM(ICT)	5
Computer Operator	1
Data Entry/Control Operator (DECO)	20

 MIS is designed as:

Backend- Oracle database 11g

Frontend- Oracle developer 11g

GM(ICT) plays the role of Database Administrator(DBA). DBA carefully tried to design the Access Control System (ACS) for MIS for maintaining a high level of security and business integrity. DBA also identified required users, privileges and roles for the MIS department for the purpose of everyday smooth operation.

 DBA listed the following system privileges

         Connect, select, resource, create table, create sequence, create view, create procedure, dba, (You can add more )

 After six months, DGM-2 resigned from the job. DECO-15 makes some offences and authority decided for temporary suspension.

 

Questions:

1.   Create USER like GM1, DGM1………………DECO20.

2.   Create ROLE like GM, DGM, MANAGER, CO, DECO.

3.   Identify privileges for each role according to rank.

4.   Grant roles to users in an appropriate manner.

5.   What action will you take for DGM2? Implement action and test as DBA.

6.    What action will you take for DECO15? Implement action and test as DBA.

Athour

N.B. This information in this case study is purely fictitious and has been prepared for assessment purposes only. Any resemblance to any organization or person is purely coincidental.