1 / 68

Managing users

Managing users. Hussam A. Halim Computer Science Department 2011. Objectives. After completing this lesson, you should be able to do the following: Differentiate system privileges from object privileges Grant privileges on tables View privileges in the data dictionary Grant roles

ulani
Download Presentation

Managing users

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Managing users Hussam A. Halim Computer Science Department 2011 Hussam A. Halim Computer Science Department 2011

  2. Objectives After completing this lesson, you should be able to do the following: • Differentiate system privileges from object privileges • Grant privileges on tables • View privileges in the data dictionary • Grant roles • Distinguish between privileges and roles Hussam A. Halim Computer Science Department 2011

  3. Database security is principally the role of the database administrator (DBA). • Database security is classified into two categories: -system security - data security.  • System security includes the mechanisms that control the access and use of the database at the system level. • System security is implemented by means of usernames and passwords. • System security includes restricting the amount of disk space available to a user and limiting the types of operations the user can perform. • Data security includes the mechanisms that control the access and the use of the database at the schema object level. Hussam A. Halim Computer Science Department 2011

  4. Privileges • Database security: – System security – Data security • System privileges: Gaining access to the database • Object privileges: Manipulating the content of the database objects. An object privilege is a privilege or right to perform a particular action on a specific table, view, sequence, or procedure. Each object has a particular set of grantable privileges. • Schemas: Collection of objects such as tables, views, and sequences Hussam A. Halim Computer Science Department 2011

  5. System Privileges • More than 100 privileges are available. • The database administrator has high-level system privileges for tasks such as: – Creating new users – Removing users – Removing tables – Backing up tables Hussam A. Halim Computer Science Department 2011

  6. Typical DBA Privileges Hussam A. Halim Computer Science Department 2011

  7. Creating Users • · Creating users and granting them specific privileges allows a DBA to maintain security.  • Create a new user by executing the CREATE USER statement. Initially, the user does not have any privileges.  • The privileges you grant the user determine which actions the user can perform in the database. The syntax for creating a new user: CREATE USER user IDENTIFIED BY password; Hussam A. Halim Computer Science Department 2011

  8. Ex: Create a user jamal by using the CREATE USER statement. Assign jamal the password alquds.  SQL> CREATE USER jamal IDENTIFIED BY alquds; Hussam A. Halim Computer Science Department 2011

  9. The DBA has created your user account and given you an initial password. You may want to change your password from time to time to secure data from unauthorized access. •   Use the ALTER USER statement to change your password. Enter the new password in the IDENTIFIED BY clause. ·  SQL> ALTER USER user IDENTIFIED BY password; • Ex: Change jamal’s password to nablus using the ALTER USER statement. jamal current password is alquds. SQL> ALTER USER jamal IDENTIFIED BY nablus;  Hussam A. Halim Computer Science Department 2011

  10. Grant Privileges  • Besides creating users, the DBA is responsible for granting system privileges. System privileges enable the user to perform a wide variety of operations.  • There are more than 80 different system privileges for an Oracle database, each with its own functionality.  • Nine commonly used system privileges are: CREATE USER DROP USER DROP ANY TABLE BACKUP ANY TABLE CREATE SESSION  CREATE TABLE CREATE SEQUENCE CREATE VIEW  CREATE PROCEDURE Hussam A. Halim Computer Science Department 2011

  11. System privileges are divided into two types: • DBA privileges • and user privileges. • DBA privileges typically belong only to the database administrator and are the most powerful.  • The most commonly used DBA privileges: CREATE USER, DROP USER, DROP ANY TABLE, and BACKUP ANY TABLE. • The CREATE USER privilege enables the grantee to create Oracle users. The user must be a DBA in order to have the CREATE USER privilege. • The DROP USER privilege enables the grantee to remove a user from the server. • The DROP ANY TABLE privilege enables the grantee to drop a table in any user's schema.  • The BACKUP ANY TABLE privilege enables the grantee to make a backup of any table by using the export utility. Hussam A. Halim Computer Science Department 2011

  12. Most users do not have DBA privileges. It is the job of the DBA to grant each individual user the privileges that are appropriate for that user's job. • The following are some of the more commonly granted user privileges: CREATE SESSION, CREATE TABLE, CREATE SEQUENCE, CREATE VIEW, and CREATE PROCEDURE. • The CREATE SESSION privilege enables the grantee to connect to the Oracle server. This is the most basic privilege of all. • The CREATE TABLE privilege enables the grantee to create a table. The table becomes a part of the user's schema. • The CREATE SEQUENCE privilege enables the grantee to create a sequence. The sequence becomes a part of the user's schema. • The CREATE VIEW privilege enables the grantee to create a view. The view becomes a part of the user's schema.  • The CREATE PROCEDURE privilege enables the grantee to create a stored procedure, function, or package. This privilege is essential for developers who write PL/SQL code. • A DBA executes a GRANT statement to allocate privileges to individual users.  • Once the DBA grants a privilege, the user can use that privilege immediately.  Hussam A. Halim Computer Science Department 2011

  13. Hussam A. Halim Computer Science Department 2011

  14. Ex: Grant the user sami the privileges to create a session and a table.  SQL> GRANT create session, create table TO sami;  Hussam A. Halim Computer Science Department 2011

  15. Creating Role • In a multiuser environment, a group of users working on the same project or having similar job responsibilities require similar privileges.  • Granting each user the same set of privileges is a tedious task. • The DBA can create a role that consists of a set of privileges, and then grant each user that role.  • Roles also are created for database applications. A user can have access to several roles, and several users can have the same role. Hussam A. Halim Computer Science Department 2011

  16. ROLEs are an Oracle feature designed for use by DBAs. • ROLEs are most useful when there are groups of users with similar access requirements • Use of ROLEs allows DBA to assign and/or alter permissions for a group of users in one go, instead of having to manage the permissions for each user separately. • When a new user is added, if it can be determined what role or roles the user has with respect to using the database, then the DBA can GRANT the appropriate ROLE(s) to the user. This will give the new user all the appropriate permissions to carry out the database activities of other users in the same roles. • Changes to the requirements of an identified group of users can be implemented by changing the privileges of the appropriate ROLE. Hussam A. Halim Computer Science Department 2011

  17. First, the DBA creates the role. Next, the DBA assigns privileges to the role. Finally, the DBA grants the role to users. Hussam A. Halim Computer Science Department 2011

  18. Role Characteristics • Granted to and revoked from users with the same commands used to grant and revoke system privileges • May be granted to any user or role, except to itself (even indirectly) • Can consist of both system and object privileges • May be enabled or disabled for each user granted the role • Can require a password to enable • Each role name must be unique among existing usernames and role names • Are not owned by anyone; are not in any schema • Have their descriptions stored in the data dictionary Hussam A. Halim Computer Science Department 2011

  19. The syntax to create a role is following: CREATE ROLE role; • The role parameter is the name of the role. • Ex: Create a role named manager using the CREATE ROLE statement.  • SQL> CREATE ROLE manager;  •  SQL> GRANT create session, create table, create view, create sequence,              create procedure TO manager;  • SQL> GRANT manager TO sami,jamal;  • · If the DBA adds a new privilege to the role manager, any users who have the manager role receive that privilege the next time they log on.  Hussam A. Halim Computer Science Department 2011

  20. We can group users into ROLES or GROUPS and grant or revoke privileges from the collection. • In Oracle we can do something like: CREATE ROLE purchasing_clerks; GRANT SELECT ON vendors TO purchasing_clerks; GRANT SELECT, INSERT, UPDATE, DELETE ON purchase_orders TO purchasing_clerks; GRANT SELECT, INSERT, UPDATE, DELETE ON po_items TO purchasing_clerks; • GRANT purchasing_clerks TO sami; • GRANT purchasing_clerks TO jamal; • GRANT purchasing_clerks TO hani; Hussam A. Halim Computer Science Department 2011

  21. example of setting up a new role to be applied to a basic user. • sql> create role basim;Statement processed. • sql> alter role basim identified by b00ser;Statement processed. • (Oops - I didn't really want to put a password on this role) • sql> drop role basim;Statement processed. • sql> create role basim;Statement processed. • sql> grant create session, create table, create view, create trigger to basim;Statement processed. Hussam A. Halim Computer Science Department 2011

  22. Query Role Information • Many of the data dictionary views that contain information on privileges granted tousers also contain information on privileges to roles. SQL> SELECT role, password_required FROM dba_roles; Hussam A. Halim Computer Science Department 2011

  23. sql> select * from dba_sys_privs where grantee =‘basim'; Hussam A. Halim Computer Science Department 2011

  24. Grant Object Privileges • Any object you create belongs to your schema. You automatically receive all the associated privileges for that database object.  • To give another user or role access to your database object, you must grant object privileges to that user or role.  • Object privileges vary from object to object. An object owner can grant any object privilege to any other user or role.  • example: the owner of a table can grant the UPDATE privilege to another user, allowing that user access to one or more columns of the table. • There are two ways to grant privileges on an object to other users on the system. 1) is to be the owner of the object.  2)is to be granted a privilege from the owner of an object with the WITH GRANT OPTION. This clause allows you to give other users on the system access to that object. • You execute the GRANT statement to allow another user access to your database objects.  Hussam A. Halim Computer Science Department 2011

  25. The syntax for granting object privileges GRANT privilege (columns) [ALL] [, privilege] ON object TO user|role|PUBLIC • The privilege parameter specifies the privilege to be granted. Multiple privileges are separated by commas. • The columns parameter in the GRANT statement specifies the columns in a table or view on which the privileges are granted.  • The column parameter applies only to the INSERT and UPDATE privileges. If omitted, then access applies to the entire table. • The ON clause followed by the object name specifies the object on which the privileges are granted. • The TO clause identifies the user or the role to which the privilege is granted. The PUBLIC keyword grants object privileges to all the users in the database domain. • The ALL keyword signifies all of the associated object privileges.  Hussam A. Halim Computer Science Department 2011

  26. Ex: Grant the user named Scott the privilege that enables him to select the DEPT table.  SQL> GRANT SELECT ON dept TO scott;  • · There are two ways to grant privileges on an object to other users on the system. • One is to be the owner of the object.  • The other way is to be been granted a privilege from the owner of an object with the WITH GRANT OPTION.  • A user who receives the privilege WITH GRANT OPTION can then grant that privilege to others on the system.  • if that user's privilege is revoked, the others on the system lose that privilege as well. • The WITH GRANT OPTION clause applies only to users. You cannot use WITH GRANT OPTION when granting a privilege to a role Hussam A. Halim Computer Science Department 2011

  27. Ex: Grant the user Jamal the privileges to query and add rows to the DEPT table. Also, allow Jamal to provide other users those same privileges.  SQL> GRANT SELECT, INSERT ON dept TO Jamal WITH GRANT  OPTION;  Hussam A. Halim Computer Science Department 2011

  28. ماذا لو أردنا أن نجعل hussam قادراً على منح الصلاحية لمستخدم آخر ؟؟ • SQL> grant select on scott.emp to hussam with grant option;Grant succeeded Hussam A. Halim Computer Science Department 2011

  29. ما الفرق بين grant option التي استخدمناها مع Object Level و Admin option التي استخدمناها مع System LeveL ؟ • تخيلوا السيناريو التالي : -1 قمنا بمنح صلاحية ما إلى المستخدم hussam . 2 -قام المستخدم hussam بمنح هذه الصلاحية إلى المستخدم hussam2 . -3 سحبنا الصلاحية من المستخدم hussam . السؤال : • هل سيتأثر المستخدم hussam2 بعملية السحب ؟؟ • الجواب : • إذا تم استخدام admin option فإنه لن يتأثر مطلقاً و بوسعه استخدام الصلاحية . • إذا تم إستخدام grant option فإن الصلاحية ستسحب منه أيضاً . الآن ملاحظة هامة جداً • إذا كانت إجرائياتكم تستفيد من الصلاحيات التي يتمتع بها المستخدم الذي يقوم بتنفيذها ، و تم سحب هذه الصلاحيات لاحقاً ، فإن جميع هذه الإجرائيات ستصبح معطلة و يلزم عملية Recompiles لها .. Hussam A. Halim Computer Science Department 2011

  30. Grants can also be done on specific columns in a table: GRANT SELECT ON products TO jones ; GRANT UPDATE ON products (price) TO jones ; Hussam A. Halim Computer Science Department 2011

  31. Hussam A. Halim Computer Science Department 2011

  32. Retrieve Privileges Information • Suppose, as a DBA, you receive a complaint from a user that an attempt to access the EMP table generates an error message: the table does not exist.  What can I do? • You must check the privileges granted to the user by querying the data dictionary.  • There are several dictionary views that contain information about users and the object privileges granted to them.  • You can access the data dictionary to confirm the privileges granted to you or the privileges granted by you.  Hussam A. Halim Computer Science Department 2011

  33. Hussam A. Halim Computer Science Department 2011

  34. REVOKE statement • Suppose you granted the user Jamal the object privileges INSERT and UPDATE on the DEPT table.  • You want to remove these privileges because Jamal has been transferred to a job where they are not required.  • You can use the REVOKE statement selectively to remove one or more object privileges from one or more users. The privileges you name are revoked from the users you specify.  • In addition, the privileges granted to other users through the WITH GRANT OPTION clause are also revoked when you execute the REVOKE statement. Hussam A. Halim Computer Science Department 2011

  35. · The syntax for the REVOKE statement is: REVOKE privilege [, privilege] ON object FROM user [CASCADE CONSTRAINTS]; • The CASCADE CONSTRAINTS keywords remove any referential integrity constraints defined for the object by means of the REFERENCES privilege.  • Ex: Remove the INSERT and UPDATE privileges on the DEPT table from the user jamal by using the REVOKE statement.  SQL> REVOKE INSERT, UPDATE ON dept FROM jamal;  Hussam A. Halim Computer Science Department 2011

  36. If Mr. Sami leaves the company, then we should: REVOKE INSERT, DELETE, UPDATE, SELECTON employees, departments FROM sami ; REVOKE INSERT, SELECT ON orders FROM sami; REVOKE SELECT ON products FROM sami ; • Many RDBMS have an ALL PRIVILEGES option that will revoke all of the privileges on an object from a subject: REVOKE ALL PRIVILEGESON employees, departments, orders, products FROM sami ; Hussam A. Halim Computer Science Department 2011

  37. Examples : • To gant any database user read-only access to the Course table : • GRANT SELECT ON Course TO PUBLIC • To allow course managers to enter marks into the Enrolment table for their course only : Define COMP2400Enrol (for 2003) as CREATE VIEW COMP2400Enrol(StudentId, CourseCode, Year, Mark, Grade) AS SELECT StudentId, CourseCode, Year, Mark, Grade FROM Enrolment WHERE CourseCode = 'COMP2400' AND Year = 2004 WITH CHECK OPTION • Then grant select on the view, and update of the Mark and Grade columns to the user COMP2400. GRANT SELECT, UPDATE(Mark, Grade) ON COMP2400Enrol TO COMP2400 /* Oracle user COMP2400 */ WITH GRANT OPTION The with grant option allows user comp2400 to grant the privileges to other users (e.g. read-only access to comp2400 tutors) : GRANT SELECT ON COMP2400Enrol TO OPS$U8100582, OPS$9606272 • To allow COMP2400Student to view StudentId and CourseCode, but not Mark and Grade, comp2400 must first create an appropriate view based on COMP2400Enrol, and then grant the SELECT permission on this new view. CREATE VIEW StudentCOMP2400Enrol AS SELECT StudentId, CourseCode FROM COMP2400Enrol GRANT SELECT ON StudentCOMP2400Enrol TO COMP2400Student /* Oracle role */ Hussam A. Halim Computer Science Department 2011

  38. Modifying Roles • ALTER ROLE hr_clerk IDENTIFIED EXTERNALLY; • ALTER ROLE hr_manager NOT IDENTIFIED; ALTER ROLE oe_clerk IDENTIFIED BY order; Hussam A. Halim Computer Science Department 2011

  39. Hussam A. Halim Computer Science Department 2011

  40. Passwords allow a role to be enabled only through an application. This technique is shown in the example above • The DBA has granted the user two roles, PAY_CLERK and PAY_CLERK_RO. • The PAY_CLERK has been granted all of the privileges needed to perform the payroll clerk function. • The PAY_CLERK_RO (RO for read only) has been granted only SELECT privileges on the tables required to perform the payroll clerk function. • The user can log in to SQL*Plus to perform queries, but cannot modify any of the data, because the PAY_CLERK is not a default role, and the user does not know the password for PAY_CLERK. • When the user logs on to the payroll application, it enables the PAY_CLERK by providing the password. It is coded in the program; the user is not prompted for it. Hussam A. Halim Computer Science Department 2011

  41. Hussam A. Halim Computer Science Department 2011

  42. Hussam A. Halim Computer Science Department 2011

  43. BY password provides the password used when enabling the role indicates that a user must be authorized by an external service (such as the operating system or a third-party service) before enabling the role indicates that no verification is required when enabling the role Hussam A. Halim Computer Science Department 2011

  44. Hussam A. Halim Computer Science Department 2011

  45. Hussam A. Halim Computer Science Department 2011

  46. How does one switch to another user in Oracle? Users normally use the "connect" statement to connect from one database user to another. However, DBAs can switch from one user to another without a password. SQL> select password from dba_users where username='SCOTT'; PASSWORD ----------------------------- F894844C34402B67 SQL> alter user scott identified by lion; User altered. SQL> connect scott/lion Connected. REM Do whatever you like... SQL> connect system/manager Connected. SQL> alter user scott identified by values 'F894844C34402B67'; User altered. SQL> connect scott/tiger Connected. Hussam A. Halim Computer Science Department 2011

  47. Who created all these users in my database?/ Can I drop this user? Oracle creates a number of default database users or schemas when a new database is created. Below are a few of them: • SYS/CHANGE_ON_INSTALL or INTERNALOracle Data Dictionary/ CatalogCreated by: ?/rdbms/admin/sql.bsq and various cat*.sql scriptsCan password be changed: Yes (Do so right after the database was created)Can user be dropped: NO • SYSTEM/MANAGERThe default DBA user name (please do not use SYS)Created by: ?/rdbms/admin/sql.bsqCan password be changed: Yes (Do so right after the database was created)Can user be dropped: NO • SCOTT/TIGER, ADAMS/WOOD, JONES/STEEL, CLARK/CLOTH and BLAKE/PAPER.Training/ demonstration users containing the popular EMP and DEPT tablesCreated by: ?/rdbms/admin/utlsampl.sqlCan password be changed: YesCan user be dropped: YES - Drop users cascade from all production environments Hussam A. Halim Computer Science Department 2011

  48. Oracle has a special tablespace, called SYS, which holds all the system information. SYS in total holds hundreds of tables. The list below gives a few of these table names. • USER_OBJECTS • TAB • USER_TABLES • USER_VIEWS • ALL_TABLES • USER_TAB_COLUMNS • USER_CONSTRAINTS • USER_TRIGGERS • USER_CATALOG • DBA_USERS Hussam A. Halim Computer Science Department 2011

  49. the DBA_USERS table holds user information. SQL> describe dba_users; Name Null? Type ----------------------------------------- -------- ------------ ---------------- USERNAME NOT NULL VARCHAR2(30) USER_ID NOT NULL NUMBER PASSWORD VARCHAR2(30) ACCOUNT_STATUS NOT NULL VARCHAR2(32) LOCK_DATE DATE EXPIRY_DATE DATE DEFAULT_TABLESPACE NOT NULL VARCHAR2(30) TEMPORARY_TABLESPACE NOT NULL VARCHAR2(30) CREATED NOT NULL DATE PROFILE NOT NULL VARCHAR2(30) INITIAL_RSRC_CONSUMER_GROUP VARCHAR2(30) EXTERNAL_NAME VARCHAR2(4000) Hussam A. Halim Computer Science Department 2011

  50. An example of a table holding the more internal features of the DBMS is the USER_CONSTRAINTS table. SQL> describe user_constraints; Name Null? Type ----------------------------------------- -------- ---------------------------- OWNER NOT NULL VARCHAR2(30) CONSTRAINT_NAME NOT NULL VARCHAR2(30) CONSTRAINT_TYPE VARCHAR2(1) TABLE_NAME NOT NULL VARCHAR2(30) ...... DEFERRABLE VARCHAR2(14) DEFERRED VARCHAR2(9) ...... LAST_CHANGE DATE ...... Oracle allows constraint checking to be put off till the end of a transaction, and this is known as DEFERRING. Hussam A. Halim Computer Science Department 2011

More Related