1 / 41

Controlling User Access

Controlling User Access. Objectives. After completing this lesson, you should be able to do the following: Create users Create roles to ease setup and maintenance of the security model Use the GRANT and REVOKE statements to grant and revoke object privileges

dobry
Download Presentation

Controlling User Access

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Controlling User Access

  2. Objectives After completing this lesson, you should be able to do the following: • Create users • Create roles to ease setup and maintenance of the security model • Use the GRANT and REVOKE statements to grant and revoke object privileges • Create and access database links

  3. Controlling User Access Database administrator Username and password Privileges Users

  4. Privileges • Database security: • System security • Data security • System privileges: Gaining access to the database • Object privileges: Manipulating the content of the database objects • Schemas: Collections of objects, such as tables, views, and sequences

  5. System Privileges • More than 100 privileges are available. • The database administrator has high-level system privileges for tasks such as: • Creating new users • Removing users • Removing tables • Backing up tables

  6. Creating Users The DBA creates users by using the CREATE USER statement. CREATE USER user IDENTIFIED BY password; CREATE USER scott IDENTIFIED BY tiger; User created.

  7. User System Privileges • Once a user is created, the DBA can grant specific system privileges to a user. • An application developer, for example, may have the following system privileges: • CREATE SESSION • CREATE TABLE • CREATE SEQUENCE • CREATE VIEW • CREATE PROCEDURE GRANT privilege [, privilege...] TO user [, user| role, PUBLIC...];

  8. Granting System Privileges The DBA can grant a user specific system privileges. GRANT create session, create table, create sequence, create view TO scott; Grant succeeded.

  9. What is a Role? Users Manager Privileges Allocating privileges without a role Allocating privileges with a role

  10. Creating and Granting Privileges to a Role • Create a role • Grant privileges to a role • Grant a role to users CREATE ROLE manager; Role created. GRANT create table, create view TO manager; Grant succeeded. GRANT manager TO DEHAAN, KOCHHAR; Grant succeeded.

  11. Changing Your Password • The DBA creates your user account and initializes your password. • You can change your password by using the ALTER USER statement. ALTER USER scott IDENTIFIED BY lion; User altered.

  12. Object Privileges Object Privilege Table View Sequence Procedure ALTERÖ Ö DELETEÖ Ö EXECUTEÖ INDEXÖ INSERTÖ Ö REFERENCESÖ Ö SELECTÖ Ö Ö UPDATEÖ Ö

  13. Object Privileges • Object privileges vary from object to object. • An owner has all the privileges on the object. • An owner can give specific privileges on that owner’s object. GRANT object_priv [(columns)] ON object TO {user|role|PUBLIC} [WITH GRANT OPTION];

  14. Granting Object Privileges • Grant query privileges on the EMPLOYEES table. • Grant privileges to update specific columns to users and roles. GRANT select ON employees TO sue, rich; Grant succeeded. GRANT update (department_name, location_id) ON departments TO scott, manager; Grant succeeded.

  15. Using the WITHGRANTOPTION and PUBLIC Keywords • Give a user authority to pass along privileges. • Allow all users on the system to query data from Alice’s DEPARTMENTS table. GRANT select, insert ON departments TO scott WITH GRANT OPTION; Grant succeeded. GRANT select ON alice.departments TO PUBLIC; Grant succeeded.

  16. Confirming Privileges Granted Data Dictionary View Description ROLE_SYS_PRIVS System privileges granted to roles ROLE_TAB_PRIVS Table privileges granted to roles USER_ROLE_PRIVS Roles accessible by the user USER_TAB_PRIVS_MADE Object privileges granted on the user’s objects USER_TAB_PRIVS_RECD Object privileges granted to the user USER_COL_PRIVS_MADE Object privileges granted on the columns of the user’s objects USER_COL_PRIVS_RECD Object privileges granted to the user on specific columns USER_SYS_PRIVS Lists system privileges granted to the user

  17. How to Revoke Object Privileges • You use the REVOKE statement to revoke privileges granted to other users. • Privileges granted to others through the WITH GRANT OPTION clause are also revoked. REVOKE {privilege [, privilege...]|ALL} ON object FROM {user[, user...]|role|PUBLIC} [CASCADE CONSTRAINTS];

  18. Revoking Object Privileges As user Alice, revoke the SELECT and INSERT privileges given to user Scott on the DEPARTMENTS table. REVOKE select, insert ON departments FROM scott; Revoke succeeded.

  19. Database Links A database link connection allows local users to access data on a remote database. Local Remote EMP Table SELECT * FROM emp@HQ_ACME.COM; HQ_ACME.COM database

  20. Database Links • Create the database link. • Write SQL statements that use the database link. CREATE PUBLIC DATABASE LINK hq.acme.com USING 'sales'; Database link created. SELECT * FROM emp@HQ.ACME.COM;

  21. Summary In this lesson, you should have learned about DCL statements that control access to the database and database objects: Statement Action CREATE USER Creates a user (usually performed by a DBA) GRANT Gives other users privileges to access the your objects CREATE ROLE Creates a collection of privileges (usually performed by a DBA) ALTER USER Changes a user’s password REVOKE Removes privileges on an object from users

  22. Practice Overview This practice covers the following topics: • Granting other users privileges to your table • Modifying another user’s table through the privileges granted to you • Creating a synonym • Querying the data dictionary views related to privileges

  23. Using SET Operators

  24. Objectives After completing this lesson, you should be able to do the following: • Describe SET operators • Use a SET operator to combine multiple queries into a single query • Control the order of rows returned

  25. A B INTERSECT The SET Operators A B A B UNION/UNION ALL A B MINUS

  26. Tables Used in This Lesson The tables used in this lesson are: • EMPLOYEES: Provides details regarding allcurrent employees • JOB_HISTORY: Records the details of the start date and end date of the former job, and the job identification number and department when an employee switches jobs

  27. The UNION Operator A B The UNION operator returns results from both queries after eliminating duplications.

  28. Using the UNION Operator Display the current and previous job details of all employees. Display each employee only once. SELECT employee_id, job_id FROM employees UNION SELECT employee_id, job_id FROM job_history; … …

  29. A B The UNION ALL Operator The UNION ALL operator returns results from both queries, including all duplications.

  30. Using the UNION ALL Operator Display the current and previous departments ofall employees. SELECT employee_id, job_id, department_id FROM employees UNION ALL SELECT employee_id, job_id, department_id FROM job_history ORDER BY employee_id; … …

  31. A B The INTERSECT Operator

  32. Using the INTERSECT Operator Display the employee IDs and job IDs of employees who currently have a job title that they held before beginning their tenure with the company. SELECT employee_id, job_id FROM employees INTERSECT SELECT employee_id, job_id FROM job_history;

  33. A B The MINUS Operator

  34. The MINUS Operator Display the employee IDs of those employees who have not changed their jobs even once. SELECT employee_id,job_id FROM employees MINUS SELECT employee_id,job_id FROM job_history; …

  35. SET Operator Guidelines • The expressions in the SELECT lists must match in number and data type. • Parentheses can be used to alter the sequence of execution. • The ORDER BY clause: • Can appear only at the very end of the statement • Will accept the column name, aliases from the first SELECT statement, or the positional notation

  36. The Oracle Server and SET Operators • Duplicate rows are automatically eliminated except in UNION ALL. • Column names from the first query appear in the result. • The output is sorted in ascending order by default except in UNION ALL.

  37. Matching the SELECT Statements Using the UNION operator, display the department ID, location, and hire date for all employees. SELECT department_id, TO_NUMBER(null) location, hire_date FROM employees UNION SELECT department_id, location_id, TO_DATE(null) FROM departments; …

  38. SELECT employee_id, job_id,salary FROM employees UNION SELECT employee_id, job_id,0 FROM job_history; Matching the SELECT Statement • Using the UNION operator, display the employee ID, job ID, and salary of all employees. …

  39. Controlling the Order of Rows Produce an English sentence using two UNION operators. COLUMN a_dummy NOPRINT SELECT 'sing' AS "My dream", 3 a_dummy FROM dual UNION SELECT 'I''d like to teach', 1 FROM dual UNION SELECT 'the world to', 2 FROM dual ORDER BY 2;

  40. Summary In this lesson, you should have learned how to: • Use UNION to return all distinct rows • Use UNION ALL to returns all rows, including duplicates • Use INTERSECT to return all rows shared byboth queries • Use MINUS to return all distinct rows selected by the first query but not by the second • Use ORDER BY only at the very end ofthe statement

  41. Practice Overview This practice covers using the Oracle9i datetime functions.

More Related