Experiment No-10
Title :- Case study of Oracle Database
Administration and Security.
Aim : To understand Oracle Database Administration
and Security.
Theory:
Managing users and establishing security:
One of the most common database administration
activities is creating and managing database users. (Actually, the creation of
user IDs is just the first component of any well-planned database security
function. Database security is one of the most important database
administration tasks.)
The Security section of the Oracle Enterprise
Manager’s Administration page enables the DBA to create users, roles, and
profiles.
· A user is a uniquely identifiable object that
allows a given person to log on to the database. The DBA assigns privileges for
accessing the objects in the database. Within the privilege assignment, the DBA
may specify a set of limits that define how many of the database’s resources
the user can use.
· A role is a named collection of database access
privileges that authorize a user to connect to the database and use the
database system resources. Examples of roles are as follows:
- CONNECT allows a user to connect to the database
and create and modify tables, views, and other data-related objects.
- RESOURCE allows a user to create triggers,
procedures, and other data management objects.
- DBA gives the user database administration
privileges.
· A profile is a named collection of settings that
control how much of the database resource a given user can use. By specifying
profiles, the DBA can limit how much storage space a user can use, how long a
user can be connected, and how much idle time may be used before the
user is disconnected, and so on. In an ideal world,
all users would have unlimited access to all resources at all times, but in the
real world, such access is neither possible nor desirable.
· To create a new user, the DBA uses the Create User
page. The Create User page contains many links; the most important ones are as
follows: The General link allows the DBA to assign the name, profile, and
password to the new user. Also in this page, the DBA defines the default
tablespace used to store table data and the temporary tablespace for temporary
data. The Roles link allows the DBA to assign the roles for a user. The Object
Privileges link is used by the DBA to assign specific access rights to other
database objects. The Quotas link allows the DBA to specify the maximum amount
of storage that the user can have in each assigned tablespace.
· Granting and Revoking on Oracle database)
1. Create user username identified by password.
2. Drop user username;
There are two levels for assigning privileges:
a. System/ Account level:
i. Grant system privilege, [system privilege, …..]
To username | role name [,username | role name,…….]
[with admin option];
Examples of system privileges: create user,
connect, create session, create table etc.
ii. To revoke system privilege:
Revoke system privilege from username;
b. Object/ Relation level:
i. Grant {object privilege | all} [(column name ],
object privilege ( column name )]
On object name
To { username| role name | public} [With grant
option];
Examples of object privileges: select, insert,
update, delete, index etc.
ii. To revoke object privilege:
Revoke object privilege [,…..,object privilege] on
object name
From username | role name;
Role: It is a collection of privileges.
Some predefined roles: DBA, connect, resource, etc.
i. Create a role : create role rolename;
ii. Granting a Role to a user:
Grant rolename [, rolename] to username[,
username];
iii. Granting privileges to role:
Grant object privileges on object name to rolename;
iv. Revoke a role :
Revoke rolename from username| rolename;
v. Drop a role:
Drop role rolename;
Conclusion: Students are able to understand Oracle
database administartion and security.