Oracle ---Controlling User Access

1.The DBA creates the user by executing the CREATE USER statement. The user does not have any privileges at this point. The DBA can then grant a number of privileges to that user. These privileges determine what the user can do at the database level.

SQL> CREATE USER  scott
  2  IDENTIFIED BY tiger;
User created.

2.The DBA can grant a user specific system privileges.

SQL> GRANT  create table, create sequence, create view
  2  TO     scott;
Grant succeeded.

3.Creating and Granting Privileges to a Role
SQL> CREATE ROLE manager;
Role created.
SQL> GRANT create table, create view    
  2       to manager;
Grant succeeded.
SQL> GRANT manager to BLAKE, CLARK;    
Grant succeeded.

4.Change password
SQL> ALTER USER scott                 
  2       IDENTIFIED BY lion;
User altered.

5.Grant object privilege
SQL> GRANT select
  2  ON emp
  3  TO sue, rich;
Grant succeeded.
SQL> GRANT update (dname, loc)
  2  ON dept
  3  TO scott, manager

6.Give a user authority to pass along the privileges.
SQL> GRANT select, insert
  2  ON dept
  3  TO scott
  4  WITH GRANT OPTION;
Grant succeeded.

7.Allow all users on the system to query data from Alice’s DEPT table.

SQL> GRANT select
  2  ON alice.dept
  3  TO PUBLIC;
Grant succeeded.

8.Revoke object privilege
SQL> REVOKE select, insert
  2  ON dept
  3  FROM scott;