Basic management of Oracle accounts

1. username and password after Oracle installation is complete
Sys/change_on_install
System/Manager
Scott/Tiger
Sysman/oem_temp
Internal/Oracle
 
2. Change the user's password
SQL> conn sys/change_on_install as sysdba
Connected.
SQL> alter user SYS identified ******;
User altered.
 
3. Unlock the user
SQL> conn Scott/Tiger
Error:
ORA-28000: the account is locked
Warning: you are no longer connected to Oracle.
SQL> conn sys/change_on_install as sysdba
Connected.
SQL> alter user Scott account unlock;
User altered.
 
Locked users:
SQL> alter user Scott account lock;
 
4. View All Users:
Select * From dba_users;
Select * From all_users;
Select * From user_users;

5. View user or role system permissions:
Select * From dba_sys_privs;
Select * From user_sys_privs;

6. View user object permissions:
Select * From dba_tab_privs;
Select * From all_tab_privs;
Select * From user_tab_privs;

7. view all roles:
Select * From dba_roles;

8. View roles owned by a user or role:
Select * From dba_role_privs;
Select * From user_role_privs;
 
9. Create a user
SQL> create user Kevin identified by password
2 default tablespace users
3 temporary tablespace temp
4 quota 10 m on users;
User Created.
SQL> conn Kevin/Password
Error:
The ORA-01045: User Kevin lacks create session privilege; logon denied
SQL> grant create session to Kevin; // authorize the user to connect to the database
Grant succeeded.
 
10. Authorize the user to connect and resource roles
SQL> grant connect to Kevin;
Grant succeeded.
SQL> grant resource to Kevin;
Grant succeeded.
SQL> grant connect, resource to Kevin;
Grant succeeded.
 
11. View connect and resource Permissions
SQL> select * From dba_sys_privs where grantee = 'connection ';
Grantee privilege Adm
-------------------------------------------------------------------------
Connect alter session no
Connect create cluster no
Connect create database link No
Connect create Sequence No
Connect create session no
Connect create synonym No
Connect create table No
Connect create view no

8 rows selected.
 
SQL> select * From dba_sys_privs where grantee = 'resource ';
Grantee privilege Adm
-------------------------------------------------------------------------
Resource create trigger no
Resource create Sequence No
Resource create type no
Resource create Procedure No
Resource create cluster no
Resource create operator no
Resource create indextype No
Resource create table No
8 rows selected.
 
Connect role: -- is the most basic and typical right granted to end users.
Alter session -- modify a session
Create Cluster -- create a cluster
Create database link -- create a database link
Create sequence -- create a sequence
Create session -- create a session
Create synonym -- create a synonym
Create View -- create View

Resource role: -- grant permissions to developers
Create Cluster -- create a cluster
Create procedure -- creation process
Create sequence -- create a sequence
Create Table -- create a table
Create trigger -- create a trigger
Create type -- create type
Create indextype -- create index type
Create operator -- create an operator
 
12. Authorize the user the operation permission on the table
SQL> grant alter any table to Kevin; // authorize Kevin users to access any table
Grant succeeded.
SQL> grant alter, insert, update on Scott. EMP to Kevin; // authorize Kevin to modify, insert, and update table EMP permissions.
 
SQL> grant create session, CREATE TABLE to Kevin with admin option;
Grant succeeded.
SQL> grant alter, insert, update on Scott. EMP to Kevin with grant option;
Grant succeeded.
 
13. Differences between with admin option and with grant option
With admin Option
Enables the grantee to grant the system privilege or role to other users or roles
If Kevin's system privilege is revoked, James's system privilege permission still exists.

With grant option
Enables the grantee to grant the Object Privilege to other users or roles
If Kevin's Object Privilege is revoked, James's system privilege permission is also revoked.
 
14. delete a user:
SQL> drop user Kevin;
User dropped.
 
15. Create and delete roles
SQL> Create role myrole;
Role created.
SQL> drop role myrole;
Role dropped.
 
16. Bind The role to the user
SQL> grant myrole to Kevin;
Grant succeeded.
 
17. Create a profile)
Create a profile that the user can only access three times (wrong password)
Create profile myprofile limit
Sessions_per_user default
Cpu_per_session default
Cpu_per_call default
Connect_time default
Idle_time default
Logical_reads_per_session default
Logical_reads_per_call default
Composite_limit default
Private_sga default
Failed_login_attempts default
Password_life_time default
Password_reuse_time default
Password_reuse_max default
Password_lock_time 3
Password_grace_time default
Password_verify_function NULL;
 
18. Bind the profile to the user
Create user kevin1 identified by T profile myprofile;