Transcript revoking object privileges

Creating and Revoking
Object Privileges
What Will I Learn?
• Explain what a ROLE is and what its advantages
are.
• Construct a statement to create a ROLE and
GRANT privileges to it
• Construct a GRANT .. ON .. TO.. WITH GRANT
OPTION statement to assign privileges to
objects in their schema to other users and/or
PUBLIC
• Construct and execute a statement to REVOKE
object privileges from other users and/or from
PUBLIC
• Explain the purpose of a database link
home back first prev next last
2
Why Learn It?
• If you share a computer with others, whether at school or
at home, you've probably had something you're working
on or something you've saved either viewed, changed or
deleted by someone else.
• Wouldn't it be nice to be able to control the privileges
others have to your personal files?
– For databases, data security is very important.
• In this lesson, you will learn how to
grant or take away access to database
objects as a means to control
who can alter, delete, update, insert,
index, or reference the database objects.
home back first prev next last
3
ROLES
• A role is a named group of related
privileges that can be granted to
the user.
– This method 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.
– To create and assign a role, first
the DBA must create the role.
– Then the DBA can assign
privileges to the role and the role
to users.
home back first prev next last
4
ROLES
• Use the following syntax to create a role:
–
–
–
–
–
CREATE ROLE role_name;
After the role is created, the DBA can use the GRANT statement
to assign the role to users as well as assign privileges to the role.
The example shown creates a manager role and then allows
managers to create tables and views. It then grants the role to a
user.
Now the user can create tables and views.
If users have multiple roles granted to them, they receive all of
the privileges associated with all of the roles.
Note: The CREATE ROLE is a system privilege that has not been
issued to Academy classrooms.
home back first prev next last
5
CHARACTERISTICS OF ROLES
• They are named groups
of related privileges.
• They can be granted to
users.
• They simplify the process
of granting and revoking
privileges.
• They are created by a
DBA.
权限1
权限2
……
用户1
角色1
用户2
权限n
用户n
权限21
权限22
……
角色2
权限2n
home back first prev next last
6
GRANTING OBJECT PRIVILEGES
• Use the following syntax to grant object
privileges:
GRANT object_priv [(column_list)]
ON object_name
TO {user|role|PUBLIC}
[WITH GRANT OPTION];
home back first prev next last
7
OBJECT PRIVILEGE Guidelines
• 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.
home back first prev next last
8
GRANT Examples
• Scott King (username scott_king) has
created a d_songs table.
– In Example 1 on the right, all users are
granted permission to SELECT from
Scott's d_songs table.
– Example 2 grants UPDATE privileges to
Jennifer and to the manager role on
specific columns in Scott's d_songs table.
– If Jennifer now wants to SELECT data from
Scott's table, the syntax she must use is listed
in Example 3.
– Alternatively, Jennifer could create a synonym
for Scott's table and SELECT from the
synonym. See the syntax in Examples 4 and 5.
– Different object privileges are available for
different types of schema objects. A user
automatically has all object privileges for
schema objects contained in that user’s
schema. A user can grant any object privilege
on any schema object that the user owns to
any other user or role.
home back first prev next last
9
WITH GRANT OPTION
• A privilege that is granted using the WITH
GRANT OPTION clause can be passed on to
other users and roles by the grantee.
• Object privileges granted using the WITH
GRANT OPTION clause are revoked when the
grantor’s privilege is revoked.
• The example below gives user Scott access to
your d_songs table with the privileges to query
the table and add rows to the table.
• The example also allows Scott to give others
these privileges:
GRANT select, insert ON d_songs TO scott_king
WITH GRANT OPTION;
home back first prev next last
10
WITH GRANT OPTION
-- 传递授权是否会收回试验
--ssession sys
GRANT select on scott.emp to zxg WITH GRANT OPTION;
CREATE USER zzz
IDENTIFIED BY zzz;
GRANT connect TO zzz;
--ssession zxg
select * from scott.emp;
GRANT select on scott.emp to zzz WITH GRANT OPTION;
--ssession zzz
select * from scott.emp;
--ssession sys
REVOKE select on scott.emp FROM zxg;
--ssession zzz
-- 失败，说明传递授权也会级联回收
select * from scott.emp;
SYS
ZXG
ZZZ
home back first prev next last
11
THE PUBLIC KEYWORD
• An owner of a table can grant access to all users
by using the PUBLIC keyword.
– The example shown below allows all users on the
system to query data from Jason’s d_songs table:
GRANT select
ON jason_tsang.d_songs
TO PUBLIC;
– If you attempt to perform an unauthorized operation,
the Oracle Server does not permit the operation to
take place. It will return this information “table or view
does not exist”
home back first prev next last
12
Confirm privilege
• You can access the
data dictionary to
view the privileges
that you have.
• The chart at right
describes various
data dictionary
views.
home back first prev next last
13
REVOKING OBJECT PRIVILEGES
• You can remove privileges granted to other users by
using the REVOKE statement.
REVOKE {privilege [, privilege...]|ALL}
ON object
FROM {user[, user...]|role|PUBLIC}
[CASCADE CONSTRAINTS];
• When you use the REVOKE statement, the privileges
that you specify are revoked from the users that you
name and from any other users to whom those privileges
were granted through the WITH GRANT OPTION clause.
• CASCADE CONSTRAINTS is required to remove any
referential integrity constraints made to the object by
means of the REFERENCES privilege.
home back first prev next last
14
CASCADE CONSTRAINTS demo
-- 作为 sys 执行
create table emp(eno number primary key);
grant select,references(eno) on sys.emp to s01;
-- 作为 s02 执行
create table dept(mnrno number
constraint dept_mnrno_fk references sys.emp(eno));
select constraint_name,column_name from
user_cons_columns where table_name='DEPT';
-- 作为 sys 执行
revoke references on sys.emp from s01 cascade constraint;
-- 作为 s02 执行,结果显示 未选定行
select constraint_name,column_name from
user_cons_columns where table_name='DEPT';
home back first prev next last
15
REVOKING OBJECT PRIVILEGES
• The example below revokes SELECT and
INSERT privileges given to user Scott on the
d_songs table.
REVOKE select, insert ON d_songs FROM scott_king;
• If a user is granted a privilege with the WITH
GRANT OPTION clause, that user can also grant
the privilege using the WITH GRANT OPTION
clause.
– This means that a long chain of grantees is
possible, but no circular grants are permitted.
– If the owner revokes a privilege from a user who
granted privileges to other users, the revoke
statement cascades to all privileges granted.
home back first prev next last
16
REVOKING OBJECT PRIVILEGES
• For example,
– if user A grants SELECT privileges on a table to
user B, including the WITH GRANT OPTION clause,
– user B can grant to user C the SELECT privilege
including the WITH GRANT OPTION clause as well.
– Now, user C can grant to user D the SELECT
privilege.
home back first prev next last
17
REVOKING OBJECT PRIVILEGES
• However, if user A revokes privileges from
user B, then those privileges granted to
users C and D are also revoked.
home back first prev next last
18
DATABASE LINKS
• A database link is a pointer that defines a one-way
communication path from one Oracle database to
another Oracle database.
– The link pointer is actually defined as an entry in a data
dictionary table.
– To access the link, you must be connected to the local
database that contains the data dictionary entry.
– A database link connection is “one-way” in the sense that a
client connected to local database A can use a link stored in
database A to access information in remote database B, but
users connected to database B cannot use the same link to
access data in database A.
 CREATE DATABASE LINK – In
Oracle Application Express,
there is no constant connection to
the database therefore this
feature is not available.
home back first prev next last
19
DATABASE LINKS
– If local users on database B want to access data
on database A, they must define a link that is
stored in the data dictionary of database B.
– A database link connection gives local users
access to data on a remote database.
 For this connection to occur, each database in the
distributed system must have a unique global database
name.
 The global database name uniquely identifies a database
server in a distributed system.
– The great advantage of database links is that they
allow a local user access a remote database
without having to be a user on the remote
database.
 The example shows a user scott_king accessing the EMP
table on the remote database with the global name
home back first prev next last 20
HQ.ACME.COM.
DATABASE LINKS
– Typically, the DBA is responsible for creating the
database link.
– The dictionary view USER_DB_LINKS contains
information on links to which a user has access.
– Once the database link is created, you can write
SQL statements against the data in the remote site.
– If a synonym is set up, you can write SQL
statements using the synonym. For example:
CREATE PUBLIC SYNONYM HQ_EMP
FOR [email protected];
SELECT * FROM HQ_EMP;
– You cannot grant privileges on remote
home objects.
back first prev next last
21
Summary
• In this lesson you have learned to:
– Explain what a ROLE is and what its advantages are.
– Construct a statement to create a ROLE and GRANT
privileges to it
– Construct a GRANT .. ON .. TO.. WITH GRANT
OPTION statement to assign privileges to objects in
their schema to other users and/or PUBLIC
– Construct and execute a statement to REVOKE object
privileges from other users and/or from PUBLIC
– Explain the purpose of a database link
home back first prev next last
22