{"id":56791,"date":"2024-10-10T17:43:52","date_gmt":"2024-10-10T17:43:52","guid":{"rendered":"https:\/\/dbtut.com\/?p=56791"},"modified":"2024-10-10T17:43:52","modified_gmt":"2024-10-10T17:43:52","slug":"roles-in-oracle-database","status":"publish","type":"post","link":"https:\/\/dbtut.com\/index.php\/2024\/10\/10\/roles-in-oracle-database\/","title":{"rendered":"Roles In Oracle Database"},"content":{"rendered":"

In today’s article, we will explain concepts such as defining roles in Oracle and which users have which roles.<\/p>\n

When you want to grant the privileges in the role to all users, they are granted as PUBLIC.<\/p>\n

CREATE ROLE HR_EMP;\r\nGRANT SELECT ON HR.EMPLOYEES TO HR_EMP;\r\nGRANT HR_EMP TO PUBLIC;<\/pre>\n
As a result of this process, all users can now select the hr.employees table.<\/p>\n
Role Definition<\/h3>\n
1. We create a role.<\/p>\n
CREATE ROLE GUN_ISIGI;\r\n<\/pre>\n
2. Rights are granted to the role.<\/p>\n
GRANT DELETE ON IPTVMWC.EPG TO GUN_ISIGI;\r\nGRANT DELETE ON IPTVMWC.PROGRAM TO GUN_ISIGI; \r\nGRANT DELETE ON IPTVMWC.DVBSI_EVENT TO GUN_ISIGI;\r\nGRANT DELETE ON WEBTELMWCORE.PROGRAM TO GUN_ISIGI;\r\nGRANT DELETE ON WEBTELMWCORE.EPG TO GUN_ISIGI;<\/pre>\n
3. The role is assigned to the user.<\/p>\n
GRANT GUN_ISIGI TO AITERZI;\r\n<\/pre>\n
NOTES:<\/strong> After the role is created, there is no need for COMMIT operation. Because COMMIT command is only required after DML operations. GRANT is a DCL operation.<\/p>\n
4. After the role is created, it is checked whether it has been created in the following places.<\/p>\n
a. From Sqlplus;<\/p>\n
select * from dba_roles order by 1;\r\n<\/pre>\n
\"\"<\/p>\n
b. From TOAD; it is checked whether the Role came from Users and whether the Object Grants are correct from Roles.<\/p>\n
\"\"<\/p>\n
\"\"<\/p>\n
Which Users Have Which Roles?<\/h3>\n
There are 2 ways to find out which users have which roles.<\/p>\n
1. SQL Plus and the other is TOAD.<\/p>\n
It can be seen in the interface in TOAD.<\/p>\n
It can be seen in SqlPlus with the following command.<\/p>\n
The columns of the table from which the information will be taken are seen.<\/p>\n
SQL> DESC DBA_ROLE_PRIVS;\r\nName                                      Null?    Type\r\n----------------------------------------- -------- ----------------------------\r\nGRANTEE                                            VARCHAR2(30)\r\nGRANTED_ROLE                              NOT NULL VARCHAR2(30)\r\nADMIN_OPTION                                       VARCHAR2(3)\r\nDEFAULT_ROLE<\/pre>\n
2. The following command is run and all users are seen.<\/p>\n
SQL> SELECT * FROM DBA_ROLE_PRIVS;<\/pre>\n
<\/div>\n
<\/path><\/svg><\/i> \"Loading\"<\/p>\n
<\/div>\n","protected":false},"excerpt":{"rendered":"
In today’s article, we will explain concepts such as defining roles in Oracle and which users have which roles. When you want to grant the privileges in the role to all users, they are granted as PUBLIC. CREATE ROLE HR_EMP; GRANT SELECT ON HR.EMPLOYEES TO HR_EMP; GRANT HR_EMP TO PUBLIC; As a result of this …<\/p>\n
<\/div>\n
<\/path><\/svg><\/i> \"Loading\"<\/p>\n
<\/div>\n","protected":false},"author":484,"featured_media":56797,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"om_disable_all_campaigns":false,"_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0,"_uf_show_specific_survey":0,"_uf_disable_surveys":false,"footnotes":""},"categories":[4],"tags":[],"class_list":["post-56791","post","type-post","status-publish","format-standard","has-post-thumbnail","","category-oracle"],"aioseo_notices":[],"a3_pvc":{"activated":true,"total_views":107,"today_views":0},"yoast_head":"\nRoles In Oracle Database - Database Tutorials<\/title>\n<meta name=\"description\" content=\"In today's article, we will explain concepts such as defining roles in Oracle and which users have which roles.\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/dbtut.com\/index.php\/2024\/10\/10\/roles-in-oracle-database\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Roles In Oracle Database - Database Tutorials\" \/>\n<meta property=\"og:description\" content=\"In today's article, we will explain concepts such as defining roles in Oracle and which users have which roles.\" \/>\n<meta property=\"og:url\" content=\"https:\/\/dbtut.com\/index.php\/2024\/10\/10\/roles-in-oracle-database\/\" \/>\n<meta property=\"og:site_name\" content=\"Database Tutorials\" \/>\n<meta property=\"article:published_time\" content=\"2024-10-10T17:43:52+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/dbtut.com\/wp-content\/uploads\/2024\/10\/Ekran-goruntusu-2024-10-10-203850.png\" \/>\n\t<meta property=\"og:image:width\" content=\"685\" \/>\n\t<meta property=\"og:image:height\" content=\"284\" \/>\n\t<meta property=\"og:image:type\" content=\"image\/png\" \/>\n<meta name=\"author\" content=\"Onur ARDAHANLI\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Onur ARDAHANLI\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"2 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\/\/dbtut.com\/index.php\/2024\/10\/10\/roles-in-oracle-database\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/dbtut.com\/index.php\/2024\/10\/10\/roles-in-oracle-database\/\"},\"author\":{\"name\":\"Onur ARDAHANLI\",\"@id\":\"https:\/\/dbtut.com\/#\/schema\/person\/7fcd466cd0d347ec64aaa48f18f780c6\"},\"headline\":\"Roles In Oracle Database\",\"datePublished\":\"2024-10-10T17:43:52+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/dbtut.com\/index.php\/2024\/10\/10\/roles-in-oracle-database\/\"},\"wordCount\":209,\"commentCount\":0,\"publisher\":{\"@id\":\"https:\/\/dbtut.com\/#organization\"},\"image\":{\"@id\":\"https:\/\/dbtut.com\/index.php\/2024\/10\/10\/roles-in-oracle-database\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/dbtut.com\/wp-content\/uploads\/2024\/10\/Ekran-goruntusu-2024-10-10-203850.png\",\"articleSection\":[\"ORACLE\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\/\/dbtut.com\/index.php\/2024\/10\/10\/roles-in-oracle-database\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/dbtut.com\/index.php\/2024\/10\/10\/roles-in-oracle-database\/\",\"url\":\"https:\/\/dbtut.com\/index.php\/2024\/10\/10\/roles-in-oracle-database\/\",\"name\":\"Roles In Oracle Database - Database Tutorials\",\"isPartOf\":{\"@id\":\"https:\/\/dbtut.com\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\/\/dbtut.com\/index.php\/2024\/10\/10\/roles-in-oracle-database\/#primaryimage\"},\"image\":{\"@id\":\"https:\/\/dbtut.com\/index.php\/2024\/10\/10\/roles-in-oracle-database\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/dbtut.com\/wp-content\/uploads\/2024\/10\/Ekran-goruntusu-2024-10-10-203850.png\",\"datePublished\":\"2024-10-10T17:43:52+00:00\",\"description\":\"In today's article, we will explain concepts such as defining roles in Oracle and which users have which roles.\",\"breadcrumb\":{\"@id\":\"https:\/\/dbtut.com\/index.php\/2024\/10\/10\/roles-in-oracle-database\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/dbtut.com\/index.php\/2024\/10\/10\/roles-in-oracle-database\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/dbtut.com\/index.php\/2024\/10\/10\/roles-in-oracle-database\/#primaryimage\",\"url\":\"https:\/\/dbtut.com\/wp-content\/uploads\/2024\/10\/Ekran-goruntusu-2024-10-10-203850.png\",\"contentUrl\":\"https:\/\/dbtut.com\/wp-content\/uploads\/2024\/10\/Ekran-goruntusu-2024-10-10-203850.png\",\"width\":685,\"height\":284},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/dbtut.com\/index.php\/2024\/10\/10\/roles-in-oracle-database\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/dbtut.com\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Roles In Oracle Database\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/dbtut.com\/#website\",\"url\":\"https:\/\/dbtut.com\/\",\"name\":\"Database Tutorials\",\"description\":\"MSSQL, Oracle, PostgreSQL, MySQL, MariaDB, DB2, Sybase, Teradata, Big Data, NOSQL, MongoDB, Couchbase, Cassandra, Windows, Linux\",\"publisher\":{\"@id\":\"https:\/\/dbtut.com\/#organization\"},\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/dbtut.com\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Organization\",\"@id\":\"https:\/\/dbtut.com\/#organization\",\"name\":\"dbtut\",\"url\":\"https:\/\/dbtut.com\/\",\"logo\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/dbtut.com\/#\/schema\/logo\/image\/\",\"url\":\"https:\/\/dbtut.com\/wp-content\/uploads\/2021\/02\/dbtutlogo.jpg\",\"contentUrl\":\"https:\/\/dbtut.com\/wp-content\/uploads\/2021\/02\/dbtutlogo.jpg\",\"width\":223,\"height\":36,\"caption\":\"dbtut\"},\"image\":{\"@id\":\"https:\/\/dbtut.com\/#\/schema\/logo\/image\/\"}},{\"@type\":\"Person\",\"@id\":\"https:\/\/dbtut.com\/#\/schema\/person\/7fcd466cd0d347ec64aaa48f18f780c6\",\"name\":\"Onur ARDAHANLI\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/dbtut.com\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/ecd20c3e1374ced4e1aefc82101cce4cd437be8fd957d1be3d106668b8a1b990?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/ecd20c3e1374ced4e1aefc82101cce4cd437be8fd957d1be3d106668b8a1b990?s=96&d=mm&r=g\",\"caption\":\"Onur ARDAHANLI\"},\"url\":\"https:\/\/dbtut.com\/index.php\/author\/onurardahanli\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Roles In Oracle Database - Database Tutorials","description":"In today's article, we will explain concepts such as defining roles in Oracle and which users have which roles.","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/dbtut.com\/index.php\/2024\/10\/10\/roles-in-oracle-database\/","og_locale":"en_US","og_type":"article","og_title":"Roles In Oracle Database - Database Tutorials","og_description":"In today's article, we will explain concepts such as defining roles in Oracle and which users have which roles.","og_url":"https:\/\/dbtut.com\/index.php\/2024\/10\/10\/roles-in-oracle-database\/","og_site_name":"Database Tutorials","article_published_time":"2024-10-10T17:43:52+00:00","og_image":[{"width":685,"height":284,"url":"https:\/\/dbtut.com\/wp-content\/uploads\/2024\/10\/Ekran-goruntusu-2024-10-10-203850.png","type":"image\/png"}],"author":"Onur ARDAHANLI","twitter_card":"summary_large_image","twitter_misc":{"Written by":"Onur ARDAHANLI","Est. reading time":"2 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/dbtut.com\/index.php\/2024\/10\/10\/roles-in-oracle-database\/#article","isPartOf":{"@id":"https:\/\/dbtut.com\/index.php\/2024\/10\/10\/roles-in-oracle-database\/"},"author":{"name":"Onur ARDAHANLI","@id":"https:\/\/dbtut.com\/#\/schema\/person\/7fcd466cd0d347ec64aaa48f18f780c6"},"headline":"Roles In Oracle Database","datePublished":"2024-10-10T17:43:52+00:00","mainEntityOfPage":{"@id":"https:\/\/dbtut.com\/index.php\/2024\/10\/10\/roles-in-oracle-database\/"},"wordCount":209,"commentCount":0,"publisher":{"@id":"https:\/\/dbtut.com\/#organization"},"image":{"@id":"https:\/\/dbtut.com\/index.php\/2024\/10\/10\/roles-in-oracle-database\/#primaryimage"},"thumbnailUrl":"https:\/\/dbtut.com\/wp-content\/uploads\/2024\/10\/Ekran-goruntusu-2024-10-10-203850.png","articleSection":["ORACLE"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/dbtut.com\/index.php\/2024\/10\/10\/roles-in-oracle-database\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/dbtut.com\/index.php\/2024\/10\/10\/roles-in-oracle-database\/","url":"https:\/\/dbtut.com\/index.php\/2024\/10\/10\/roles-in-oracle-database\/","name":"Roles In Oracle Database - Database Tutorials","isPartOf":{"@id":"https:\/\/dbtut.com\/#website"},"primaryImageOfPage":{"@id":"https:\/\/dbtut.com\/index.php\/2024\/10\/10\/roles-in-oracle-database\/#primaryimage"},"image":{"@id":"https:\/\/dbtut.com\/index.php\/2024\/10\/10\/roles-in-oracle-database\/#primaryimage"},"thumbnailUrl":"https:\/\/dbtut.com\/wp-content\/uploads\/2024\/10\/Ekran-goruntusu-2024-10-10-203850.png","datePublished":"2024-10-10T17:43:52+00:00","description":"In today's article, we will explain concepts such as defining roles in Oracle and which users have which roles.","breadcrumb":{"@id":"https:\/\/dbtut.com\/index.php\/2024\/10\/10\/roles-in-oracle-database\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/dbtut.com\/index.php\/2024\/10\/10\/roles-in-oracle-database\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/dbtut.com\/index.php\/2024\/10\/10\/roles-in-oracle-database\/#primaryimage","url":"https:\/\/dbtut.com\/wp-content\/uploads\/2024\/10\/Ekran-goruntusu-2024-10-10-203850.png","contentUrl":"https:\/\/dbtut.com\/wp-content\/uploads\/2024\/10\/Ekran-goruntusu-2024-10-10-203850.png","width":685,"height":284},{"@type":"BreadcrumbList","@id":"https:\/\/dbtut.com\/index.php\/2024\/10\/10\/roles-in-oracle-database\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/dbtut.com\/"},{"@type":"ListItem","position":2,"name":"Roles In Oracle Database"}]},{"@type":"WebSite","@id":"https:\/\/dbtut.com\/#website","url":"https:\/\/dbtut.com\/","name":"Database Tutorials","description":"MSSQL, Oracle, PostgreSQL, MySQL, MariaDB, DB2, Sybase, Teradata, Big Data, NOSQL, MongoDB, Couchbase, Cassandra, Windows, Linux","publisher":{"@id":"https:\/\/dbtut.com\/#organization"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/dbtut.com\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Organization","@id":"https:\/\/dbtut.com\/#organization","name":"dbtut","url":"https:\/\/dbtut.com\/","logo":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/dbtut.com\/#\/schema\/logo\/image\/","url":"https:\/\/dbtut.com\/wp-content\/uploads\/2021\/02\/dbtutlogo.jpg","contentUrl":"https:\/\/dbtut.com\/wp-content\/uploads\/2021\/02\/dbtutlogo.jpg","width":223,"height":36,"caption":"dbtut"},"image":{"@id":"https:\/\/dbtut.com\/#\/schema\/logo\/image\/"}},{"@type":"Person","@id":"https:\/\/dbtut.com\/#\/schema\/person\/7fcd466cd0d347ec64aaa48f18f780c6","name":"Onur ARDAHANLI","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/dbtut.com\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/ecd20c3e1374ced4e1aefc82101cce4cd437be8fd957d1be3d106668b8a1b990?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/ecd20c3e1374ced4e1aefc82101cce4cd437be8fd957d1be3d106668b8a1b990?s=96&d=mm&r=g","caption":"Onur ARDAHANLI"},"url":"https:\/\/dbtut.com\/index.php\/author\/onurardahanli\/"}]}},"amp_enabled":true,"_links":{"self":[{"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/posts\/56791","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/users\/484"}],"replies":[{"embeddable":true,"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/comments?post=56791"}],"version-history":[{"count":1,"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/posts\/56791\/revisions"}],"predecessor-version":[{"id":56798,"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/posts\/56791\/revisions\/56798"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/media\/56797"}],"wp:attachment":[{"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/media?parent=56791"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/categories?post=56791"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/tags?post=56791"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}