Oracle user role and privilege interview questions

 Answer Credit:-

//www.linkedin.com/in/skantali/

//www.linkedin.com/in/hemant-shahi-59921762/

• How we can execute PLSQL without any privilege to change it

• What are the default user created when Oracle is installed ?

• Can you create password less user in Oracle ? If yes how ?

• How we can see the procedure /package body with out having privilege to execute it

• How we can create password file in ASM

• What is the difference between a user and schema?Ans:-A user will not have any of his own objects and will always access others objects. Schema is a collection of objects.

• What is the disadvantage of resource role?Ans:-It will override the quota granted for a user and makes it unlimited.

• How to create user and grant the permission in a single command?Ans:-grant create session to user_a identified by <password>;

• You got a requirement to run a script as user “SCOTT” and you don’t have the password of SCOTT. How you will take next step?Ans:-a. We will ask application support. In case they cannot be reached, we can take ASCII password stored in dba_users and change the password. Once work is done we can change the password back using ASCII that we stored earlier.

          b. Oracle proxy user – //oracle-help.com/oracle-database/oracle-proxy-users/

Test Case:-
SYS user does not know the password of a schema called ‘New_hemant’ but without asking him, SYS can perform any operation inside the schema with that user’s access.
Here the user is – “NEW_HEMANT” with the password “password”

SQL> conn new_hemant/password
Connected.

SQL> sho user;

USER is “NEW_HEMANT”

SQL> create table data_drop as select * from data_t;
Table created.

SQL> select * from data_drop;
EMP_ID NAME SALARY
———- ——————————————–
1 mr. a 4561234
2 mr. b 3451930
3 mr. c 2030300
4 mr. d 2130380
5 mr. e 2131380
6 mr. f 2131380
6 mr. g 3131380
6 mr. h 3134380
6 mr. i 3134380
7 mr. l 9134380
1 mr. a 4561234
2 mr. b 3451930
3 mr. c 2030300
4 mr. d 2130380
14 rows selected.

SQL> conn / as sysdba
Connected.

SQL> sho user;
USER is “SYS”

SQL> select owner,table_name from dba_tables where table_name=’DATA_DROP’;
OWNER TABLE_NAME
—————————— ——————————
NEW_HEMANT DATA_DROP

SQL> drop table data_drop;
drop table data_drop
*
ERROR at line 1:
ORA-00942: table or view does not exist
// sys cannot see this table without having privilege on this as the owner new_hemant user did not grant privilege to anyone.
// but sys wants to do something so that this drop should look like from owner.

SQL> select username,password from dba_users where username=’NEW_HEMANT’;
USERNAME PASSWORD
—————————— ——————————
NEW_HEMANT

//oops ! nothing .
// this is a security feature from 11g onwards, the data dictionary view DBA_USERS no more shows encrypted passwords of any user.
// rather 11g stores it in another data dictionary called USER$ and not in the password column but in spare4 column.

SQL> select name,password,spare4 from sys.user$ where name=’NEW_HEMANT’;
NAME PASSWORD SPARE4
—————————— ——————————————————————————————————————-
NEW_HEMANT 95110033565548B2 S:E727ECB1B6CD195798730F93DC0ECD55E934825812C9F2AA1013580F8A8D

SQL> alter user new_hemant identified by temp_password;
User altered.

SQL> conn new_hemant/temp_password
Connected.

SQL> drop table data_drop;
Table dropped.

// SYS user changed the password of new_hemant , logged in through his ID and dropped the table.
// now time to revert the password , failing to do so would confirm the user that his password was changed
.
// so let’s revert the password.

SQL> conn new_hemant/password
ERROR:
ORA-01017: invalid username/password; logon denied
//old password is not working now.

SQL> conn / as sysdba
Connected.

SQL> sho user;
USER is “SYS”

SQL> select name,password,spare4 from sys.user$ where name=’NEW_HEMANT’;
NAME PASSWORD SPARE4
—————————— ——————————————————————————————————————-
NEW_HEMANT 95110033565548B2 S:E323ECD1D6CD105398339F93DC9ECD55E934825812C9F2AA1013589F8A8D

// you can see there is no change in password column even though we have changed the password , so it does not depend on password column or any combination of it with SPARE4 but solely on spare4 column.
// now keep the old value of spare4 column intact and see the magic.

SQL> alter user new_hemant identified by values ‘S:E727ECB1B6CD195798730F93DC0ECD55E934825812C9F2AA1013580F8A8D’;
User altered.
SQL> conn new_hemant/temp_password
ERROR:
ORA-01017: invalid username/password; logon denied
SQL> conn new_hemant/password
Connected.
SQL> exit;

// old password is set back and that user does not even know what happened

• What are SNIPPED sessions?Ans:-Those are sessions terminated from database because of crossing IDLE_TIME limit. But disadvantage is even though oracle level sessions are cleared, at OS level processes willstill occupy resources which is a burden to the server.

• Which grant is needed to select all dictionary views?Ans:-select any dictionary
  select_catalog_role

• Why should we not grant resource to any user?

• How we can create read only user

• Difference between Sys, System  user.

• Can you create user without specifying default tablespce ?

• Can you install Oracle database without OS user Oracle ?

• What is OS authentication

• Can I install oracle without oracle OS user?

• Why we shouldnt grant privilges directly to users?

• User creation without default tablespace

• How audit information can be reflected to syslog?

• Can you restrict user sessions in oracle ?

• How to restrict a user in oracle ?

• How to check which users are granted with sysdba role ?

• How new auditing for roles/privileges changed in 12c from earlier version ?

• What is ldap authentication and external directories authentication in oracle?

• How can you centralize user security in Oracle?

• How will you create the user?

• What are the pre check creating a user? Also post creation?

•  How to check user an schema size?

• How can we restrict one IP to connect to database?

• what is invoker right in plsql?

• Is it a good practice to grant DBA roles to GGuser?