1 / 40

Database Programming

Database Programming. Sections 13–Creating, revoking objects privileges. Control of User Access. DCL data control language Oracle Server database security, you can do the following: Control database access Give access to specific objects in the database

colman
Download Presentation

Database Programming

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. Database Programming Sections 13–Creating, revoking objects privileges

  2. Control of User Access • DCL data control language • Oracle Server database security, you can do the following: • Control database access • Give access to specific objects in the database • Confirm given and received privileges within the Oracle data dictionary • Create synonyms for database objects Marge Hohly

  3. System vs. Object Privileges • System Privileges (system security) • System level access • Creating users, usernames & passwords, etc. • Allocating disk space • Granting system privileges • Generally granted by the DBA • Object Privileges (data security) • Object privileges • Access and use • Being able to execute DML statements Marge Hohly

  4. Privileges • Right to execute particular SQL statements. • DBA – high-level user with ability to grant users access • Users require system privileges to gain, access to databases/objects to manipulate content • Users can be given privilege to grant additional privileges to other users/roles Marge Hohly

  5. Schema • A schema is a collection of objects, such as tables, views, and sequences. • The schema is owned by a database user and has the same name as that user. • In this course, your schema name is - US_2856_SQL01_Sxx , where xx is your number. Marge Hohly

  6. System Privileges (Security) • Below are listed typical privileges provided by the database administrator. Marge Hohly

  7. System privileges of DBA • DBAs generally allocate system privileges • Any user who owns an object can grant object privileges Marge Hohly

  8. User System Privileges • Determine what the use can do at the database level • GRANT privilege [,privilege….]TO user [,user|role, PUBLIC…]; • GRANT create session, create table, create sequence, create viewTO scott; Marge Hohly

  9. Object privileges • Each object has set of grantable privileges • Only privileges that apply to a sequence are SELECT and ALTER • Can grant UPDATE, REFERENCES, and INSERT on individual columns of a table.Example:GRANT UPDATE(auth_expense) ON d_partners TO allison_plumb; • Restrict privileges using a view. Can’t grant SELECT on individual columns • A privilege granted on a synonym converts to a privilege on the base table referenced. Marge Hohly

  10. Object privileges Marge Hohly

  11. Object Privileges (Security) • This level covers access and use of database objects and actions users have on an object • An owner can give specific privileges on that owner’s object • GRANT obj_privilege(columns)ON objectTO USER|ROLE|PUBLIC{WITH GRANT OPTION} • To grant privileges on an object, the object must be in your schema, or you must have been granted the object privileges WITH GRANT OPTION • An object owner can grant any object privilege on the object to any other user or role of the database • The owner of an object automatically acquires all object privileges on that object • GRANT select, insert (name, email)ON address_bookTO Scott WITH GRANT OPTION • REVOKE select, insertON address_bookFROM scott; Marge Hohly

  12. Roles • Role is a named group of related privileges that can be granted to a user • Easier to revoke and maintain privileges • User may be granted several roles • Several users may be assigned to a role • Typically created for a database application • DBA creates roles, adds privileges and assigns to users Marge Hohly

  13. Roles • CREATE ROLE manager; • GRANT create table, create view TO manager; • GRANT manager TO jennifer_cho; • PRIVILEGES ARE GRANTED TO ROLES • PEOPLE ARE ASSIGNED TO ROLES Marge Hohly

  14. Role characteristics • Named groups of related privileges • Granted to users • Simplify the process of granting and revoking privileges • Created by the DBA Marge Hohly

  15. Why Roles are easier? • How it works. Marge Hohly

  16. Grant Object privileges • GRANT object_priv[(column_list)]ON object_nameTO{user|role|PUBLIC}[WITH GRANT OPTION] • Be careful using WITH GRANT OPTION Marge Hohly

  17. Guidelines to grant object privileges • To grant privileges on an object, the object must be in your own schema, or you must have been granted the object privileges WITH GRANT OPTION. • An object owner can grant any object privilege on the object to any other user or role of the database. • The owner of an object automatically acquires all object privileges on that object. • Cont. next slide Marge Hohly

  18. Syntax Marge Hohly

  19. Granting privileges for objects • Only privileges that apply to a sequence are SELECT and ALTER. • You can grant UPDATE, REFERENCES, and INSERT on individual columns on a table. For example: • GRANT UPDATE (auth_expense)ON d_partners TO allison_plumb; • You can restricted SELECT privilege by creating a view with a subset of columns and granting the SELECT privilege only on the view. • You can't grant SELECT on individual columns. • Privilege granted to synonym is converted to a privilege on the base table referenced by the synonym. Marge Hohly

  20. Grant & Revoke Syntax • GRANT object_priv[(columns)]ON objectTO {user|role|PUBLIC}[WITH GRANT OPTION]; • REVOKE {privilege [, privilege...]|All}ON objectFROM {user[, user...]|role|PUBLIC}[CASCADE CONSTRAINTS]; • CASCADE CONSTRAINTS - required to remove any referential integrity constraints made to the object by means of the REFERENCES privilege – like creating a reference to your table via foreign key Marge Hohly

  21. Examples DP.13.3.10 • 1. GRANT  select (Scott owns d_songs and typed this command)ON     d_songs TO   PUBLIC; • 2. GRANT  update (title, artist) (Scott owns d_songs and is granting ON     d_songs authorization to update these columns) TO     jennifer_cho, manager; • 3. SELECT  * (Jennifer now types this to view Scotts d_songs table) FROM    scott_king.d_songs;Jennifer types the following: • 4. CREATE SYNONYM songs FOR scott_king.d_songs; • 5. SELECT * FROM songs; (songs is the synonym) Marge Hohly

  22. WITH GRANT OPTION • GRANT  select, insertON     d_songsTO     scott_kingWITH   GRANT OPTION; • With grant option clause allows the privileges to be passed on to other users. • With Grant Option can be revoked when user’s privileges are revoked. Marge Hohly

  23. Pictorial view WITH GRANT OPTION Marge Hohly

  24. PUBLIC keyword • GRANT  selectON     jason_tsang.d_songsTO     PUBLIC; • Owner of a table can grant access to all users by using keyword PUBLIC Marge Hohly

  25. REVOKE privilege • REVOKE {privilege [, privilege...]|ALL}ON  objectFROM   {user[, user...]|role|PUBLIC}[CASCADE CONSTRAINTS]; • CASCADE CONSTRAINTS required to remove any referential integrity constraints made to the object by means of the REFERENCES privilege. Marge Hohly

  26. Revoke privilege • REVOKE select, insertON d_songsFROM usca_cc_sql01_sxx; Marge Hohly

  27. View Privileges • Access the data dictionary to view privileges you have • In APEX try to view the privileges of USER_ROLE_PRIVS Marge Hohly

  28. Viewing privilege in data dictionary Marge Hohly

  29. Access errors • Oracle Server error message “table or view does not exist,” you have done one of the following: • Named a table or view that does not exist • Attempted to perform an operation on a table or view for which you do not have the appropriate privileges Marge Hohly

  30. Example of privileges commands • SELECT *FROM role_tab_privsWHERE role = ‘MANAGER’; • SELECT *FROM user_sys_privs; • SELECT *FROM user_role_privs; Marge Hohly

  31. Displaying your privileges • To show what privileges a user has on the databases enter: • SELECT * FROM SESSION_PRIVS ; • You have a list of privileges you have displayed. • Run the command to see what you get. See next slide. Marge Hohly

  32. Private and Public Synonyms • Simplifies object names, with an alternate name for tables, view, sequence, procedures or other objects • Synonyms can be private (default) or public • Public synonyms created by DBA or those with that privilege • CREATE PUBLIC SYNONYM not given to you in APEX Marge Hohly

  33. Regular expressions • Regular expressions are a method of describing both simple and complex patterns for searching and manipulating. • In Oracle it is an extension of POSIX (Portable Operating System for UNIX) • Based on the use of meta characters which are special characters with special meaning • See next slide Marge Hohly

  34. META characters Marge Hohly

  35. Meta characters cont. Marge Hohly

  36. Example • Which of the following strings would match ‘a.c’? An ‘a’ followed by the letter ‘c’.‘ABC’, ‘abc’, ‘aqx’, ‘axc’, ‘aBc’, ‘abC’ • Standard SQL: WHERE column LIKE ‘a_c’ • Regular expression would be: ‘a.c’ Marge Hohly

  37. Example answer • ‘ABC’, ‘abc’, ‘aqx’, ‘axc’, ‘aBc’, ‘abC’ • Red matched the regular expression • Others failed either wrong letters or in the wrong place or wrong case Marge Hohly

  38. Example • Search for Stephen or Steven • Regular expression = ‘^Ste(v|ph)en$’ • ^ = start of string to search • (start of group • | specifies an OR • )finishes the group of choices • $ specifies the end of the string being searched Marge Hohly

  39. Regular Expression Functions Marge Hohly

  40. Examples • Review the examples provided in iLearning Marge Hohly

More Related