Transcript 16a

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
 Confirm given and received privileges
within the Oracle data dictionary
 Create synonyms for database objects
Marge Hohly
2
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
3
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
Marge Hohly
4
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.
Marge Hohly
5
System Privileges (Security)
 Below are listed typical privileges provided by the
database administrator.
Marge Hohly
6
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 user’s schema
CREATE SEQUENCE
Create sequences in the user’s
schema
CREATE VIEW
Create a view in the user’s schema
CREATE PROCEDURE
Create a stored procedure,
function, or package in the user’s
schema
Marge Hohly
7
User System Privileges
 Determine what the user can do at
the database level
 GRANT privilege [,privilege….]
TO user [,user|role, PUBLIC…];
 GRANT create session, create table,
create sequence, create view
TO scott;
Marge Hohly
8
Object privileges
 Each object has set of grantable
privileges
1.
2.
3.
4.
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
9
Object privileges
Object
Privilege
Table
ALTER
X
DELETE
X
View
Sequence
Procedure
X
X
EXECUTE
X
INDEX
X
X
INSERT
X
X
REFERENCES
X
SELECT
X
X
UPDATE
X
X
Marge Hohly
X
10
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 object
TO 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_book
TO Scott WITH GRANT OPTION
REVOKE select, insert
ON address_book
FROM scott;
Marge Hohly
11
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
12
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
13
Role characteristics
 Named groups of related privileges
 Granted to users
 Simplify the process of granting and
revoking privileges
 Created by the DBA
Marge Hohly
14
Why Roles are easier?
 How it works.
Marge Hohly
15
Grant Object privileges
 GRANT object_priv[(column_list)]
ON object_name
TO{user|role|PUBLIC}
[WITH GRANT OPTION]
 Be careful using WITH GRANT
OPTION
Marge Hohly
16
Syntax
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
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
19
Grant & Revoke Syntax
 GRANT object_priv[(columns)]
ON object
TO {user|role|PUBLIC}
[WITH GRANT OPTION];
 REVOKE {privilege [, privilege...]|All}
ON object
FROM {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
20
Examples DP.13.3.10

1. GRANT select
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;
(Scott owns d_songs and typed this command)
Jennifer types the following:

4. CREATE SYNONYM songs
FOR scott_king.d_songs;

5. SELECT *
FROM songs;
(songs is the synonym)
Marge Hohly
21
WITH GRANT OPTION
 GRANT select, insert
ON
d_songs
TO
scott_king
WITH 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
22
Pictorial view WITH GRANT OPTION
Marge Hohly
23
PUBLIC keyword
 GRANT select
ON
jason_tsang.d_songs
TO
PUBLIC;
 Owner of a table can grant access to
all users by using keyword PUBLIC
Marge Hohly
24
REVOKE privilege
 REVOKE {privilege [,
privilege...]|ALL}
ON object
FROM {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
25
Revoke privilege
 REVOKE select, insert
ON d_songs
FROM us_1859_SQL01_Sxx;
Marge Hohly
26
View Privileges
 Access the data dictionary to view
privileges you have
 In APEX try to view the privileges of
USER_ROLE_PRIVS
Marge Hohly
27
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 user’s
objects
USER_TAB_PRIVS_RECD
Object privileges granted to the user
USER_COL_PRIVS_MADE Objects 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
Marge Hohly
28
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
29
Example of privileges commands
 SELECT *
FROM role_tab_privs
WHERE role = ‘MANAGER’;
 SELECT *
FROM user_sys_privs;
 SELECT *
FROM user_role_privs;
Marge Hohly
30
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
31
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
32
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
33
META characters
Symbol
Description
*
Matches zero or more occurrences
|
Alteration operator for specifying alternative matches
^/$
[]
{m}
{m.n}
[::]
Matches the start-of-line/end-of-line
Bracket expression for a matching list matching any
one of the expressions represented in the list
Matches exactly m times
Matches at least m times but no more than n times
Specifies a character class and matches any
character in that class
Marge Hohly
34
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 multicharacter element
Marge Hohly
35
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
36
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
37
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
38
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).
Marge Hohly
39
Examples
 Review the examples provided in
iLearning
Marge Hohly
40
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 grep—were the first to popularize the concept of
regular expressions.


Marge Hohly
41
 Read notes
Marge Hohly
42