8 Replies Latest reply: Apr 24, 2013 2:10 PM by sb92075 RSS

    Passwords and Users

    1002499
      I am working on some problems and would like some help.

      Here is the first problem:

      User SCOTT, who had the password TIGER, changed it to something more secure, but has since forgotten it. If possible, demonstrate what you as a DBA can do to give SCOTT access to his account.

      This is what I think will work for user SCOTT.
      SQL> conn / as sysdba
      Connected.
      SQL> alter user SCOTT identified by NewPassword;
      User altered.

      Here is the second problem.

      Other users need access to the tables belonging to IGGY. Create a role named CLERK that has SELECT privileges for tables PART, SUPPLIER, and QUOTE.
      Assign the role CLERK to user SCOTT. Connect as user SCOTT and SELECT * FROM QUOTE.

      CREATE ROLE CLERK
      GRANT SELECT, INSERT ON oe. Orders TO CLERK;
      GRANT r1 TO scott;
      GRANT SELECT ON oe.SUPPLIER TO scott;
      REVOKE SELECT ON oe. SUPPLIER FROM QUOTE.


      If anyone has any more advice, I would greatly appreciate it.
        • 1. Re: Passwords and Users
          SomeoneElse
          What do you need help with?
          • 2. Re: Passwords and Users
            1002499
            I would like to know if there are other ways of recovering user passwords and granting priviledges SELECT priviledges to user IGGY?
            • 3. Re: Passwords and Users
              SomeoneElse
              You can't recover passwords because Oracle doesn't store them anywhere.

              It stores only a hash code which (as far as we know) can never be un-hashed.
              • 4. Re: Passwords and Users
                sb92075
                999496 wrote:
                granting priviledges SELECT priviledges to user IGGY?
                can only be done issuing GRANT statements
                • 5. Re: Passwords and Users
                  Frank Kulash
                  Hi,
                  999496 wrote:
                  I am working on some problems and would like some help.

                  Here is the first problem:

                  User SCOTT, who had the password TIGER, changed it to something more secure, but has since forgotten it. If possible, demonstrate what you as a DBA can do to give SCOTT access to his account.

                  This is what I think will work for user SCOTT.
                  SQL> conn / as sysdba
                  Connected.
                  SQL> alter user SCOTT identified by NewPassword;
                  User altered.
                  That's right. Not even SYS can tell what scott's password is; all you can do is change it.
                  Here is the second problem.

                  Other users need access to the tables belonging to IGGY. Create a role named CLERK that has SELECT privileges for tables PART, SUPPLIER, and QUOTE.
                  Assign the role CLERK to user SCOTT. Connect as user SCOTT and SELECT * FROM QUOTE.

                  CREATE ROLE CLERK
                  You need a semicolon after the CREATE ROLE command.
                  GRANT SELECT, INSERT ON oe. Orders TO CLERK;
                  GRANT r1 TO scott;
                  GRANT SELECT ON oe.SUPPLIER TO scott;
                  REVOKE SELECT ON oe. SUPPLIER FROM QUOTE.
                  This doesn't match the requirements. Is the table owner iggy or oe? Is the new role clerk or r1?
                  Usually, roles are created so that you don't need to grant privielges directly to the users. Did you mean to grant priivileges on supplier to scott, or to the role?
                  If anyone has any more advice, I would greatly appreciate it.
                  If you have the table owner grant the privileges, then if you export the schema, you can export the privileges along with the tables.
                  I would like to know if there are other ways of recovering user passwords and granting priviledges SELECT priviledges to user IGGY?
                  Do you want to grant privileges to user iggy, or user scott, or role clerk, or role r1?
                  • 6. Re: Passwords and Users
                    1002499
                    Other users need access to the tables belonging to IGGY. Create a role named CLERK that has SELECT privileges for tables PART, SUPPLIER, and QUOTE.
                    Assign the role CLERK to user SCOTT. Connect as user SCOTT and SELECT * FROM QUOTE.


                    CREATE ROLE CLERK

                    You need a semicolon after the CREATE ROLE command.

                    GRANT SELECT, INSERT ON oe. Orders TO CLERK;
                    GRANT r1 TO scott;
                    GRANT SELECT ON oe.SUPPLIER TO scott;
                    REVOKE SELECT ON oe. SUPPLIER FROM QUOTE.
                    This doesn't match the requirements. Is the table owner iggy or oe? Is the new role clerk or r1?
                    Usually, roles are created so that you don't need to grant privielges directly to the users. Did you mean to grant priivileges on supplier to scott, or to the role?

                    the owner is iggy and the new role is clerk.
                    • 7. Re: Passwords and Users
                      1002499
                      Other users need access to the tables belonging to IGGY. Create a role named CLERK that has SELECT privileges for tables PART, SUPPLIER, and QUOTE.
                      Assign the role CLERK to user SCOTT. Connect as user SCOTT and SELECT * FROM QUOTE.

                      CREATE ROLE CLERK

                      You need a semicolon after the CREATE ROLE command.

                      GRANT SELECT, INSERT ON oe. Orders TO CLERK;
                      GRANT r1 TO scott;
                      GRANT SELECT ON oe.SUPPLIER TO scott;
                      REVOKE SELECT ON oe. SUPPLIER FROM QUOTE.
                      This doesn't match the requirements. Is the table owner iggy or oe? Is the new role clerk or r1?
                      Usually, roles are created so that you don't need to grant privielges directly to the users. Did you mean to grant priivileges on supplier to scott, or to the role?

                      the owner is iggy and the new role is clerk.

                      How about this giving others priveledges to the tables listed?

                      create role clerk;
                      grant select on PART, SUPPLIER, QUOTE TO PUBLIC;

                      Not sure how to assign CLERK to SCOTT?
                      • 8. Re: Passwords and Users
                        sb92075
                        999496 wrote:
                        Other users need access to the tables belonging to IGGY. Create a role named CLERK that has SELECT privileges for tables PART, SUPPLIER, and QUOTE.
                        Assign the role CLERK to user SCOTT. Connect as user SCOTT and SELECT * FROM QUOTE.

                        CREATE ROLE CLERK

                        You need a semicolon after the CREATE ROLE command.

                        GRANT SELECT, INSERT ON oe. Orders TO CLERK;
                        GRANT r1 TO scott;
                        GRANT SELECT ON oe.SUPPLIER TO scott;
                        REVOKE SELECT ON oe. SUPPLIER FROM QUOTE.
                        This doesn't match the requirements. Is the table owner iggy or oe? Is the new role clerk or r1?
                        Usually, roles are created so that you don't need to grant privielges directly to the users. Did you mean to grant priivileges on supplier to scott, or to the role?

                        the owner is iggy and the new role is clerk.

                        How about this giving others priveledges to the tables listed?

                        create role clerk;
                        grant select on PART, SUPPLIER, QUOTE TO PUBLIC;

                        Not sure how to assign CLERK to SCOTT?
                        GRANT CLERK TO SCOTT;