SQL

Controlling User Access : 

Controlling User Access

Objectives : 

Objectives After completing this lesson, you should be able to do the following: Differentiate system privileges from object privileges Grant privileges on tables View privileges in the data dictionary Grant roles Distinguish between privileges and roles

Controlling User Access : 

Controlling User Access Database administrator Users Username and password Privileges

Privileges : 

Privileges Database security: System security Data security System privileges: Gaining access to the database Object privileges: Manipulating the content of the database objects Schemas: Collection of objects such as tables, views, and sequences

System Privileges : 

System Privileges 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 tables

Creating Users : 

Creating Users The DBA creates users with the CREATE USER statement. CREATE USER HR IDENTIFIED BY HR; User created. CREATE USER user IDENTIFIED BY password;

User System Privileges : 

User System Privileges After a user is created, the DBA can grant specific system privileges to that user. An application developer, for example, may have the following system privileges: CREATE SESSION CREATE TABLE CREATE SEQUENCE CREATE VIEW CREATE PROCEDURE GRANT privilege [, privilege...] TO user [, user| role, PUBLIC...];

Granting System Privileges : 

Granting System Privileges The DBA can grant specific system privileges to a user. GRANT create session, create table, create sequence, create view TO scott; Grant succeeded.

What Is a Role? : 

What Is a Role? Allocating privileges without a role Allocating privileges with a role Privileges Users Manager

Creating and Granting Privileges to a Role : 

Creating and Granting Privileges to a Role Create a role Grant privileges to a role Grant a role to users CREATE ROLE manager; Role created. GRANT create table, create view TO manager; Grant succeeded. GRANT manager TO DE HAAN, KOCHHAR; Grant succeeded.

Changing Your Password : 

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 HR IDENTIFIED BY employ; User altered.

Object Privileges : 

Object Privileges

Object Privileges : 

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_priv [(columns)] ON object TO {user|role|PUBLIC} [WITH GRANT OPTION];

Granting Object Privileges : 

Granting Object Privileges Grant query privileges on the EMPLOYEES table. Grant privileges to update specific columns to users and roles. GRANT select ON employees TO sue, rich; Grant succeeded. GRANT update (department_name, location_id) ON departments TO scott, manager; Grant succeeded.

Passing On Your Privileges : 

Passing On Your Privileges Give a user authority to pass along privileges. Allow all users on the system to query data from Alice’s DEPARTMENTS table. GRANT select, insert ON departments TO scott WITH GRANT OPTION; Grant succeeded. GRANT select ON alice.departments TO PUBLIC; Grant succeeded.

Confirming Privileges Granted : 

Confirming Privileges Granted

Revoking Object Privileges : 

Revoking Object Privileges You use the REVOKE statement to revoke privileges granted to other users. Privileges granted to others through the WITH GRANT OPTION clause are also revoked. REVOKE {privilege [, privilege...]|ALL} ON object FROM {user[, user...]|role|PUBLIC} [CASCADE CONSTRAINTS];

Revoking Object Privileges : 

Revoking Object Privileges As user Alice, revoke the SELECT and INSERT privileges given to user Scott on the DEPARTMENTS table. REVOKE select, insert ON departments FROM scott; Revoke succeeded.

Summary : 

Summary In this lesson, you should have learned about statements that control access to the database and database objects.

Practice 1: Overview : 

Practice 1: Overview This practice covers the following topics: Granting other users privileges to your table Modifying another user’s table through the privileges granted to you Creating a synonym Querying the data dictionary views related to privileges