7.a . Scripts - sayanbhattacharya1234/Oracle_DBA GitHub Wiki

Scripts

create a new user. grant create session privilege.

HR have what the privileges and view

view ' desc dba_objects "

Q. HR have whatever tables ,those tables select and insert privileges give a new user and view it. write a script.

ans> select 'grant select,insert on '||owner||'.'||object_name||' to nick;' from dba_objects where owner like 'HR' and object_type like 'TABLE';

this is called partly automated script.

after that if we copy this seven lines and paste it. those object privileges granted to nick.

how to view what privileges grant HR to NICK user

command is [ select grantee,owner,table_name,grantor,privilege from dba_tab_privs where grantee like 'NICK' and owner like 'HR' ]

Q. Now HR's those Table or object privilege have nick , remove from nick. write a script.

select 'revoke '||privilege||' on '||owner||'.'||table_name||' from nick;' from dba_tab_privs where grantee like 'NICK';

to off the heading bar command> [ set headind off ]

to off the feedback lable command> [ set feedback off ]

view it

Now Fully automated script process

spool is write something through sql and give a file with path. write something then spool off

go to a new tab

open that nick_privs.sql file and edit it

now execute it