|
Granting Object privileges
Hands-On
introduction
In this
Hands-On, you will open three sessions. You
login as oracle/learning 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 oracle
schema.
Notice that no
access to the table was granted to the user,
yet.
SQL> SELECT * FROM oracle.customer;
Query the
customers table as a public table.
SQL> SELECT * FROM customer;
No access.
Go to the
third window.
Login to sqlplus as "oracle/learning.�
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 oracle's customers table.
SQL> SELECT * FROM oracle.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 oracle table.
SQL> CREATE PUBLIC SYNONYM customer FOR
oracle.customer;
Go to the "Newuser"
session; and query oracle's customers table
using the public synonym.
SQL> SELECT * FROM customer;
No problem
this time.
GRANT EXECUTE
ON �
Go back to the
"oracle" session; and grant execute
privilege on the "dept_name" function to the
"newuser".
SQL> GRANT EXECUTE ON dept_name TO newuser;
Notice that
the oracle 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
oracle.dept_name;
SQL> CREATE
PUBLIC SYNONYM emp FOR oracle.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
oracle 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.
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? |
