ORACLE USERS, GRANTS, REVOKES AND DROP

Create USER:

create user username identified by password default tablespace users temporary tablespace temp quota 50M on users;

Create ROLE:


CREATE ROLE ROLE_NAME;

Granting Privilege:

GRANT SELECT, UPDATE ON table_name TO role_name;
GRANT SELECT ON table_name TO role_name;

Grant Column Privilege:


GRANT SELECT, UPDATE (ISSUE_BR_ID,DRAWN_BR_ID,AMOUNT_CCY,AMOUNT_LCY,ORGN_BR_ID,RSPD_BR_ID,ADV_NO,ADV_RECEIVED_STATUS,entry_br_id,PAYMENT_STATUS) ON COR_BRM_INFO TO HELP_DESK;
GRANT SELECT, UPDATE (ORGN_BR_ID,RSPD_BR_ID,ADVICE_NO,NARRATION) ON COR_IBT_ORGN_REG TO HELP_DESK;

The following query will help you to generate a set of query of a database user 'ULTIMUS':


select 'GRANT SELECT ON ULTIMUS.' || t.table_name ||
       ' to ZAKIR;'
  from all_tables t
 where t.owner = 'ULTIMUS'
   --and t.table_name like 'TFN_%'
 order by t.table_name

The query can help you to find out the table privilege of any given user or all user:

SELECT t.GRANTEE,
       t.OWNER,
       T.table_name,
       t.GRANTOR,
       t.PRIVILEGE,
       t.GRANTABLE
  FROM DBA_TAB_PRIVS t
 WHERE t.grantee in ('ZAKIR','BACH' )
   and t.privilege='UPDATE' -- SELECT/DELETE etc.
 ORDER BY t.table_name;

The Following query will give a Tree View of ALL the Users along with their assigned ROLE's:

select

  lpad(' ', 2*level) || granted_role "User, his roles and privileges"

from

  (

  /* THE USERS */

    select 

      null     grantee, 

      username granted_role

    from 

      dba_users

    where

      default_tablespace = 'USERS'

  /* THE ROLES TO ROLES RELATIONS */ 

  union

    select 

      grantee,

      granted_role

    from

      dba_role_privs

  /* THE ROLES TO PRIVILEGE RELATIONS */ 

  union

    select

      grantee,

      privilege

    from

      dba_sys_privs

  )

start with grantee is null

connect by grantee = prior granted_role;

REVOKE role from user:

REVOKE role FROM {user, | role, |PUBLIC}
REVOKE ALL FROM {user, | role, |PUBLIC}
REVOKE object_priv [(column1, column2..)] ON [schema.]object FROM {user, | role, |PUBLIC} [CASCADE CONSTRAINTS] [FORCE]

DROP a user:

drop user username cascade;


For More Info About Oracle Users Click Check Here.

-------------------------------------------------------------------------------------------

Happy to Help !!!