1. view the default user account
SQL> select username from dba_users;
2. lock all users and set their password to expired
SQL> select ‘alter user ‘|| username || ‘ password expire account lock;’ from dba_users;
3. A locked user can only be accessed by altering the user to an unlocked state
SQL> alter user scott account unlock;
4. As a DBA, you can change the password for a user
SQL> alter user <username> identified by <new password>;
5. Run this query to display users that have been created by another DBA versus those created by Oracle.For default users,there should be 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.
SQL> select distinct u.username
,case when d.user_name is null then ‘DBA created account’
else ‘Oracle created account’
from dba_users u
,default_pwd$ d
where u.username=d.user_name(+);
6. You can check the DBA_USERS_WITH_DEFPWD view to see whether any Oracle-created user accounts are still to the default password
SQL> select * from dba_users_with_defpwd;
7. Creating a User with Database Authentication
SQL> create user user_name identified by password
default tablespace users
temporaty tablespace temp
quote unlimited on users;
SQL> grant create session to user_name; #to make the user useful
SQL> grant create table to user_name; #to be able to create tables.
SQL> grant create table,create session to user_name identified by password; #you can also use the GRANT . . . IDENTIFIED BY statement to create a user.
8.Creating a User with OS Authentication
Oracle strongly recommends that you set the OS_AUTHENT_PREFIX parameter to a null string
SQL> alter system set os_authent_prefix=’’ scope=spfile;
SQL> create user user_name identified externally;
SQL> grant create session to user_name;
$ sqlplus / #when user_name logs in to the database server,this user can connect to SQL*Plus.
9. You can alter your current user’s session to point at a different schema via ALTER SESSION statement
SQL> alter session set current_schema = hr;
10. Assiging Default Permanent and Temporary Tablespaces
SQL> alter user user_name default tablespace tb_name temporary tablespace temp_name;
11. Modifying Password
SQL> alter user user_name identified by new_password;
12. SQL*PLUS password command
SQL> passw user_name
Changing password for user_name
New password:
13. Modifying Users
SQL> alter user user_name account lock;
SQL> alter user user_name quota 500m on users;
14. Dropping Users. Before you drop a user,I recommend that you first lock the user.Locking the user prevents others from connecting to a locked database account.
SQL> alter user user_name account lock;
SQL> select username,lock_date from dba_users;
SQL> alter user user_name account unlock;
SQL> drop user user_name;
SQL> drop user user_name cascade; #the prior commend won’t work if the user owns any database objects.Use the CASCADE clause to remove a user and have its objects dropped.
15. Password Strength. You can enforce a minimum standard of password complexity by assigning a password verification function to a user’s profile. Oracle supplies a default password verification function that you create by running the following script as the SYS schema
SQL> @?/rdbms/admin/utlpwdmg
SQL> alter profile default limit PASSWORD_VERIFY_FUNCTION ora12c_verify_function;
SQL> alter profile default limit PASSWORD_VERIFY_FUNCTION null; #disable the password function.
16. Limiting Database Resource Usage
SQL> alter system set resource_limit=true scope=both;
17. Assigning Database System Privileges
SQL> select destinct privilege from dba_sys_privs;
SQL> grant create session to user_name #minimally a user needs CREATE SESSION to be able to connect to the database.
SQL> revoke cteate table from user_name; #to take away privileges.
SQL> grant create table to user_name with admin option; #allows you to grant a system privilege to a user and also give that user the ability to administer a privilege.You can do this with the WITH ADMIN OPTION clause.
18. Assigning Database Object Privileges
SQL> grant insert,update,delete,select on object_owner to user_name;
SQL> grant insert(id,name,desc) on table_name to user_name #grants INSERT privileges to specific columns in the table.
SQL> grant insert on object_owner to user_name with grant option; #if you want a user that is being granted object privileges to be able to subsequently grant those same object privileges to other users,then use the WITH GRANT OPTION clause.
19. Grouping and Assigning Privileges
SQL> create role role_name;
SQL> grant select any table to role_name;
SQL> grant role_name to user_name;