Security and Sharing

Three levels of database privileges:

• Connect Privilege: allows users to query and modify tables (if they have authority to).
• Resource Privilege: in addition to connect privileges, allows users to create new tables, indexes, etc.
• DBA Privilege: highest level; grant privileges; create database; drop database; create user-ids; The DBA grants privileges as follows:

    GRANT connect TO smith;
    GRANT resource TO jones;

Table-level privileges:

GRANT [select|insert|delete|update(col-names)] 
ON table_name
TO [user|PUBLIC];

REVOKE [select|insert|delete|update(col-names)|ALL] 
ON table_name
FROM [user|PUBLIC];

Examples:

grant select on customers to public;
grant insert on orders to jones;
grant update(quantity) on products to jones,smith;

revoke all on customers from jones;