Oracle Study Note: Users and Basic Security, using lenote

Oracle Study Note: Users and Basic Security, using lenote

1. view the default user account

1 SQL> select username from dba_users;

2. lock all users and set their password to expired

1 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

1 SQL> alter user scott account unlock;

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

1 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 shoshould 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.

1 SQL> select distinct u.username

2 ,case when d.user_name is null then ‘DBA created account’

3 else ‘Oracle created account’

4 from dba_users u5 ,default_pwd$ d6 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

1 SQL> select * from dba_users_with_defpwd;

7. Creating a User with Database Authentication

1 SQL> create user user_name identified by password
2 default tablespace users
3 temporaty tablespace temp
4 quote unlimited on users;
5 SQL> grant create session to user_name; #to make the user useful
6 SQL> grant create table to user_name;  #to be able to create tables.
7 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 stronugly recommends that you set the OS _AUTHENT_PREFIX parameter to a null string

1 SQL> alter system set os_authent_prefix=’’ scope=spfile;
2 SQL> create user user_name identified externally;
3 SQL> grant create session to user_name;
4 $ 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

1 SQL> alter session set current_schema = hr;

10. Assiging Default Permanent and Temporary Tablespaces

1 SQL> alter user user_name default tablespace tb_name temporary tablespace temp_name;

11. Modifying Password

1 SQL> alter user user_name identified by new_password;

12. SQL * PLUS password command

1 SQL> passw user_name2 Changing password for user_name3 New password:

13. Modifying Users

1 SQL> alter user user_name account lock;2 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.

1 SQL> alter user user_name account lock;
2 SQL> select username,lock_date from dba_users;
3 SQL> alter user user_name account unlock;
4 SQL> drop user user_name;
5 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
2 SQL> alter profile default limit PASSWORD_VERIFY_FUNCTION ora12c_verify_function;
3 SQL> alter profile default limit PASSWORD_VERIFY_FUNCTION null; #disable the password function.

16. Limiting Database Resource Usage

1 SQL> alter system set resource_limit=true scope=both;

17. Assigning Database System Privileges

1 SQL> select destinct privilege from dba_sys_privs;
2 SQL> grant create session to user_name  #minimally a user needs CREATE SESSION to be able to connect to the database.
3 SQL> revoke cteate table from user_name;  #to take away privileges.
4 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

1 SQL> grant insert,update,delete,select on object_owner to user_name;
2 SQL> grant insert(id,name,desc) on table_name to user_name  #grants INSERT privileges to specific columns in the table.
3 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

1 SQL> create role role_name;
2 SQL> grant select any table to role_name;
3 SQL> grant role_name to user_name;