Database Programming

1
Database Programming

• Sections 13Creating, 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

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

4
Privileges

• Right to execute particular SQL statements.
• DBA high-level user with ability to grant users
  access to database and its objects
• 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

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_1859_SQL01_Sxx , where xx is your number.

6
System Privileges (Security)

• Below are listed typical privileges provided by
  the database administrator.

7
System privileges of DBA

• DBAs generally allocate system privileges
• Any user who owns an object can grant object
  privileges

System Privilege Operations Authorized
CREATE SESSION Connect to the database
CREATE TABLE Create tables in the users schema
CREATE SEQUENCE Create sequences in the users schema
CREATE VIEW Create a view in the users schema
CREATE PROCEDURE Create a stored procedure, function, or package in the users schema
8
User System Privileges

• Determine what the user can do at the database
  level
• GRANT privilege ,privilege.TO user
  ,userrole, PUBLIC
• GRANT create session, create table, create
  sequence, create viewTO scott

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.ExampleGRANT
  UPDATE(auth_expense) ON d_partners
  TO allison_plumb
• Restrict privileges using a view. Cant grant
  SELECT on individual columns
• A privilege granted on a synonym converts to a
  privilege on the base table referenced.

10
Object privileges
Object Privilege Table View Sequence Procedure
ALTER X X
DELETE X X
EXECUTE X
INDEX X X
INSERT X X
REFERENCES X
SELECT X X X
UPDATE X X
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
  owners object
• GRANT obj_privilege(columns)ON objectTO
  USERROLEPUBLICWITH 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

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

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

14
Role characteristics

• Named groups of related privileges
• Granted to users
• Simplify the process of granting and revoking
  privileges
• Created by the DBA

15
Why Roles are easier?

• How it works.

16
Grant Object privileges

• GRANT object_priv(column_list)ON
  object_nameTOuserrolePUBLICWITH GRANT
  OPTION
• Be careful using WITH GRANT OPTION

17
Syntax
18
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

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.

20
Grant Revoke Syntax

• GRANT object_priv(columns)ON objectTO
  userrolePUBLICWITH GRANT OPTION
• REVOKE privilege , privilege...AllON
  objectFROM user, user...rolePUBLICCASCADE
  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

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)

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 users
  privileges are revoked.

23
Pictorial view WITH GRANT OPTION
24
PUBLIC keyword

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

25
REVOKE privilege

• REVOKE privilege , privilege...ALLON
   objectFROM   user, user...rolePUBLICCASC
  ADE CONSTRAINTS
• CASCADE CONSTRAINTS required to remove any
  referential integrity constraints made to the
  object by means of the REFERENCES privilege.

26
Revoke privilege

• REVOKE select, insertON d_songsFROM
  us_1859_SQL01_Sxx

27
View Privileges

• Access the data dictionary to view privileges you
  have
• In APEX try to view the privileges of
  USER_ROLE_PRIVS

28
Viewing privilege in data dictionary
Data Dictionary View Description
ROLE_SYS_PRIVS System privileges granted to roles
ROLE_TAB_PRIVS Tables privileges granted to roles
USER_ROLE_PRIVS Roles accessible by the user
USER_TAB_PRIVS_MADE Object privileges granted on the users objects
USER_TAB_PRIVS_RECD Object privileges granted to the user
USER_COL_PRIVS_MADE Objects privileges granted on the columns of the users objects
USER_COL_PRIVS_RECD Object privileges granted to the user on specific columns
USER_SYS_PRIVS Lists system privileges granted to the user
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

30
Example of privileges commands

• SELECT FROM role_tab_privsWHERE role
  MANAGER
• SELECT FROM user_sys_privs
• SELECT FROM user_role_privs

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.

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

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

34
META characters
Symbol Description
Matches zero or more occurrences
Alteration operator for specifying alternative matches
/ Matches the start-of-line/end-of-line
Bracket expression for a matching list matching any one of the expressions represented in the list
m Matches exactly m times
m.n Matches at least m times but no more than n times
Specifies a character class and matches any character in that class
35
Meta characters cont.
Symbol Description
Can have 4 different meanings 1. stand for itself. 2. Quote the next character. 3. Introduce an operator. 4. Do nothing.
Matches one or more occurrence
? Matches zero or one occurrence
. Matches any character in the supported character set, except NULL
() Grouping expression, treated as a single subexpression
Specifies equivalence classes
\n Back-reference expression
.. Specifies one collation element, such as a multi-character element
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

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

38
Example

• Search for Stephen or Steven
• Regular expression Ste(vph)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

39
Regular Expression Functions
Name Description
REGEXP_LIKE Similar to the LIKE operator, but performs regular expression matching instead of simple pattern matching
REGEXP_REPLACE Searched for a regular expression pattern and replaces it with a replacement string
REGEXP_INSTR Searches for a given string for a regular expression pattern and returns the position where the match is found
REGEXP_SUBSTR Searches for a regular expression pattern within a given string and returns the matched substring
REGEXP_COUNT Returns the number of times a pattern appears in a string. You specify the string and the pattern. You can also specify the start position and matching options(for example, c for case sensitivity).
40
Examples

• Review the examples provided in iLearning

41

• From Wikipedia, the free encyclopedia
  (http//en.wikipedia.org/wiki/Regular_expression)
  
• In computing, regular expressions provide a
  concise and flexible means for identifying text
  of interest, such as particular characters,
  words, or patterns of characters. Regular
  expressions are written in a formal language that
  can be interpreted by a regular expression
  processor, a program that either serves as a
  parser generator or examines text and identifies
  parts that match the provided specification. The
  following examples illustrate a few
  specifications that could be expressed in a
  regular expression
• ? The sequence of characters car in any
  context, such as car, cartoon,
• or bicarbonate.
• ? The word car, when it appears as an isolated
  word.
• ? The word car when preceded by the word
  blue or red. This would not
• find green car.
• ? A dollar sign immediately followed by one or
  more digits, and then optionally a period and
  exactly two more digits. Regular expressions can
  be much more complex than these
• examples.
• Regular expressions (abbreviated as regex or
  regexp, with plural forms regexes, regexps
• , or regexen) are used by many text editors,
  utilities, and programming languages to search
  and manipulate text based on patterns. For
  example, Perl and Tcl have a powerful regular
  expression engine built directly into their
  syntax. Several utilities provided by UNIX
  distributions including the editor ed and the
  filter grepwere the first to popularize the
  concept of regular expressions.

42

• Read notes