Oracle PL/SQL Tutorial/User Privilege/System Privileges

Grant a privilege to all users by granting to PUBLIC

   <source lang="sql">

GRANT EXECUTE ANY PROCEDURE TO PUBLIC;</source>

Granting System Privileges to a User

   <source lang="sql">

GRANT CREATE SESSION, CREATE USER, CREATE TABLE TO yourUserName;</source>

Revoke system privileges from a user using REVOKE

   <source lang="sql">

REVOKE CREATE TABLE FROM userName; REVOKE EXECUTE ANY PROCEDURE FROM userName;</source>

System Privileges

A system privilege allows a user to perform certain actions within the database-such as executing DDL statements.

Commonly Used System Privileges are

System Privilege Allows You to CREATE SESSION Connect to a database. CREATE SEQUENCE Create a sequence. CREATE SYNONYM Create a synonym. CREATE TABLE Create a table. CREATE ANY TABLE Create a table in any schema. DROP TABLE Drop a table. DROP ANY TABLE Drop a table from any schema. CREATE PROCEDURE Create a stored procedure. EXECUTE ANY PROCEDURE Execute a procedure in any schema. CREATE USER Create a user. DROP USER Drop a user. CREATE VIEW Create a view.

The EXECUTE ANY PROCEDURE can then be granted to another user

   <source lang="sql">

GRANT EXECUTE ANY PROCEDURE TO userName;</source>

Use WITH ADMIN OPTION to enable a user to grant a privilege to another user

   <source lang="sql">

GRANT EXECUTE ANY PROCEDURE TO yourUserName WITH ADMIN OPTION;</source>