Transcript Week 15

Database Programming
Sections 13–Creating, revoking
objects privileges
System vs. Object Privileges
 System Privileges
 The right to access the database and its objects
– create table, create view, back up any table,
etc.
 Generally granted by the DBA
 Object Privileges
 The right to manipulate the content of the
objects database – alter, delete, execute, index,
insert, reference, select, update
 Grant by the owner of the object
Marge Hohly
2
Control of User Access
 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
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
4
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
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 usca_cc_sql01_sxx, where xx is your
number.
Marge Hohly
6
System Privileges
 Below are listed typical privileges provided by the
database administrator.
Marge Hohly
7
Object Privileges







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
8
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.
Marge Hohly
9
Syntax
Marge Hohly
10
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
11
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
12
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
13
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
14
Pictorial view WITH GRANT OPTION
Marge Hohly
15
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
16
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
17
Revoke privilege
 REVOKE select, insert
ON d_songs
FROM usca_cc_sql01_sxx;
Marge Hohly
18
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
19
Viewing privilege in data dictionary
 Types of privilege you can view
Marge Hohly
20
Example of privileges commands
 SELECT *
FROM role_tab_privs
WHERE role = ‘MANAGER’;
 SELECT *
FROM user_sys_privs;
 SELECT *
FROM user_role_privs;
Marge Hohly
21
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
22
Results of previous command
Marge Hohly
23
What is a Role?
 A role is a named group of related
privileges that can be granted to the user.
 Makes it easier to revoke and maintain
privileges.
 A user can have access to several roles,
and several users can be assigned the
same role.
 Roles are typically created for a database
application.
 Syntax:
 CREATE ROLE role;
Marge Hohly
24
Example of a Role
 CREATE ROLE manager;
 Role created.
 GRANT create table TO manager;
 Grant succeeded.
 GRANT manager TO User_name;
 Grant succeeded.
 PRIVILEGES ARE GRANTED TO ROLES
 PEOPLE ARE ASSIGNED ROLES
Marge Hohly
25
Why Roles are easier?
 How it works.
Marge Hohly
26