Les 07

7

Controlling User Access

Copyright © 2004, Oracle. All rights reserved.

Objectives After completing this lesson, you should be able to do the following: • Discuss the concepts of users, roles, and privileges • Create users • Create roles • Grant and revoke object privileges • Create and access database links

7-2

Copyright © 2004, Oracle. All rights reserved.

Controlling User Access •

Database security: – System security – Data security

• •

•

Database administrator

System privileges: Access to the database Username Object privileges: Ability and password privileges to manipulate the content of the database objects Schema: A collection of objects, such as tables, views, and sequences Users

7-3

Copyright © 2004, Oracle. All rights reserved.

7-4

Copyright © 2004, Oracle. All rights reserved.

Understanding System Privileges • •

There are more than 100 privileges. The DBA has high-level system privileges, including the right to: – – – –

7-5

Create new users Remove users Remove tables Back up tables

Copyright © 2004, Oracle. All rights reserved.

Creating Users The DBA creates users by using the CREATE USER statement. Syntax: CREATE USER user IDENTIFIED BY password;

Example: CREATE USER scott IDENTIFIED BY tiger; User created.

7-6

Copyright © 2004, Oracle. All rights reserved.

Granting System Privileges to a User • •

After a user is created, the DBA can grant that user specific system privileges. An application developer may have the following system privileges: – – – – –

7-7

CREATE CREATE CREATE CREATE CREATE

SESSION TABLE SEQUENCE VIEW PROCEDURE

Copyright © 2004, Oracle. All rights reserved.

Granting System Privileges Syntax: GRANT { system_privilege | role | ALL PRIVILEGES } [, { system_privilege | role | ALL PRIVILEGES }]... TO { user | role | PUBLIC } [, { user | role | PUBLIC }]... [IDENTIFIED BY password] [WITH ADMIN OPTION]

Example: GRANT create table, create sequence, create view TO scott; Grant succeeded.

7-8

Copyright © 2004, Oracle. All rights reserved.

Understanding Roles

Users

Manager

Privileges Allocating privileges without a role

7-9

Allocating privileges with a role

Copyright © 2004, Oracle. All rights reserved.

Creating a Role •

Create a role:

CREATE ROLE manager; Role created.

•

Grant privileges to a role:

GRANT create table, create view TO manager; Grant succeeded.

•

Grant a role to users:

GRANT manager to BLAKE, CLARK; Grant succeeded.

7-10

Copyright © 2004, Oracle. All rights reserved.

Changing Your Password • •

When the user account is created, a password is initialized. Users can change their passwords by using the ALTER USER statement.

ALTER USER scott IDENTIFIED BY lion; User altered.

7-11

Copyright © 2004, Oracle. All rights reserved.

Understanding Object Privileges Object Privilege

Table

View

ALTER

√

√

DELETE

√

Sequence Procedure

√ √

EXECUTE

7-12

INDEX

√

INSERT

√

REFERENCES

√

SELECT

√

√

UPDATE

√

√

√

√

Copyright © 2004, Oracle. All rights reserved.

Understanding Object Privileges • • •

Object privileges vary from object to object. An owner has all the privileges on the object. An owner can grant specific privileges on the owner’s object to another user. GRANT ON TO [WITH

7-13

{ALL [PRIVILEGES]|object_priv [(columns)]} object {user|role|PUBLIC} GRANT OPTION];

Copyright © 2004, Oracle. All rights reserved.

Granting Object Privileges •

Grant query privileges on the EMPLOYEES table.

GRANT ON TO Grant

•

Grant privileges to update specific columns to users and roles.

GRANT ON TO Grant

•

7-14

select employees sue, rich; succeeded.

update (department_id, location_id) departments scott, manager; succeeded.

To access the objects of other schemas on which you have access privileges, prefix the object name with the schema name followed by a period. Copyright © 2004, Oracle. All rights reserved.

Using the WITH GRANT OPTION and PUBLIC Keywords •

Give a user authority to pass along the privileges.

GRANT select, insert ON departments TO scott WITH GRANT OPTION; Grant succeeded.

•

Allow all users on the system to query data from Alice’s DEPARTMENTS table.

GRANT ON TO Grant

7-15

select alice.departments PUBLIC; succeeded.

Copyright © 2004, Oracle. All rights reserved.

7-16

Copyright © 2004, Oracle. All rights reserved.

Confirming Privileges Granted

7-17

Data Dictionary View

Description

ROLE_SYS_PRIVS

System privileges granted to roles

ROLE_TAB_PRIVS

Table 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

Object 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

System privileges granted to the user

Copyright © 2004, Oracle. All rights reserved.

7-18

Copyright © 2004, Oracle. All rights reserved.

Revoking Object Privileges Syntax: REVOKE {privilege [, privilege...]|ALL} ON object FROM {user[, user...]|role|PUBLIC} [CASCADE CONSTRAINTS];

Example: As user ALICE, revoke the SELECT and INSERT privileges given to user SCOTT on the DEPARTMENTS table. REVOKE ON FROM Revoke 7-19

select, insert departments scott; succeeded. Copyright © 2004, Oracle. All rights reserved.

Understanding Database Links A database link connection allows local users to access data on a remote database. Local

Remote

EMP table

SELECT * FROM emp@HQ_ACME.COM;

7-20

HQ_ACME.COM database

Copyright © 2004, Oracle. All rights reserved.

7-21

Copyright © 2004, Oracle. All rights reserved.

Creating Database Links •

Create the database link.

CREATE PUBLIC DATABASE LINK hq.acme.com USING 'sales'; Database link created.

•

Write SQL statements that use the database link.

SELECT * FROM [email protected];

7-22

Copyright © 2004, Oracle. All rights reserved.

7-23

Copyright © 2004, Oracle. All rights reserved.

Summary In this lesson, you should have learned how to do the following: • Discuss the concepts of users, roles, and privileges • Create users • Create roles • Grant and revoke object privileges • Create and access database links

7-24

Copyright © 2004, Oracle. All rights reserved.

Practice 7: Overview This practice covers the following topics: • Granting other users privileges to your table • Modifying another user’s table through the privileges granted to you • Querying the data dictionary views related to privileges

7-25

Copyright © 2004, Oracle. All rights reserved.

7-26

Copyright © 2004, Oracle. All rights reserved.

7-27

Copyright © 2004, Oracle. All rights reserved.

7-28

Copyright © 2004, Oracle. All rights reserved.