Oracle Study note:users and Basic Security

1. View the Default user account

1 SQL>Select from Dba_users;

2. Lock all users and set their password to expired

1 SQL>select 'alteruser '| | ||  from Dba_users;

3. A locked user can only is accessed by altering the user to an unlocked state

1 SQL>alteruser scott account unlock;

4. As a DBA, you can change the password for a user

1 SQL>alteruser<username>by< New Password>;

5. Run this query to display users, which has been created by another DBA versus those created by oracle.for default users, There should is a record in the default_pwd$ view. So,if a user doesn ' t exist in default_pwd$,then you can assume it's not a DEFAULT account.

1Sql> Select distinctU.username2, Case  whenD.user_name  is NULL  Then' DBA created account '3 Else' Oracle created account '4  fromdba_users u5 , default_pwd$ D6 whereU.username=D.user_name(+);

6. You can check the Dba_users_with_defpwd view for whether any oracle-created user accounts is still to the default P Assword

1 SQL>Select* from Dba_users_with_defpwd;

7. Creating a User with Database authentication

1Sql> Create User user_nameIdentified byPassword2 defaulttablespace users3Temporaty tablespaceTemp4Quote Unlimited onusers;5Sql> Grant CreateSession to user_name; # toMake theUserUseful6Sql> Grant Create Table  to user_name; # toBe able to Createtables.7Sql> Grant Create Table,CreateSession to user_nameIdentified byPassword #you can also UseTheGRANT. . . Identified byStatement to CreateAUser.

8.Creating a User with OS authentication

Oracle strongly recommends that's set the Os_authent_prefix parameter to a null string

1Sql> AlterSystemSetOs_authent_prefix="Scope=SPFile;2Sql> Create User user_nameidentified externally;3Sql> Grant CreateSession to user_name;4$ sqlplus/# when user_nameLogsinch  toTheDatabaseServer,thisUserCan connect toSql*Plus.

9. Can alter your current user's session to point at a different schema via ALTER SESSION statement

1 SQL>alterset= hr;

Ten. Assiging Default Permanent and temporary tablespaces

1 SQL>alteruseruser_namedefaulttemporary Tablespace temp_name;

modifying Password

1 SQL>alteruseruser_name by New_password;

Sql*plus Password command

1 SQL>user_name2foruser_name3 New Password

modifying Users

1 SQL>alteruseruser_name account lock; 2 SQL>alteruseruser_name on users;

Dropping Users. Before you drop a user,i recommend, that's you first lock the user. Locking the user prevents others from connecting to a locked database account.

1Sql> Alter User user_nameAccount lock;2Sql> SelectUsername,lock_date fromdba_users;3Sql> Alter User user_nameAccount unlock;4Sql> Drop User user_name;5Sql> Drop User user_name Cascade; #the Prior commend won ' t Work ifTheUserOwns any DatabaseObjects. UseTheCASCADEClause toRemove aUser  andHas its objects dropped.

Password strength. You can enforce a minimum standard of password complexity by assigning a password verification function to a user ' s Profil E. Oracle supplies a default password verification function that's create by running the following script as the SYS Sch Ema

1 SQL> @? /RDBMS/admin/utlpwdmg2 SQL>alterdefault  limit password_verify_function ora12c_verify_function; 3 SQL>alterdefaultnullfunction.

limiting Database Resource Usage

1 SQL>alterset resource_limit=true Scope=both;

Assigning Database System Privileges

1Sql> SelectDESTINCT privilege fromDba_sys_privs;2Sql> Grant CreateSession to user_name#minimally AUserNeedsCREATESESSION toBe able toConnect toTheDatabase.3Sql> RevokeCteateTable  from user_name; # toTake awayPrivileges.4Sql> Grant Create Table  to user_name  withAdminoption; #allows You to GrantA system privilege toAUser  andalso give thatUserThe ability toAdminister a privilege. You can does this withThe withADMINOPTIONClause.

Assigning Database Object Privileges

1Sql> Grant Insert,Update,Delete,Select  onObject_owner to user_name;2Sql> Grant Insert(Id,name,desc) ontable_name to user_name#grantsINSERT Privileges  toSpecific columnsinchTheTable.3Sql> Grant Insert  onObject_owner to user_name  with Grant option; #ifYou want aUserThat isBeing granted objectPrivileges  toBe able tosubsequentlyGrantThose same objectPrivileges  toOther users, Then  UseThe with GRANT OPTIONClause.

Grouping and assigning privileges

1 SQL>create  role role_name; 2 SQL>Grantselectanytable to role_name; 3 SQL>Granttouser_name;

Oracle Study note:users and Basic Security