Page 1 :
(UNIT - v |, , , , , , , , Database Security, and Transaction Processing, , , , , , , , , , , , ubcomponent of any organization's overall, nformation systems Security plan. In addition to the, need to preserve and Protect data for the smooth, unctioning of the organization, database designers, , ave a Tesponsibility to protect the privacy of, ndividuals about whom data is kept., , , , , , 5.1.1 | Data Security Requirements, , , , database security Tequirements come up from the, eed to protect data : First, from accidental loss and, corruption, and second, from intentional, unauthorized attempts to access or alter that data., secondary, concerns include protecting against, xcessive delays in accessing or using data, or even, igainst interference to the point of rejection of, ervice, The global costs of such security breaches run, 0 billions of dollars annually, and the cost to, individual companies can be severe, sometimes, terrible., , hese requirements are dynamic. New ee, i i or, , and practices continually provide new arenas a, itati ways, , unauthorized exploitation, as well as new ry’, , erate misuse to fect ev table, cci i te suse to affect even s, accidental or delibera’ a, , environments. Today's ;, i i ant, involves a_ globally changing ae, cultural environment, in which accu) Pe, necessarily affect both the use of existing, , and the development of new ones:, , products and, , 6-2), , The basic security standards which technology can, ensure are confidentiality, integrity and availability., , Confidentiality, , A secure database system ensures the confidentiality, of data .this means that it allows individual to see, only the data that they are supposed to see., Confidentiality has several aspects like privacy of, communications, secure storage of sensitive data,, authenticated users And granular access control., , Integrity, , Data integrity means that data is protected from, deletion and corruption ,both while it resides within, the database and while it is being transmitted over, the network.a secure database system ensures that the, data it contain is valid integrity consist several, aspects like only authorized users can alter data ie, system and object privilege contol, ability to maintain, valid relationship between values in the database i.e, referential inegrity,a database must be protected, against viruses., , Availability, , In a secure system ,data is made available to, authorized users without delay. availability of system, consist following aspects like system can be protected, against users using too much memory ie resistence, ,system performance must remain adequate regardless, of the number of users i.e scalibilty,administrator, must manage the users population ie flexibilty, , EES SSS SSE

Page 2 :
Different Types of database users, , * Application Programmers, , Application programmers are the one who writes, application programs that uses the database. These, application programs are written in programming, languages like COBOL or Programming language, , Application programmers' responsibilities, , , , , , , , , , , , , , = Designing and developing the database, application, , = Designing the database structure for an, application, , Estimating storage requirement, database application, , for the, , Tuning the application during development, Specifying modifications, , structure for an application, : End Users - End users are those who access the, database from the terminal end. They use the, developed applications and they don't have any, knowledge about the design and working of, , of the database, , user's responsibilities, Querying the database for decision making and, pate related activities, : * Performing data entry, = Performing modifications and deletion, existing data, = Generating reports and charts from the data, * DBA (Database Administrator)- DBA can be a single, person or it can be a group of person. Database, Administrator is responsible for everything that is, related to database. He makes the policies, strategies, and provides technical supports., _ A database administrator's responsibilities, , of, , , , , , , , , _= Contacting dbms vendors for technical support, , _ Control and monitor user's access to the, , atchived data on tape, objects like tables,views,indexes, e database structure, , TECHNICAL PUBLICATIONS” An up thrust for knowiedge, , , , , , , , , , , , , , , , , , , Database Security and Tran, , » Installing and upgrading the, application tool ,, = Planning for backup and recovery, « System Analyst :, es analyst is responsible for =, and properties of database. All, the end users are handled by system, , Feasibility, economic and technical aspects of, is the main concern of system analyst., , 3| Creating, Altering and Deleting Users, in MySQL, Creating a new user is very simple in MySQL., , , , , , «To create a new user, “create user, used : 4, CREATE USER ‘non-root'@ localhost’ IDENTIFIED 3 a, , In this command, ‘non-root’ is the name we've, , to our new user., , And '123' is the password for this user., , To grant the newly created user all privileges of, , database, execute the following command:, , GRANT ALL PRIVILEGES ON * .* TO, , ‘non-root'@ localhost’;, , Granting Specific Privileges for a MySQL User, , GRANT [permission type] ON [database name]. [table, , name] TO ‘non-root'@ localhost’ , You simply have to replace the ‘permission, , value with the kind of permission you want to g, , the new user. You also have to specify database a, table names., , MySQL has following permission types, + CREATE - Enables users to create database, * SELECT - Enables users to retrieve data, , + INSERT - Enables users to add new, tables, , +¢ UPDATE - Enables users to modify existing ent, in tables ., , entries, , + DELETE - Enables users to delete table entries, , *DROP - = Enables users to delete, databases/tables, , , , = The DROP USER statement removes, , more MySQL accounts and, DROP USER Syntax, , DROP USER [IF EXISTS] user [, user}, , their Privileges. —

Page 3 :
Database Privileges, , System Privileges, , ete ao Objects include tables, views,, - ed views, Synonyms, indexes, sequences,, hoeronees teplication schemes and PL/SQL, a Ss, procedures and Packages. Only the instance, administrator or a user with ADMIN privilege, grant or revoke system Privileges. oT, For example, System privileges, , Privilege Description, , ALTER ANY INDEX, , ALTER ANY, PROCEDURE, , ALTER ANY SEQUENCE, , Enables a user to alter any |, index in the database., Note: There is no ALTER, INDEX statement., , Enables a user to alter any, PL/SQL procedure,, function or package in the, database., , Enables a user to alter any, sequence in the database., , | Note: There is no ALTER, SEQUENCE statement., , ALTER ANY TABLE, , ALTER ANY VIEW, , CREATE ANY INDEX, , , , , , Enables a user to alter any, table in the database., , Enables a user to alter any, , view in the database., , Note: There is no ALTER, , VIEW statement., , Enables a user to create an, index on any table or, , materialized view in the |, database. }, , , , Priv Description |, Privilege i eo |, | Jes a user to create a |, CREATE ANY | PLB proc, age | function or package | |, | Bwned by any user in the, | database. i), , Sl, , , , TECHNICAL PUBLICATIONS™- An, , , , Database Security and Transaction Processing, CREATE ANY Enables a user to create a, SEQUENCE sequence owned by any, user in the database., , CREATE ANY TABLE Enables a user to create a, table owned by any user, } in the database., , | CREATE ANY VIEW Enables a user to create a, view owned by any user, in the database., , SELECT ANY TABLE Enables a user to select, from any table, view,, materialized view, or, synonym in the database., , | UPDATE ANY TABLE Enables a user to update |, | any table or synonym in, | the database., , , , , , , , 5.2.2 | Object Privileges, , An object privilege is the right to perform a, particular action on an object or to access another, user's object. Objects include tables, views,, materialized views, indexes, synonyms, sequences,, cache groups, replication schemes and PL/SQL, functions, procedures and packages., , , , An object's owner has all object privileges for that, object, and those privileges cannot be revoked. The, object's owner can grant object privileges for that, object to other database users. A user with ADMIN, privilege can grant and revoke object privileges from, users who do not own the objects on which the, privileges are granted., , Object privileges, , Privilege | Object type Description, , |, | |, DELETE | Table Enables a user to delete, | from a table., - 4 ss, EXECUTE | PL/SQL Enables a user to execute a, | package, PL/SQL package,, | procedure procedure or function, | or function directly., [INDEX | Table or _—_Enables a user to create an, | Aas, } | materialized index on a table or, | view materialized view., INSERT Table or Enables a user to insert, | synonym into a table or into the, , table through a synonym. |, , up thrust for knowledge

Page 4 :
Database Management System, SELECT Table, Enables a user to select, sequence, from a table, sequence,, view, view, materialized view, |, materialized or synonym, |, view, or The SELECT privilege |, synonym enables a user to perform |, all operations on a |, sequence, |, A user can be granted the |, SELECT privilege on a |, synonym or a view |, without being explicitly |, granted the SELECT |, privilege on the |, originating table,, UPDATE Table Enables a user to update a, table. |, 5.2.3 | Granting and Revoking Privileges, , , , , , , , To grant or revoke a privilege using one of the SQL, GRANT or REVOKE statements, the user must have, the following permissions for the GRANT/REVOKE, statement to succeed, , The syntax for granting and revoking privileges is, different for each database object, such as schema,, database, table, view, sequence, procedure, function., , Normally, a administrator first creates a user and, then uses GRANT syntax to define the user's, privileges or roles or both. For example, the following, series of statements creates user Rahul and grants, Rahul access to the db1 database in the PUBLIC, schema and also lets Rahul grant SELECT privileges, to other users on the Employee table :, , => CREATE USER Rahul;, , => GRANT USAGE ON SCHEMA PUBLIC to Rahul;, , => GRANT ALL ON DATABASE db1 TO Rahul;, , => GRANT SELECT ON Employee TO Rahul WITH GRANT, OPTION;, , A dbaadmin/superuser, database object types to other users, as well as grant, privileges to roles. Users who have been granted the, ‘role will then gain the..privilege as soon as they, enable it., ers may grant or revoke any object privilege, of the object owner, which means a, grant or revoke the object privilege if, , can grant privileges on all, , , , , , ner could have granted or revoked the , , , , , ' ansaction P, Database Security and Tr, ‘A superuser may revoke, , ; failene: 4, same object privil ee owmer grante d, as well as the, , privilege that an objec, reverse, :, &, How to Revoke Privileges t ,, In general, ONLY the user who originally granted a, privilege can revoke it using 4 REVOKE statement., That user must have superuser privilege or havg the, optional WITH GRANT OPTION on the privilege., The user also must have USAGE privilege on the, schema and appropriate privileges on the object for, the REVOKE statement to succeed., , In order to revoke a privilege, this privilege must, have been granted to the specified grantee by this, grantor before. example : ,, => REVOKE SELECT ON Employee FROM Rahul;, , REVOKE PRIVILEGE, , The above REVOKE statement removes the privilege é, , - (and WITH GRANT OPTION privilege, if applicable) ~, , from the grantee, , In order to revoke grant option for a privilege, the, grantor must have previously granted the grant, option for the privilege to the specified grantee. The, following REVOKE statement removes the GRANT, option only but leaves the privilege intact:, , => GRANT INSERT on Employee TO Rahul WITH GRANT, OPTION;, , GRANT PRIVILEGE, , => REVOKE GRANT OPTION FOR INSERT ON Employee, FROM Rahul;, , REVOKE PRIVILEGE, , When a user revokes an explicit list of privileges,, such as GRANT INSERT, DELETE, REFERENCES ON, Employee TO Rahul:, , «The REVOKE statement succeeds only if all the, roles are revoked successfully. If any revoke, operation fails, the entire statement rolls back., , When a user revokes ALL privileges, such as, , REVOKE ALL ON Employee TO Rahul, the statement, , always succeeds. revokes all the privileges on which, , the grantor has the optional WITH GRANT OPTION, , and skips those privileges without the WITH GRANT, OPTION., , For example, if the user Bob has delete privileges, with the optional grant option on the applog table,, , TECHNICAL PUBLICATIONS™- An up thrust for knowledge

Page 5 :
Database Management System, , only grant option js revoked f, TO! e, statement succeeds without NOTICE. Bob, and the, , ~ REVOKE GRANT OPTION Fo!, , motte R DELETE ON Employee, , , , , , (5.2.4 | Grant and Revoke Command, , DCL commands are used to enforce database security, in & multiple user database environment, Two types, of DCL commands are GRANT and REVOKE, Only, Database Administrator's of the database object can, provide/remove privileges on a database object, SQL GRANT Command, SQL GRANT is a command used to provide access or, privileges on the database objects to the users,, , The Syntax for the GRANT command is :, , GRANT Privilege_name, , ON object_name, , TO {user_name |PUBLIC |role_name}, , (WITH GRANT OPTION];, , ¢ privilege name is the access tight or privilege, granted to the user. Some of the access rights are, ALL, EXECUTE, and SELECT., , * object_name is the name of an database object like, TABLE, VIEW, STORED PROC and SEQUENCE., , euser_name is the name of the user to whom an, access right is being granted., , euser_name is the name of the user to whom an, access right is being granted., , » PUBLIC is used to grant access rights to all users., , +» ROLES are a set of privileges grouped together., , ¢ WITH GRANT OPTION - allows a user to grant, , access rights to other users., , For Example : GRANT SELECT ON employee TO, , user1;, this command grants a SELECT permission on, mployee table to userl.You should use the WITH, ‘RANT option carefully because for example if you, GRANT SELECT privilege on employee table to userl, using the WITH GRANT option, then userl can, GRANT SELECT privilege on employee table another user, such as user2 etc. Later, if you at, the SELECT privilege on employee from ee, user2 will have SELECT privilege on employee table., , 8., , Database Security and Transaction Processing, , SQL REVOKE Command :, , The REVOKE command removes user access rights or, privileges to the database objects., , The Syntax for the REVOKE command is:, REVOKE privilege name, , ON object_name, , FROM [user_name |PUBLIC lrole_name}, , For Example ; REVOKE SELECT ON employee, FROM user;, , This command will REVOKE a SELECT privilege on, employee table from userl.When you REVOKE, SELECT privilege on a table from a user, the user, will not be able to SELECT data from that table, anymore. However, if the user has received SELECT, privileges on that table from more than one users,, he/she can SELECT from that table until everyone, who granted the permission revokes it. You cannot, REVOKE privileges if they were not initially granted, by you., , , , , , , , 5.3 | Transaction, , , , A transaction is a unit of program execution that, accesses and possibly updates various data items. A, transaction must see a consistent database. During, transaction execution the database may be, Inconsistent. When the transaction is committed, the, database must be consistent., , Transaction initiated by user program written in, high data manipulation languages or, programming language(for example, sql,cobol,c,ct++,java), , level, , , , 5.3.1 | Transaction ACID Properties, , , , , , , , ‘s Atomicity : Either all operations of the transaction, , are Properly reflected in the database or none are, , e Consistency : Execution of a transaction in isolation, preserves the consistency of the database., , e Isolation : Although multiple transactions may, execute concurrently, each transaction must be, unaware other concurrently _ executing, transactions. Intermediate transaction results must, be hidden from other concurrently, Executed transactions., , of, , TECHNICAL PUBLICATIONS” An up thrust for knowledge