Scribd
Upload
6 views16 pages

Controlling User Access

The document outlines the management of user access and privileges in a database, detailing system and object privileges, user creation, and the granting and revoking of these privileges. It explains the creation of roles and how privileges can be assigned to users and roles, as well as how to change passwords. Additionally, it includes information on data dictionaries that provide insights into user privileges and roles within the database system.

Uploaded by

mohamedaniyal
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
6 views16 pages

Controlling User Access

The document outlines the management of user access and privileges in a database, detailing system and object privileges, user creation, and the granting and revoking of these privileges. It explains the creation of roles and how privileges can be assigned to users and roles, as well as how to change passwords. Additionally, it includes information on data dictionaries that provide insights into user privileges and roles within the database system.

Uploaded by

mohamedaniyal
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd

Controlling User Access

Privileges
• Database security:
– System security
– Data security
• System privileges: Gaining access to the database
• Object privileges: Manipulating the content of the
database objects
• Schemas: Collections of objects, such as tables, views,
and sequences
Creating Users
The DBA creates users by using the CREATE USER
statement.

CREATE USER user


IDENTIFIED BY password;
User System Privileges
• Once a user is created, the DBA can grant specific
system privileges to a user.
GRANT privilege [, privilege...]
TO user [, user| role, PUBLIC...];

• An application developer, for example, may have


the following system privileges:
– CREATE SESSION
– CREATE TABLE
– CREATE VIEW
– CREATE PROCEDURE
Granting System Privileges
The DBA can grant a user specific system privileges.

CREATE USER scott


IDENTIFIED BY tiger;

GRANT create session, create table, create view


TO scott;
What is a Role?
Creating and Granting Role
• Create a role
CREATE ROLE manager;

• Grant privileges to a role


GRANT create table, create view
TO manager;

• Grant a role to users


GRANT manager TO DEHAAN, SMITH;
Changing Your Password
• The DBA creates your user account and initializes your
password.
• You can change your password by using the ALTER
USER statement.
ALTER USER scott
IDENTIFIED BY lion;
Object Privileges
• Object privileges vary from object to object.
• An owner has all the privileges on the object.
• An owner can give specific privileges on that owner’s
object.

GRANT object_priv [(columns)]


ON object
TO {user|role|PUBLIC}
Granting Object Privileges
• Grant query privileges on the EMPLOYEES table.
GRANT select ON employees
TO sue, rich;

• Grant privileges to update specific columns to


users and roles.
GRANT update (department_name, location_id)
ON departments
TO scott, manager;
Using the WITH GRANT OPTION and
PUBLIC Keywords

• Give a user authority to pass along privileges.


GRANT select, insert
ON departments TO scott
WITH GRANT OPTION;

• Allow all users on the system to query data from


Alice’s DEPARTMENTS table.
GRANT select
ON [Link]
TO PUBLIC;
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 clause are also revoked.
REVOKE {privilege [, privilege...]|ALL}
ON object
FROM {user[, user...]|role|PUBLIC};
Revoking Object Privileges
As user Alice, revoke the SELECT and INSERT
privileges given to user Scott on the DEPARTMENTS
table.
REVOKE select, insert
ON departments
FROM scott;
Data Dictionary
USER_TAB_PRIVS – Shows object-level privileges
granted to the current user.

SELECT TABLE_NAME, PRIVILEGE, GRANTOR,

GRANTEE FROM USER_TAB_PRIVS;


Data Dictionary
USER_ROLE_PRIVS – Lists the roles granted to the
current user.

SELECT USERNAME, GRANTED_ROLE FROM

USER_ROLE_PRIVS;
Data Dictionary
USER_SYS_PRIVS – Displays system privileges assigned
to the user.

SELECT USERNAME, PRIVILEGE FROM

USER_SYS_PRIVS;

You might also like