Change the password of an user:

Unlock an user account:

Script for giving privilege to user:

Hr user owns this tables.

select owner, object_name, object_type from dba_objects where lower(owner) like 'hr' and lower(object_type) like 'table';

There are 2 approaches for this. The semi automated and fully automated.

The seni automated approach.

select 'grant select, insert on '||owner||'.'||'object_name'||' to agnivo;' from dba_objects where lower(owner) like 'hr' and object_type like 'TABLE';

Then copy and paste all the output in a new sql prompt and they will get executed one by one and will give the privileges to the user.

The above select statement's meaning is this. We can use select like this

Select 'Name', first_name from hr.employees where employee_id = 102;

This will give 2 fields, the first one is Name and the other as the first name of the record with id 102. Lets say Peter.

Select 'Name='||first_name from hr.employees where employee_id = 102;

This will give 1 field with a value something like Name=Peter. The text gets concatinated with the value of first_name name of id 102 (Peter) due to the concatenation operator (||). This is how the above select statement will correct create all the grant statements by itself for execution.

But this is still a semi automated approach as we are having to copy paste ourselves. For that there is an automated approach.

The automated approach.

There is a feature in oracle called spool.

spool /home/oracle/nik_privs select 'grant select, insert on '||owner||'.'||'object_name'||' to agnivo;' from dba_objects where lower(owner) like 'hr' and object_type like 'TABLE';

After the first line with spool whatever we write below it and its output all of it will be redirected to the file we gave (/home/oracle/nik_privs).

Then we can execute it and get the whole process done automated. We can put the select statement and spool in a .sql script and execute it to get the whole process automated and reusable.

Sql Script for granting privileges:

set heading off // The heading is the heading of the output of a select statement the one with the field names. The 1st line turned that off since we only need the outputed grant statements to execute. set feedback off // Feedback is the line we get after the output telling us how many rows got selected. This line turned that off too. set pagesize 100 set echo off spool /home/oracle/nik_privs.sql //The below line will get redirected to the file given in the spool.During execution of the sql script the select statement will get executed and the generated grant statements will redirected to the file.

select 'grant select, insert on '||owner||'.'||'object_name'||' to agnivo;' from dba_objects where lower(owner) like 'hr' and object_type like 'TABLE'; //The main select statement which will create the grant statements.

spool off //This has turned of the spool so that below lines will go not go inside the file. @/home/oracle/nik_privs.sql //Then we execute the file which will execute the grant statements inside the file. set heading on set feedback on //We again turn on the heading and feedback which we turned off the purpose of the script execution. set echo on

Grant scripts object privilege:

Grant script system privilege:

set heading off set feedback off set echo off spool /home/oracle/grant_revoke_temp.sql select 'grant '||privilege||' to &user;' from dba_sys_privs where lower(grantee) like '&another_user'; spool off @/home/oracle/grant_revoke_temp.sql set heading on set feedback on `

Grant script for both system and object privilege together:

Since we can't write single grant statement for both system and object privilege we just have to write 2 grant statements in a single script for this.

set heading off set feedback off set echo off spool /home/oracle/grant_revoke_temp.sql select 'grant '||privilege||' to &user;' from dba_sys_privs where lower(grantee) like '&another_user_for_system_privilege'; spool off @/home/oracle/grant_revoke_temp.sql

spool /home/oracle/grant_revoke_temp.sql select 'grant insert, select, update, delete on '||owner||'.'||object_name||' to &user;' from dba_objects where lower(owner) like '&owner_for_object_privilege' and lower(object_type) like 'table'; spool off @/home/oracle/grant_revoke_temp.sql set heading on set feedback on

            `

Revoke script for both system and object privilege:

set heading off set feedback off set echo off spool /home/oracle/grant_revoke_temp.sql select 'revoke '||privilege||' on '||owner||'.'||table_name||' from &user_revoke_object_privileges;' from dba_tab_privs where lower(grantee) like '&user'; spool off @/home/oracle/grant_revoke_temp.sql

spool /home/oracle/grant_revoke_temp.sql select 'revoke '||privilege||' from &user;' from dba_sys_privs where lower(grantee) like '&user_revoke_system_privileges'; spool off @/home/oracle/grant_revoke_temp.sql set heading on set feedback on

---

Role

Now in an industry there are millions of users so of course we can't always run the grant statements or even the grant or revoke script everytime. For that there is a concept called role.

Like for example sysdba is a role. We can create roles like we can create users

Creating a role:

Create role newbie_hr;

Granting the role privileges:

We can then grant privileges both system and object to a role.

The process to granting privileges is same for both users and roles.

grant select, insert on hr.employees to rolename;

grant create session to newbie_hr;

Lets give the role newbie hr just the select privileges to the hr tables as its a role for newbies. We can the run the grant_select.sql script which we created just for granting select privilege.

View for roles:

desc role_tab_privs

This view shows the details of each role.

The after that we can grant the role to an user which in turn will that user all the privileges associated with that role.

grant rolename to agnivo;

Like at first user agnivo has no privileges not even for looging in. After granting the role the user agnivo automatically get those privileges associated with that role.

We can assisgn one role to another role.

Droping a role:

Drop role newbie_hr;