Learning Objectives – after this chapter you will be able to:
- Create users and explain system privileges vs object privileges.
- Grant and revoke object privileges, including column-level and WITH GRANT OPTION.
- Access other users’ objects with owner prefixes, private and public synonyms.
- Create, grant, enable (SET ROLE) and drop roles, including password-protected roles.
- Use ALTER USER and common data dictionary views.
Users and Privileges
Every Oracle user needs a valid username and password. When a database is created Oracle creates SYS and SYSTEM, who can perform administrative operations (create/alter/drop users, roles, profiles, tablespaces). A new user is created by SYS or SYSTEM:
Creating a user who can log on
create user srikanth identified by oraclebook; connect srikanth/oraclebook -- ORA-01045: user SRIKANTH lacks CREATE SESSION privilege; logon denied grant connect, resource to srikanth; -- roles: log on, create and use objects
A privilege is a right to access an object or to execute a particular type of SQL command. A session starts at login and ends at logout; CONNECT moves from the current user to another user.
| System privilege | Object privilege | |
|---|---|---|
| Right to | Perform an operation in the system (CREATE SESSION, CREATE TABLE …) | Perform an operation on a specific object (table, view, sequence, procedure, function, package) |
| Usually granted | Through roles, by the DBA | By the object’s owner |
Object Privileges
The owner has complete access to his objects; other users have no access unless explicitly granted.
| Privilege | Allows | Object | Privileges available |
|---|---|---|---|
| ALTER | Change the definition | TABLE | SELECT, INSERT, DELETE, UPDATE, ALTER, INDEX, REFERENCES |
| DELETE / INSERT / UPDATE | Change rows | VIEW | SELECT, INSERT, UPDATE, DELETE |
| EXECUTE | Execute the object | SEQUENCE | SELECT, ALTER |
| INDEX | Create an index on it | PROCEDURE, FUNCTION, PACKAGE, OBJECT TYPE | EXECUTE |
| REFERENCES | Refer to it in a foreign key | ||
| SELECT | Select rows |
Granting, accessing and revoking
grant select on courses to praneeth; grant all on compbatches to praneeth; grant update(duration) on courses to praneeth; -- column-level (UPDATE/INSERT/REFERENCES) grant select on courses to praneeth with grant option; -- grantee may grant further -- as PRANEETH: select * from courses; -- ORA-00942: table or view does not exist select * from srikanth.courses; -- owner.tablename revoke select on courses from praneeth;
- ALL grants every applicable privilege; PUBLIC grants to every user.
- GRANT and REVOKE are DCL commands.
- CASCADE CONSTRAINTS drops foreign keys that were created using a REFERENCES privilege being revoked.
- Revoking is cascading: if A grants to B and B grants to C, revoking from B also removes it from C.
- Even with a privilege received from PRANEETH, ANURAG must still write
srikanth.coursesbecause SRIKANTH owns the table.
Synonyms
A synonym is an alias for a table or view that avoids writing the owner name. A private synonym is created in the account of the user who wants to use it; a public synonym is available to all users and needs the CREATE PUBLIC SYNONYM system privilege.
create synonym courses for srikanth.courses; -- in PRANEETH's account select * from courses; grant select on courses to public; -- by SRIKANTH create public synonym courses for srikanth.courses;
Roles
- A role is a collection of system and/or object privileges – granting privileges user by user and table by table is avoided.
- Roles are dynamic: privileges added to a role later are available to all its users.
- Creating a role needs the CREATE ROLE system privilege. Predefined roles include CONNECT, RESOURCE and DBA.
- A user can have many roles; only roles enabled (by default or with SET ROLE) give their privileges.
Creating and using a role
create role manager; -- or: identified by efficient grant select on courses to manager; grant select, update, insert, delete on batches to manager; grant manager to praneeth, anurag; set role manager; -- enable a granted role set role manager identified by efficient; -- password-protected role -- without password: ORA-01979: missing or invalid password for role 'MANAGER' drop role manager;
ALTER USER and Data Dictionary Views
alter user praneeth identified by tunu; -- change password alter user book default role all except manager; -- default roles
A user can change only his own password; other attributes are changed by the administrator. Data dictionary views (based on tables owned by SYS, created with the database) describe the database:
| View (synonym) | Contains |
|---|---|
| DICTIONARY (DICT) | Description of data dictionary views |
| USER_CATALOG (CAT) | Tables, views, synonyms and sequences owned by the user |
| USER_TABLES (TABS), USER_TAB_COLUMNS (COLS) | User’s tables / their columns |
| USER_OBJECTS (OBJ) | Objects owned by the user |
| USER_SEQUENCES (SEQ), USER_SYNONYMS (SYN), USER_INDEXES (IND) | Sequences, private synonyms, indexes |
| ALL_TAB_COLUMNS, ALL_OBJECTS | Columns / objects accessible to the user |
| USER_TAB_PRIVS, USER_COL_PRIVS | Table / column grants where the user is owner, grantor or grantee |
| USER_VIEWS, USER_ERRORS, USER_TRIGGERS, USER_TYPES, USER_CLUSTERS | View text, errors on stored objects, triggers, object types, clusters |
Key Takeaways
- System privileges control commands; object privileges control access to objects.
- WITH GRANT OPTION lets grantees re-grant; revoking cascades down the chain.
- Synonyms hide owner names; roles group privileges and must be enabled.
Chapter 13 Notes (PDF)
ch13.pdfKeys: ← → page · + − zoom · F full screen
Question / Answer (15 questions)
Moderate-level questions based on the Chapter 13 notes (PDF). Click a question to see the answer.
Answer : Oracle creates SYSTEM and SYS, which have privileges to perform administrative operations such as creating, altering and dropping users, roles, profiles and tablespaces. To create a new user (CREATE USER name IDENTIFIED BY password) you must log on as SYSTEM or SYS.
Answer : The new user lacks the CREATE SESSION system privilege, so logging on gives ORA-01045 (user lacks CREATE SESSION privilege; logon denied). Granting the CONNECT and RESOURCE roles (GRANT connect, resource TO srikanth) lets the user connect and create and use objects.
Answer : A privilege is a right to access an object or to execute a particular type of SQL command. A system privilege is a right to perform an operation in the system, e.g. CREATE SESSION or CREATE TABLE; it is generally granted through roles by DBAs. An object privilege is a right to perform an operation on a particular object such as a table, view, sequence, procedure, function or package.
Answer : ALTER (change the definition), DELETE (delete rows), EXECUTE (execute the object), INDEX (create an index on it), INSERT (insert rows), REFERENCES (refer to it in a foreign key constraint), SELECT (select rows) and UPDATE (update data).
Answer : TABLE: SELECT, INSERT, DELETE, UPDATE, ALTER, INDEX, REFERENCES. VIEW: SELECT, INSERT, UPDATE, DELETE. SEQUENCE: SELECT, ALTER. PROCEDURE, FUNCTION, PACKAGE and OBJECT TYPE: EXECUTE.
Answer : GRANT object_priv ON object TO user/role/PUBLIC grants a privilege. ALL grants all available object privileges on the object, PUBLIC grants the privilege to all users, and for UPDATE, INSERT and REFERENCES the privilege can be restricted to columns, e.g. GRANT UPDATE(duration) ON courses TO praneeth.
Answer : Oracle assumes the table belongs to the current user (PRANEETH); since it is not found in his account it gives ORA-00942: table or view does not exist. The table must be prefixed with the owner’s name: SELECT * FROM srikanth.courses, or a synonym must be used.
Answer : A synonym is an alias for a table or view that avoids giving the owner name. A private synonym is created in the account of the user who uses it (e.g. CREATE SYNONYM courses FOR srikanth.courses in PRANEETH’s account). A public synonym, created with CREATE PUBLIC SYNONYM (which needs the CREATE PUBLIC SYNONYM system privilege), is available to all users.
Answer : It allows the grantee to grant the privilege he received to other users. If SRIKANTH grants SELECT on COURSES to PRANEETH WITH GRANT OPTION, PRANEETH can grant it to ANURAG, who still accesses the table as srikanth.courses.
Answer : REVOKE privilege ON object FROM user/role/PUBLIC takes back privileges granted earlier; ALL revokes all of them. CASCADE CONSTRAINTS drops any referential (foreign key) constraint that was created using a REFERENCES privilege that is being revoked. GRANT and REVOKE are DCL commands.
Answer : When a privilege is revoked from a user who has granted it to others, all those grantees also lose the privilege. If A grants to B and B grants to C, both B and C lose the privilege when A revokes it from B.
Answer : A role is a collection of system and/or object privileges. It is dynamic – privileges added later are available to all its users; creating one needs the CREATE ROLE system privilege; Oracle has predefined roles such as CONNECT, RESOURCE and DBA; a user can be granted multiple roles; and only enabled roles provide privileges, though users can enable or disable their roles.
Answer : Create the role (CREATE ROLE manager), grant the required privileges to it (e.g. GRANT SELECT ON courses TO manager), and grant the role to users (GRANT manager TO praneeth). If the role is not enabled by default, the user enables it with SET ROLE manager. DROP ROLE removes it.
Answer : A password can be assigned with CREATE ROLE manager IDENTIFIED BY efficient (or later with ALTER ROLE). The user must give it when enabling the role: SET ROLE manager IDENTIFIED BY efficient; otherwise ORA-01979: missing or invalid password for role is raised.
Answer : ALTER USER changes a user’s password (ALTER USER praneeth IDENTIFIED BY tunu) and default roles (DEFAULT ROLE ALL EXCEPT manager); a user can change only his own password. Data dictionary views present information about the database in a simple form, are based on tables owned by SYS, and include USER_TABLES (TABS), USER_OBJECTS (OBJ), USER_CATALOG (CAT), USER_VIEWS and ALL_TAB_COLUMNS.
MCQ Test : Check Your Understanding (5 questions)
Select an option and press Check Answer, or answer all questions and press Submit Test to see your score. Each answer comes with an explanation.
Bibliography/References
The chapter notes PDF is the course study material and is credited to its author below. The explanations, questions and MCQs on this page are original study aids written for this course from the topics covered in that PDF. The other works are cited for verification and further reading. Oracle, SQL*Plus and PL/SQL are trademarks of Oracle Corporation; other names belong to their respective owners.
- Srikanth, P. (n.d.). Oracle For Beginners (chapter notes – the PDF shown on each chapter page). Srikanth Technologies. http://www.srikanthtechnologies.com/
- Oracle Corporation. (n.d.). Oracle Database Security Guide, 19c. https://docs.oracle.com/en/database/oracle/oracle-database/19/dbseg/
- Oracle Corporation. (n.d.). Oracle Database SQL Language Reference, 19c. https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/
- Oracle Corporation. (n.d.). Oracle Database Reference (initialization parameters and data dictionary views), 19c. https://docs.oracle.com/en/database/oracle/oracle-database/19/refrn/
- Oracle Corporation. (n.d.). Oracle Database Administrator’s Guide, 19c. https://docs.oracle.com/en/database/oracle/oracle-database/19/admin/