Oracle 1z0-061: SQL statements to create and assign role, then grant privileges.

Question

You need to perform these tasks:

1. Create and assign a MANAGER role to Blake and Clark
2. Grant CREATE TABLE and CREATE VIEW privileges to Blake and Clark

Which set of SQL statements achieves the desired results?

A. CREATE ROLE manager; GRANT create table, create view TO manager; GRANT manager TO BLAKE, CLARK;
B. CREATE ROLE manager; GRANT create table, create voew TO manager; GRANT manager ROLE TO BLAKE, CLARK;
C. GRANT manager ROLE TO BLAKE, CLARK; GRANT create table, create voew TO BLAKE CLARK; ***MISSING***

Answer

A. CREATE ROLE manager; GRANT create table, create view TO manager; GRANT manager TO BLAKE, CLARK;

Explanation

The correct answer is A. CREATE ROLE manager; GRANT create table, create view TO manager; GRANT manager TO BLAKE, CLARK;

The following SQL statements will create a MANAGER role, grant the CREATE TABLE and CREATE VIEW privileges to the MANAGER role, and then assign the MANAGER role to Blake and Clark:

CREATE ROLE manager; 
GRANT create table, create view TO manager; 
GRANT manager TO BLAKE, CLARK;

The following SQL statements will not work as expected:

CREATE ROLE manager; 
GRANT create table, create voew TO manager; 
GRANT manager ROLE TO BLAKE, CLARK;

This is because the second GRANT statement does not specify the privileges that are being granted. The following SQL statements will work as expected, but they are not necessary because the privileges have already been granted to the MANAGER role:

GRANT create table TO BLAKE, CLARK; 
GRANT create view TO BLAKE, CLARK;

Here is a detailed explanation of each SQL statement:

CREATE ROLE manager;

This statement creates a new role named MANAGER.

GRANT create table, create view TO manager;

This statement grants the CREATE TABLE and CREATE VIEW privileges to the MANAGER role.

GRANT manager TO BLAKE, CLARK;

This statement assigns the MANAGER role to Blake and Clark.

Reference

• SQL Language Reference (oracle.com)
• Learn Oracle CREATE ROLE Statement By Practical Examples (oracletutorial.com)
• SQL Server CREATE ROLE (sqlservertutorial.net)
• CREATE ROLE (oracle.com)
• Oracle / PLSQL: Roles (techonthenet.com)
• How to Create a User and Grant Permissions in Oracle | Tutorial by Chartio
• Creating and using database roles | Oracle Database 12c Security Cookbook (packtpub.com)

Oracle Database 12c SQL Fundamentals 1z0-061 certification exam practice question and answer (Q&A) dump with detail explanation and reference available free, helpful to pass the Oracle Database 12c SQL Fundamentals 1z0-061 exam and earn Oracle Database 12c SQL Fundamentals 1z0-061 certification.