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?

 

 

One Reply to “Oracle user role and privilege interview questions”

  1. Olà,

    Brilliant article, glad I slogged through the clouddba:-DBA blog on Oracle,Oracle cloud,DevOps,PostgreSQL and Other Databasesit seems that a whole lot of the details really come back to from my past project.

    i creat a view as(see below)
    the problem is that i get dublicat rows and with UNION they are deselected,
    i dont want to use UNION ALL

    so my question is, how can i rewrite the two “select .. ” to a Group by expression, Group by: YEAR, Month
    and get the same result as UNION ALL

    CREAT VIEW v_test_rerytering (…….) AS
    select
    F.ALDER
    age,
    cast(P.AR as VARCHAR2(4)) year,
    ccast((P.MANAD)as VARCHAR2(2)) month,
    cast(K.KON_KOD as VARCHAR2(50)) SEX,
    cast(‘Ext’ as VARCHAR2(50)) R_KOD,
    F.NR_REK
    REK
    from test.Table_anstallda F join test.PERIOD P on P.PERIOD =
    F.PERIOD

    union

    select
    F.ALDER
    age,
    cast(P.AR as VARCHAR2(4)) year,
    ccast((P.MANAD)as VARCHAR2(2)) month,
    cast(K.KON_KOD as VARCHAR2(50)) SEX,
    cast(‘Internal’ as VARCHAR2(50)) R_KOD,
    F.NR_dism
    REK
    from test.Table_anstallda F join test.PERIOD P on P.PERIOD_SN =
    F.PERIOD;

    Please keep providing such valuable information.

    Merci Beaucoup,
    Irene Hynes

Leave a Reply

Your email address will not be published. Required fields are marked *