SQLPlus Security/Privileges

The SQL statements GRANT and REVOKE, respectively allow /disallow access.

GRANT {role | privilege_list ON object_name} TO user;

ROLE

ADMIN: CREATE SCHEMA, CREATE USER, ALTER USER, DROP USER, DROP SCHEMA, GRANT, and REVOKE
DBA/DDL: All ADMIN privileges, CREATE TABLE, CREATE ANY TABLE, CREATE VIEW, CREATE ANY VIEW, CREATE INDEX, CREATE ANY INDEX, ALTER TABLE, ALTER VIEW, DROP TABLE, DROP VIEW, and DROP INDEX.
RESOURCE: The RESOURCE role grants the same level of control as the DBA/DDL role, but only over the user’s own domain.

Example:

CREATE USER user IDENTIFIED BY PAssWord;
- GRANT ADMIN TO user;
- GRANT INSERT, DELETE ON EMP TO user;
- GRANT ALL ON PRODUCT TO user;

more example:

ALTER USER user IDENTIFIED BY password ;
REVOKE ADMIN FROM user;
REVOKE INSERT,DELETE ON EMP FROM user;
DROP USER user; [CASCADE] Drops all objects associated with the user.
CREATE ROLE role NOT IDENTIFIED; Create role without a password

CREATE ROLE role IDENTIFIED BY password;
SET ROLE role [IDENTIFIED BY password][,...]
ALTER ROLE role IDENTIFIED BY password;
ALTER ROLE role NOT IDENTIFIED;
DROP ROLE role;