oracle dba -USERS MANAGEMENT

ORACLE DBA - SECURITY MANAGEMENT TYPES

 User
 Privileges and roles
 Profiles
 Auditing
Whenever we install oracle there are two phases in it. 1st oracle software will be
install. Then oracle database will be created.
By default is called as DBA as and DBA is a role not a individual
[tejasri@localhost~]$ sqlplus system/manager
LOGIN TO THE DATABASE
How to connect the database as a DBA user
SQL> set oracle_sid=sample [Windows]
SQL> export ORACLE_SID=sample [Linux]
SQL> sqlplus / as sysdba
How to connect the database as a normal user
SQL> sqlplus username/password@database name
SQL> sqlplus   sekhar /  sekhar @sample
Default create 3 user accounts in oracle
1) user name : sys
Password: change_ON_INSTALL
2) user name : system
Password: manager
3) user name : scott
Password: tiger
1) User name : scott
2) Password : tiger
3) Host name : sample
DBA users
Normal user
Emp, Dept, Salgrade, Bonus

USERS MANAGEMENT
Guide lines for user creation
 User name should always begin with a character.
 A user name can’t exceed 32 characters.
 Special characters are allowed in user name.
Create our own user accounts/schemas/user
 DBA only can create accounts
Syntax: create user <user name> identified by <password>;
SQL> create user chandra  identified by chandra ;
To give permissions to the user
SQL> grant connect, resource to chandra ;
Connect  to connect oracle server and to create objects like tables, views etc.
Resource  to create objects like index, sequence etc.
To create user with grant permissions
SQL> grant connect, resource to  sekharidentified by   sekhar ;
How to change the password
 Both dba & user’s can change the password
Syntax: alter user <user name> identified by <password>;
SQL> alter user   sekhar  identified by   sekhar ; OR
Syntax: password <user name>
SQL> password   sekhar
Old password:
New password:
Conform password:
SQL> show user
SQL> cl scr

How lock the user accounts
Syntax: alter user <user name> account lock;
SQL> alter user chandra  account lock;
Note: when user account is locked it throws a error called ORA-28000
How to unlock the user accounts
Syntax: alter user <user name> account unlock;
SQL> alter user chandra  account unlock;
How to drop the user accounts
 Only DBA can drop the accounts.
Syntax: drop user <user name> [cascade];
SQL> drop user chandra  cascade;
 If user is empty cascade is not required.
 If user is not empty cascade is required.
PROBLEM: User  sekharhas password called welcome. Ravi the dba of the company want
see what is there in user   sekhar . At the same time he don’t want to  sekharsomebody
Hague the password.
SQL> select username, password from dba_users where username=’  sekhar ’;
NOTE: Encrypted password will be displayed. Save this encrypted password in a notepad
Connect as dba(system/manager)
Syntax: alter user  sekharidentified by <new password>;
SQL> alter user  sekhar identified by   sekhar_1;
SQL> conn   sekhar /  sekhar 1
Note: Now you want to put back old password.
Syntax: alter user  sekhar identified by values <’encrypted password’>;
SQL> alter user  sekharidentified by values ‘0D24BB59A35BF303’;
SQL> set pagesize 0
SQL> set linesize 0
SQL> set sqlprompt “&_users”










Grant table from one user to another user
SQL> grant select on emp to chandra ;
SQL> create table emp as select * from scott.emp;
DATA DICTIONARY VIEWS OF USERS
DBA_USERS
ALL_USERS
USER_USERS
DATA DICTIONARY VIEWS OF QUOTAS
DBA_TS_QUOTAS
USER_TS_QUOTAS