MySQL Questions and Answers – Security for Stored Programs and Views

This set of MySQL Problems 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

Answer: c
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

Answer: a
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

Answer: b
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.
advertisement
advertisement

4. Triggers and events are invoked automatically by the server.
a) True
b) False
View Answer

Answer: a
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

Answer: c
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

Answer: a
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

Answer: a
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.
advertisement

8. The locking statements LOCK TABLES and UNLOCK TABLES are not permitted in stored routines.
a) True
b) False
View Answer

Answer: a
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
advertisement

a) 0
b) 1
c) 2
d) 3
View Answer

Answer: d
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

Answer: a
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 Problems, here is complete set of 1000+ Multiple Choice Questions and Answers.

advertisement
advertisement
Subscribe to our Newsletters (Subject-wise). Participate in the Sanfoundry Certification contest to get free Certificate of Merit. Join our social networks below and stay updated with latest contests, videos, internships and jobs!

Youtube | Telegram | LinkedIn | Instagram | Facebook | Twitter | Pinterest
Manish Bhojasia - Founder & CTO at Sanfoundry
Manish Bhojasia, a technology veteran with 20+ years @ Cisco & Wipro, is Founder and CTO at Sanfoundry. He lives in Bangalore, and focuses on development of Linux Kernel, SAN Technologies, Advanced C, Data Structures & Alogrithms. Stay connected with him at LinkedIn.

Subscribe to his free Masterclasses at Youtube & discussions at Telegram SanfoundryClasses.