Case study of Oracle Database Administration and Security

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.

Post a Comment

Previous Post Next Post