PLSQL- Assertion , Roles & Privileges, Embedded & Dynamic SQL

1. PLSQL- Assertion , Roles & Privileges, Embedded & Dynamic SQL

2. Assertion • Assertions - An assertion is a piece of SQL which makes sure a condition is satisfied or it stops action being taken on a database object. It could mean locking out the whole table or even the whole database. • Assertions - Assertions do not modify the data, they only check certain conditions.

3. Assertion Syntax: CREATE ASSERTION <name> CHECK ( <condition> ); Example: Assertion There should not any customer in bank having balance less than 1000. Create Assertion c1 Check ( NOT EXISTS ( Select * From customer Where balance<1000));

4. Roles & Privileges

5. Controlling User Access Database administrator Username and password Privileges Users

6. 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

7. 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

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

9. 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...];

10. 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.

11. Creating and Granting Privileges toa 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.

12. 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];

13. 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.

14. 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.

15. 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];

16. 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.

17. Statement Action CREATE USER Creates a user (usually performed by a DBA) GRANT Gives other users privileges to access the objects CREATE ROLE Creates a collection of privileges (usually performed by a DBA) ALTER USER Changes a user’s password REVOKE Removes privileges on an object from users Summary In this lesson, you should have learned about statements that control access to the database and database objects.

18. Embedded SQL

19. Embedded SQL –Most SQL statements can be embedded in hostprogramming language such as COBOL, C, Java int loop; EXEC SQL BEGIN DECLARE SECTION; varchar name[16],addr[30]; char eid[10]; int sal; EXEC SQL END DECLARE SECTION; • SET CONNECTION connection-name; • DISCONNECT connection-name; • EXEC SQL select NAME,ADDR,SAL into :name,:addr,:sal from EMPLOYEE where EID == :eid; Declaration of SQL variables in C SQL Database Connection in C SQL Select Query in C

20. Embedded SQL for JAVA- Steps in JDBC Database Access • Import JDBC library (java.sql.*) • Load JDBC driver: Class.forname(“oracle.jdbc.driver.OracleDriver”) • Define appropriate variables • Create a connect object (via getConnection) • Create a statement object from the Statementclass: • Identify statement parameters (designated by question marks) • Bound parameters to program variables • Execute SQL statement (referenced by an object) via JDBC’s executeQuery • Process query results (returned in an object of type ResultSet) • ResultSet is a 2-dimentional table

21. Embedded SQL for JAVA- Example • import java.sql.*; • public class MySQLProcedure{public static void main(String[] args) { • try{ • Class.forName("com.mysql.jdbc.Driver"); • Connection c = DriverManager.getConnection("jdbc:mysql:// localhost: 3306/ db1","root", ""); • Statement sm= c.createStatement(); • String sql= "SELECT name, age FROM stud"; • ResultSetrs= sm.executeQuery(sql); • while(rs.next()){ • String name1 = rs.getString("name"); • int age1 = rs.getInt(“age"); • System.out.print(“Name age " + name1,age1);} • catch(Exception e){e.printStackTrace();} } }

23. Dynamic SQL

24. Dynamic SQL

25. Dynamic SQL: An Example in C EXEC SQL BEGIN DECLARE SECTION; varchar s1[256]; EXEC SQL END DECLARE SECTION; prompt (“Enter command“, s1); EXEC SQL PREPARE s2 FROM :s1; EXEC SQL EXECUTE s2;