{"id":10749,"date":"2019-07-28T16:15:55","date_gmt":"2019-07-28T16:15:55","guid":{"rendered":"https:\/\/ittutorial.org\/?p=10749"},"modified":"2019-07-28T16:51:30","modified_gmt":"2019-07-28T16:51:30","slug":"oracle-sql-tutorial-system-and-user-privileges","status":"publish","type":"post","link":"https:\/\/ittutorial.org\/oracle-sql-tutorial-system-and-user-privileges\/","title":{"rendered":"Oracle SQL Tutorial – System and User Privileges"},"content":{"rendered":"

Hi everyone, i want to show you schema subject, system and user privileges. it seems easy but required for dba<\/span><\/p>\n

SCHEMA :<\/h3>\n
\n
\n
\n
\n
\n

Schemas are called database objects aggregated together. <\/span>A schema contains tables, procedures, sequences,synonyms, index and database links. <\/span>Each of users has a schema, <\/span>each schema has the same name as the user name. <\/span>When a user is created, the schema with the same name is created.
\n<\/span><\/p>\n

While schemas logically hold users’ objects, their physical counterparts are tablespace<\/strong>.<\/span><\/span><\/p>\n

System Privileges:<\/h3>\n

The Database administrator has a multiple authority that users can grant in SQL language. A database administrator is the user with the highest authority in a database.<\/span><\/span><\/p>\n

    \n
  • Creating Nnew Users<\/span><\/span><\/li>\n
  • Droping Users<\/li>\n
  • Droping Tables<\/li>\n
  • Backup Tables<\/li>\n<\/ul>\n

    Creating Users:<\/h3>\n

    In SQL, the user is created on the database side.<\/span><\/span> A normal user does not have the authorized to create users on the system.<\/p>\n

    Basic syntax ;<\/p>\n

    CREATE USER \"USERNAME\" PROFILE \"DEFAULT\" IDENTIFIED BY PASSWORD DEFAULT TABLESPACE \"USERS\" TEMPORARY TABLESPACE \"TEMP\";<\/pre>\n
      \n
    • DEFAULT TABLESPACE “USERS”<\/strong> specify the user’s storage in tablespace.<\/span><\/span><\/li>\n
    • TEMPORARY TABLESPACE “TEMP”<\/strong> specify we specify the user’s temp tablespace.<\/span><\/span>
      \n<\/span><\/li>\n
    • PROFILE “DEFAULT”<\/strong> which profile will be included<\/span><\/span><\/li>\n<\/ul>\n
       <\/p>\n<\/div>\n
      Granting System Rights To the User:<\/h3>\n<\/div>\n<\/div>\n<\/div>\n<\/div>\n
      A user can be granted the following rights by the database administrator, for example
      \n<\/span><\/span><\/p>\n
        \n
      • CREATE SESSION<\/strong> Required to connect to the database.<\/span><\/span><\/li>\n
      • CREATE TABLE\u00a0 <\/strong>Required to create table on database.
        \n<\/span><\/span><\/li>\n
      • CREATE SEQUENCE<\/strong> Required for objects that generate ascending or descending sequential numeric values in the database.<\/span><\/span><\/li>\n
      • CREATE VIEW<\/strong> Allows us to extract data from multiple tables in the database as if it were a single table.<\/span><\/span><\/li>\n
      • CREATE PROCEDURE<\/strong> Required to create procedure in the database<\/span><\/span><\/li>\n<\/ul>\n
        Syntax,<\/p>\n
        GRANT\u00a0<\/strong> \"authority name<\/span>\"\u00a0 to<\/strong> \"user name<\/span>\";<\/span><\/span><\/pre>\n
         <\/p>\n
        Database Role Concept :<\/span><\/span><\/h3>\n
        What is the role<\/span><\/span> ?<\/span><\/h4>\n
        Role consist of authorize and aims to facilitate authority management and dynamic. We can assign the necessary privileges under a role to users. \u00a0<\/span><\/span>When the privileges under the role change, this affects all users with that role.<\/span><\/span><\/p>\n
        Role creation<\/span><\/span> :<\/h4>\n
        In SQL language, if you want to create a role, the following steps are followed<\/span><\/span>.<\/p>\n
        CREATE ROLE<\/strong> role_name<\/span><\/pre>\n
        If we want to give the role we created to a user<\/span><\/span> ,<\/p>\n
        GRANT <\/strong>role_name<\/span> to <\/strong>user_name<\/span><\/pre>\n
         <\/p>\n
        Object Authority :<\/span><\/span><\/h3>\n
        Object rights are privileges granted to users to perform a specific action on a particular table, view, or procedure.<\/span><\/span><\/p>\n
        \"\"<\/p>\n
        Syntax,<\/p>\n
        GRANT object_priv [(columns)]\r\n\r\nON\u00a0\u00a0 object\r\n\r\nTO\u00a0 {user | role | PUBLIC} \r\n\r\n\r\n[WITH GRANT OPTION];\r\n\r\n<\/pre>\n
        Granting object rights to other users<\/span><\/span> :<\/h3>\n
        In SQL, the rights of a user can be granted to different users if requested.<\/span><\/span><\/p>\n
        For example, If we want to give the query right in the Customers table to other users<\/span><\/span><\/p>\n
        SQL> GRANT SELECT ON CUSTOMERS TO Micheal<\/span><\/pre>\n
         <\/p>\n
        Public Parameter :<\/h3>\n
        All rights of the users table are used when you want to give other users in bulk.<\/p>\n
        For example Lazy’s products table, if we want other users to view, <\/span><\/span><\/p>\n
        SQL> GRANT SELECT ON Lazy.products<\/span> TO PUBLIC<\/p>\n
         <\/p>\n
        Revocation of object rights<\/span><\/span> :<\/h3>\n
        In sql, REVOKE will revoke rights granted to other users.\u00a0 Also, the rights defined with WITH GRANT OPTION are revoked.
        \n<\/span><\/span><\/p>\n
        Syntax,<\/p>\n
        REVOKE {privilege [, privilege...] | ALL} ON object FROM {user [,user] | role | PUBLIC } [CASCADE CONSTRAINTS];<\/pre>\n
        We have come to the end of this article, I hope everything is descriptive See you in the next article<\/span><\/span><\/p>\n
         <\/p>\n
         <\/p>\n","protected":false},"excerpt":{"rendered":"
        Hi everyone, i want to show you schema subject, system and user privileges. it seems easy but required for dba SCHEMA : Schemas are called database objects aggregated together. A schema contains tables, procedures, sequences,synonyms, index and database links. Each of users has a schema, each schema has the same name as the user name. …<\/p>\n","protected":false},"author":67,"featured_media":9920,"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,"_jetpack_memberships_contains_paid_content":false,"footnotes":""},"categories":[3019],"tags":[4936,4929,5597,4875,5600,5609,4883,4873,4869,4910,5603,5607,5608,5605,5604,1340,5611,4870,4895,5602,5610,4905,5599,5601,4935,4926,4915,4921,4919,4920,4918,4917,3192,4872,4916,4928,4876,4912,4881,4880,4890,4867,3062,4879,4891,4923,4924,4922,4925,4909,4911,4877,4896,4908,4882,4884,4897,4901,4900,4898,4902,4903,4899,4871,3015,3018,4868,4904,4906,4893,4930,4934,4914,4933,4885,4887,4913,3000,4931,4889,4927,4907,4888,4886,4892,4874,4878,4894,4932,5598,5606],"class_list":["post-10749","post","type-post","status-publish","format-standard","has-post-thumbnail","","category-oracle-sql","tag-case-insensetive","tag-create-view","tag-database-audit","tag-database-nedir","tag-database-schema","tag-grant","tag-insert-nedir","tag-kolay-sql","tag-mysql-sql","tag-or","tag-oracle-dba-title","tag-oracle-grant","tag-oracle-revoke","tag-oracle-role","tag-oracle-role-creating","tag-oracle-sql","tag-oracleprivileges-usage","tag-php-sql","tag-plsql","tag-publiv-parameter","tag-revoke","tag-right-outer-join","tag-schema","tag-schema-privileges","tag-selecting-multiple-colum","tag-sql-all-drop","tag-sql-all-statements","tag-sql-alter","tag-sql-alter-colum","tag-sql-alter-database","tag-sql-alter-statement","tag-sql-alter-table","tag-sql-and","tag-sql-baslangic-dersleri","tag-sql-creat-table","tag-sql-create-view","tag-sql-database","tag-sql-date-functions","tag-sql-dcl","tag-sql-ddl","tag-sql-delete","tag-sql-dersleri","tag-sql-distinct","tag-sql-dml","tag-sql-dml-nedir","tag-sql-drop-colum","tag-sql-drop-database","tag-sql-drop-statement","tag-sql-drop-table","tag-sql-filtering-data","tag-sql-functions","tag-sql-ifadeleri","tag-sql-ile-ne-yapilir","tag-sql-in-statement","tag-sql-insert","tag-sql-insert-nasil-yapilir","tag-sql-is-ilanlari","tag-sql-join-yapisi","tag-sql-kaynaklari","tag-sql-kullanmak","tag-sql-lef-join","tag-sql-left-outer-join","tag-sql-makaleleri","tag-sql-nasil-ogrenilir","tag-sql-nedir","tag-sql-ogrenme","tag-sql-ogrenmek","tag-sql-right-join","tag-sql-right-outer-join","tag-sql-select-islemi","tag-sql-set-auto-increment","tag-sql-sorting-results","tag-sql-subqueries","tag-sql-syntaxt-rule","tag-sql-tablo-olusturma","tag-sql-tablo-yapisi","tag-sql-to_char-function","tag-sql-tutorial","tag-sql-union-statement","tag-sql-update-islemi","tag-sql-view","tag-sql-where-statement","tag-sql-yapisi","tag-tablo-nedr","tag-tablodan-veri-cekmek","tag-temel-seviye-sql","tag-temel-seviyede-sql","tag-tsql","tag-union-table","tag-user-privileges","tag-what-is-role-in-sql"],"aioseo_notices":[],"jetpack_featured_media_url":"https:\/\/ittutorial.org\/wp-content\/uploads\/2019\/06\/sql-express.png","jetpack_sharing_enabled":true,"amp_enabled":true,"_links":{"self":[{"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/posts\/10749","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/users\/67"}],"replies":[{"embeddable":true,"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/comments?post=10749"}],"version-history":[{"count":3,"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/posts\/10749\/revisions"}],"predecessor-version":[{"id":10783,"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/posts\/10749\/revisions\/10783"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/media\/9920"}],"wp:attachment":[{"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/media?parent=10749"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/categories?post=10749"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/tags?post=10749"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}