Dear Readers,
My name is Franz Devantier, creator of this blog. I am an Oracle Certified
Professional (OCP DBA 11g) Security DBA.
I will be sharing with you the basic duties of an Oracle DBA, and also
some of the undocumented, and not so well known tasks.
I will make a deal with you: If you refer me to a company that needs
database support, from a few hours per week to full time, and I am able to sign
a contract with them.
Then I will give you 10% of the monthly
contract or deal price every month. When
the contract ends, and we re-sign the contract, I will again give you 10% of
the monthly contract price. This will go
on until the company no longer employs or contracts me or my agents to look
after their databases.
I can do this, because that 10% is my
marketing budget. When we re-sign the
contract, in the future, it may depend on you giving the thumbs up again, and
that is worth 10% of the monthly contract price, to be given to you as
commission.
Contact:
Franz
Security,
Privileges, Roles, Profiles and Resource Limitations - Part
6
Privileges Required to Create Types and Tables Using Types
There are a few requirements that must be met, by a user in
order to create types.
·
You must have the
CREATE TYPE system privilege to be able to create a type I your own
schema. You need the CREATE ANY TYPE
system privilege to create a type in another schema. You can acquire these privileges either
explicitly or through a role.
·
The owner of the type
must be explicitly granted the EXECUTE object privileges to access all other
types referenced within the definition of the type. Or have been explicitly granted the EXECUTE
ANY TYPE system privilege. These
privileges can’t be granted through a role.
·
In order for the Type
owner to grant access to the types to other users, the Type owner must have received
the EXECUTE privileges to the referenced Types with the GRANT OPTION. Also the EXECUTE ANY TYPE system privilege
with the ADMIN OPTION will enable the type owner to grant access on the Type to
other users. Otherwise the Type owner
has insufficient privileges to grant access on the Type to other users.
There are a few requirements for creating a table using
types. Firstly you will need to meet all
the requirements for creating a table, plus a few additional requirements.
·
The owner of the table
must have been granted explicitly; the EXECUTE object privileges to access all
types referenced by the table.
Alternatively the EXECUTE ANY TYPE system privilege. The privileges must be granted explicitly,
and not through a role.
·
In order for the table
owner to grant access to the table to other users, the owner must have the
EXECUTE privileges to the referenced Types with the GRANT OPTION or the EXECUTE
ANY TYPE system privilege with the ADMIN
OPTION. Otherwise the table owner will
have insufficient privileges to grant access on the type to other users.
Description of Privileges for Creating Types and Tables
Using Types
There are three users with the CONNECT and RESOURCE roles
granted to them. Alan, Bob, and Cliff.
Alan executes this DDL:
SQL> CREATE TYPE typea AS OBJECT(attra NUMBER);
SQL> CREATE TYPE typeb AS OBJECT(attrb NUMBER);
SQL> GRANT EXECUTE ON typea TO bob;
SQL> GRANT EXECUTE ON typeb TO bob WITH GRANT OPTION;
Alan executes this DDL:
SQL> CREATE TABLE taba OF alan.typea;
SQL> CREATE TYPE typec AS OBJECT(attrc alan.typeb);
SQL> CREATE TABLE tabb (cola alan.typeb);
The following statements succeed because bob has EXECUTE
privilege on alan.typeb with the GRANT OPTION:
SQL> GRANT EXECUTE ON typec TO cliff;
SQL> GRANT SELECT on tabb TO cliff;
However, the following grant fails because bob does not
have EXECUTE privilege on alan.typea with the GRANT OPTION:
SQL> GRANT SELECT ON taba TO cliff;
Cliff can now perform the following statements:
SQL> CREATE TYPE typed AS OBJECT (attrd bob.typec);
SQL> CREATE TABLE tabc OF typed;
It is no longer good practice to grant the CONNECT and
RESOURCE roles to a new user. Rather
just grant the CREATE SESSION privilege to a new user, because that is all that
the CONNECT role has in it.
Franz Devantier,
Need a database health check, or a security audit?
devantierf@gmail.com
Classifieds
No comments:
Post a Comment