"It is the mark of
an educated mind to be able to entertain a thought without
accepting it." Aristotle (384 BC - 322 BC) |
Read
first then play the video:
PLS-VIDEO -Granting
Objects Privileges
Section 3: Dependency
Granting Object privileges
Hands-On introduction
In this Hands-On, you will
open three sessions. You login as iself/schooling in the first
session, login as system/manager in the second session, and login to
the third session as a newuser/newpass that is going to be created by
system/manager (dba).
Login "SQLPLUS" as
"system/manager"
Create User Default
Tablespace
Create a username "newuser"
with password "newpass". And assign its default and
temporary tablespaces.
SQL> CREATE USER newuser
IDENTIFIED BY newpass
DEFAULT TABLESPACE users
TEMPORARY TABLESPACE temp;
GRANT RESOURCE "
Once you create an object
in the Oracle database, it can be administered by either you or a user
who has granted any privilege. The object privileges will allow users
to manipulate the object by adding, changing, removing, or viewing
data plus the ALTER, REFERENCES, and EXECUTE privileges in the
database object.
On the other hand, System
privileges control the altering, dropping, and creating of all
database objects, such as rollback segments, synonyms, tables, and
triggers.
Grant resource and connect
roles to it. So, the newuser can login to the database and create its
own objects.
SQL> GRANT resource,
connect TO newuser;
Note that the CONNECT role
contains the following system privileges:
ALTER SESSION, CREATE
CLUSTER, CREATE DATABASE LINK, CREATE SEQUENCE, CREATE SESSION, CREATE
SYNONYM, CREATE TABLE, and CREATE VIEW.
The RESOURCE role contains
the following system privileges:
CREATE CLUSTER, CREATE
INDEXTYPE, CREATE OPERATOR,
CRREATE PROCEDURE, CREATE SEQUENCE, CREATE
TABLE, CREATE TRIGGER, and CREATE TYPE.
Go to the "NEXT"
window.
Login to "sqlplus" as
"newuser" password "newpass"
Query the customers
information under the iself schema.
Notice that no access to
the table was granted to the user, yet.
SQL> SELECT * FROM
iself.customer;
Query the customers table
as a public table.
SQL> SELECT * FROM
customer;
No access.
Go to the third window.
Login to sqlplus as "iself/schooling."
GRANT SELECT ON "
Grant select on the
customers table to the newuser.
SQL> GRANT SELECT ON
customer TO newuser;
The newuser should not be
able to update, insert or delete a record.
Go to the "newuser"
session; and query iself's customers table.
SQL> SELECT * FROM
iself.customer;
No problem to query.
We know that the newuser
has no objects.
Check to see the customers
table is a public table?
CREATE PUBLIC SYNONYM "
Go to the
"system" session; and create a public synonym for the iself
table.
SQL> CREATE PUBLIC
SYNONYM customer FOR iself.customer;
Go to the "Newuser"
session; and query iself's customers table using the public synonym.
SQL> SELECT * FROM
customer;
No problem this time.
GRANT EXECUTE ON "
Go back to the "iself"
session; and grant execute privilege on the "dept_name"
function to the "newuser".
SQL> GRANT EXECUTE ON
dept_name TO newuser;
Notice that the iself user
does not grant select privilege on the department table to the "newuser."
Grant the select privilege
on the employee table to the "newuser".
SQL> GRANT SELECT ON emp
TO newuser;
Go to the
"system" session and create a public synonym for those two
objects.
SQL> CREATE PUBLIC
SYNONYM dept_name FOR iself.dept_name;
SQL> CREATE PUBLIC
SYNONYM emp FOR iself.emp;
Go to the newuser session;
and query against the department table.
SQL> SELECT * FROM dept;
Notice that the access
privilege to the department table was not granted to the "newuser."
Use the "department name"
function to query the department name for the department number 10.
SQL> SELECT
dept_name(10) as DEPARTMENT_NAME
FROM dual;
Use the "column"
command to change the DEPARTMENT_NAME output format length to 20
characters.
SQL> COL department_name
FORMAT a20
Use the "department name"
function to query the department name against the employee table.
SQL> SELECT
dept_name(deptno) as department_name
FROM customer
GROUP BY department_name;
Notice that although you
did not join these two table but still you can get the department
name.
REVOKE SELECT ON "
Go to the iself session;
and revoke the select privilege on the employee table from the "newuser"
SQL> REVOKE SELECT ON
emp FROM newuser;
Then go back to the newuser
session; and query against the employee table.
SQL> SELECT * FROM emp;
No access.
Query the customer last
names of the customers table.
SQL> SELECT last_name
FROM customers;
You should have access to
that table.
Then, try to delete the
entire customers table.
SQL> DELETE FROM
customers;
No delete privilege was
granted.
"What's money? A
man is a success if he gets up in the morning and goes to bed at
night and in between does what he wants to do." Bob Dylan
(1941) |
Questions:
Q: What is an Object
Privilege?
Q: What are System
Privileges?
Q: How do you create a user
in the Oracle database?
Q: How do you assign a
default and temporary tablespace to a user in the Oracle database?
Q: What are the System
Privileges in the RESOURCE and CONNECT roles?
Q: How do you grant an
object privilege to a user?
Q: How do you grant a
system privilege to a user?
Q: What is the Public
Synonym in the Oracle database?
Q: How do you create a
PUBLIC SYNONYM?
Q: Why do you need a PUBLIC
SYNONYM?
Q: What is the EXECUTE
privilege? Is it a system privilege or an object privilege?
Q: Can you grant the
EXECUTE privilege to a table?
Q: What is the Private
Synonym in the Oracle database?
Q: What are the differences
between a private synonym and public synonym?
Q: How do you revoke a
system privilege from an Oracle user?
Q: How do you revoke an
object privilege from an Oracle user?
Q: Mr. A granted to Mr. B
an object privilege with a "WITH GRANT OPTION" and then Mr. B
granted the same privilege to Mr. C. You decide to revoke the Mr. B"s
object privilege. What would be happen to Mr. C"s granted object
privilege?
Q: Mr. A granted to Mr. B a
system privilege with a "WITH ADMIN OPTION" and then Mr. B granted
the same privilege to Mr. C. You decide to revoke the Mr. B"s system
privilege. What would be happen to Mr. C"s granted system privilege?
Q: How do you know that a
privilege is the system privilege or object privilege?
Q: On the GRANT ALL
statement, what ALL means if your grant is on a PL/SQL procedure?
|