Controlling data access

Types of Privileges in Database
  • System Privileges
  • Schema Object Privileges
  • Table Privileges
  • View Privileges
  • Procedure Privileges
  • Type Privileges
System privileges
A system privilege is the right to perform a particular action, or to perform an   action on any schema objects of a particular type
— More than 100 privileges are available.
–- The database administrator has high-level system privileges for tasks such as:
– Creating new users
– Removing users
– Removing tables
– Backing up tablesCreating user:
syntax:

CREATE USER user_name
IDENTIFIED BY password;

Example:

CREATE USER EMP_10
IDENTIFIED BY emp1234;

After a user is created, the DBA can grant specific system privileges to that user

syntax:

GRANT privilege[, privilege...]
TO user [, user| role, PUBLIC...];

An application developer, for example, may have the following system privileges:
–CREATE SESSION
–CREATE TABLE
–CREATE SEQUENCE
–CREATE VIEW
–CREATE PROCEDURE

Example:
The DBA can grant specific system privileges to a user

GRANT create session, create table,
create sequence, create view
TO demo;

Role in SQL
SQL uses role-based security, which allows you to assign permissions to a role, or group of users.

Creating and Granting Privileges to a Role

Creating Role:

CREATE ROLE MGR_ROLE;

Assigning create table and Create view privilege to MGR_ROLE

GRANT create table, create view, Create session
TO MGR_ROLE;

Granting Role to user;

GRANT MGR_ROLE TO hr,scott;

Changing Your Password:
The DBA creates your user account and initializes your password.
–You can change your password by using the ALTER USER statement.

ALTER USER EMP_10
IDENTIFIED BY emp4321

Object Privileges

–- Object privileges vary from object to object.
–- An owner has all the privileges on the object.
–- An owner can give specific privileges on that owner’s object.

GRANT object_privilege[(columns)]
ON object
TO{user|role|PUBLIC}
[WITH GRANT OPTION];

Grant query privileges on the EMPLOYEES table:

GRANT select
ON employees
TO demo;

–Grant privileges to insert data to users and roles:

GRANT insert(department_name, location_id)
ON departments
TO EMP_10, MGR_ROLE;

Revoking Object Privileges

syntax:

REVOKE {privilege [, privilege...]|ALL}
ON object
FROM {user[, user...]|role|PUBLIC}
[CASCADE CONSTRAINTS];

Example:

REVOKE select, insert
ON departments
FROM demo

Leave a Reply

Your email address will not be published. Required fields are marked *