Oracle Study note:users and Basic Security

Source: Internet
Author: User

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

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.