Basic SQL Fundamentals

Controlling User Access

In this section mainly deals with the security of the database. And security of the database mainly consists of two parts-

# System Security and

# Data Security

System Security

It is related to the safety of the system as a whole such the system where the database is stored should not be accessible by the unauthorized users. All the system related issues are included in the security of the system whether it may be manual or not.

Data Security

It includes all the issues related to the data in the database. For safety of the data should be secured from the unauthorized users, should be backed up on regular basis so that data can be restored to the previous state, it may be due to the unpredictable system crash or user error.

Data Dictionary Vies of Privileges

Data Dictionary Views	Descriptions
ROLES_SUS_PRIVS	System privileges granted to roles.
ROLES_TABLES_PRIVS	Table privileges granted to roles.
USER_ROLES_PRIVS	Roles accessible by the users.
USER_TAB_PRIVS_MADE	Object Privileges granted to the user’s object.
USER_TAB_PRIVS_RECD	Object privileges granted to users.
USER_COL_PRIVS_MADE	Object privileges granted to the columns of the user’s objects.
USER_COL_PRIVS_RECD	Object privileges granted to the users on specific column.
USER_SYS_PRIVS	System privileges granted to the users.

Privilege and its Types

It is the right to access the database or execute a particular SQL statement. The database administrator(DBA) has the high level privileges in the system and database. There are two types of privileges

# System Privilege and

# Object Privilege

System Privilege

The system privilege/s allow the users to access the database.

Some system privileges are

# CREATE SESSION

# CREATE PROCEDURE

# CREATE VIEW

# CREATE SEQUENCE

Object Privilege

It allows the users to perform a particular task on the objects in the database such as tables, indices, sequences, views, etc. It differs from object to object.

Roles

It is a group of privileges. It is used to ease to management and granting of privileges to users.

Syntax for creating Role:

CREATE role role_name;

GRANT connect, resource to role_name;

Some DBA Privileges

# CREATE A USER

# REMOVE A USER

# BACKUP DATABASE

# STARTUP DATABASE

# SHUTDOWN DATABASE