Oracle Study Note : Users and Basic Security

1 SQL> select username from dba_users;

1 SQL> select ‘alter user ‘|| username || ‘ password expire account lock;’ from dba_users;

1 SQL> alter user scott account unlock;

1 SQL> alter user <username> identified by <new password>;

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 u
5 ,default_pwd$ d
6 where u.username=d.user_name(+);

1 SQL> select * from dba_users_with_defpwd;

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.

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.

1 SQL> alter session set current_schema = hr;

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

1 SQL> alter user user_name identified by new_password;

1 SQL> passw user_name
2 Changing password for user_name
3 New password:

1 SQL> alter user user_name account lock;
2 SQL> alter user user_name quota 500m on users;

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.

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

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

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.

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.

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