This set of MySQL Database Multiple Choice Questions & Answers (MCQs) focuses on “Security for Stored Programs and Views”.
1. The creation of a stored program is similar to the definition of a _______________
a) Trigger
b) Event
c) View
d) Table
View Answer
Explanation: When a stored program is created, an object is created that is to be executed later. This also the case when a view is defined: It sets up a SELECT statement intended for later invocation.
2. When the security context enables carefully written stored programs to be set up that provide controlled access to tables for users, it is called __________
a) good
b) bad
c) illegal
d) fare
View Answer
Explanation: The security context is good if it enables carefully written stored programs to be set up that provide controlled access to tables for users who are not able to access them directly.
3. When a user creates a stored program that accesses sensitive data but forgets that other people who can invoke the object have the same access, the security context is __________
a) good
b) bad
c) illegal
d) fare
View Answer
Explanation: The security context is bad if a user creates a stored program that accesses sensitive data but forgets that other people who can invoke the object have the same access to that data as its definer.
4. Triggers and events are invoked automatically by the server.
a) True
b) False
View Answer
Explanation: The triggers and events are invoked automatically by the server, so the concept of invoking user is not applied. Thus, they have no SQL SECURITY characteristic and always execute with definer privileges.
5. A stored procedure is invoked using the statement __________
a) INVOKE
b) SEE
c) CALL
d) RETURN
View Answer
Explanation: In MySQL, a stored procedure is invoked using the CALL statement. A stored procedure does not have a return value but can modify its parameters. It also returns some result sets.
6. What produces result sets?
a) View
b) SELECT
c) Stored function
d) Trigger
View Answer
Explanation: In MySQL, views are the stored queries that when referenced produce a result set. A view acts as a virtual table. A stored procedure is also a component that can return result sets.
7. Which privilege is enabled to create of alter a stored function?
a) SUPER
b) PRIOR
c) TOP
d) SUPERIOR
View Answer
Explanation: In order to create or alter a stored function, the SUPER privilege should be given in addition to the CREATE ROUTINE privilege that is normally required. Depending on the DEFINER value in the function definition SUPER might be required.
8. The locking statements LOCK TABLES and UNLOCK TABLES are not permitted in stored routines.
a) True
b) False
View Answer
Explanation: In MySQL, the stored routines cannot contain arbitrary SQL statements. For example, the locking statements LOCK TABLES and UNLOCK TABLES are not permitted in the stored routines.
9. How many of the following can be used in stored procedures?
PREPARE, EXECUTE, DEALLOCATE PREPARE
a) 0
b) 1
c) 2
d) 3
View Answer
Explanation: The SQL prepared statements, PREPARE, EXECUTE, DEALLOCATE PREPARE can be used in the stored procedures. They can however, not be stored in the stored functions or triggers.
10. How many of the following can be used in stored functions?
PREPARE, EXECUTE, DEALLOCATE PREPARE
a) 0
b) 1
c) 2
d) 3
View Answer
Explanation: In MySQL, the SQL prepared statements: PREPARE, EXECUTE and DEALLOCATE PREPARE can be used in the stored procedures. They cannot be stored in stored functions or triggers.
Sanfoundry Global Education & Learning Series – MySQL Database.
To practice all areas of MySQL Database, here is complete set of 1000+ Multiple Choice Questions and Answers.
- Check MySQL Books
- Apply for Programming Internship
- Check Information Technology Books
- Practice Programming MCQs
