Difference between revisions of "Users"

From SQLZOO
Jump to navigation Jump to search
(Created page with "Create a new user. <div class='ht'> <div class=params>schema:gisq</div> <div> Give the new user permission to connect and to create their own tables etc. </div> <source lang=s...")
 
Line 59: Line 59:
to the file /var/lib/postgres/data/pg_hba.conf this allows postgres user scott to connect to database template1.</div>
to the file /var/lib/postgres/data/pg_hba.conf this allows postgres user scott to connect to database template1.</div>
<div class="ecomm e-oracle" style="display: none">You should specify the "tablespace" - if you don't it defaults to SYS which causes a whole world of pain.
<div class="ecomm e-oracle" style="display: none">You should specify the "tablespace" - if you don't it defaults to SYS which causes a whole world of pain.
The CONNECT and RESOURCE grants permit users to connect to the system and to create stuff.</div>
The CONNECT and RESOURCE grants permit users to connect to the system and to create stuff.</div>
<div class="ecomm e-sqlserver" style="display: none">[http://msdn.microsoft.com/en-us/library/ms173463.aspx CREATE USER]
<div class="ecomm e-sqlserver" style="display: none">[http://msdn.microsoft.com/en-us/library/ms173463.aspx CREATE USER]
Line 67: Line 66:
USE scott;
USE scott;
sp_changedbowner scott</div>
sp_changedbowner scott</div>
div class="ecomm e-mimer" style="display: none">An IDENT is a user. A DATABANK corresponds to a file where the data is actually stored. Users normally have a SCHEMA - this is created as the user is created. </div>
<div class="ecomm e-mimer" style="display: none">An IDENT is a user. A DATABANK corresponds to a file where the data is actually stored. Users normally have a SCHEMA - this is created as the user is created. </div>
</div>
</div>
{{Meta Data ref}}
{{Users ref}}

Revision as of 13:01, 17 July 2012

Create a new user.

schema:gisq

Give the new user permission to connect and to create their own tables etc.

CREATE USER scott IDENTIFIED BY 'tiger'
Use operating system.
create user scott with password = 'tiger'
grant all on database scott to scott
grant access on database gisq to scott
CREATE USER scott IDENTIFIED BY 'tiger'
CREATE USER scott WITH PASSWORD 'tiger';
CREATE DATABASE scott;
GRANT ALL ON DATABASE scott to scott;
CREATE USER scott IDENTIFIED BY tiger
  TEMPORARY TABLESPACE temp
  DEFAULT TABLESPACE users;
GRANT CONNECT TO scott;
GRANT RESOURCE TO scott
CREATE LOGIN scott 
    WITH PASSWORD = 'tiger';
CREATE DATABASE scottsdb;
USE scottsdb;
CREATE USER scott FOR LOGIN scott;
--The foolowing doesn't work since 5.0
--INSERT INTO mysql.user (user, host, password)
--VALUES ('scott', 'localhost', PASSWORD('tiger'));

CREATE DATABASE scott;

GRANT SELECT, INSERT,UPDATE,DELETE,CREATE,DROP
   ON scott.* TO scott@localhost
   IDENTIFIED BY 'tiger';

FLUSH PRIVILEGES;
CREATE IDENT scott AS USER USING 'tiger';
CREATE DATABANK userBank;
GRANT TABLE ON userBank TO scott;
CREATE USER scott IDENTIFIED BY 'tiger'