Difference between revisions of "Users"

From SQLZOO
Jump to navigation Jump to search
(Blanked the page)
 
Line 1: Line 1:
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=sql class='tidy'>
</source>
<source lang=sql class='setup'></source>
<source lang='sql' class='def e-sqlite'>CREATE USER scott IDENTIFIED BY 'tiger'
</source>
<source lang='sql' class='def e-db2'>Use operating system.
</source>
<source lang='sql' class='def e-ingres'>create user scott with password = 'tiger'
grant all on database scott to scott
grant access on database gisq to scott
</source>
<source lang='sql' class='def e-access'>CREATE USER scott IDENTIFIED BY 'tiger'
</source>
<source lang='sql' class='def e-postgres'>CREATE USER scott WITH PASSWORD 'tiger';
CREATE DATABASE scott;
GRANT ALL ON DATABASE scott to scott;
</source>
<source lang='sql' class='def e-oracle'>CREATE USER scott IDENTIFIED BY tiger
  TEMPORARY TABLESPACE temp
  DEFAULT TABLESPACE users;
GRANT CONNECT TO scott;
GRANT RESOURCE TO scott
</source>
<source lang='sql' class='def e-sqlserver'>CREATE LOGIN scott
    WITH PASSWORD = 'tiger';
CREATE DATABASE scottsdb;
USE scottsdb;
CREATE USER scott FOR LOGIN scott;
</source>
<source lang='sql' class='def e-mysql'>--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;
</source>
<source lang='sql' class='def e-mimer'>CREATE IDENT scott AS USER USING 'tiger';
CREATE DATABANK userBank;
GRANT TABLE ON userBank TO scott;
</source>
<source lang='sql' class='def e-sybase'>CREATE USER scott IDENTIFIED BY 'tiger'</source>
<div class="ecomm e-db2" style="display: none">Create a user from the operating system. Have that user run the command:
. /home/db2inst1/sqllib/db2profile
as part of the .bashrc (or whatever). </div>
<div class="ecomm e-access" style="display: none">Access is a single user system. (Please email me if you know otherwise.)</div>
<div class="ecomm e-postgres" style="display: none">If scott is the name of a Unix account there is no problem - otherwise you must insert the following line before the default lines
local scott template1 password
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.
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]
Old Style:
sp_addlogin 'scott', 'tiger';
CREATE DATABASE scott;
USE scott;
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>
{{Users ref}}

Latest revision as of 13:03, 17 July 2012