USER MANAGEMENT IN ORACLE

USER  MANAGEMENT IN ORACLE

Introduction:

In this blog i am going to expalin USER MANAGEMENT IN ORACLE,User is basically used to connect a database  it may be production, pre-production,development or testing server (UAT) but users  depends on  what the role and designation it should be manager, admin or programmer,test user 

all database objects like Table,index,view,etc can be created under the user,in oracle users,and schemas are important you can consider that user is the account connect to database ,and schema is the set of objects (tables,vies,indexes).

USER MANAGEMENT IN ORACLE that belong to the account any user minimum privelige is necessary to connect a database it should be like admin priviles   

User Management  - SQL

Create user ( creating the user in database)

alter user  (alter the user in database)

drop user   (drop the user in database)

sql>create user chaitanya identified by chaitu123;

Already exists/list of user / got created.

Using dictionary view/table owned sys user.

Information from dba_users

sql>desc dba_users;

sql>select username,password,account_status from dba_users where username like 'CHAITANYA';

sql>select username,password,account_status from dba_users where username in ('CHAITANYA','SYS','SCOTT');

sql>select distinct account_status from dba_users;

Locked

Expired & Locked

Open

Account Lock/Unlock/reset

sql>alter user Chaitanya account lock;

sql>select username,account_status from dba_users where username like 'CHAITANYA';

sql>alter user chaitanya account unlock;

sql>alter user chaitanya identified by chaitu123;

Dropping user

sql>drop user chaitanya;

Creating user :

sql>create user abhiram identified by abhi123;

sys owned dictionary tables, can find the information of user details.

Note : dba_users

sql>select username,account_status from dba_users where username in ('CHAITANYA','MANASA','ABHIRAM','SAIBABU','PAVAN','VASANTH','SYS','SYSTEM');

sql>select username,password from dba_users;

Finding number of users in DB

sql>select count(*) from dba_users;

Altering user 

Account lock/unlock

sql>select username,account_status from dba_users where username like 'CHAITANYA';

sql>alter user chaitanya account lock;

sql>alter user chaitanya account unlock;

Password reset / Changing

sql>alter user Manasa identified by manu123;

Drop user - removing user from db

sql>drop user abhiram;

   

    note: including dependencies , his objects to remove.

 

      If its a schemas, then use cascade.

sql>drop user abhiram cascade;

    In case, scott user

 

    sql>drop user scott cascade;

Roles :

Roles are groupings or collection of privileges that you can use to create different levels of database access.Defining subset of privileges through a role to a user.it allows easier management of privileges in USER MANAGEMENT IN ORACLE

We can also grant role to a role and to a user.

• manager
• dba
• developer
• tester
• privilege - > user
• privilege -> role - > user
• privileges - > role - > role - > user
• 
  
• 
  
•         dba_roles
•         dba_role_privs
•         role_role_privs
•         dba_sys_privs

sql>select role from dba_roles;

ROLE

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

CONNECT

RESOURCE

DBA

SELECT_CATALOG_ROLE

EXECUTE_CATALOG_ROLE

DELETE_CATALOG_ROLE

EXP_FULL_DATABASE

IMP_FULL_DATABASE

Creating role:

sql>create role dev_role;

sql>create role test_role;

sql>select role from dba_roles where role like 'DEV_ROLE';

How to grant role to a user?

sql>grant dev_role to scott;

Connect role :

Connect : is a system defined role with create session privilege.

    Every new user needs create session privilege.That granted with role connect.

Privilege(create session) ---> user (abhiram) 

SQL>grant create session to dev_role;

Privilege(create session) -> connect(role) -> user (abhiram)

privileges - > role(connect) - > role(dev) -> user

dev(role) -> user will get including connect role 

sql>grant connect to chaitanya;

sql>connect chaitanya/chaitu123;

Resource role : built in system defined role

    resource role has create objects privileges.

    create table,create index,create trigger

SQL> select privilege from dba_sys_privs where grantee like 'RESOURCE';

SQL> select privilege from dba_sys_privs where grantee like 'CONNECT';

SQL> select privilege from dba_sys_privs where grantee like 'DBA';

SQL> select privilege from dba_sys_privs where grantee like 'DEV_ROLE';

sql> grant dba to scott;

Now scott has DBA role, have all privileges.

sql>grant resource to abhiram;

  role - role ----> user

  privilege ---> role ----> user

  privilege ---> user

  privilege ---> role ---> role ---> user

  create session ---> connect,resource ---> dev_role---> abhiram

  create user,drop user---> dev_role ---> abhiram

DBA Role :

        If user does need all sys privileges to manage database. 

provide DBA role which has all system privileges.

sql>grant dba to chaitanya;

sql>revoke dba from chaitanya;

Instead of giving connect,resource or other roles.we can create and grant with one single role.So we can grant role to role.

sql>grant connect,resource to  dev_role;

sql>grant dev_role to manasa;

So now manasa has create session,create table,trigger,index privileges via dev_role.So for next new user, directly we can grant with one single role.

sql>grant dev_role to vasanth;

What roles granted for a role?

sql>select granted_role from role_role_privs where role like 'DEV';

What roles granted for a user?

SQL> select grantee,granted_role from dba_role_privs where grantee like 'PAVAN';

Dropping a role:

sql>drop role dev_role cascade;

 Note : use cascade, to remove for dependencies

user-----> create/alter/drop

Role ----> create/drop/assign/revoke

Privileges/Permissions

Two Types of Privileges

1. System Level Privileges
2. Object Level Privileges

dba_tab_privs - Object Level Privileges---->(select,insert,update,delete)

sql>desc dba_tab_privs

dba_sys_privs - System Level Privileges---->(shutdown,backups,create user,alter user,create role,drop role,create table,alter table)

sql>desc dba_sys_privs

System Privileges -

SQL> select grantee,privilege from dba_sys_privs where grantee like 'CONNECT';

SQL> select grantee,privilege from dba_sys_privs where grantee like 'RESOURCE';

List of system Privileges

sql>select distinct privilege from dba_sys_privs; 

CREATE USER

SELECT ANY TABLE

CREATE SESSION

CREATE TABLESPACE

CREATE PROFILE

ALTER USER

DROP USER

How to grant /provide system privilege to scott ?

sql>grant create user,drop user,alter user to scott;

SQL> select privilege from dba_sys_privs where grantee like 'SCOTT';

To remove/revoke privileges

sql>revoke drop user from scott;

Privileges--->(Create session,create table..)--->Role(Connect,resource)---> role(devop)---> User(ravi)

sql>create user chaitanya identified by chaitu123;

sql>create role devop;

sql>grant connect,resource,create user,drop user to devop;

sql>grant devop to chaitanya;

sql>create user manasa identified by manu123;

Now grant role devop to manasa(developer)

sql>grant devop to manasa;

sql>revoke drop user from devop;

    but this applicable for all users under devop role.

Object / Table Privileges

sql>desc dba_tab_privs

SQL> select owner,grantee,grantor,privilege from dba_tab_privs where owner like 'SCOTT' and table_name like 'EMP';

sql>grant select,insert,update on scott.emp to hr;

SQL> select grantee,grantor,privilege from dba_tab_privs where owner like 'SCOTT' and table_name like 'EMP';

sql>revoke update  on scott.emp from hr;

sql>connect hr/hr123

sql>select * from scott.emp;

Profiles:

       Profile enforces set of password security rules and resources usage limit while creating a user if no profile is mentioned, then DEFAULT profile will be assigned to the user ,limiting resources for a user defining a profile.USER MANAGEMENT IN ORACLE

sql>desc dba_profiles

       

    system defined---> two profiles

        default

        monitoring_profile

SQL> select resource_name,limit from dba_profiles where profile like 'DEFAULT';

Altering profile:

SQL> alter profile devop limit idle_time 380;

create profile:

sql>create profile devop limit idle_time 180 failed_login_attempts 3;

Assign a profile to a user:

SQL> alter user scott profile devop;

Verify:

SQL> select username,profile from dba_users where username like 'SCOTT';

Drop profile :

sql>drop profile devop;

If user dependency , then use cascade to drop

sql>drop profile devop cascade;

• dba_users - username,paswd,profile,account_status
• dba_roles - roles
• dba_role_privs
• role_role_privs - roles
• dba_sys_privs - system level privs
• dba_tab_privs - object level privs
• dba_profiles - profiles details - default

Here i am giving some tables create it in your database and assign to user and roles in  

USER MANAGEMENT IN ORACLE

Tables

• agents
• customers
• orders

sql > CREATE TABLE  AGENTS("AGENT_CODE" CHAR(6) NOT NULL PRIMARY KEY,AGENT_NAME CHAR(40),WORKING_AREA CHAR(35),COMMISSION NUMBER(10,2),PHONE_NO CHAR(15),COUNTRY VARCHAR2(25));

sql>CREATE TABLE CUSTOMER(CUST_CODE VARCHAR2(6) NOT NULL PRIMARY KEY,CUST_NAME VARCHAR2(40) NOT NULL,CUST_CITY CHAR(35),WORKING_AREA VARCHAR2(35) NOT NULL,CUST_COUNTRY" VARCHAR2(20) NOT NULL, GRADE NUMBER,OPENING_AMT NUMBER(12,2) NOT NULL,RECEIVE_AMT NUMBER(12,2) NOT NULL,PAYMENT_AMT NUMBER(12,2) NOT NULL, OUTSTANDING_AMT NUMBER(12,2) NOT NULL,PHONE_NO" VARCHAR2(17) NOT NULL,AGENT_CODE CHAR(6) NOT NULL REFERENCES AGENTS);

sql>CREATE TABLE ORDERS(ORD_NUM NUMBER(6,0) NOT NULL PRIMARY KEY,ORD_AMOUNT NUMBER(12,2) NOT NULL,ADVANCE_AMOUNT" NUMBER(12,2) NOT NULL,ORD_DATE DATE NOT NULL,CUST_CODE VARCHAR2(6) NOT NULL REFERENCES CUSTOMER,AGENT_CODE CHAR(6) NOT NULL REFERENCES AGENTS,ORD_DESCRIPTION VARCHAR2(60) NOT NULL);

 Inserting values into agent table values

sql>INSERT INTO AGENTS VALUES ('A003', 'vasanth', 'Bangalore', '0.15', '077-25814763', 'india');

sql>INSERT INTO AGENTS VALUES ('A006', 'saibabu ', 'hyderabad', '0.13', '075-12458969', 'india');

sql>INSERT INTO AGENTS VALUES ('A009', 'pavan', 'rajahmundry', '0.12', '044-25874365', 'india');

 Inserting values into customer table values

INSERT INTO CUSTOMER VALUES ('C00012', 'tribhuvan', 'visakhapatnam', 'vizag', 'india', '2', '6000.00', '5000.00', '7000.00', '4000.00', '9898989898', 'A003');

INSERT INTO CUSTOMER VALUES ('C00021', 'vinod', 'rajahmundry', 'rjy', 'india', '2', '3000.00', '5000.00', '2000.00', '6000.00', '7878787878', 'A008');

INSERT INTO CUSTOMER VALUES ('C00027', 'kalyan', 'hyderabad', 'hyd', 'india', '3', '5000.00', '7000.00', '6000.00', '6000.00', '9400940021', 'A008');

 Inserting values into orders table values

INSERT INTO ORDERS VALUES('200102', '1000.00', '600.00', '08/01/2008', 'C00013', 'A003', 'SOD');

INSERT INTO ORDERS VALUES('200110', '3000.00', '500.00', '04/15/2008', 'C00019', 'A006', 'SOD');

INSERT INTO ORDERS VALUES('200107', '4500.00', '900.00', '08/30/2008', 'C00007', 'A009', 'SOD');

Creating user

create user abhiram identified by abhi123;

grant create session to abhiram;

create user manasa identified by manu123;

grant create session to manasa;

Creating Roles &Assigning Roles

create role cust_serv_clerk;

grant select on customer,agents,orders, to cust_serv_clerk;

grant select,insert,update on customer to cust_serv_clerk;

grant insert,update,delete on customer,agents,orders to cust_serv_mgr;

create role cust_serv_mgr;

grant cust_serv_clerk to cust_serv_mgr;

Assiginging Roles to the Particular User 

grant cust_serv_clerk to abhiram

grant cust_serv_mgr to manasa;

create user chaitanya identified by chaitu123

default tablespace users

quota 10m on users

temporary tablespace temp

quota 5m on system

profile application_user

password expire;

Note: Info on USER MANAGEMENT IN ORACLE,This is for the practical purpose how to assign a role and profile and resources to the user to a database it may be differ from your environment

like production,testing,development etc

THANKS FOR VIEWING MY BLOG FOR MORE UPDATES FOLLOW ME AND SUBSCRIBE ME