Question

3. Write the statements to: • create a user named FooBar with a password of “candybar” • assign the payments role created in the previous question to FooBar • assign the create session privilege to FooBar • Show the statements to connect to FooBar

4. For this question, I only need the query statement and not the results. Write the queries that use the user_sys_privs, user_tab_privs, user_role_privs, role_sys_privs and role_tab_privs objects to view the user and role privileges that are available to FooBar. You need a WHERE clause to check for the proper OWNER.

5. Write a script that grants the privilege of creating public synonyms to the payments role. Which user should you be connected to, in order to provide this privilege?

6. Show the statements to connect as FooBar and create a public synonym for the AP’s Invoices table.

Answer 1

3. For creating user with password use this syntax

Create user Foo bar identified by candybar;

For assigning payment role to Foo bar use this command

Ensure we have roles available if not use this command

Create role payments ;

Grant payment to FooBar:

Set role payments;

This needs to be done in the FooBar session.

For creating the session to FooBar use this query:

Grant create session to FooBar ;

For connecting to database use the login credentials as

Enter user name : FooBar

Enter password: candybar (which will be hidden )

4. For querying the roles associated with FooBar use this query

Select * from session_roles where Owner = ''FooBar'';

For creating public synonyms please use the following query and name the table as required:

Create public synonym synonym_name

For payments;

The public synonym can be created for people who has underlying necessary privileges to access the object which is payments table here to use public synonyms.

5. Use same privileges of username and password which is FooBar and candybar and connect to Db.

And use this query:

Create public synonym AP Invoice table

For payments;