Transcript TO user

(SQL)
Controlling User Access
Asif Sohail
University of the Punjab
Punjab University College of Information Technology (PUCIT)
© 2009 Punjab University College of Information Technology (PUCIT)
September 8, 2009
Slide 1
Objectives
• After completing this lesson, you should be
able to do the following:
– Create users
– Create roles to ease setup and maintenance of
the security model
– Use the GRANT and REVOKE statements to
grant and revoke object privileges
© 2009 Punjab University College of Information Technology (PUCIT)
September 8, 2009
Slide 2
Controlling User Access
Database
administrator
Username and password
privileges
Users
© 2009 Punjab University College of Information Technology (PUCIT)
September 8, 2009
Slide 3
Privileges
– Database security:
• System security
• Data security
– System privileges: Gain access to the database
– Object privileges: Manipulate the content of the
database objects
– More than 80 privileges are available.
– The DBA has high-level system privileges:
• Create new users
• Remove users
• Remove tables
• Back up tables
© 2009 Punjab University College of Information Technology (PUCIT)
September 8, 2009
Slide 4
Creating Users
• The DBA creates users by using the CREATE USER
statement.
CREATE USER
IDENTIFIED BY
user
password;
SQL> CREATE USER scott
2 IDENTIFIED BY tiger;
User created.
© 2009 Punjab University College of Information Technology (PUCIT)
September 8, 2009
Slide 5
Changing Your Password
– The DBA creates your user account and initializes
your password.
– You can change your password by using the ALTER
USER statement.
SQL> ALTER USER scott
2
IDENTIFIED BY lion;
User altered.
© 2009 Punjab University College of Information Technology (PUCIT)
September 8, 2009
Slide 6
User System Privileges
• Once a user is created, the DBA can grant specific
system privileges to a user.
GRANT privilege [, privilege...]
TO user [, user...];
• An application developer may have the following
system privileges:
– CREATE TABLE
– CREATE SEQUENCE
– CREATE VIEW
– CREATE INDEX
– CREATE PROCEDURE
© 2009 Punjab University College of Information Technology (PUCIT)
September 8, 2009
Slide 7
Granting System Privileges
• The DBA can grant a user specific system privileges.
SQL> GRANT create table, create sequence, create view
2 TO
scott;
Grant succeeded.
© 2009 Punjab University College of Information Technology (PUCIT)
September 8, 2009
Slide 8
What Is a Role?
Users
Manager
Privileges
Allocating privileges
without a role
© 2009 Punjab University College of Information Technology (PUCIT)
Allocating privileges
with a role
September 8, 2009
Slide 9
What Is a Role?
•A role is a privilege or set of privileges that allows a
user to perform certain functions in the database.
•To grant a role to a user, use the following syntax:
SQL> GRANT role TO USER
2
[WITH ADMIN OPTION];
Grant succeeded.
• If WITH ADMIN OPTION is used, that user can then
grant roles to other users.
© 2009 Punjab University College of Information Technology (PUCIT)
September 8, 2009
Slide 10
Creating and Granting Privileges to a Role
SQL> CREATE ROLE manager;
Role created.
SQL> GRANT create table, create view
2
to manager;
Grant succeeded.
SQL> GRANT manager to BLAKE, CLARK;
Grant succeeded.
© 2009 Punjab University College of Information Technology (PUCIT)
September 8, 2009
Slide 11
Standard Role Names
•
a)
b)
c)
•
Oracle lets you register as one of three roles:
Connect
Resource
DBA (or database administrator)
These three roles have varying degrees of privileges.
© 2009 Punjab University College of Information Technology (PUCIT)
September 8, 2009
Slide 12
Standard Role Names
a) The Connect Role
• The Connect role can be thought of as the entry-level
role.
• A user who has been granted Connect role access can
be granted various privileges that allow him or her to do
something with a database.
• The Connect role enables the user to select, insert,
update, and delete records from tables belonging to
other users (after the appropriate permissions have been
granted).
© 2009 Punjab University College of Information Technology (PUCIT)
September 8, 2009
Slide 13
Standard Role Names
b) The Resource Role
• The Resource role gives the user more access to Oracle
databases.
• The user can also create tables, views, sequences, and
synonyms.
• In addition to the permissions that can be granted to the
Connect role, Resource roles can also be granted
permission to create procedures, triggers, and indexes.
© 2009 Punjab University College of Information Technology (PUCIT)
September 8, 2009
Slide 14
Standard Role Names
c) The DBA Role
• The DBA role includes all privileges.
• Users with this role are able to do essentially anything
they want to the database system.
© 2009 Punjab University College of Information Technology (PUCIT)
September 8, 2009
Slide 15
Revoking a Role
SQL> REVOKE role FROM user;
SQL> REVOKE connect FROM manager;
SQL> REVOKE create table FROM manager;
© 2009 Punjab University College of Information Technology (PUCIT)
September 8, 2009
Slide 16
Object Privileges
– After you decide which roles to grant your users, your
next step is deciding which permissions or privileges
these users will have on database objects.
– If you actually create an object, you can grant
privileges on that object to other users.
– Object privileges vary from object to object.
– An owner has all the privileges on the object.
GRANT
ON
TO
[WITH GRANT
{object_priv | ALL} [(columns)]
object
{user|role|PUBLIC}
OPTION];
© 2009 Punjab University College of Information Technology (PUCIT)
September 8, 2009
Slide 17
Object Privileges
•Object
Privilege
Table
•ALTER
Ö
•DELETE
Ö
View
Sequence
Procedure
Ö
Ö
•EXECUTE
Ö
•INDEX
Ö
•INSERT
Ö
•REFERENCES
Ö
•SELECT
Ö
Ö
•UPDATE
Ö
Ö
© 2009 Punjab University College of Information Technology (PUCIT)
Ö
September 8, 2009
Ö
Slide 18
Granting Object Privileges
– Grant query privileges on the EMP table.
SQL> GRANT
select
2 ON
emp
3 TO
boota,bala;
Grant succeeded.
– Grant all privileges on the EMP table.
SQL> GRANT
ALL
2 ON
emp
3 TO
boss;
Grant succeeded.
• Grant privileges to update specific columns to users and roles.
SQL> GRANT
update (dname, loc)
2 ON
dept
3 TO
scott, manager;
Grant succeeded.
© 2009 Punjab University College of Information Technology (PUCIT)
September 8, 2009
Slide 19
Using WITH GRANT OPTION and PUBLIC
Keywords
• Give a user authority to pass along the privileges.
SQL> GRANT
select, insert
2 ON
dept
3 TO
scott
4 WITH GRANT OPTION;
Grant succeeded.
– Allow all users on the system to query data
from Alice’s DEPT table.
SQL> GRANT
select
2 ON
alice.dept
3 TO
PUBLIC;
Grant succeeded.
© 2009 Punjab University College of Information Technology (PUCIT)
September 8, 2009
Slide 20
Using the objects of other users
Qualifying the granted object
• Let A is a table name create by user X and X has granted
SELECT privilege on A to a user Y.
• User Y can SELECT rows from table A using:
• SELECT * FROM X.A;
© 2009 Punjab University College of Information Technology (PUCIT)
September 8, 2009
Slide 21
Confirming Privileges Granted
Data Dictionary Table
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
© 2009 Punjab University College of Information Technology (PUCIT)
September 8, 2009
Slide 22
How to Revoke Object Privileges
– You use the REVOKE statement to revoke
privileges granted to other users.
– Privileges granted to others through the WITH
GRANT OPTION will also be revoked.
REVOKE {privilege [, privilege...]|ALL}
ON
object
FROM
{user[, user...]|role|PUBLIC};
© 2009 Punjab University College of Information Technology (PUCIT)
September 8, 2009
Slide 23
Revoking Object Privileges
• As user Alice, revoke the SELECT and INSERT
privileges given to user Scott on the DEPT table.
SQL> REVOKE select, insert
2 ON
dept
3 FROM
scott;
Revoke succeeded.
© 2009 Punjab University College of Information Technology (PUCIT)
September 8, 2009
Slide 24
Summary
•Statement
Action
•CREATE USER
Allows the DBA to create a user
•GRANT
Allows the user to give other users
privileges to access the user’s
objects
•CREATE ROLE
Allows the DBA to create a collection
of privileges
•ALTER USER
Allows users to change their
password
•REVOKE
Removes privileges on an object from
users
© 2009 Punjab University College of Information Technology (PUCIT)
September 8, 2009
Slide 25
Practice Overview
– Granting other users privileges to your table
– Modifying another user’s table through the privileges
granted to you
– Creating a synonym
– Querying the data dictionary views related to
privileges
© 2009 Punjab University College of Information Technology (PUCIT)
September 8, 2009
Slide 26
Thank you for your attention.
Asif Sohail
Assistant Professor
University of the Punjab
Punjab University College of Information Technology (PUCIT)
Allama Iqbal (Old) Campus, Anarkali
Lahore, Pakistan
Tel:
+92-(0)42-111-923-923 Ext. 154
E-mail:
[email protected]
© 2009 Punjab University College of Information Technology (PUCIT)
September 8, 2009
Slide 27