Oracle Interview question about Profile and Password Policies

Profile and Password Policies

Profiles are used to limit resources and implement password policies. Oracle implements password checking by defining a function to the database at login time. The default function provided by Oracle is implemented in a file call utlpwdmg.sql. This file exists in Doc sharing for your inspection.

Profiles can be defined to limit resource usage.

1. Of the nine resource limits defined, what limits would you want to define in a profile for your database?
2. Why would you want to limit resource usage at all?
3. What happens to a user's session when a resource limit is exceeded, and how does it affect the database?

Managing Privileges

Managing privileges can be very confusing if not planned and implemented in an organized manner.

1. How do roles simplify database administration with regard to privileges?

2. Should all privileges be granted though a role? Explain your reasons why or why not.

3. Is there a situation where granting a privilege to the role would not work?

Privilege Management

1.      "Your manager has asked you to evaluate the privileges granted to each user in the database. What data dictionary views must be queried to answer this request? Give an example of the query needed to provide the information and explain what the data represents."

2.      How are privileges removed?

3.      For practice with privileges here Which condition must be met for a user to give SELECT privileges on the CUSTOMER table to everyone using the PUBLIC keyword?

Advanced Features

1.      Research and explain what "Regular Expressions" are and what they produce. Examples and URLs are welcome.

2.       Also research and discuss when and why Regular Expressions should be used.

3.      Let's use this forum to discuss advanced features and also practice security questions similar to the ones that will be on the quiz.

Evaluate this SQL script and post your thought on the result:

CREATE USER hr IDENTIFIED BY hr01;
CREATE ROLE hr_manager;
CREATE ROLE hr_clerk;
GRANT hr_clerk TO hr_manager;
CREATE ROLE hr_director;
GRANT hr_manager TO hr_director;
GRANT hr_director TO hr
/

How many roles will the HR user have access to?

Database Applications and Programming - Week 6 Homework

Database Applications and Programming.
Week 6 Homework

Using the following Data Dictionary views write the statements that will perform the following actions. Be sure to test your statements. (Do not use SELECT *)
ROLE_ROLE_PRIVS
ROLE_SYS_PRIVS
ROLE_TAB_PRIVS
USER_ROLE_PRIVS
USER_SYS_PRIVS
USER_TAB_PRIVS

1. Determine what privileges your account has been granted through a role. (3 points)

2. Determine what system privileges your account has been granted. (3 points)

3. Execute the following statement then determine what table privileges your account has been granted. (3 points)

Grant select on student to public;

4. Determine what system privileges the DVONLINE role has. (3 points)

5. Analyze the following query and write a description of the output it produces. (3 points)



SELECT COUNT(DECODE(SIGN(total_capacity-20),-1,1,0,1)) "<=20",
COUNT(DECODE(SIGN(total_capacity-21), 0, 1, -1, NULL,
DECODE(SIGN(total_capacity-30), -1, 1)))"21-30",
COUNT(DECODE(SIGN(total_capacity-30), 1, 1)) "31+"
FROM
(SELECT SUM(capacity) total_capacity, course_no
FROM section
GROUP BY course_no);


6. Determine the top three zip codes where most of the students live. Use an analytical function. The query will produce 10 rows. (5 points)

If you need the solution of above exercise contact at arjuiut at gmail.com