3130703 – DBMS Lab 3rd, CSE/IT
Practical: 08
Aim: To apply the concept of security and privileges.
1. GRANT Statement: Grant privileges to a user (or to a user role)
SYNTAX:
GRANT PRIVILEGES (like create, select, insert, update, delete) ON TABLENAME TO
USERNAME
ADMIN SIDE
select * from student_info
create user in oracle Home->administration->manage database user->create user
give username and password with roles connect.
1. GRANT SELECT, INSERT, UPDATE ON STUDENT_INFO TO ALPA;
2. grant all on student_info to alpa
USER SIDE LOGIN
select * from system.student_info;
ENROLLMENT_NO S_NAME CONTACT AGE BCODE GENDER
622 mushkan 9367204750 25 7 f
692 Jyoti 9898008840 23 7 f
225 anurag 9657000123 22 7 m
226 gaurav 9427456090 22 7 m
2. REVOKE Statement: Revoke privileges from a user
SYNTAX:
REVOKE PRIVILEGES (like create, select, insert, update, delete) ON TABLENAME
FROM USERNAME
ADMIN SIDE
1. REVOKE SELECT, INSERT, UPDATE ON STUDENT_INFO FROM ALPA;
2. revoke all on student_info from alpa
USER SIDE LOGIN
select * from system.student_info
Output: access denied
1
3130703 – DBMS Lab 3rd, CSE/IT
Practical: 09
Aim: To study Transaction Control Command.
1. COMMIT: Save changes (transactional).
SYNTAX:
COMMIT [ WORK ] [ COMMENT clause ] [ WRITE clause ] [ FORCE clause ];
WORK - Optional. It was added by Oracle to be SQL-compliant. Issuing the COMMIT with or
without the WORK parameter will result in the same outcome.
COMMENT clause - Optional. It is used to specify a comment to be associated with the current
transaction. The comment that can be up to 255 bytes of text enclosed in single quotes.
WRITE clause - Optional. It is used to specify the priority that the redo information for the
committed transaction is to be written to the redo log.
FORCE clause - Optional. It is used to force the commit of a transaction that may be corrupt or in
doubt.
If a network or machine failure prevents a distributed transaction from committing properly,
Oracle will store any commit comment in the data dictionary along with the transaction ID.
INPUT:
SQL>commit;
RESULT:
Commit complete.
2. SAVEPOINT: Save changes to a point (transactional).
SYNTAX:
SAVEPOINT text_identifier
3. ROLLBACK: Undo work done (transactional).
SYNTAX:
ROLLBACK [WORK] [TO [SAVEPOINT]' savepoint_text_identifier '];
ROLLBACK [WORK] [FORCE 'force_text'];
FORCE - will manually rollback an in-doubt distributed transaction
INPUT:
SQL>rollback;
RESULT:
Rollback complete.
Example:
2
3130703 – DBMS Lab 3rd, CSE/IT
select * from account;
ACC_NO NAME AMOUNT CITY
101 alpa 7000 ahmedabad
102 shiv 7000 delhi
103 nisha 3000 ahmedabad
115 foram 9500 surat
BEGIN
UPDATE account set amount = 9000 WHERE acc_no = 101;
UPDATE account set city = ‘delhi’ WHERE acc_no = 101;
SAVEPOINT sp1;
UPDATE account SET city= 'ujjain' WHERE acc_no = 101;
ROLLBACK TO sp1;
commit;
END;
ACC_NO NAME AMOUNT CITY
101 alpa 9000 delhi
102 shiv 7000 delhi
103 nisha 3000 ahmedabad
115 foram 9500 surat
3
3130703 – DBMS Lab 3rd, CSE/IT
Practical: 10
Aim: Write Cursor
SYNTAX:
DECLARE
CURSOR <cursor_name> IS <SELECT statement^>
<cursor_variable declaration>
BEGIN
OPEN <cursor_name>;
FETCH <cursor_name> INTO <cursor_variable>;
.
.
CLOSE <cursor_name>;
END;
EXAMPLE:
DECLARE
acc_rcd account%ROWTYPE;
CURSOR acc_cur IS
SELECT * FROM ACCOUNT WHERE CITY = 'surat' and amount > 9000;
BEGIN
OPEN acc_cur;
FETCH acc_cur INTO acc_rcd;
DBMS_OUTPUT.PUT_LINE('Emp no: ' || acc_rcd.acc_no);
DBMS_OUTPUT.PUT_LINE('Emp Name: ' || acc_rcd.name);
CLOSE acc_cur;
END;
Output:
Emp no: 115
Emp Name: foram
Statement processed.
0.00 seconds
select * from account;
ACC_NO NAME AMOUNT CITY
101 alpa 7000 ahmedabad
109 bhumika 11000 jodhpur
112 amit 7500 surat
113 anu 2500 ahmedabad
115 foram 9500 surat
4
3130703 – DBMS Lab 3rd, CSE/IT
Practical: 11
Aim: Write Trigger
SYNTAX:
CREATE [OR REPLACE] TRIGGER trigger_name
{BEFORE | AFTER } triggering_event ON table_name
[FOR EACH ROW]
[FOLLOWS | PRECEDES another_trigger]
[ENABLE / DISABLE ]
[WHEN condition]
DECLARE
declaration statements
BEGIN
executable statements
EXCEPTION
exception_handling statements
END;
EXAMPLE:
CREATE or REPLACE TRIGGER trg1
BEFORE
INSERT ON employee
FOR EACH ROW
BEGIN
:new.empname:= initcap(:new.empname);
END;
insert into employee values(12,'shiv',7)
Output:
Trigger created.
0.02 seconds
EMPID EMPNAME DEPTID
3 alpa 7
7 FORAM 31
8 Priya 9
9 MUSHKAN 6
12 Shiv 7
