User Information in Oracle introduction.pptx

helpfull

1. SELECT sid , osuser , username , status , TO_CHAR ( logon_time , 'DAY HH24:MI:SS') LOGON_TIME , FLOOR ( last_call_et /3600)||':'|| FLOOR ( MOD ( last_call_et ,3600)/60)||':'|| MOD ( MOD ( last_call_et ,3600),60) IDLE , program FROM v_$session WHERE username IS NOT NULL ORDER BY last_call_et ; USER Information

2. USER Information

3. Total Active USER Information SELECT COUNT (*) "ACTIVE USERS" FROM v_$session WHERE username IS NOT NULL ;

4. Active USER Information

5. USER Memory SELECT username , VALUE || 'bytes' "Current UGA memory" FROM v_$session sess , v_$ sesstat sstat , v_$ statname sname WHERE sess . sid = sstat . sid AND sstat . statistic # = sname . statistic # AND sname . name = 'session uga memory';

6. USER Memory

7. SCOTT User Open & Lock SELECT username, account_status FROM DBA_USERS WHERE username = 'SCOTT' ;

8. Total User Open & Lock SELECT username, lock_date , account_status , default_tablespace FROM dba_users ;

9. User Sample List HR - Human resources, basic topics, supports Oracle Internet Directory OE - Order entry, intermediate topics, various data types PM - Product media, used for multimedia data types QS - Queued shipping, shows advanced queuing ( renamed IX in Oracle 10g) SH - Sales history, large amount of data, analytic processing SCOTT- Demo User (Only Deptt , and Sale)

10. User List

11. User Sample 11 G

12. User Sample 12c

13. Check SYS Info How to get SID name select instance_name from v$instance ; OR select global_name from global_name ; OR select name from v$database ; use the v$database table, you must have a sysdba privilege

14. Change Oracle passwords, expire, and lock unnecessary users There are many users on a full installation of Oracle, most of which you probably won't need. To lock an Oracle user account, you can use the following command : alter user username account lock ; To unlock the user, simply replace 'lock' with 'unlock .‘ Oracle comes with a few default accounts that should never be locked or dropped. These include: SYS, SYSTEM, SYSMAN

15. you do want always to change the password for these users. The default password for SYS is change_on_install . It is important that you follow these directions. To change the password for a user: alter user username identified by new_password ; Change Oracle passwords, expire, and lock unnecessary users

16. Setting the Oracle database user password lifetime SQL> select * from dba_profiles s where s.profile ='DEFAULT' and resource_name =' PASSWORD_LIFE_TIME '; PROFILE RESOURCE_NAME RESOURCE LIMIT ----------------------------------------------------------- ---------------------------- DEFAULT PASSWORD_LIFE_TIME PASSWORD 180dys The output shows that the lifetime of the user is 180 days. Set the password lifetime to Unlimited. SQL> ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME UNLIMITED;

17. User Lock and password expired ALTER USER user_name IDENTIFIED BY password ACCOUNT UNLOCK; alter user scott identified by tiger account lock password expire;

18. User Sample Scott

19. User Sample Scott ALTER USER scott ACCOUNT UNLOCK;

20. User Sample Scott alter user scott identified by tiger;

21. User Sample Scott

22. User Sample Scott

23. User Sample Scott Run this script: CREATE USER scott IDENTIFIED BY tiger ; scott is the user tiger is the password. Grant all access to user scott,run this script : GRANT ALL PRIVILEGES TO scott ; Extract the downloaded file in your system. Then Connect to Scott user as: CONNECT scott Password: tiger Then type this in your sql command prompt: @( extract file path)\ oracle.sql ; for example: @C:\Users\ABC\oracle\ oracle.sql ;

24. Show all table and table structure select * from tab ; SQL > DESC emp ;

25. view User privileges select * from SESSION_PRIVS

26. System User The SYSTEM account is one of a handful of predefined administrative accounts generated automatically when Oracle is installed. SYSTEM is capable of most administrative tasks, but the task we’re particularly interested in is account management.

27. System User no DBA

28. Create a new user

29. Create a new user

30. Create a new user

31. Create a new user

32. Create A New User CREATE USER zahid IDENTIFIED BY password123 DEFAULT TABLESPACE USERS TEMPORARY TABLESPACE temp QUOTA UNLIMITED ON USERS ACCOUNT UNLOCK ; SELECT username, lock_date , account_status FROM dba_users where username='ZAHID'; QUOTA 100M on users

33. Check New User

34. Drop User The DROP USER statement is used to remove a user from the Oracle database and remove all objects owned by that user.

35. Drop User If zahid did own objects in its schema, you would need to run the following DROP USER statement instead : DROP USER zahid CASCADE ; This DROP USER statement would remove the user zahid , drop all objects ( ie : tables and views) owned by zahid , and all referential integrity constraints on zahid objects would also be dropped.

36. View User privileges desc dba_sys_privs ;

37. Grant P rivileges grant create session, resource to zahid ; Check Grant P rivileges SELECT grantee, PRIVILEGE FROM dba_sys_privs WHERE grantee = 'ZAHID' ORDER BY privilege;

38. Check Grant P rivileges

39. Check Grant P rivileges SELECT grantee, COUNT(privilege) FROM dba_sys_privs GROUP BY grantee;

40. DBA Grant P rivileges

41. Check User how many tables select table_name FROM user_tables ;

42. Create a Table CREATE TABLE suppliers ( supplier_id number(10) NOT NULL, supplier_name varchar2(50) NOT NULL, address varchar2(50), city varchar2(50), state varchar2(25), zip_code varchar2(10) );

43. Create a Table

44. Data Control Language Statements are used to grant privileges on tables, views, sequences, synonyms, procedures to other users or roles. Data Control Language

45. System Privileges System Privileges are normally granted by a DBA to users. Examples of system privileges are CREATE SESSION, CREATE TABLE, CREATE USER etc . Object privileges Object privileges means privileges on objects such as tables, views, synonyms, procedure. These are granted by owner of the object. Data Control Language

46. Syntax for tables GRANT privilegeType ON [ TABLE ] { tableName | viewName } TO grantees Grant Privileges

47. Grant Privileges on Table You can grant users various privileges to tables. These privileges can be any combination of SELECT, INSERT, UPDATE, DELETE, REFERENCES, ALTER, INDEX, or ALL. Syntax The syntax for granting privileges on a table in Oracle is: GRANT privileges ON object TO user; Grant Privileges

48. Grant Privileges Grant on Table You can grant users various privileges to tables. These privileges can be any combination of SELECT, INSERT, UPDATE, DELETE, REFERENCES, ALTER, INDEX, or ALL.

49. Grant Privileges For example, if you wanted to grant SELECT, INSERT, UPDATE, and DELETE privileges on a table called suppliers to a user name zahid , you would run the following GRANT statement: GRANT select, insert, update, delete ON suppliers TO zahid ;

50. Grant Privileges You can also use the ALL keyword to indicate that you wish ALL permissions to be granted for a user named Zahid . For example: GRANT ALL ON suppliers TO zahid ;

51. Grant Privileges If you wanted to grant only SELECT access on your table to all users, you could grant the privileges to the public keyword . For example: GRANT SELECT ON suppliers TO public;

52. Grant Privileges If you wanted to grant only SELECT access on your table to all users, you could grant the privileges to the public keyword . For example: GRANT SELECT ON suppliers TO public;

53. Grant Privileges Suppose you want to grant update and insert privilege on only certain columns not on all the columns then include the column names in grant statement. For example you want to grant update privilege on ename column only and insert privilege on empno and ename columns only. grant update ( ename ), insert ( empno , ename )  on emp to zahid ;

54. Grant Privileges To grant select statement on emp table to zahid and to make zahid be able further pass on this privilege you have to give WITH GRANT OPTION clause in GRANT statement like this. grant select on emp to zahid with grant option;

55. REVOKE Privileges Use to revoke privileges already granted to other users. For example to revoke select, update, insert privilege you have granted to zahid then give the following statement . revoke select, update, insert on emp from zahid ; REVOKE DELETE ON suppliers FROM anderson ; REVOKE ALL ON suppliers FROM anderson ; REVOKE ALL ON suppliers FROM public;

56. REVOKE Privileges

57. ROLES A role is a group of Privileges. A role is very handy in managing privileges, Particularly in such situation when number of users should have the same set of privileges.

58. ROLES

59. ROLES

60. ROLES

61. ROLES

62. ROLES

63. ROLES For example you have four users : zahid , Scott, khalid , Ali in the database. To these users you want to grant select ,update privilege on emp table, select , delete privilege on dept table. To do this first create a role by giving the following statement create role clerks; Check how many roles SELECT * FROM USER_ROLE_PRIVS;

64. ROLES Then grant privileges to this role. grant select , update on Scott.emp to clerks; grant select , delete on Scott. dept to clerks ; Now grant this clerks role to users like this grant clerks to zahid , scott , khalid , ali ;

65. ROLES Suppose after one month you want grant delete on privilege on emp table all these users then just grant this privilege to clerks role and automatically all the users will have the privilege. grant delete on emp to clerks; If you want to take back update privilege on emp table from these users just take it back from clerks role . revoke update on emp from clerks; To Drop a role Drop role clerks;