Oracle PL/SQL Tutorial/User Privilege/System Privileges

Grant a privilege to all users by granting to PUBLIC

GRANT EXECUTE ANY PROCEDURE TO PUBLIC;

Granting System Privileges to a User

GRANT CREATE SESSION, CREATE USER, CREATE TABLE TO yourUserName;

Revoke system privileges from a user using REVOKE

REVOKE CREATE TABLE FROM userName;
REVOKE EXECUTE ANY PROCEDURE FROM userName;

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

GRANT EXECUTE ANY PROCEDURE TO userName;

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

GRANT EXECUTE ANY PROCEDURE TO yourUserName WITH ADMIN OPTION;